Home » SQL & PL/SQL » SQL & PL/SQL » using index and cursor (4.1.3 sql developer)
using index and cursor [message #648054] Mon, 15 February 2016 20:51 Go to next message
M123
Messages: 47
Registered: February 2016
Location: USA
Member

Hi all

I want a help how to fetch values into cursor using for loop concept, store in index by table and then display the values of index .


declare
type t1 is table of varchar2(20)
index by binary_integer;
vt t1;
   cursor c1 is select last_name from employees;
   i=i+1;
  begin
for i in c1 loop  

   i:=i+1;
 end loop;
  for i in vt.first..vt.last
    loop
     dbms_output.put_line(vt(i));
   end loop;
 end;


here in the c1 ,i want to fetch values ,index by.
can some help on this ?
Re: using index and cursor [message #648055 is a reply to message #648054] Mon, 15 February 2016 20:57 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Running your code (you did run it, I hope) throws this error:
ERROR at line 6:
ORA-06550: line 6, column 5:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
because you are trying to do something to a variable that you have not declared. Ithink you are confusing the declaration section of a pl/sql block with the procedural section.
Re: using index and cursor [message #648056 is a reply to message #648055] Mon, 15 February 2016 21:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> i=i+1;
is above or below valid syntax? (both exist in the posted code)
> i:=i+1;
Re: using index and cursor [message #648057 is a reply to message #648056] Mon, 15 February 2016 21:36 Go to previous messageGo to next message
M123
Messages: 47
Registered: February 2016
Location: USA
Member

leave that one , i only mentioned as example

declare
type t1 is table of varchar2(20)
index by binary_integer;
vt t1;
   cursor c1 is select last_name from employees;
  begin
for i in c1 loop  
   i:=i+1;
 end loop;
  for i in vt.first..vt.last
    loop
     dbms_output.put_line(vt(i));
   end loop;
 end;


ORA-06550: line 10, column 7:
PLS-00306: wrong number or types of arguments in call to '+'

i know there is some error in the loop , can you please suggest

Re: using index and cursor [message #648058 is a reply to message #648057] Mon, 15 February 2016 22:17 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
What do you think i is? A number? A row?? What???
Re: using index and cursor [message #648059 is a reply to message #648057] Mon, 15 February 2016 22:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl> declare
  2    type t1 is table of varchar2(20) index by binary_integer;
  3    vt t1;
  4    cursor c1 is select last_name from employees;
  5    -- declare your variable i and initialize the value:
  6    i number := 0;
  7  begin
  8    -- use something different (like r1) instead of i for your
  9    -- record identifier to avoid conflicts:
 10    for r1 in c1 loop
 11  	 -- increment the variable i by 1 each time through the loop:
 12  	 i := i + 1;
 13  	 -- set the value of the ith record of the vt associative array
 14  	 -- using the last_name of the record (r1) of the cursor c1:
 15  	 vt(i) := r1.last_name;
 16    end loop;
 17    for i in 1 .. vt.count loop
 18  	 dbms_output.put_line(vt(i));
 19    end loop;
 20  end;
 21  /
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.

Re: using index and cursor [message #648061 is a reply to message #648059] Mon, 15 February 2016 22:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
never do in PL/SQL that which you can do in plain SQL

SQL> select last_name from employees;

LAST_NAME
-------------------------
Abel
Ande
Atkinson
Austin
Baer
Baida
Banda
Bates
Bell
Bernstein
Bissot
Re: using index and cursor [message #648079 is a reply to message #648061] Tue, 16 February 2016 02:14 Go to previous messageGo to next message
JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar
Member

Quote:
never do in PL/SQL that which you can do in plain SQL

very very true

simple example with BULK COLLECT:

declare
    type t1 is table of employees.last_name%TYPE index by binary_integer;
    vt t1;
  begin

    select last_name  BULK COLLECT INTO vt from employees;

    for i in 1 .. vt.count loop
     dbms_output.put_line(vt(i));
    end loop;
  end;
/  
Re: using index and cursor [message #648125 is a reply to message #648059] Wed, 17 February 2016 00:13 Go to previous messageGo to next message
M123
Messages: 47
Registered: February 2016
Location: USA
Member

Hi barbara

obrigado


Regards
M123
Re: using index and cursor [message #648126 is a reply to message #648079] Wed, 17 February 2016 00:14 Go to previous messageGo to next message
M123
Messages: 47
Registered: February 2016
Location: USA
Member

thankyou Nagtz and Ed
Re: using index and cursor [message #648127 is a reply to message #648058] Wed, 17 February 2016 00:15 Go to previous messageGo to next message
M123
Messages: 47
Registered: February 2016
Location: USA
Member

hi watson

for i in c1 here it is row

and i:=i+1 here it is number.

?

[Updated on: Wed, 17 February 2016 00:16]

Report message to a moderator

Re: using index and cursor [message #648131 is a reply to message #648127] Wed, 17 February 2016 02:12 Go to previous message
JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar
Member

in my case i is the index of a for loop, which is an implicit declared integer.
Previous Topic: Queries for User and Password Policy
Next Topic: Getting Desire Data According to the interval...
Goto Forum:
  


Current Time: Fri Apr 26 10:10:16 CDT 2024