Solved

Calling select from other table in different Schema

Posted on 2011-03-07
7
754 Views
Last Modified: 2012-05-11
Please find attached my Stored Procedure.  I am creating the stored proc in one schema and I get error message Table or View not found, but when I excute individual slect statements in Toad it works fine.

Whats wrong with this procedure? Please help.
CREATE OR REPLACE PROCEDURE spQES_Count_Report
IS
BEGIN 
	BEGIN
		INSERT INTO CSA_RPT.ADMIN_LOG_QES(TABLENAME,COUNT,CLIENTID,LOGSTAMP)
		Select 'QHOS Violation' as TableName,count(*)as QHOSViolationCount,clientid,current_timestamp  from csa_qes.qhosdriverviolationsbygroup group by clientid;
		
		COMMIT;
		
		INSERT INTO CSA_RPT.ADMIN_LOG_QES(TABLENAME,COUNT,CLIENTID,LOGSTAMP)
		Select 'Critical Events' as TableName, count(*) as CriticalEventsCount,clientid,current_timestamp  from csa_qes.spcerevent group by clientid;
		
		COMMIT;
		
		INSERT INTO CSA_RPT.ADMIN_LOG_QES(TABLENAME,COUNT,CLIENTID,LOGSTAMP)
		Select  'Scores' as TableName,count(*)  as ScoresCount,clientid,current_timestamp from csa_rpt.qes_driverfact where ttlqesscore>0 group by clientid;
		
		COMMIT;
		
		INSERT INTO CSA_RPT.ADMIN_LOG_QES(TABLENAME,COUNT,CLIENTID,LOGSTAMP)
		Select  'QHOS Violation Item' as TableName,count(*)  as QHOSViolationItemCount,clientid,current_timestamp from csa_qes.qhosdriverviolationitem group by clientid;
		
		COMMIT;
		
		INSERT INTO CSA_RPT.ADMIN_LOG_QES(TABLENAME,COUNT,CLIENTID,LOGSTAMP)
		Select  'CER Proximity' as TableName,count(*)  as CERProximityCount,clientid,current_timestamp from csa_qes.spcerproximity group by clientid;
		
		COMMIT;
		
		INSERT INTO CSA_RPT.ADMIN_LOG_QES(TABLENAME,COUNT,CLIENTID,LOGSTAMP)
		Select  'CER Position' as TableName,count(*)  as CERPositionCount,clientid,current_timestamp from csa_qes.spcerposition group by clientid;
		
		COMMIT;
		
		INSERT INTO CSA_RPT.ADMIN_LOG_QES(TABLENAME,COUNT,CLIENTID,LOGSTAMP)
		Select  'CER Offset' as TableName,count(*)  as CEROffsetCount,clientid,current_timestamp from csa_qes.spcereventdata group by clientid;
		
		COMMIT;
	
	END;
END spQES_Count_Report;

Open in new window

0
Comment
Question by:codemonkey2480
  • 3
  • 3
7 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35062059
What's the exact error message / ORA- code?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35062176
In a stored procedure you need to explicitly grant the permissions on the tables/objects.  These cannot be granted through a role.

When you run them through toad, my guess is you have permission through a role.
0
 

Author Comment

by:codemonkey2480
ID: 35062411
wdosanjos: Error Message below.
LINE/COL ERROR
-------- ----------------------------------------------
5/3      PL/SQL: SQL Statement ignored
6/111    PL/SQL: ORA-00942: table or view does not exis
10/3     PL/SQL: SQL Statement ignored
11/115   PL/SQL: ORA-00942: table or view does not exis
20/3     PL/SQL: SQL Statement ignored
21/122   PL/SQL: ORA-00942: table or view does not exis
25/3     PL/SQL: SQL Statement ignored
26/111   PL/SQL: ORA-00942: table or view does not exis
30/3     PL/SQL: SQL Statement ignored
31/109   PL/SQL: ORA-00942: table or view does not exis
35/3     PL/SQL: SQL Statement ignored

LINE/COL ERROR
-------- ----------------------------------------------
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:codemonkey2480
ID: 35062434
slightwv:
How can this issue be solved? Please let me know.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 35062476
While logged in as csa_qes:

Grant select on table_name to proc_owner;

Replacing table_name and proc_onwer with the appropriate values.

Do this for all the tables used in the procedure.
0
 

Author Closing Comment

by:codemonkey2480
ID: 35062728
Thank you slightwv.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35069103
no problem.  glad to help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question