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.

Answer 2

In PL/SQL Developer you can click on the procedure name and choose 'Test', which will generate a calling script as a Test window.

You can only use execute in a Command window, because it's a SQL*Plus command and not part of SQL or PL/SQL, and the Command window is a SQL*Plus emulator.

In a SQL window you could either use a complete PL/SQL block:

begin
    d_x;
end
/

or use the SQL call command:

call d_x();

Note that call() requires brackets regardless of whether or not the procedure has any parameters.

The / character is useful as a separator when using PLSQL blocks in a SQL window where you have more than one statement, otherwise PL/SQ Developer won't know where one ends and the next starts.

A Test window can only have one statement and only a single PL/SQL block or SQL statement is allowed, so there is no / character at the end.