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

From: <nicolas246_at_gmail.com>
Date: 27 Jul 2006 07:19:41 -0700
Message-ID: <1154009981.342066.166360@i42g2000cwa.googlegroups.com>

Hello,

I have the following PLSQL code which just doesnt work.

[PURPOSE/OBJECTIVE]

The purpose of this code is to get the names of tables starting with the prefix T_XML and store them on two tables: table_xml and table_col - I created for that session.
The script main_cleanup_xml is supposed to do it all together with no external help (or calls available)

[PROBLEM]

The problem comes when I try to compile the procedure BUT because the tables are not previously created the SQL PLUS prompt complains with the following message:

"Warning: Procedure created with compilation errors."

SQL> show errors
Errors for PROCEDURE MAIN_CLEANUP_XML:

LINE/COL ERROR

---

---

36/7 PL/SQL: SQL Statement ignored
36/19 PL/SQL: ORA-00942: table or view does not exist

[SUPPOSED WORKAROUND / DEBUG TESTING]

1.- I created the tables beforehand

2.- Compiled the procedure: main_cleanup_xml -> Which it did it SUCCESFULY 3.- Dropped the tables I created on point -1.-

4.- Executed the procedure and the following message ONCE again came up, therefore not letting me run the procedure as is.

SQL> execute main_cleanup_xml(1);
BEGIN main_cleanup_xml(1); END;

      *
ERROR at line 1:

ORA-06550: line 1, column 7:
PLS-00905: object DBO.MAIN_CLEANUP_XML is invalid
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

[WHAT I KNOW / HELP AVAILABLE]
a.- I've been told there are TEMPORARY tables that Oracle uses for that

session but how are they to be used (Am I applaying the same concept of

creating a couple of tables I only intend to use for this procedure? - which I believe I am)
b.- Cursors are OUT OF THE QUESTION as whatever concerns working with buffer in the long run can lead to many unidentified problems (which I really don't want)
c.- If any of you know of a better way (if simple way) of recreating the procedure, suggestions are completely welcome.

---------------------------------------------------------------------------­--------------------------------

----------------------------------- BEGIN PLSQL CODE
--------------------------------------
---------------------------------------------------------------------------­--------------------------------

CREATE OR REPLACE PROCEDURE main_cleanup_xml(exp_days NUMBER) AS

   v_nExists NUMBER;
BEGIN

• dbms_output.put_line('funciona');

   FOR i IN 0..2 LOOP

      BEGIN

• PREVIOUS - DO THE FOLLOWING TABLES EXIST:
• table_xml
• table_col
• IF SO, DROP THESE TABLES

      v_nExists := 0;
      SELECT 1 INTO v_nExists
          FROM dual WHERE EXISTS (SELECT * FROM all_tables WHERE
table_name = UPPER('table_xml'));
      IF v_nExists = 1 THEN
          EXECUTE IMMEDIATE 'DROP TABLE table_xml cascade constraints';



      END IF;


      v_nExists := 0;
      SELECT 1 INTO v_nExists
          FROM dual WHERE EXISTS (SELECT * FROM all_tables WHERE
table_name = UPPER('table_col'));
      IF v_nExists = 1 THEN
          EXECUTE IMMEDIATE 'DROP TABLE table_col cascade constraints';



      END IF;


      -- CREATE TEMPORARY TABLES:
      -- table_xml
      -- table_col


      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%';


      --INSERT INTO table_col(COL_DTTIME, COL_TNAME)
      --   SELECT LOWER(column_name), LOWER(table_name)
      --   FROM user_tab_columns, table_col
      --   WHERE table_name = table_col.COL_TNAME
      --   AND column_name = 'DEVICE_DTTIME';


      -- EXECUTE IMMEDIATE 'DROP TABLE table_xml cascade constraints';
      -- EXECUTE IMMEDIATE 'DROP TABLE table_col cascade constraints';


      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            BEGIN
               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))';
            END;
      END;

   END LOOP;
END;
/

---------------------------------------------------------------------------­--------------------------------

------------------------------------------- END CODE
---------------------------------------------
---------------------------------------------------------------------------­--------------------------------

NOTE: I have this same message on Group: Oracle PL/SQL Received on Thu Jul 27 2006 - 09:19:41 CDT