Error Inserting records into a dynamically created table using PL/SQL 1

[bogman]

Hi,

Within the same PLSQL block I am dynamically creating a table and then attempting to BULK insert records into it from an index by table called periods. However, the insert fails to compile and the error returned is ORA-0942 indicating that the table nmltrx_0304 does not exist.However, if I run a script which dynamically creates the table and then does the inserts, respectively as two subsequent PLSQL blocks ( not nested ) then then whole thing compiles and executes successfully. This isn't a big issue but I'm very interested in finding out why this is happening. The main code fragments in the block are listed below along with the error message:

---
---
---
v_string := 'CREATE TABLE nmltrx_0304 AS
SELECT *
FROM nmltrx
WHERE 0 = 1 ' ;

---
---
---

FORALL i IN periods.FIRST .. periods.LAST
INSERT INTO nmltrx_0304 VALUES periods(i) ;

---
---

INSERT INTO nmltrx_0304 VALUES periods(i) ;
*
ERROR at line 30:
ORA-06550: line 30, column 21:
PL/SQL: ORA-00942: table or view does not exist

 

[SantaMufasa]

When PL/SQL interprets code, it confirms that the objects (e.g. tables) exist to which the code refers. The fact that your PL/SQL code block eventually creates the table to which your code refers is not good enough...the table must exist at the time PL/SQL interprest your code.

If, however, a "dummy" version of the table exists at interpret time, then your code can process dynamic SQL which DROPs the table, then finally executes your dynamic code that again CREATEs the table, then you should be fine.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[bogman]

Hi,

Thanks for your help. Sorry for taking so long to acknowledge it