OraFAQ Forum: SQL & PL/SQL » what array or collection type to use

Home » SQL & PL/SQL » SQL & PL/SQL » what array or collection type to use (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

what array or collection type to use [message #600436]

Wed, 06 November 2013 12:27

gentleman777us
Messages: 122
Registered: April 2005

Senior Member

Hi,

Requirement: I need to come up with a procedure where I could pass category, subcategory, criteria1 and criteria 2 as an array( or if there is any other better way). This array should take multiple rows of information ex: first row = agriculture, farms, michigan, detroit;
second row: energy, power plants, colorado, denver etc.

My question is what is the best way to declare this variable as string_array or any other type and I need to access the values of this variable in my procedure.

an example would be greatly appreciated.

Thanks

Report message to a moderator

Re: what array or collection type to use [message #600437 is a reply to message #600436]

Wed, 06 November 2013 12:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I need to come up with a procedure where I could pass
pass from where to where?
REFCURSOR can return one row at a time.
what problem are you really trying to solve?

Report message to a moderator

Re: what array or collection type to use [message #600439 is a reply to message #600437]

Wed, 06 November 2013 12:41

gentleman777us
Messages: 122
Registered: April 2005

Senior Member

my problem I need a declarative type in my procedure where I can pass 4 different values to the same row and retrieve them in my procedure body.

can you assign values to a refcursor?

Report message to a moderator

Re: what array or collection type to use [message #600440 is a reply to message #600439]

Wed, 06 November 2013 12:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

why should I answer your questions when you don't answer my questions?

ROWS exist in tables.
post CREATE TABLE statements for all tables involved with this issue & sample data INSERT statements

Report message to a moderator

Re: what array or collection type to use [message #600441 is a reply to message #600440]

Wed, 06 November 2013 13:08

gentleman777us
Messages: 122
Registered: April 2005

Senior Member

Actually there is no need of sample sql statements. All I need to do is how can you pass a row of information atleast 4 different values to a variable and print them in the body

like
procedure p1(i_criteria strin_array) as

begin
for i_criteria in..first i_criteria.last
loop

dbms_output.put_line(the string array values are '||i_criteria);

end loop;
end p1;

The output should be
agriculture, farms, michigan, detroit
energy, power plants, colorado, denver

Hope Iam clear.

Report message to a moderator

Re: what array or collection type to use [message #600442 is a reply to message #600441]

Wed, 06 November 2013 13:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/wiki/Array

Report message to a moderator

Re: what array or collection type to use [message #600443 is a reply to message #600442]

Wed, 06 November 2013 13:28

gentleman777us
Messages: 122
Registered: April 2005

Senior Member

That example assigns only one value i.e

v_strarr(1) := 'val1';
v_strarr(2) := 'val2';
v_strarr(3) := 'val3';

how can we do this

v_strarr(1) := 'val1', 'val2', 'val3', 'val4';
v_strarr(2) := 'val5', 'val6', 'val7', 'val7';

Report message to a moderator

Re: what array or collection type to use [message #600444 is a reply to message #600443]

Wed, 06 November 2013 13:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Do you want an ARRAY of ARRAY?

Report message to a moderator

Re: what array or collection type to use [message #600446 is a reply to message #600444]

Wed, 06 November 2013 13:40

gentleman777us
Messages: 122
Registered: April 2005

Senior Member

Iam not sure what you meant by ARRAY of ARRAY but my goal is how can I assign more than one value for the same variable. i.e v_strarr(1) should take val1, val2, val3 and val4 VALUES.

Report message to a moderator

Re: what array or collection type to use [message #600448 is a reply to message #600446]

Wed, 06 November 2013 14:53

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Not sure what you want exactly, but you can try below:

CREATE OR REPLACE TYPE t_obj_cat AS OBJECT (
  category VARCHAR2(100),
  subcategory VARCHAR2(100),
  criteria1 VARCHAR2(100),
  criteria VARCHAR2(100)
);
/


CREATE OR REPLACE PACKAGE my_pkg AS

  TYPE t_cat_tab IS TABLE OF t_obj_cat INDEX BY BINARY_INTEGER;

  PROCEDURE A();
  PROCEDURE B(p_cat_tab t_cat_tab);
  --Call procedure B from A
END;
/

Make package body as per your need.

Manu

Report message to a moderator