Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2639
  • Last Modified:

ORA-00942: table or view does not exist ORA-06512:

CREATE OR REPLACE PROCEDURE PROC_Test  IS

tmpVar NUMBER;
prev_owner     VARCHAR2(50);
current_owner     VARCHAR2(50);
sql_stmt                    VARCHAR2(200) ;


BEGIN

     --RETRIEVE THE VALUE FOR THE CURRENT OWNER
      BEGIN
             SELECT DISTINCT TABLE_OWNER
             INTO current_owner
             FROM USER_SYNONYMS
             WHERE SYNONYM_NAME = 'Test_Tab';
           
      EXCEPTION  
            WHEN OTHERS THEN
              tmpVar := 99;
      END;  
     


      IF current_owner = 'TEST12' THEN
         prev_owner := 'Owner1';
      ELSE
         prev_owner := 'Owner2';
      END IF;  
     
      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?

Thanks.

0
dayiku
Asked:
dayiku
1 Solution
 
hgmsaludCommented:
you need to write an when_no_data_found handler

   BEGIN
             SELECT DISTINCT TABLE_OWNER
             INTO current_owner
             FROM USER_SYNONYMS
             WHERE SYNONYM_NAME = 'Test_Tab';
           
      EXCEPTION  
           when no_data_found then
                --handler's code
            WHEN OTHERS THEN
              tmpVar := 99;
      END;  

i think the table you are searchig for does not have a synonym, does it is?

why does not search that table in dba_objects or in an other table of the data dictionary?
(see select * from dict)
0
 
dayikuAuthor Commented:
It gives me the same error message, table or view does not exist, but when i copy the generated query from the procedure it runs without errors.
0
 
hgmsaludCommented:
is the same user that execute the query in procedure and SQL/PLUS?

may be it is a privs problem
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
JacekMychaCommented:
When you create a stored procedure, you must have object privileges granted direclty, not through a role.
Grant select on the table directly to the user who creates a procedure.

JacekMycha
0
 
DLyallCommented:
If you still get a problem after checking the grants, I would output the contents of the string you are generating for the execute immediate, i.e

'INSERT INTO Temp_Table  SELECT Column1, "Literal1","Literal2" FROM' || current_owner || '.TableName1  MINUS   SELECT Column1, ''Literal1'',''Literal2''    FROM  ' || prev_owner  || '.TableName2'

to confirm that it is correct.  I would also cut and paste the "select" statement  and run it separately to confirm where the error is occuring.
0
 
haidersyedCommented:
connect to owner2
grant privileges using following command

begin
for i in (select table_name from user_tables) loop
execute immediate 'grant reference on '||i.table_name||' to xyz';
end loop;
end;

0
 
haidersyedCommented:
The problem is if you want to access other users table in procedure you need reference procedure
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now