Home » SQL & PL/SQL » SQL & PL/SQL » an error
an error [message #250527] Tue, 10 July 2007 06:23 Go to next message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member

hi
i have a table like this--

16:06:01 SQL> select *from ab;

EMPNO EMPNAME JOBNAME
---------- -------------------- ----------
0001 Roger Programer
0002 Miriam Developer
0001 Roger Analyst
0003 Roman Encoder
0002 Miriam Analyst

Elapsed: 00:00:00.01

now i am doing it in this code but an error coming wat i cann't understand--


1 declare
2 type bp is ref cursor return ab%rowtype;
3 v_bp bp;
4 v_tab ab%rowtype;
5 begin
6 open v_bp for select *from ab;
7 fetch v_bp into v_tab;
8 for i in v_tab.first..v_tab.last loop
9 DBMS_OUTPUT.PUT_LINE(v_tab(i));
10 end loop;
11* end;
16:10:00 SQL> /
for i in v_tab.first..v_tab.last loop
*
ERROR at line 8:
ORA-06550: line 8, column 16:
PLS-00302: component 'FIRST' must be declared
ORA-06550: line 8, column 1:
PL/SQL: Statement ignored
Re: an error [message #250531 is a reply to message #250527] Tue, 10 July 2007 06:34 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You have declared a ROWtype not a TABLE type. Rowtypes don't have first or last attributes.

Look at this:
SQL> DECLARE
  2     TYPE bp IS REF CURSOR RETURN emp%ROWTYPE;
  3
  4     v_bp    bp;
  5     -- Declare a table type
  6     TYPE    emptabtype IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
  7     v_tab   emptabtype;
  8  BEGIN
  9     OPEN v_bp FOR SELECT * FROM   emp;
 10
 11     FETCH v_bp
 12     INTO  v_tab(v_bp%rowcount);
 13
 14     FOR i IN v_tab.FIRST .. v_tab.LAST
 15     LOOP
 16        DBMS_OUTPUT.put_line (v_tab (i).ename);
 17     END LOOP;
 18  END;
 19  /
KING

PL/SQL procedure successfully completed.


But the procedure, as you coded it, will only fetch one record.

MHE
Re: an error [message #250539 is a reply to message #250527] Tue, 10 July 2007 06:53 Go to previous messageGo to next message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
thank you very much.my concept was wrong.


but can u explain me a little what is the different b/w ref cursor and cursor if i used ony cursor in it will it be right?
Re: an error [message #250557 is a reply to message #250539] Tue, 10 July 2007 07:45 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
This is the point where you have a look at the documentation. The concepts are explained very well.

A cursor, as Oracle tells us, is a "handle or name for a private SQL area — an area in memory in which a parsed statement and other information for processing the statement are kept."

A ref cursor, on the other hand, is a cursor variable. It is a pointer to a given cursor. You can pass resulsets from one procedure to another.

If you want more and detailed explanations, fire up your browser and search for it yourself. I really suggest you have a look at the Oracle documentation though. And you might want to search OraFAQ.

MHE
Re: an error [message #250559 is a reply to message #250557] Tue, 10 July 2007 07:47 Go to previous message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
thank you
Previous Topic: Need Help on Oracle Function
Next Topic: PL/SQL wrapper error
Goto Forum:
  


Current Time: Sat Dec 10 06:40:21 CST 2016

Total time taken to generate the page: 0.07783 seconds