CREATE OR REPLACE PROCEDURE PROC_Test IS
sql_stmt VARCHAR2(200) ;
--RETRIEVE THE VALUE FOR THE CURRENT OWNER
SELECT DISTINCT TABLE_OWNER
WHERE SYNONYM_NAME = 'Test_Tab';
WHEN OTHERS THEN
tmpVar := 99;
IF current_owner = 'TEST12' THEN
prev_owner := 'Owner1';
prev_owner := 'Owner2';
EXECUTE IMMEDIATE 'INSERT INTO Temp_Table SELECT Column1, "Literal1","Literal2" FROM' || current_owner || '.TableName1 MINUS SELECT Column1, ''Literal1'',''Literal2'' FROM ' || prev_owner || '.TableName2';
I am trying to run the query above in a procedure and use the value retrieved to populate a table but i keep getting the following errors.
An exception occured in the anonymous block.
Check for syntax errors in the "Set Parameters" dialog
ORA-00942: table or view does not exist
ORA-06512: at "Schema.PROC_Test ", line 55
ORA-06512 at line 2
This is what i have done,
a. I have a Checked the table and it exists.
b. I tried to qualify the table name with the schema name, but that fails
c. I debugged the output for the 2 variables, current_owner and prev_owner and they both had values.
d. I printed the output for the whole query to make sure the values were there and that worked.
e. I copied the query using the DBMS_ouput and ran the query durectly against the server, that worked.
Can somebody tell me why it is that it will not run when i call it through ,my procedure?