Ошибка процедуры Oracle PL/SQL при проверке IF-таблицы EXISTS в схеме

Question

Я использую SQL Developer Tool для создания процедуры, которая проверяет, существует ли таблица с именем TRANSPORT_PRODUCT, если она затем обрезает ее, если она не создает ее.

Сценарий 1: когда TRANSPORT_PRODUCT не существует в схеме, и я компилирую процедуру, я получаю следующую ошибку при компиляции процедуры

Error(44,2): PL/SQL: SQL Statement ignored
Error(44,14): PL/SQL: ORA-00942: table or view does not exist

Сценарий 2: Если я создаю таблицу TRANSPORT_PRODUCT, а затем создаю процедуру и запускаю, все выглядит нормально, за исключением того, что я использую следующий запрос внутри процедуры, чтобы проверить, существует ли таблица, где переменная TABLE_EXISTS в инициализирована равной 0,

select  COUNT(*) INTO   TABLE_EXISTS from    user_tables where   table_name= TABLE_NAME;
DBMS_OUTPUT.PUT_LINE('IF 0 THEN TABLE DOES NOT EXISTS ELSE TABLE EXISTS -'||TO_CHAR(TABLE_EXISTS,99));

Значение 48 хранится в TABLE_EXISTS .. это совершенно странно для меня:(

Сценарий 3: Если я создаю таблицу TRANSPORT_PRODUCT, а затем создаю процедуру, отбрасываю TRANSPORT_PRODUCT и запускаю процедуру, я получаю следующее сообщение, я не уверен, почему процедура зависит от таблицы!

Error report:
ORA-06550: line 1, column 7:
PLS-00905: object XAAL5.EXPORT_PRODUCT is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Моя процедура:

    CREATE OR REPLACE PROCEDURE EXPORT_PRODUCT IS
        TABLE_NAME      VARCHAR2(50) NULL;
        TABLE_EXISTS    INTEGER:=0;
        TRUNC_TABLE     VARCHAR2(50) NULL;
        CREATE_TABLE    VARCHAR2(1000) NULL;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Procedure Start Time :' || to_char(systimestamp,'DD-MM-YY HH:MM:SS'));

        TABLE_NAME:='TRANSPORT_PRODUCT';

        select  COUNT(*) INTO   TABLE_EXISTS from    user_tables where   table_name= TABLE_NAME;

        DBMS_OUTPUT.PUT_LINE('IF 0 THEN TABLE DOES NOT EXISTS ELSE TABLE EXISTS -'||TO_CHAR(TABLE_EXISTS,99));

        IF (TABLE_EXISTS<>0) THEN
            BEGIN
                DBMS_OUTPUT.PUT_LINE(TABLE_NAME||' TRANSPORT_PRODUCT TABLE EXISTS AND WILL BE TRUNCATED');
                TRUNC_TABLE:='truncate table '|| TABLE_NAME;
                --DBMS_OUTPUT.PUT_LINE(TRUNC_TABLE);
                execute immediate TRUNC_TABLE;
            END;
        ELSE
            BEGIN 
                DBMS_OUTPUT.PUT_LINE(TABLE_NAME ||' DOES NOT EXISTS AND WILL BE CREATED');
                CREATE_TABLE:=
                    'CREATE TABLE TRANSPORT_PRODUCT (
                        ROW_NUMBER      NUMBER,             
                        PRODUCTID       VARCHAR2(20), 
                        PRODUCTNAME     VARCHAR2(100), 
                        OWNER           VARCHAR2(20), 
                        DIVISIONID      VARCHAR2(20), 
                        MERCHLEVEL1     VARCHAR2(20), 
                        MERCHLEVEL2     VARCHAR2(20), 
                        MERCHLEVEL3     VARCHAR2(20), 
                        MERCHLEVEL4     VARCHAR2(20), 
                        STREAMID        VARCHAR2(20), 
                        SPECIFICATION   VARCHAR2(20), 
                        SSS             VARCHAR2(20))';
                execute immediate CREATE_TABLE;
            END;
        END IF;

     -- INSERT PRODUCT INTO TRANSPORT_PRODUCT
        INSERT INTO TRANSPORT_PRODUCT (ROW_NUMBER,PRODUCTID,PRODUCTNAME,OWNER, DIVISIONID,MERCHLEVEL1, MERCHLEVEL2, MERCHLEVEL3,MERCHLEVEL4,STREAMID,SPECIFICATION,SSS) 
        SELECT  ROWNUM,
                PRODUCTID,
                PRODUCTNAME,
                OWNER,
                DIVISIONID,
                MERCHLEVEL1,
                MERCHLEVEL2,
                MERCHLEVEL3,
                MERCHLEVEL4,
                STREAMID,
                SPECIFICATION,
                SSS
        FROM PRODUCT; 

        DBMS_OUTPUT.PUT_LINE('Procedure End Time :' || to_char(systimestamp,'DD-MM-YY HH:MM:SS'));

   exec buildfile('select * from TRANSPORT_PRODUCT where rownum<=500');

      commit;   
    END;

Answer 1

Я вижу, что проблема Сценария 2 заключается в том, что вы используете то же имя параметра процедуры TABLE_NAME, что и имя столбца USER_TABLES. И тогда запрос дает вам общее количество таблиц, которые у вас есть в схеме. TABLE_NAME = TABLE_NAME соответствует всегда.

И я думаю, что то же самое для вашей процедуры. Вы используете таблицу статически внутри процедуры.

С уважением.