Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> Re: CREATE TABLE in procedure?
A copy of this was sent to Roger G. <rgorden_at_my-deja.com> (if that email address didn't require changing) On Fri, 28 Jan 2000 22:19:23 GMT, you wrote:
>Alan;
>
>You cannot do any DDL (create table etc.) statements in a PL/SQL
>procedure, or any PL/SQL block.
it is quite easy to do ddl in plsql.
In Oracle8i, it is even easier. In Oracle8.0 and before it would look like
create or replace procedure execute_immediate( sql_stmt in varchar2 ) as
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
begin
dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native ); rows_processed := dbms_sql.execute(exec_cursor); dbms_sql.close_cursor( exec_cursor );end;
execute_immediate( 'create table t ( x int )' );
end;
/
In Oracle8i, it'll just be:
begin
EXECUTE IMMEDIATE 'create table t ( x int )'; end;
(execute immediate being a new feature in 8i).
If you get insufficient privs error, see http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html for the reason and the solution.
>There are 2 ways around this.
>
>1- Create a table in SQLPLUS using simple commands and delete the
> table when you're through
> -or-
>2- Declare a PL/SQL table datatype and use this in your procedure.
> (The advantage to this is that hte PL/SQL table lives only while the
> procedure is being run and takes up no permanent space on the
> database).
>
>I hope this helps.
>
>Roger
>
>
>
-- See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA Opinions are mine and do not necessarily reflect those of Oracle CorporationReceived on Sat Jan 29 2000 - 00:00:00 CST
![]() |
![]() |