Skip to Main Content

Programming Languages & Frameworks

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

dynamically convert oracle object type to plsql record type

Sithra kalJan 25 2024

I want to convert given sql object type to plsql record type dynamically. i have different plsql packages. each packages has different record type structure. so i have created sql object type/collection with structure similar to plsql record type structure. i want to convert incoming object type to corresponding record type. how to do it without referring column names explicitly. because each record type might have different field and different structure. i want to write one generic wrapper procedure to convert incoming sql object type to plsql record type. can someone help?

CREATE OR REPLACE procedure convertObjecttoRecordType012424 (sPackageName varchar2,sProcName varchar2,sPLsqlRecName varchar2,sPlsqlTableName varchar2,p_object Prov_Table_obj) is

v\_sql varchar2(32767);

BEGIN

  v\_sql := 'DECLARE ' ||

         '   v\_table ' || sPackageName || '.' || sPlsqlTableName || '; ' ||

         ' i integer ;' ||

         'BEGIN ' ||

          ' i := 1;'||

         '   FOR rec IN (SELECT \* FROM TABLE(:obj)) LOOP' ||

         '      v\_table(i).recordid := rec.recordid; ' ||

' v_table(i).firstname := rec.firstname; ' ||

' v_table(i).lastname := rec.lastname; ' ||

' v_table(i).address := rec.address; ' ||

         ' i := i + 1; '||

         '   END LOOP; ' ||

         sPackageName || '.' || sProcName || '(v\_table); ' || -- Pass v\_table as a parameter

         'END;';

-- Execute the dynamic SQL with the object parameter

EXECUTE IMMEDIATE v\_sql USING p\_object ; -- Bind v\_table as OUT parameter

end convertObjecttoRecordType012424;

Comments
Post Details
Added on Jan 25 2024
0 comments
31 views