[SOLVED] Table exists in stored procedure while used in select but not when Used in Insert statement

Table exists in stored procedure while used in select but not when Used in Insert statement

My stored procedure is like this:

create or replace procedure tpk.sp_Test_proc
IS
   err_code      NUMBER;
   err_msg       VARCHAR (500);
   v_tbl_cnt     NUMBER;
   v_tbl_valid   NUMBER;
Begin
SELECT COUNT(*) INTO v_tbl_cnt  FROM USER_TABLES 
  WHERE TABLE_NAME IN (UPPER('Tbl1'),UPPER('tbl2'),UPPER('tbl3'));

IF(v_tbl_cnt =3) THEN

EXECUTE IMMEDIATE 'TRUNCATE TABLE Tbl1';
    EXECUTE IMMEDIATE 'TRUNCATE TABLE Tbl2'; 
    EXECUTE IMMEDIATE 'TRUNCATE TABLE Tbl3';

    EXECUTE IMMEDIATE 'DROP TABLE Tbl1';
    EXECUTE IMMEDIATE 'DROP TABLE Tbl2'; 
    EXECUTE IMMEDIATE 'DROP TABLE Tbl3';

EXECUTE IMMEDIATE
    'CREATE global temporary TABLE tbl1
    ( Id Integer... )'

Insert into tbl1
Select * from another_schema.Dw_table /* In this line it throws error Table does not exist */
end if;
end;

I tired same table with store procedure only to fetch the data its working there but when I used in Insert statement it throws an error

PL/SQL: ORA-00942 table or view does not exist.

I am totally confused - what's wrong here?

Solution

Select * from another_schema.Dw_table

You don't have a privilege to select from that table. Even if you think you do (granted via a role), it won't work in stored procedures - you have to grant it directly to user you're connected to.

Besides, there's no point in truncating tables first, and dropping them next. Just drop them.

Furthermore, there's rarely need to create tables dynamically (the way you do it), especially global temporary tables. Create them once, use them many times. No dropping. No (re)creating them in PL/SQL.