Works interactively but not in procedure

From: H elp_me <good_dba_at_hotmail.com>
Date: Tue, 30 Oct 2001 10:23:02 -0800
Message-ID: <F001.003B8784.20011030102524@fatcity.com>

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). Received on Tue Oct 30 2001 - 12:23:02 CST