| 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: Can you CREATE Table through PL/SQL
Thomas Kyte wrote:
>
> A copy of this was sent to dolans_at_stripe.Colorado.EDU (Sean Dolan)
> (if that email address didn't require changing)
> On 26 Feb 99 20:10:09 GMT, you wrote:
>
> > I can not find in any documentation whether you can create a table "on-the-fly" through PL/SQL procedures. I have tried to implement an idea in some pseudo code but it doesn't compile. I'm creating a table based on a SELECT call that pulls certain data from many tables before the data is cleared out of the tables. Then the "saved" data resides in a table all by itself for an export. Any ideas?
> >
> >Thanks again,
> >Sean Dolan
> >sean_at_3si.com
>
> you can do it with dynamic sql:
>
> SQL> create or replace procedure execute_immediate( sql_stmt in varchar2 )
> 2 as
> 3 exec_cursor integer default dbms_sql.open_cursor;
> 4 rows_processed number default 0;
> 5 begin
> 6 dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
> 7 rows_processed := dbms_sql.execute(exec_cursor);
> 8 dbms_output.put_line( 'rows processed = ' || rows_processed );
> 9 dbms_sql.close_cursor( exec_cursor );
> 10 end;
> 11 /
>
> Procedure created.
>
> Just make sure you have the appropriate privs granted DIRECTLY to you, not via a
> role (such as DBA for example). Consider:
>
> SQL> exec execute_immediate( 'create table t( x int )' );
> begin execute_immediate( 'create table t( x int )' ); end;
>
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
> ORA-06512: at "SYS.DBMS_SQL", line 32
> ORA-06512: at "TKYTE.EXECUTE_IMMEDIATE", line 6
> ORA-06512: at line 1
>
> SQL> grant create table to tkyte;
> Grant succeeded.
>
> SQL> exec execute_immediate( 'create table t( x int )' );
> rows processed = 0
> PL/SQL procedure successfully completed.
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
"execute_immediate" ?
Aren't the 8.i people going to get you for plagiarism ?
hee hee
--
 |
 |