For execution of dynamic SQL in oralce

For execution of dynamic SQL in oralce

-- Execute dynamic SQL CREATE OR REPLACE FUNCTION fun_checkguaranteetype (TranId varchar2) return varchar2 is V_result varchar2 (1000); Begin declare zhiyavar NUMBER; diyavar NUMBER; dabaoren NUMBER; v_ SQL varchar2 (1000); -- defines type TYPE accountno_type IS TABLE OF actaccount. accountno % type index by BINARY_INTEGER; TYPE id_type is table of bizpawn. id % type index by BINARY_INTEGER; -- set column accountno accountno_type; id id_type; begin v_ SQL: = 'select p1.accountno accountno, pawn. id, pawn. nocredencemode from bizpawn pawn, ('; v_ SQL: = v_ SQL | 'select act. relationaccountid, act. accountno from actaccount act where act. id in '; v_ SQL: = v_ SQL |' (select trans. accountid from acttransaction trans where trans. id in ('| TranId |') p1 '; v_ SQL: = v_ SQL | 'where pawn. actaccountid = p1.relationaccountid '; -- EXECUTE the dynamic SQL execute immediate v_ SQL BULK COLLECT into accountno, id; -- for V1 IN 1 .. accountno. count loop -- get the value dbms_output ('accountno: '| accountno (v1) |' --- id: '| id (v1); end loop; return (V_result ); end; end fun_checkguaranteetype;