codemonkey2480
asked on
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.
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;
What's the exact error message / ORA- code?
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.
When you run them through toad, my guess is you have permission through a role.
ASKER
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
-------- -------------------------- ---------- ----------
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
-------- --------------------------
ASKER
slightwv:
How can this issue be solved? Please let me know.
How can this issue be solved? Please let me know.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you slightwv.
no problem. glad to help