Hi,
is there any way to pass some optional arguments of type dbms_sql (namely dbms_sql.number_table, dbms_sql.varchar2_table, dbms_sql.date_table) to procedures?
For example, i have a package(packageA) which accepts a sql script, it executes it and should output the result set into an appropriate set of arrays.
Also, there is another package(packageB) which should accept these set of arrays and outputs them in excel file.
So, there is packageA,
create or replace package packageA
is
procedure get_rows(i_sql in clob, o_eq_type_vtab out dbms_sql.varchar2_table ??????) is
eq_type_vtab dbms_sql.varchar2_table;
......
......
begin
<code to execute the i_sql statement and outputs the results to o_eq_type_vtab structure.>
end get_rows;
end packageA ;
... and packageB
create or replace package packageB
is
procedure outputs_rows(i_eq_type_vtab in dbms_sql.varchar2_table ??????) is
begin
<code to output the i_eq_type_vtab array to excel>
end outputs_rows;
end packageB;
If the i_eq_type_vtab, o_eq_type_vtab parameters of the two procedures above were of type number/varchar2 e.t.c then
in order to make these arguments optional then we'd write "default 0, ' ' "(replacing the ???? above in the signatures of the two routines) but in this particular case (dbms_sql.varchar2_table)
what should we write...???
Note: I use oracle 10g v1.
Thanks a lot
Sim