| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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).
-- 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).Received on Tue Oct 30 2001 - 14:57:06 CST
 |
 |