When i used the create statement with in the body of the plsql procedure i am getting an error PLS-00103 encountered the symbol create when expecting one of the following.... Can somebody tell me what's the reason and solution for this.
1
-
1Why do you want to create a table from PL/SQL? Usually this is not a desirable thing to do.Tony Andrews– Tony Andrews2011-05-03 10:18:31 +00:00Commented May 3, 2011 at 10:18
Add a comment
|
1 Answer
In PL/SQL you cannot use DDL statement directly. You can either
use:
EXECUTE IMMEDIATE 'CREATE ' || 'your command'as a string.use the SYS.DBMS_SQL system package.
curs := dbms_sql.open_cursor
dbms_sql.parse ( curs, 'create ...' )
dbms_sql.execute ( curs )
dbms_sql.close_cursor ( curs )use SYS.DBMS_DDL if you want to create a wrapped procedure.
4 Comments
tbone
and maybe change the privs to authid current_user
cbelsole
When I try to access the temp table I get an error saying that the table does not exist. Obviously that is correct because the table has not been created yet. It will be there at runtime, but is there any way to get the function to compile in the meantime?
Alain Pannetier
@Stopher87 I understand you have a piece of pl/sql that creates a table and adds some rows inside. The answer is "no". What you can do however is carry on using dynamic sql to populate your temp table. You just carry on using statement such as execute immediate 'insert into tmptab (col1, ... coln) values ( val1, ... valn)' etc etc...
cbelsole
I see. The better solution in this case then would probably be to just make the table and call it "something_temp_table" outside of the function and flush it at the beginning so it is clean to use. That way you can A, compile the code cleanly, and B, not have to worry about the table's existence.