Solved

Calling select from other table in different Schema

Posted on 2011-03-07
7
753 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

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.

Question has a verified solution.

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

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

776 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