Home » SQL & PL/SQL » SQL & PL/SQL » How to run the procedure that have array output?
How to run the procedure that have array output? [message #673584] Mon, 26 November 2018 04:20 Go to next message
Ican321
Messages: 8
Registered: November 2018
Junior Member
Hi all,

I need your help how to run the package that have array output ?

in my spec body I have :

create or replace package PKG_tst_array is
TYPE rec_msf_fld IS RECORD
( a varchar2(255),
b VARCHAR2(250),
c VARCHAR2(2000)
);

TYPE TBL_OBJ_MSG IS TABLE OF rec_msf_fld;

procedure process (po_a out TBL_OBJ_MSG
);
end;

-------------------------------------------------


CREATE OR REPLACE package BODY PKG_tst_array IS
procedure process (po_a out TBL_OBJ_MSG
)
IS
l_idx NUMBER :=0;

BEGIN
For CREC in (SELECT AA,BB,CC FROM TMP_TABLE)
)
Loop
l_idx := l_idx+1;

PO_A(l_idx).uw_cd := CREC.AA;
PO_A(l_idx).msg_id := CREC.BB;
PO_A(l_idx).msg_txt := CREC.CC;
end loop;
END;

----------------------------------------------------

How can I run this package? I want to see the value of array using dbms_output.

declare
l_msg SYS_REFCURSOR;
begin
PKG_tst_array.process(l_msg);
dbms_output.put_line ('l_msg = '||l_msg );
end;


Thank you for your help.
Re: How to run the procedure that have array output? [message #673585 is a reply to message #673584] Mon, 26 November 2018 04:48 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
By calling it with an array variable of the appropriate type (not sys_refcursor - that's not an array at all) and then looping over the array and passing the individual items from it to dbms_output since it only accepts simple scalar variables.
Re: How to run the procedure that have array output? [message #673587 is a reply to message #673585] Mon, 26 November 2018 04:53 Go to previous messageGo to next message
Ican321
Messages: 8
Registered: November 2018
Junior Member
Do you have any suggestion regarding the array ?

how do I run this package ? PKG_tst_array.process ??


CREATE OR REPLACE package BODY PKG_tst_array IS
procedure process (po_a out TBL_OBJ_MSG
)
IS
l_idx NUMBER :=0;

BEGIN
For CREC in (SELECT AA,BB,CC FROM TMP_TABLE)
)
Loop
l_idx := l_idx+1;

PO_A(l_idx).a := CREC.AA;
PO_A(l_idx).b := CREC.BB;
PO_A(l_idx).c := CREC.CC;
end loop;
END;



Thanks you
Re: How to run the procedure that have array output? [message #673591 is a reply to message #673587] Mon, 26 November 2018 05:28 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
What suggestion do you need?
You create an array variable of the same type as the one the procedure parameter uses - so PKG_tst_array.tbl_obj_msg in your case.
Re: How to run the procedure that have array output? [message #673593 is a reply to message #673584] Mon, 26 November 2018 05:40 Go to previous message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
If you don't know how to format the code, learn it using SQL Formatter.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Previous Topic: HAVING ISSUES UNDERSTANDING HOW TO SOLVE THIS ORACLE QUESTION
Next Topic: How to get apps view DDL statement from non-apps schema
Goto Forum:
  


Current Time: Fri Aug 07 21:45:44 CDT 2020