RE: Works interactively but not in procedure

This is a privilege issue.  The stored proc runs with the security of the connected user except for those inherited via roles.  Try adding the pragma for current user as follows to see if this is what you want:

CREATE OR REPLACE procedure LOAD_MGR.nik1_test

authid current_user

as ......

If not, then you will need to grant Select on the objects directly to the account.

HTH. Tony Aponte

-----Original Message-----

From: H elp_me [mailto:good_dba_at_hotmail.com]

Sent: Tuesday, October 30, 2001 1:25 PM

To: Multiple recipients of list ORACLE-L

Subject: Works interactively but not in procedure

Hi All,

Here is  the code.. Please let me know. where i went wrong..

SQL> set serveroutput on

SQL> CREATE OR REPLACE
  2  procedure LOAD_MGR.nik1_test

  3   as

  4      nik_COL1 VARCHAR2(32);

  5      nik_COL2 VARCHAR2(32);

  6          CURSOR c_col IS

  7  --        SELECT owner,table_name FROM all_TABles

  8  --        where rownum < 10;

  9         SELECT column_name,table_name FROM SYS.ALL_TAB_COLUMNS

10         WHERE table_name = 'SHIP_LOTS';

11      BEGIN 12      open c_col;

13      LOOP 14      fetch c_col into nik_col1,nik_col2;

15     EXIT WHEN c_col%NOTFOUND;

16     dbms_output.put_line ('V_COL  :   ' || nik_col2 || '.' || nik_col1);

17     END LOOP; 18     close c_col;

19     END; 20  /

Procedure created.

SQL> exec nik1_test

PL/SQL procedure successfully completed.

SQL> SELECT column_name,table_name FROM SYS.ALL_TAB_COLUMNS

  2  WHERE table_name = 'SHIP_LOTS';

COLUMN_NAME                    TABLE_NAME

------------------------------ ------------------------------

QUANTITY                       SHIP_LOTS

QUANTITY_UOM                   SHIP_LOTS

MIX_LOT_FLAG                   SHIP_LOTS

SAP_IM_PLACE                   SHIP_LOTS

SPSID                          SHIP_LOTS

LOT_NUM                        SHIP_LOTS

PARENT_LOT_NUM                 SHIP_LOTS

7 rows selected.

SQL>  declare

  2       nik_COL1 VARCHAR2(32);

  3       nik_COL2 VARCHAR2(32);

  4           CURSOR c_col IS

  5   --        SELECT owner,table_name FROM all_TABles

  6   --        where rownum < 10;

  7          SELECT column_name,table_name FROM SYS.ALL_TAB_COLUMNS

  8          WHERE table_name = 'SHIP_LOTS';

  9       BEGIN
10       open c_col;

11       LOOP 12       fetch c_col into nik_col1,nik_col2;

13      EXIT WHEN c_col%NOTFOUND;

14      dbms_output.put_line ('V_COL  :   ' || nik_col2 || '.' || nik_col1);

15      END LOOP; 16      close c_col;

17*     END; SQL> / V_COL  :   SHIP_LOTS.QUANTITY V_COL  :   SHIP_LOTS.QUANTITY_UOM V_COL  :   SHIP_LOTS.MIX_LOT_FLAG V_COL  :   SHIP_LOTS.SAP_IM_PLACE V_COL  :   SHIP_LOTS.SPSID V_COL  :   SHIP_LOTS.LOT_NUM V_COL  :   SHIP_LOTS.PARENT_LOT_NUM PL/SQL procedure successfully completed.

SQL> NOTE : I tried with ALL_TABLES and that works in procedure too... Only

ALL_TAB_COLUMNS fails.

All these output and execution are from one user, one session, in the

sequence shown above.

Thanks

Nikunj

Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: H elp_me



  INET: good_dba_at_hotmail.com



Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California        -- Public Internet access / Mailing Lists

--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).