?
Solved

Calling select from other table in different Schema

Posted on 2011-03-07
7
Medium Priority
?
758 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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 take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

800 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