Passing a dynamic value to "p_cols" parameter of getRows

[cristabardilla]

Hi, First, thank you for this very useful program. Just want to ask if passing a dynamic value to "p_cols" parameter is accepted, I encounter this error when I pass a dynamic value to "p_cols". Error(90,5): PL/SQL: ORA-20722: Error at position 1 : unexpected symbol '' instead of ''

ex: insert into cut_batchpay_gtmp (
select t.* from table( exceltable.getrows( p_file => v_data --, p_cols => p_column , p_cols => q'{"TRAN_NO" VARCHAR2(20), "MAIN_ACCT_NO" VARCHAR2(20), "BENEFICIARY_NAME" VARCHAR2(150), "BENEFICIARY_ACCOUNT" VARCHAR2(20), "REFERENCE_NO" VARCHAR2(20), "TRAN_AMOUNT" NUMBER}' , p_skip => 0 , p_line_term => v_eol_pattern , p_field_sep => v_field_separator ) ) t );

Thanks again.

Cris Tabardilla

[mbleron]

Hi Cris,

The list of resulting columns must be known at parse time, just like a regular SQL query. That means we can't pass the column list through a variable.

However, we can make the whole query dynamic and substitute the column list literally into the SQL query string, and then execute it using built-in dynamic SQL utilities (EXECUTE IMMEDIATE or DBMS_SQL).

Function getCursor may also be used to get a REF cursor. You can pass the column list through a variable this way.