Inserting from TABLE of VARRAY

I am writing a reporting solution that allows users to make selection criteria of columns and filters and sorting options. I collect that data and create a dynamic SQL statement. I am using 10.2.0.3.

All of this works well if using the current database as the source of data. We are in the process of extending the solution to cover databases in Sybase as well. I cannot figure out how to make outer joins work with the generic connectivity I am using, so I am exploring the use of the dbms_hs_passthrough. I have created a wrapper function for this that returns a fairly generic collection.

My type:
CREATE OR REPLACE TYPE results IS VARRAY(100) OF VARCHAR2(1024);

My collection of that type:
CREATE OR REPLACE TYPE result_tbl IS TABLE OF results;

My wrapper function:
CREATE OR REPLACE PACKAGE passthrough_sql IS

    FUNCTION get_results( p_query              IN CLOB,
                          p_database_link_name IN VARCHAR2,
                          p_column_count       IN PLS_INTEGER )

END passthrough_sql;

This function is providing the data exactly as expected and can be displayed using:
DECLARE     cols PLS_INTEGER := 4;
    mySql VARCHAR2(1000) := 'SELECT l.lsheet_id, e.lsheet_id, l.dob, l.receive_dt ' ||

'FROM landsheet l,

'                              ||
                                   'landsheet_ext e
'                              ||


                            'WHERE  l.pat_id   = 500000019

'                        ||


                            'AND    l.logsheet_id *= e.logsheet_id';

    res result_tbl;
    l_ins VARCHAR2(32676);

BEGIN     res := passthrough_sql.get_results( mySql, 'mlink_0522', cols );

    FOR i IN res.first .. res.last LOOP

        FOR c IN 1 .. cols LOOP
            dbms_output.put_line( res(i)(c) );

        END LOOP;

    END LOOP;     res.delete;

END;
/

However, when I try to use that collection to insert, I don't seem to be able to find the right syntax. My target table: create table DATA_EXPORT.T
(

  C1 VARCHAR2(1024),
  C2 VARCHAR2(1024),
  C3 VARCHAR2(1024),
  C4 VARCHAR2(1024)

DECLARE     cols PLS_INTEGER := 4;
    mySql VARCHAR2(1000) := 'SELECT l.lsheet_id, e.lsheet_id, l.dob, l.receive_dt ' ||

'FROM landsheet l,

'                              ||
                                   'landsheet_ext e
'                              ||


                            'WHERE  l.pat_id   = 500000019

'                        ||


                            'AND    l.logsheet_id *= e.logsheet_id';

    res result_tbl;
    l_ins VARCHAR2(32676);

BEGIN     res := passthrough_sql.get_results( mySql, 'mlink_0522', cols );

    INSERT INTO data_export.t( c1, c2, c3, c4 )     SELECT tbl.*
    FROM table(cast( res AS result_tbl )) tbl;

    res.delete;

END;
/
When I run this, I get ORA-00947: Not enough values.

when I change to only insert a single column:

    INSERT INTO data_export.t( c1 )
    SELECT tbl(results(1))
    FROM table(cast( res AS result_tbl)) tbl;

I get ORA-00904 invalid identifier "tbl"

I have also tried SELECT tbl.results, tbl(1) (and others) with similar results.

I dont want to have to write as single line inserts: FOR i in res.first .. res.last LOOP

    INSERT INTO data_export.t( c1, c2, c3, c4 )     VALUES (res(i)(1), (res(i)(2), (res(i)(3), (res(i)(4) );

END LOOP; All the documentation and forum issues dealing with this type of thing seem to only use a single element array and using "column_value". I tried combinations using that too, but no luck. Is there a way to do this other than individual inserts for each "row" of the collection?

Thanks.

PS. Utlimately, the inserting will be done dynamically as well, as at compile time, I do not know the number of columns involved. The above is an attempt at proof of concept. Received on Tue Mar 04 2008 - 17:30:36 CST