Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL Collection index by varchar
PLSQL Collection index by varchar [message #644547] Mon, 09 November 2015 10:02 Go to next message
wantsunny007
Messages: 10
Registered: November 2015
Junior Member
Hi everyone,

i am trying to use a plsql collection with varchar2 as index , can anyone let me know how to print all the values including the repeating values for the same index

example:
list_tab('sunny') :='salary_1';
list_tab('sunny') :='salary_2';
list_tab('test') :='salary_1';


Thank you in advance, hoping for your replies .

[Updated on: Mon, 09 November 2015 10:04]

Report message to a moderator

Re: PLSQL Collection index by varchar [message #644549 is a reply to message #644547] Mon, 09 November 2015 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

SQL> declare
  2    type tab is table of varchar2(100) index by varchar2(100);
  3    mytab   tab;
  4    surname varchar2(100);
  5  begin
  6    mytab('Cadot')    := 'Michel';
  7    mytab('Watson')   := 'John';
  8    mytab('Foot')     := 'Little';
  9    mytab('Swan')     := 'Black';
 10    mytab('monster')  := 'cookie';
 11    mytab('Boehmer')  := 'Barbara';
 12    mytab('division') := 'joy';
 13    surname := mytab.first;
 14    loop
 15      exit when surname is null;
 16      dbms_output.put_line(mytab(surname)||' '||surname);
 17      surname := mytab.next(surname);
 18    end loop;
 19  end;
 20  /
Barbara Boehmer
Michel Cadot
Little Foot
Black Swan
John Watson
joy division
cookie monster

PL/SQL procedure successfully completed.

Re: PLSQL Collection index by varchar [message #644551 is a reply to message #644547] Mon, 09 November 2015 10:42 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Hi,

There is nothing like "repeating values" for one index, it is the same situation as one variable may hold only one value - it is overwritten by the latest assignment.

According the work with the associative array, consider consulting PL/SQL Language Reference, which is available with other Oracle documentation books e.g. online on http://docs.oracle.com/en/database/database.html

It contains the chapter about collections. Here is a small code snippet anyway.
declare
  type lt_type is table of varchar2(30) index by varchar2(30);
  list_tab lt_type;
  i varchar2(30);
begin
list_tab('sunny') :='salary_1';
list_tab('sunny') :='salary_2';
list_tab('test') :='salary_1';

  i := list_tab.first;
  while i is not null loop
    dbms_output.put_line( i||': '||list_tab(i) );
    i := list_tab.next(i);
  end loop;
end;
/

sunny: salary_2
test: salary_1
Re: PLSQL Collection index by varchar [message #644552 is a reply to message #644551] Mon, 09 November 2015 11:01 Go to previous messageGo to next message
wantsunny007
Messages: 10
Registered: November 2015
Junior Member
Thank you,

actually im trying to reduce redundency of the collection ,so how can i do that without repeating the name multiple times but to print all the addresses

declare
TYPE list_tab1 IS RECORD
(name varchar2(20)
,address varchar2(20));
type lt_type is table of list_tab1 index by BINARY_INTEGER;
list_tab lt_type;
begin

list_tab(1).name:='sunny'; list_tab(1).address :='test_1';
list_tab(2).name:='sunny'; list_tab(2).address :='test_2';
list_tab(3).name:='sunny'; list_tab(3).address :='test_3';

FOR i IN list_tab.FIRST .. list_tab.LAST LOOP
dbms_output.put_line(list_tab(i).name||' '||list_tab(i).address);

end loop;
end;
/

sunny test_1
sunny test_2
sunny test_3
Re: PLSQL Collection index by varchar [message #644554 is a reply to message #644552] Mon, 09 November 2015 11:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> im trying to reduce redundency of the collection ,
Above is best accomplished by NOT using any collection.
Never do in PL/SQL that which can be done in plain SQL.
PL/SQL only adds overhead.
Only SQL actually interacts with the database.
Re: PLSQL Collection index by varchar [message #644555 is a reply to message #644552] Mon, 09 November 2015 11:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Mon, 09 November 2015 17:37

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.
...

Re: PLSQL Collection index by varchar [message #644556 is a reply to message #644552] Mon, 09 November 2015 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
without repeating the name multiple times but to print all the addresses


Where do you see you repeat the name when you print the array?
And which name are you talking about?

Re: PLSQL Collection index by varchar [message #644559 is a reply to message #644556] Mon, 09 November 2015 11:53 Go to previous messageGo to next message
wantsunny007
Messages: 10
Registered: November 2015
Junior Member
Hi Michel,

not while printing ,list_tab(1).name :='sunny' in each line to add a new address in the collection,so i dont want to repeat the each and every time for the address ,
i want to write the name once and multiple addresses in the code .
Thanks you so much
Re: PLSQL Collection index by varchar [message #644560 is a reply to message #644559] Mon, 09 November 2015 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You cannot with PL/SQL types, you have to use SQL types for this:
SQL> create or replace type list_tab1 IS object (name varchar2(20),address varchar2(20));
  2  /

Type created.

SQL> create or replace type lt_type is table of list_tab1;
  2  /

Type created.

SQL> declare
  2    list_tab lt_type;
  3  begin
  4    list_tab := lt_type(list_tab1('sunny','test_1'),
  5                        list_tab1('sunny','test_2'),
  6                        list_tab1('sunny','test_3'));
  7    FOR i IN list_tab.FIRST .. list_tab.LAST LOOP
  8      dbms_output.put_line(list_tab(i).name||' '||list_tab(i).address);
  9    end loop;
 10  end;
 11  /
sunny test_1
sunny test_2
sunny test_3

PL/SQL procedure successfully completed.

Re: PLSQL Collection index by varchar [message #644561 is a reply to message #644560] Mon, 09 November 2015 12:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also, use the way I showed in my first post instead of "FOR i IN list_tab.FIRST .. list_tab.LAST LOOP", it is safer:
SQL> declare
  2    list_tab lt_type;
  3  begin
  4    list_tab := lt_type(list_tab1('sunny','test_1'),
  5                        list_tab1('sunny','test_2'),
  6                        list_tab1('sunny','test_3'));
  7    list_tab.delete(2);
  8    FOR i IN list_tab.FIRST .. list_tab.LAST LOOP
  9      dbms_output.put_line(list_tab(i).name||' '||list_tab(i).address);
 10    end loop;
 11  end;
 12  /
sunny test_1
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 9

SQL> declare
  2    list_tab lt_type;
  3    i        pls_integer;
  4  begin
  5    list_tab := lt_type(list_tab1('sunny','test_1'),
  6                        list_tab1('sunny','test_2'),
  7                        list_tab1('sunny','test_3'));
  8    list_tab.delete(2);
  9    i := list_tab.first;
 10    loop
 11      exit when i is null;
 12      dbms_output.put_line(list_tab(i).name||' '||list_tab(i).address);
 13      i := list_tab.next(i);
 14    end loop;
 15  end;
 16  /
sunny test_1
sunny test_3

PL/SQL procedure successfully completed.

Re: PLSQL Collection index by varchar [message #644562 is a reply to message #644560] Mon, 09 November 2015 12:26 Go to previous messageGo to next message
wantsunny007
Messages: 10
Registered: November 2015
Junior Member
Thank you
Re: PLSQL Collection index by varchar [message #644563 is a reply to message #644562] Mon, 09 November 2015 13:08 Go to previous messageGo to next message
wantsunny007
Messages: 10
Registered: November 2015
Junior Member
Can you help me to write this code in two procedures i.e collection in a procedure and rest in another procedure, so that collection values are passed to another procedure Confused

Program:

declare

type t_features is table of varchar(100) index by pls_integer;
type t_car_rec is record
(
make varchar2(50),
model varchar2(50),
features t_features
);

type t_car_tab is table of t_car_rec index by pls_integer;
car_tab t_car_tab;

procedure show_detail is
car_idx pls_integer;
features_idx pls_integer;
begin
car_idx := car_tab.first;
loop
exit when car_idx is null;
dbms_output.put_line('Details for ' || car_tab(car_idx).make || ' ' || car_tab(car_idx).model);

features_idx := car_tab(car_idx).features.first;
loop
exit when features_idx is null;
dbms_output.put_line(' =>' || car_tab(car_idx).features(features_idx));

features_idx := car_tab(car_idx).features.next(features_idx);
end loop;

car_idx := car_tab.next(car_idx);
end loop;
end;

begin

-- using sequential index values
car_tab(1).make := 'Ferrari';
car_tab(1).model := 'Testarossa';
car_tab(1).features(1) := 'Fast';
car_tab(1).features(2) := 'Looks cool';
car_tab(1).features(3) := 'Expensive';

-- using random index values (sparse)
car_tab(2).make := 'Acura';
car_tab(2).model := 'TSX';
car_tab(2).features(14) := 'Small';
car_tab(2).features(200) := 'Good MPG';
car_tab(2).features(36) := 'Inexpensive';

show_detail;

end;


output:
Details for Ferrari Testarossa
=>Fast
=>Looks cool
=>Expensive
Details for Acura TSX
=>Small
=>Inexpensive
=>Good MPG
Re: PLSQL Collection index by varchar [message #644564 is a reply to message #644563] Mon, 09 November 2015 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 09 November 2015 18:35

Michel Cadot wrote on Mon, 09 November 2015 17:37

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.
...


Re: PLSQL Collection index by varchar [message #644565 is a reply to message #644564] Mon, 09 November 2015 13:12 Go to previous messageGo to next message
wantsunny007
Messages: 10
Registered: November 2015
Junior Member
Will follow all those rules , thank you michel
Re: PLSQL Collection index by varchar [message #644566 is a reply to message #644565] Mon, 09 November 2015 13:17 Go to previous messageGo to next message
wantsunny007
Messages: 10
Registered: November 2015
Junior Member
Can you help me to write this code in two procedures i.e collection in a procedure and rest in another procedure, so that collection values are passed to another procedure Confused

DECLARE
type t_features
IS
  TABLE OF VARCHAR(100) INDEX BY pls_integer;
type t_car_rec
IS
  record
  (
    make  VARCHAR2(50),
    model VARCHAR2(50),
    features t_features );
type t_car_tab
IS
  TABLE OF t_car_rec INDEX BY pls_integer;
  car_tab t_car_tab;
PROCEDURE show_detail
IS
  car_idx pls_integer;
  features_idx pls_integer;
BEGIN
  car_idx := car_tab.first;
  LOOP
    EXIT
  WHEN car_idx IS NULL;
    dbms_output.put_line('Details for ' || car_tab(car_idx).make || ' ' || car_tab(car_idx).model);
    features_idx := car_tab(car_idx).features.first;
    LOOP
      EXIT
    WHEN features_idx IS NULL;
      dbms_output.put_line(' =>' || car_tab(car_idx).features(features_idx));
      features_idx := car_tab(car_idx).features.next(features_idx);
    END LOOP;
    car_idx := car_tab.next(car_idx);
  END LOOP;
END;
BEGIN
  -- using sequential index values
  car_tab(1).make        := 'Ferrari';
  car_tab(1).model       := 'Testarossa';
  car_tab(1).features(1) := 'Fast';
  car_tab(1).features(2) := 'Looks cool';
  car_tab(1).features(3) := 'Expensive';
  -- using random index values (sparse)
  car_tab(2).make          := 'Acura';
  car_tab(2).model         := 'TSX';
  car_tab(2).features(14)  := 'Small';
  car_tab(2).features(200) := 'Good MPG';
  car_tab(2).features(36)  := 'Inexpensive';
  show_detail;
END;


output :
output:
Details for Ferrari Testarossa
=>Fast
=>Looks cool
=>Expensive
Details for Acura TSX
=>Small
=>Inexpensive
=>Good MPG

[Updated on: Mon, 09 November 2015 13:19]

Report message to a moderator

Re: PLSQL Collection index by varchar [message #644567 is a reply to message #644566] Mon, 09 November 2015 13:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 09 November 2015 20:10
Michel Cadot wrote on Mon, 09 November 2015 18:35

Michel Cadot wrote on Mon, 09 November 2015 17:37

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.
...



Re: PLSQL Collection index by varchar [message #644568 is a reply to message #644567] Mon, 09 November 2015 13:49 Go to previous messageGo to next message
wantsunny007
Messages: 10
Registered: November 2015
Junior Member
Any Help is Appreciated!!
Re: PLSQL Collection index by varchar [message #644570 is a reply to message #644568] Mon, 09 November 2015 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

We fail to see where is the problem.
Is this not what you have done?
Explain in details what you want.

Re: PLSQL Collection index by varchar [message #644571 is a reply to message #644570] Mon, 09 November 2015 14:20 Go to previous messageGo to next message
wantsunny007
Messages: 10
Registered: November 2015
Junior Member
Hi Everyone,

Sorry dint know rules to post.

i want to write a package which has two procedures in it. one procedure with just collection of data and another procedure to process it, want to know how to pass this collection data to another procedure . unable to connect to sql plus so posting using code tags, thanks in advance

DECLARE 
    TYPE t_features 
      IS TABLE OF VARCHAR(100) INDEX BY PLS_INTEGER; 
    TYPE t_car_rec IS RECORD ( 
      make VARCHAR2(50), 
      model VARCHAR2(50), 
      features T_FEATURES ); 
    TYPE t_car_tab 
      IS TABLE OF T_CAR_REC INDEX BY PLS_INTEGER; 
    car_tab      T_CAR_TAB; 
    car_idx      PLS_INTEGER; 
    features_idx PLS_INTEGER; 
BEGIN 
    -- using sequential index values 
    Car_tab(1).make := 'Ferrari'; 

    Car_tab(1).model := 'Testarossa'; 

    Car_tab(1).Features(1) := 'Fast'; 

    Car_tab(1).Features(2) := 'Looks cool'; 

    Car_tab(1).Features(3) := 'Expensive'; 

    -- using random index values (sparse) 
    Car_tab(2).make := 'Acura'; 

    Car_tab(2).model := 'TSX'; 

    Car_tab(2).Features(14) := 'Small'; 

    Car_tab(2).Features(200) := 'Good MPG'; 

    Car_tab(2).Features(36) := 'Inexpensive'; 

    car_idx := car_tab.first; 

    LOOP 
        EXIT WHEN car_idx IS NULL; 

        dbms_output.Put_line('Details for ' 
                             || Car_tab(car_idx).make 
                             || ' ' 
                             || Car_tab(car_idx).model); 

        features_idx := Car_tab(car_idx).features.first; 

        LOOP 
            EXIT WHEN features_idx IS NULL; 

            dbms_output.Put_line(' =>' 
                                 || Car_tab(car_idx).Features(features_idx)); 

            features_idx := Car_tab(car_idx).features.NEXT(features_idx); 
        END LOOP; 

        car_idx := car_tab.NEXT(car_idx); 
    END LOOP; 
END; 
/


OUTPUT:
Details for Ferrari Testarossa
=>Fast
=>Looks cool
=>Expensive
Details for Acura TSX
=>Small
=>Inexpensive
=>Good MPG


Re: PLSQL Collection index by varchar [message #644572 is a reply to message #644571] Mon, 09 November 2015 14:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In the same way you did in your previous post for one of the procedures, for instance.
Or using a parameter to your procedures; parameter which will be of T_CAR_TAB datatype.

[Updated on: Tue, 10 November 2015 00:08]

Report message to a moderator

Re: PLSQL Collection index by varchar [message #644573 is a reply to message #644572] Mon, 09 November 2015 14:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> one procedure with just collection of data
hardcoding of data smells to me like a standard homework assignment.
Why no actual SQL statements against some DB included?
Re: PLSQL Collection index by varchar [message #644574 is a reply to message #644572] Mon, 09 November 2015 15:06 Go to previous message
wantsunny007
Messages: 10
Registered: November 2015
Junior Member
Thank you michel
Previous Topic: Need Help in SQL Query
Next Topic: Determine Incomplete Orders
Goto Forum:
  


Current Time: Wed Apr 24 23:41:26 CDT 2024