0

The stored procedure (in Oracle)

CREATE OR REPLACE PROCEDURE UPDATE_OBJ_COUNT
AS
BEGIN
    FOR v_record IN (SELECT OWNER, COUNT(*) NUM_OBJ FROM ALL_OBJECTS GROUP BY OWNER ORDER BY OWNER)
    LOOP
        DBMS_OUTPUT.PUT_LINE('***DEBUG***: Schema: '||v_record.OWNER||', Obj count:'||v_record.NUM_OBJ);
    END LOOP;
END;

gives the following result

***DEBUG***: Schema: ORACLE_OCM, Obj count:2
***DEBUG***: Schema: DBCONTROL, Obj count:27
***DEBUG***: Schema: PUBLIC, Obj count:22543
***DEBUG***: Schema: SCH_PRIV, Obj count:11
***DEBUG***: Schema: SYS, Obj count:22332
***DEBUG***: Schema: SYSTEM, Obj count:5
***DEBUG***: Schema: XDB, Obj count:181

If I run the same code as anonymous block

BEGIN
    FOR v_record IN (SELECT OWNER, COUNT(*) NUM_OBJ FROM ALL_OBJECTS GROUP BY OWNER ORDER BY OWNER)
    LOOP
        DBMS_OUTPUT.PUT_LINE('***DEBUG***: Schema: '||v_record.OWNER||', Obj count:'||v_record.NUM_OBJ);
    END LOOP;
END;

I get different result (which I guess is accurate)

***DEBUG***: Schema: APPQOSSYS, Obj count:3
***DEBUG***: Schema: DBSNMP, Obj count:55
***DEBUG***: Schema: ORACLE_OCM, Obj count:8
***DEBUG***: Schema: DBCONTROL, Obj count:27
***DEBUG***: Schema: OUTLN, Obj count:8
***DEBUG***: Schema: PUBLIC, Obj count:22543
***DEBUG***: Schema: SCH_PRIV, Obj count:43
***DEBUG***: Schema: SYS, Obj count:29321
***DEBUG***: Schema: SYSTEM, Obj count:501
***DEBUG***: Schema: TEST_SYAM_123, Obj count:3
***DEBUG***: Schema: XDB, Obj count:474

Why does it happen?

Why the stored procedure unable to locate some schemas?

How do we get the accurate values using stored procedure?

Syam
  • 575
  • 2
  • 8
  • 25

1 Answers1

1

This may be because privileges granted to a user by role are not available for the stored procedure.

More on this here: http://www.techrepublic.com/article/understanding-roles-in-oracle-stored-procedures/

Rene
  • 10,391
  • 5
  • 33
  • 46