array declaration

[diwakar]

Dear All,

I have an issue as given below:

Test for multiple RSMs assigned to a single office

A. SELECT tblAgent_Office, tblAgent_Title FROM tblAgent
WHERE tblAgent_Title = "RSM"
B. Store returned records from SELECT statment in an array "arrayRSM"

Now how do I do the array declaration. Can anybody give an example/hint on how about going with this.

Regards,

K.Diwakar

[slash]

Hi you can try this

CREATE TYPE ox1 as object( tblagent_office varchar2(100), tblagent_title varchar2(100));
/

CREATE TYPE va1 as varray(1000) of ox1;
/

DECLARE
CURSOR cur_test IS
SELECT tblagent_office,
tblagent_title
FROM tblagent
WHERE tblagent_title = 'RSM';

v_counter NUMBER:=1;
va_ox va1:= va1();

BEGIN

FOR rec_test IN cur_test
LOOP
va_ox.extend;

va_ox(v_counter):= ox1(rec_test.tblagent_office,rec_test.tblagent_title);

v_counter := v_counter + 1;

END LOOP;

FOR vn_count IN 1..va_ox.COUNT
LOOP
dbms_output.put_line(va_ox(vn_count).tblagent_office);
dbms_output.put_line(va_ox(vn_count).tblagent_title);

END LOOP;

END;
/

Regards
Slash(MCP)

[diwakar]

Thank you Slash... I shall try and get back to you..

Regards,

K.Diwakar