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:[EMAIL PROTECTED]]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
