Re: PL/SQL Help....
- From: Mindaugas Navickas <mnavickas@xxxxxxxxx>
- To: gkatteri@xxxxxxxxxxx, bunjibry@xxxxxxxxx, Oracle-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 30 Jan 2007 11:05:33 -0800 (PST)
This example would be similar to one that Govindan send, however it uses TABLE
instead of VARRAY - which is not creating artificial limit of records to be
selected. Also it demonstrates BULK COLLECT - to fast populate collection.
CREATE OR REPLACE TYPE SomeObjType AS OBJECT (
Col1 VARCHAR2(10),
Col2 VARCHAR2(10),
)
/
CREATE OR REPLACE TYPE SomeColType AS TABLE OF SomeObjType
/
CREATE OR REPLACE PACKAGE BODY pkg_rpt
AS
PROCEDURE PR_RTN_DETAIL(in_s1 IN varchar2,-- start date
in_e1 IN varchar2,-- end date
in_s2 IN varchar2,-- start date + release_time/24
in_e2 IN varchar2,-- end date + release_time/24
in_acct IN varchar2,
in_subacct IN varchar2,
out_array OUT SomeColType ) IS
BEGIN
SELECT SomeObjType (col1, col2) BULK COLLECT INTO out_array FROM some_table
WHERE ...
Regards
Mindaugas
----- Original Message ----
From: GovindanK <gkatteri@xxxxxxxxxxx>
To: bunjibry@xxxxxxxxx; Oracle-L <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, January 30, 2007 1:39:41 PM
Subject: Re: PL/SQL Help....
Hello Bryan
Here is a sample
CREATE OR REPLACE TYPE Batch_RecTyp AS OBJECT (
BAT_SYS_NO NUMBER(8)
,BAT_STATUS VARCHAR2(10)
,BAT_SENT_DT VARCHAR2(10)
)
/
CREATE OR REPLACE TYPE Batch_RecList AS VARRAY(5000) OF Batch_RecTyp
/
CREATE OR REPLACE PACKAGE process_patch
AS
TYPE me_cursor IS REF CURSOR;
TYPE Batch_RecTyp_r IS RECORD (
BAT_SYS_NO NUMBER(8)
,BAT_STATUS VARCHAR2(10)
,BAT_SENT_DT VARCHAR2(10)
) ;
TYPE Batch_CurTyp IS REF CURSOR RETURN Batch_RecTyp_r
;
PROCEDURE p_save_batch(i_batch_det IN batch_RecList
,o_err_code OUT number
,o_err_mesg OUT varchar2
);
END;
/
show errors
CREATE OR REPLACE PACKAGE BODY process_batch
AS
PROCEDURE p_save_batch(i_batch_det IN batch_RecList
,o_err_code OUT number
,o_err_mesg OUT varchar2
)
AS
l_first_batch_no number ; --internal
use,hence no %TYPE
BEGIN --{
FOR bat in 1..i_batch_det.COUNT
LOOP -- {
IF i_batch_det.EXISTS(bat) THEN -- {
if bat > 1 then
o_err_code := 9009;
o_err_mesg := 'FATAL ERROR .. MORE THAN ONE BATCH NOT ALLOWED AT A
TIME';
return;
end if;
if bat = 1 then
l_first_batch_no := i_batch_det(bat).BAT_SYS_NO ;
end if;
/* Do your processing here */
INSERT INTO BATCHES(BAT_SYS_NO
,BAT_STATUS
,BAT_SENT_DT
) VALUES
(i_batch_det(bat).BAT_SYS_NO
,i_batch_det(bat).BAT_STATUS
,to_date(i_batch_det(bat).BAT_SENT_DT,'MM/DD/YYYY')
);
END IF; -- }
END LOOP;-- }
END; --Package Body
/
show errors
HTH
GovindanK
On Tue, 30 Jan 2007 08:34:41 -0700, "Bryan Wells" <bunjibry@xxxxxxxxx> said:
All,
Since my pl/sql is less than stellar, well, way less than that; i was hoping to
get some direction on how to pass an array of records back to the calling
application? i have initialized a record type, have been able to write and
read from it within the pl/sql package but just cant figure out how to pass it
out of the package. I have been able to pass an array out as a varchar and
clob, but cant get the array to distinguish end of record. Yes I'm a novice
;-)
Here is a snippet of what i have done;
CREATE OR REPLACE PACKAGE BODY pkg_rpt
AS
PROCEDURE PR_RTN_DETAIL(in_s1 IN varchar2,-- start date
in_e1 IN varchar2,-- end date
in_s2 IN varchar2,-- start date + release_time/24
in_e2 IN varchar2,-- end date + release_time/24
in_acct IN varchar2,
in_subacct IN varchar2,
out_array OUT ?)
--
/* Cursors to retrieve records for record type to pass back to application */
.
.
.
--
/* create record type */
--
TYPE rpt_detail_type IS RECORD
(PHN table.phone%TYPE,
FIRST table.first%TYPE,
ACCT table.acct%TYPE,
TRANID table.transaction%TYPE,
DT date,
STATUS varchar2(1 BYTE),
INTCODE0 table.intcode0%TYPE,
ID table.id%type);
v_br rpt_detail_type;
--
.
.
.
--
/* populate records here */
Thanks All! As usual your expertise is much appreciated!
--
Bryan S Wells
DBA VoiceLog
Email: bunjibry@xxxxxxxxx
Other related posts:
