| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Create Table in a Procedure?
I have a pl/sql stored procedure that uses dynamic sql to perform operations
like you need. I typically use it to analyze tables after a large number of
inserts in a conversion process that we have, but it could be used to create
tables also. The procedure can very dangerous as it allows you do things in
pl/sql that you would not normally do, so you need to be careful with how
you use it and who you allow to use it (you wouldn't want be dropping tables
and things like that).
Hope this helps,
Patrick Flahan
flahan_at_earthlink.net
BEGIN
PerformAction( 'CREATE TABLE my_Table(col1 NUMBER NOT NULL, col2
VARCHAR2)' );
END;
CREATE OR REPLACE PROCEDURE PerformAction(pcStatement IN VARCHAR2)
IS
/*
CHANGE HISTORY
Date Name Change ------------ -------------------- ------------------------------------------ ---- 01-Jan-1999 P Flahan Initial Creation ----------------------------------------------------------------------------
NOTES:
This is a generic utility to perform ddl and other operations in the
database.
Access to this procedure should be very restricted.
*/
curAction INTEGER := DBMS_SQL.OPEN_CURSOR;
nReturn INTEGER;
BEGIN
DBMS_SQL.PARSE(curAction,pcStatement,DBMS_SQL.NATIVE);
nReturn := DBMS_SQL.EXECUTE(curAction);
DBMS_SQL.CLOSE_CURSOR(curAction);
END PerformAction;
Alan Fusco <alan.fusco_at_gte.net> wrote in message
news:2WzQ2.1949$b73.104798_at_paloalto-snr1...
> Is it possible to create a table from a Procedure? If so, can someone
give
> me the correct syntax. I keep on getting an error message stating that a
> create was found. However, the statement works fine in Worksheet or SQL
Plus
> This is a really simple procedure to do in SQL Server.
>
>
> > >Received on Tue Apr 13 1999 - 09:56:25 CDT
 |
 |