Oracle SQL - How to execute a query stored in a variable via command line-CodePudding

I tried in many ways without success to execute the query stored in a variable:

Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 
Connected as xxx@yyy

SQL> declare
  2  var v_SQL := 'SELECT * FROM DUAL';
  3  begin
  4  exec v_SQL;
  5  end;
  6  /

SQL> begin
  4  exec immediate 'SELECT * FROM DUAL';
  5  end;
  6  /

SQL> declare
  2  var v_SQL NVARCHAR := 'SELECT * FROM DUAL';
  3  begin
  4  exec immediate v_SQL;
  5  end;
  6  /

It throws generally the following error:

ORA-06550: line 2, column 11: PLS-00103: Symbol "NVARCHAR2" encountered when one of the following is expected:
:= . ( @ % ; not null range default character
The symbol ":=" has been replaced by "NVARCHAR2" to continue. ORA-06550: line 5, column 6: PLS-00103: Symbol "V_SQL" encountered when one of the following is expected:
:= . ( @ % ;

CodePudding user response：

You syntax should be -

declare
       v_SQL VARCHAR2(100) := 'SELECT * FROM DUAL';
begin
     execute immediate v_SQL;
end;
/

This will not throw the error but will not produce any result. You have to use SYS_REFCURSOR to get the result from a table in oracle. So your correct code should look alike -

declare
       v_SQL VARCHAR2(100) := 'SELECT * FROM DUAL';
       v_result SYS_REFCURSOR;
begin
     open v_result for v_SQL;;
end;

CodePudding user response：

Executing a query that returns data requires an extra step for putting the data into something. But since you're only executing DDL commands, you can use PL/SQL like this:

declare
    v_sql clob := 'drop table temp_table1';
begin
    execute immediate v_sql;
end;
/