RE: Works interactively but not in procedure

Can you verify the you issued "GRANT SELECT ON SHIP_LOTS TO LOAD_MGR" while connected as the owner of SHIP_LOTS?  This should be enough to make it visible to LOAD_MGR via ALL_TAB_COLUMNS in a stored proc.  Also, SELECT ANY TABLE would do but I wouldn't go that route.

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

From: H elp_me [mailto:good_dba_at_hotmail.com]

Sent: Tuesday, October 30, 2001 3:38 PM

To: Aponte, Tony; ORACLE-L_at_fatcity.com

Subject: RE: Works interactively but not in procedure

Hello Tony,

I tried with 'authid current_user' and giving direct select to the user

'LOAD_MGR' but did not work..

Any other clue ?

Thanks for the time.

Nikunj

>From: "Aponte, Tony" <AponteT_at_hsn.net>

>To: <ORACLE-L_at_fatcity.com>

>CC: <good_dba_at_hotmail.com>

>Subject: RE: Works interactively but not in procedure

>Date: Tue, 30 Oct 2001 15:06:46 -0500

>

>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).

Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp Received on Tue Oct 30 2001 - 15:15:19 CST