PL/SQL Developer - Creating dynamic SQL

Question

I'm using PL/SQL Developer. I'm trying to get query results to excel via vba. Since query is so long, i decided to create table with the query results and then simply get the table results with vba. In order to create table via excel i needed to create procedure with dynamic sql. So this is what i tried so far (even this simple example doesn't work):

create or replace procedure d_x IS
 str VARCHAR(81) = 'create table as select 1 as x from dual'
BEGIN
   EXECUTE IMMEDIATE str; 
END;

Procedure completes without error. But when i try to execute it to create table it throws an error.

Execute statement:

EXECUTE d_x;

The execute statement throws 'ORA-00900' Invalid sql statement error. I'm kinda new to pl sql so i couldn't find a solution to this. Any help would be appreciated, thanks.

Answer 1

Procedure you posted can't possibly execute without errors because it is invalid. When fixed, looks like this:

SQL> create or replace procedure d_x IS
  2   str VARCHAR(81) := 'create table test as select 1 as x from dual';
  3  BEGIN
  4     EXECUTE IMMEDIATE str;
  5  END;
  6  /

Procedure created.

In tools that support execute, you can run it as:

SQL> execute d_x

PL/SQL procedure successfully completed.

SQL> select * from test;

         X
----------
         1

"Correct" way - which works anywhere - is to enclose it (the procedure) into begin-end block:

SQL> drop table test;

Table dropped.

SQL> begin
  2    d_x;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>

I suggest you do that.