Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Can you CREATE Table through PL/SQL

Re: Can you CREATE Table through PL/SQL

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Sat, 27 Feb 1999 16:55:14 +0800
Message-ID: <36D7B2F2.27B8@bhp.com.au>


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
--



Connor McDonald
BHP Information Technology
Perth, Western Australia
"Never wrestle a pig - you both get dirty and the pig likes it..." Received on Sat Feb 27 1999 - 02:55:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US