[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Calling select from other table in different Schema

Posted on 2011-03-07
7
Medium Priority
?
760 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 23

Expert Comment

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

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

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

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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 77

Expert Comment

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

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

650 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