Re: Can you CREATE Table through PL/SQL

Connor McDonald schrieb:
>
> 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

No, no, it's the other way round. Thomas pulished this yet at least two years ago.

Best regards
Matthias
--
grema_at_t-online.de

Es gibt nichts Neues mehr.
Alles, was man erfinden kann, ist schon erfunden worden. Charles H. Duell, Leiter des US Patentamtes bei seinem Rücktritt 1899 Received on Mon Mar 01 1999 - 02:30:29 CST