I have SELECT privilege granted to me on some tables, which i found through user_tab_privs view.
Now, I have written a stored procedure which is trying to insert into a table I have created with data from a query based on joins on the tables I have SELECT privilege on.
But, when I compile my procedure, it shows errors:
- PL/SQL: ORA-00942: table or view does not exist
What could be the issue? Have been breaking my head.
Can some one help me out please?
(
I found from Ask Tom &quot;Procedures, roles and grants&quot; that querying the view user_tab_privs with GRANTEE = <me> gives the list of privileges that have been granted directly to me.
If that is the case, I seem to be having DIRECT grants on the tables I have used in my procedure.
This is where my confusion is.)
**************************************CODE*********************
CREATE OR REPLACE PROCEDURE USAGE_MODULE_INSERT
IS
CURSOR C1
IS
SELECT CALL_KEY FROM DUMMY_DATE;
VDATE_KEY C1.CALL_KEY%TYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO VDATE_KEY;
EXIT WHEN C1%NOTFOUND;
INSERT INTO TABLE_USAGE_MODULE(VNUMBER, START_DATE_KEY, POSTPAID_ACC_FLAG, DES_NWK_NAME, ONOFF_KEY,ACTUAL_TIME, CHARGED_TIME, CHARGED_VOLUME, CALLS)
(
SELECT PDF.VNUMBER,
C_USG.START_DATE_KEY,
C_USG.POSTPAID_ACC_FLAG,
DES_NWK.DES_NWK_NAME,
ONNET_OFFNET.ONOFF_KEY,
SUM(C_USG.ACTUAL_TIME),
SUM(C_USG.CHARGED_TIME),
SUM(C_USG.CHARGED_VOLUME),
COUNT (CASE WHEN C_USG.TYPE_KEY = 15 THEN 1 ELSE 0 END)
FROM AM_WHM.CAL_USG C_USG
INNER JOIN AM_WHM.DES_NETWORK DES_NWK
ON DES_NWK.DES_NWK_KEY = C_USG.DES_NWK_KEY
INNER JOIN AM_WHM.D_L_NUMBER PDF
ON PDF.L_NUMBER_KEY = C_USG.L_NUMBER_KEY
WHERE C_USG.START_DATE_KEY = VDATE_KEY
GROUP BY PDF.VNUMBER,C_USG.START_DATE_KEY,C_USG.POSTPAID_ACC_FLAG, DES_NWK.DES_NWK_NAME, ONNET_OFFNET.ONOFF_KEY
);
END LOOP;
CLOSE C1;
END;
Note: Querying USER_TAB_PRIVS with GRANTEE = <me> says that I have SELECT previlege on all the tables i have used in my procedure.
Querying USER_SYS_PRIVS shows a single row as "UNLIMITED TABLESPACE"
******************************************************
Message was edited by: 3cd7ad85-b56c-4a9c-ae91-83be047aac2c
Included procedure code which gives compilation error