Home » SQL & PL/SQL » SQL & PL/SQL » fetch into object type (Oracle 11g, 11.2.0.1.0, Windows server 2003)
fetch into object type [message #525419] Sun, 02 October 2011 23:24 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,

Please tell what ios wrong in the following code

create or replace type testobj as object(col1 number);
/

create or replace type tabtest as table of testobj;
/


create or replace procedure proc(a out tabtest) is
 cursor c is
 select empno
 from emp;
 i number := 1;
begin
 open c;
 loop
 fetch c into testobj(col1);
 tabtest(i) := testobj;
 exit when c%notfound;
i := i + 1;
 end loop;
end;
/

Regards,
Ritesh
Re: fetch into object type [message #525420 is a reply to message #525419] Sun, 02 October 2011 23:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Please tell what ios wrong in the following code
I give up.
I see no error.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
Re: fetch into object type [message #525422 is a reply to message #525419] Mon, 03 October 2011 00:25 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
getritesh wrote on Mon, 03 October 2011 06:24
Please tell what ios wrong in the following code

You should fetch into a variable with the same type as returned by a SELECT statement, not a weird operation on a type.

Although, the way you use these types reveals that you have no idea what they represent. Treating with objects and collections is described in PL/SQL Language Reference book. It is available with other Oracle documentation e.g. online on http://tahiti.oracle.com/

For 11gR2, the chapter about collection is placed here: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/composites.htm#CIHIEBJC
Please, study it before any other code attempts. It contains also some sample code examples; they may be helpful for you.
Re: fetch into object type [message #525469 is a reply to message #525419] Mon, 03 October 2011 07:08 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

In sql developer

create or replace type testobj as object(col1 number);
/

type testobj Compiled.

create or replace type tabtest as table of testobj;
/

type tabtest Compiled.

create or replace
procedure proc(a out tabtest) is
 cursor c is
 select empno
 from emp;
 i number := 1;
 cat NUMBER;
begin
 open c;
 loop
 fetch c into cat;
 testobj := cat;
 tabtest(i) := testobj;
 exit when c%notfound;
i := i + 1;
 end loop;
end;

Error(12,2): PLS-00321: expression 'TESTOBJ' is inappropriate as the left hand side of 
an assignment statement
Error(12,2): PL/SQL: Statement ignored
Error(13,2): PLS-00330: invalid use of type name or subtype name
Error(13,2): PL/SQL: Statement ignored


Please tell me what is wrong?


Regards,
Ritesh

[Updated on: Mon, 03 October 2011 08:18] by Moderator

Report message to a moderator

Re: fetch into object type [message #525476 is a reply to message #525469] Mon, 03 October 2011 07:24 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Once again: you cannot fetch/assign anything to a type. You can fetch/assign anything to a variable.

Maybe you should also know how to access object members. It is described e.g. here: http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/expressions011.htm#SQLRF52088

Quiz questions:
What is CAT?
What are TESTOBJ and TABTEST?
Did you bother to read the link I posted? Or at least the couse/exam materials for you homework/test assignment?
Re: fetch into object type [message #525571 is a reply to message #525476] Mon, 03 October 2011 16:08 Go to previous message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
I have provided minimal corrections to your procedure code and some code to test execution below, followed by a simpler procedure and execution. Notice that you need to initialize and extend your collection variable before you can populate it.

-- objects:
SCOTT@orcl_11gR2> create or replace type testobj as object
  2    (col1  number);
  3  /

Type created.

SCOTT@orcl_11gR2> create or replace type tabtest as table of testobj;
  2  /

Type created.


-- procedure with minimal corrections and test execution:
SCOTT@orcl_11gR2> create or replace procedure proc
  2    (a out tabtest)
  3  is
  4    cursor c is select empno from emp;
  5    cat	   number;
  6    i	   number  := 1;
  7  begin
  8    a := tabtest(); -- initialize collection variable
  9    open c;
 10    loop
 11  	 fetch c into cat;
 12  	 a.extend; -- extend collection variable
 13  	 a(i) := testobj (cat); -- populate collection variable
 14  	 exit when c%notfound;
 15  	 i := i + 1;
 16    end loop;
 17  end;
 18  /

Procedure created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> declare
  2    v_tabtest  tabtest;
  3  begin
  4    proc (v_tabtest);
  5    for i in 1 .. v_tabtest.count loop
  6  	 dbms_output.put_line (v_tabtest(i).col1);
  7    end loop;
  8  end;
  9  /
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
7934

PL/SQL procedure successfully completed.


-- simpler procedure and test execution:
SCOTT@orcl_11gR2> create or replace procedure proc
  2    (a out tabtest)
  3  is
  4  begin
  5    select testobj (empno) bulk collect into a from emp;
  6  end;
  7  /

Procedure created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> declare
  2    v_tabtest  tabtest;
  3  begin
  4    proc (v_tabtest);
  5    for i in 1 .. v_tabtest.count loop
  6  	 dbms_output.put_line (v_tabtest(i).col1);
  7    end loop;
  8  end;
  9  /
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2>

Previous Topic: Error in sql
Next Topic: PL/SQL select insert problem
Goto Forum:
  


Current Time: Mon Jun 23 01:55:20 CDT 2025