[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Calling select from other table in different Schema

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
codemonkey2480
Asked:
codemonkey2480
  • 3
  • 3
1 Solution
 
wdosanjosCommented:
What's the exact error message / ORA- code?
0
 
slightwv (䄆 Netminder) Commented:
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
 
codemonkey2480Author Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
codemonkey2480Author Commented:
slightwv:
How can this issue be solved? Please let me know.
0
 
slightwv (䄆 Netminder) Commented:
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
 
codemonkey2480Author Commented:
Thank you slightwv.
0
 
slightwv (䄆 Netminder) Commented:
no problem.  glad to help
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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