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

Have you read the PL/SQL manual? This is a design issue, namely you should not be dropping and creating tables within the procedure. This looks like a SQL Server design. Oracle and SQL Server do not work the same way and applications cannot be designed the same way. Though SQL Server 2005 has made many changes to make the database work more like Oracle works.

Either use a permanent tables or global temporary tables to hold the data. In Oracle temporary table definitions are usually permanent. That is the temporay table is defined once and the definition is not dropped until no process uses the temporary table. Oracle creates a session version of the tempory table when data is inserted into it. See the SQL manual entry for CREATE TABLE.

HTH -- Mark D Powell -- Received on Thu Jul 27 2006 - 09:41:47 CDT