| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: CREATE ... in a stored procedure??
On 8 May 1997 00:08:31 GMT, michael_rothwell_at_non-hp-usa-om46.om.hp.com (Michael R) wrote:
>I know how to use cursors to select information from tables in a stored
>procedures, but how would I be able to Create an object such as a table from a
>stored procedure??
>
>Thanks.
>
>Michael
>
I use a small procedure such as the one at the end of this post to execute arbitrary DDL in a stored procedure (or inserts/updates/deletes). You would now be able to code:
begin
...
n := execute_immediate( 'create table foo ( x int )' );
n := execute_immediate( 'insert into foo select 1 from dual' );
dbms_output.put_line( n || ' rows added to foo' );
commit;
....
end;
for example.
Note that roles are never enabled during the executoin of a procedure.
Try this first:
SQL> set role none;
SQL> create table foo ( x int );
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence dbms_sql won't be able to do it either. You would need to have the create table privelege granted directly to you. this only affects the OWNER of the procedure.
create or replace
function execute_immediate( stmt in varchar2 )
return number
as
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
begin
dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
rows_processed := dbms_sql.execute(exec_cursor);
dbms_sql.close_cursor( exec_cursor );
return rows_processed;
exception
when others then
if dbms_sql.is_open(exec_cursor) then
dbms_sql.close_cursor(exec_cursor);
end if;
raise;
end;
/
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |