Dear All saya baru join di milis ini,
Coba membantu mas Handy, dulu pernah mengalami hal yang sama. Kalau function
bisa return record bisa menggunakan return pipeline.
Langkah-langkahnya kira-kira spt ini:
1. Buat object type.
CREATE TYPE VENDOR_LIST AS OBJECT (
VENDORID VARCHAR2(10),
VENDORNAME VARCHAR2(100)
);
CREATE TYPE VENDOR_LIST_REC AS TABLE OF VENDOR_LIST;
2. Buat function
CREATE FUNTION QUERY_VENDOR(iVENDOR_ID in VARCHAR2)
RETURN VENDOR_LIST_REC PIPELINED IS
iSQL_CURSOR NUMBER;
iROWPROCESSED NUMBER;
vSQL VARCHAR2(4000);
vVENDOR_ID VARCHAR2(10);
vVENDOR_NAME VARCHAR2(50);
BEGIN
vSQL := 'SELECT VENDOR_ID,VENDOR_NAME WHERE VENDOR_ID='''|| iVENDOR_ID ||'''';
iSQL_CURSOR := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(iSQL_CURSOR,vSQL,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(iSQL_CURSOR,1,vVENDOR_ID,10);
DBMS_SQL.DEFINE_COLUMN(iSQL_CURSOR,2,vVENDOR_NAME,50);
iROWPROCESSED := DBMS_SQL.EXECUTE(iSQL_CURSOR);
LOOP
IF DBMS_SQL.FETCH_ROWS(iSQL_CURSOR)>0 THEN
DBMS_SQL.COLUMN_VALUE(iSQL_CURSOR,1,vVENDOR_ID);
DBMS_SQL.COLUMN_VALUE(iSQL_CURSOR,2,vVENDOR_NAME);
PIPE ROW (VENDOR_LIST( vVENDOR_ID,vVENDOR_NAME));
ELSE
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(iSQL_CURSOR);
RETURN;
END;
3. Panggil function(contoh)
select * from table(cast(VENDOR_LIST('25') AS VENDOR_LIST_REC));
Contoh diatas ga dijamin bisa dicompile :D, tp kira-kira caranya seperti itu.
Semoga membantu.
Salam,
Tri Mahardhika
Handy <[EMAIL PROTECTED]> wrote: Niat nya sih
biar bs digunakan lg dgn kondisi yg berbeda dan di sisi
aplikasi ketika ada perubahan tdk perlu redeploy.
Saya pernah coba bikin packet spt di bawah ini. Ketika di toad bs return
multi record walau pun tdk scr langsung, hasil query hrs di click lg br
return multi record. Tp di aplikasi gk bs melihat multi record tsb.
CREATE OR REPLACE PACKAGE pkg_multi_rec
AS
TYPE row_cursor is REF CURSOR;
FUNCTION fn_multi_rec(vndr in varchar2, t_name in varchar2) return
row_cursor;
END pkg_multi_rec;
/
CREATE OR REPLACE PACKAGE BODY pkg_multi_rec AS
FUNCTION fn_multi_rec(vndr in varchar2, t_name in varchar2)
return row_cursor
IS
c_return row_cursor;
BEGIN
OPEN c_return FOR
select type_name from TBL_REF_EQT_TYPES where VENDOR = vndr;
return c_return;
END;
end pkg_multi_rec;
--
Handy Sanjaya ST.
Operation Support System Engineer
PT Indonesia Comnets Plus (ICONPLN)
Indonesia Power Building 9th Floor
Jl Jendral Gatot Subroto Kav. 18
Jakarta 12950
email : [EMAIL PROTECTED]
web : www.iconpln.net.id
telp : 021 - 7532488 - ext : 156
[Non-text portions of this message have been removed]
---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check outnew cars at Yahoo! Autos.
[Non-text portions of this message have been removed]
