Re: Unable to insert data on tables I create and drop for that session

nicolas246_at_gmail.com wrote:
> Hello,
>
> I have the following PLSQL code which just doesnt work.
>

...
>
> execute immediate 'create table table_xml(NAME_XML_TABLE
> varchar2(40))';
> execute immediate 'create table table_col(COL_DTTIME
> varchar2(40), COL_TNAME varchar2(40))';
>
>
> INSERT INTO table_xml(NAME_XML_TABLE)
> SELECT table_name
> FROM user_tables
> WHERE table_name LIKE 'T_XML%';
>

If you're creating tables with dynamic SQL then you'll need to insert data into them with dynamic SQL. This, of course, is a poor design concept (creating tables 'on the fly' within a stored procedure). The only way you'll get this dynamic scenario to function (i.e. compile) is with dynamic SQL since the tables don't exist until after the procedure starts.

I would seriously rethink this design and the 'need' for it. It's far more likely to break. If local data that 'disappears' when you're finished with it is your goal then use Mark's suggestion. That is far more scalable and reliable than that which you have written.

David Fitzjarrell Received on Thu Jul 27 2006 - 13:08:52 CDT