Home » SQL & PL/SQL » SQL & PL/SQL » Getting error 'invalid column name' When filling and opening ref cursor or oracle object. (9.0.1.1.1, WINDOWS xp)
Getting error 'invalid column name' When filling and opening ref cursor or oracle object. [message #362187] Mon, 01 December 2008 05:13 Go to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

Hi,
I am filling one collection type then trying to open ref cursor based on that
type.but i am getting error (during opening of ref cursor).

ERROR IS : ORA-00904: invalid column name
please suggest where i am wrong.

CREATE OR REPLACE
TYPE tt_rec_data as object 
(pid           NUMBER,
 tot_num1      NUMBER,
 tot_num2      NUMBER,
 final_descr   VARCHAR2(50),
 extract_date  date);
/


CREATE OR REPLACE
TYPE tt_tab_data IS TABLE OF tt_rec_data
/


CREATE OR REPLACE PROCEDURE test_rec
AS
   v_rec   tt_rec_data;
   v_tab   tt_tab_data   := tt_tab_data ();
   v_cnt   NUMBER        := 0;
   scur    sys_refcursor;
BEGIN
   FOR cc IN (SELECT *
                FROM tab)
   LOOP
      v_cnt := v_cnt + 1;
      v_tab.EXTEND;
      v_tab (v_cnt) :=
         tt_rec_data (v_cnt,
                      LENGTH (cc.tname),
                      LENGTH (cc.tabtype),
                      cc.tname,
                      SYSDATE
                     );
   END LOOP;

   DBMS_OUTPUT.put_line ('v_cnt=' || v_cnt);
   DBMS_OUTPUT.put_line ('tab count=' || v_tab.COUNT);

   OPEN scur FOR 
       'SELECT pid,
               tot_num1,
               tot_num2,
               final_descr,
               extract_date 
          FROM TABLE (CAST (v_tab AS tt_tab_data))';

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/
Re: Getting error 'invalid column name' When filling and opening ref cursor or oracle object. [message #362191 is a reply to message #362187] Mon, 01 December 2008 05:25 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
OPEN scur FOR 
       'SELECT pid,
               tot_num1,
               tot_num2,
               final_descr,
               extract_date 
          FROM TABLE (CAST (v_tab AS tt_tab_data))';


Does quote requires?
Re: Getting error 'invalid column name' When filling and opening ref cursor or oracle object. [message #362192 is a reply to message #362187] Mon, 01 December 2008 05:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Get rid of that WHEN OTHERS for starters.

I don't get your error. I get an Inalid identifier : V_tab when I try to execute the procedure, but that goes away when you stop trying to use dynamic SQL, and just open the cursor for the query you've specified (ie remove the quotes from the OPEN scur FOR.

Then everything works fine.
Re: Getting error 'invalid column name' When filling and opening ref cursor or oracle object. [message #362202 is a reply to message #362192] Mon, 01 December 2008 05:33 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

ok.. i removed exception block and qultes in open statement. again compiled and
executed procedure. Now, i am getting below error.

ERROR at line 1:
ORA-21700: object does not exist or is marked for delete
ORA-06512: at "GOPAL.TEST_REC", line 25
ORA-06512: at line 1


probably u people are working on some diffewrent version. My version is

16:59:21 GOPAL@GMORCL>select * from v$version
17:03:01   2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE    9.0.1.1.1       Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production

17:03:02 GOPAL@GMORCL>
Re: Getting error 'invalid column name' When filling and opening ref cursor or oracle object. [message #362210 is a reply to message #362202] Mon, 01 December 2008 05:50 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

I am sorry previous error is because of compilation. when i again compile
and executed. it sucessfully opens ref cursor.When i opened that ref
cursor, into record type variable, it saying that --

ERROR at line 1:
ORA-06504: PL/SQL: Return types of Result Set variables or query do not
match
ORA-06512: at line 27


declare
  v_rec   tt_rec_data;
   v_tab   tt_tab_data   := tt_tab_data ();
   v_cnt   NUMBER        := 0;
   scur    sys_refcursor;
v_final_descr varchar2(100);
BEGIN
   FOR cc IN (SELECT *
                FROM tab)
   LOOP
      v_cnt := v_cnt + 1;
      v_tab.EXTEND;
      v_tab (v_cnt) :=
         tt_rec_data (v_cnt,
                      LENGTH (cc.tname),
                      LENGTH (cc.tabtype),
                      cc.tname,
                      SYSDATE
                     );
   END LOOP;
   DBMS_OUTPUT.put_line ('v_cnt=' || v_cnt);
   DBMS_OUTPUT.put_line ('tab count=' || v_tab.COUNT);
   OPEN scur FOR 
       SELECT pid,
              tot_num1, 
              tot_num2, 
              final_descr, 
              extract_date
          FROM TABLE (CAST (v_tab AS tt_tab_data));
loop
fetch scur into v_rec;
DBMS_OUTPUT.put_line (v_rec.final_descr);
EXIT WHEN scur%notfound;
end loop;
END;


please suggest.
Re: Getting error 'invalid column name' When filling and opening ref cursor or oracle object. [message #362223 is a reply to message #362187] Mon, 01 December 2008 06:24 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Firstly, thank you for the testcase.

Concerning the error: you fetch some columns (just the list of them) into the object - these are not the same types. The simplest way to use same data types is to use object constructor in the query, something like
       SELECT tt_rec_data( pid,
                           tot_num1,
                           tot_num2,
                           final_descr,
                           extract_date )
          FROM TABLE (CAST (v_tab AS tt_tab_data));
Re: Getting error 'invalid column name' When filling and opening ref cursor or oracle object. [message #362225 is a reply to message #362210] Mon, 01 December 2008 06:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid the most obsolete database I can find is 9.2.0.8.0, and it works perfectly on that.
Re: Getting error 'invalid column name' When filling and opening ref cursor or oracle object. [message #362232 is a reply to message #362225] Mon, 01 December 2008 06:47 Go to previous message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

very thanks to JRowbottom, flyboy, Olivia.

Yes, we can implement thru this way also. i implemented this with for loop.
for c_Cursor_variable in (SELECT * 
          FROM TABLE (CAST (v_tab AS tt_tab_data)))
loop
DBMS_OUTPUT.put_line (cc.final_descr||to_char(cc.tot_num2));
end loop;



Thanks for your help.
Previous Topic: rows & columns dynamic [merged many]
Next Topic: Regular Expression
Goto Forum:
  


Current Time: Wed Dec 07 06:38:48 CST 2016

Total time taken to generate the page: 0.12832 seconds