fetch into object type [message #525419] |
Sun, 02 October 2011 23:24  |
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 #525422 is a reply to message #525419] |
Mon, 03 October 2011 00:25   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
getritesh wrote on Mon, 03 October 2011 06:24Please 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   |
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 #525571 is a reply to message #525476] |
Mon, 03 October 2011 16:08  |
 |
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>
|
|
|