0

In SQL Server I can do something like:

DECLARE @VAR VARCHAR(100);

SELECT @VAR = SELECT * FROM SYS.DATABASES

EXECUTE(@VAR)

How can I execute a string that I've created inside a variable in Oracle?

This is the query:

DECLARE BUSCACOLUNA  VARCHAR(2000);
BEGIN

 BUSCACOLUNA:= 'SELECT  '' SELECT '' || LTRIM(listagg ( '' , '' || ''"'' || T1.COLUMN_NAME || ''"'' || '' AS '' ||''"''|| T2.DESCRICAO ||''"''  ) within group  ( order by t1.column_name ),'' , '') || '' FROM TABELA_ENTRADA ''
  FROM ALL_TAB_COLUMNS T1
    INNER JOIN TABBASE T2
      ON T1.COLUMN_NAME=T2.NO_COL_TABBASE
    WHERE T1.TABLE_NAME=''TABELA_ENTRADA''
    AND T2.CD_CONTEUDO_ARQUIVO= ''X''';


 END;

The result of that dynamic SQL is something like:

  SELECT C1 AS B1, C2 AS B2...FROM TABLE

And I would like to execute this SELECT by executing the VARIABLE.

I tried with a loop, with EXECUTE IMMEDIATE but the query only returns:

anonymous block completed

So I can understand it's working. If it was an UPDATE or INSERT, it would work, But I would like the return of that.

  • What do you want to do with the results of the SELECT statement? You'd either need to consume the data in your PL/SQL block or you'd need to return it to the calling application. If you want to return it to the calling application, you could define a stored procedure that has an OUT parameter of type SYS_REFCURSOR and use the dynamic SQL statement to open the cursor. But I'm not sure if that's what you're after. – Justin Cave Jul 1 at 21:42
  • I just want to show it on the screen, like a normal result set, as I do with SQL Server. – Racer SQL Jul 1 at 22:01
  • 1
    That's not as easy as it is in SQL Server. What front-end are you using where you want the data to be printed out? – Justin Cave Jul 1 at 22:18
-1

In Oracle you use (for dynamic SQL):

BEGIN
    EXECUTE IMMEDIATE

Or

BEGIN
    DBMS_SQL.EXECUTE()

Like the examples here: https://www.guru99.com/dynamic-sql-pl-sql.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.