SQL & PL/SQL

Hi All,

I have a data set with more than 1000 columns in the form of a Select Statement that I am trying to load into memory.

For example below code works.

CREATE OR REPLACE TYPE obj_typ1 AS OBJECT (col1 number, col2 NUMBER);
CREATE OR REPLACE TYPE col_typ1 AS table OF obj_typ1;
DECLARE
var_typ col_typ1;
BEGIN
  SELECT obj_typ1(1,2) INTO var_typ FROM (SELECT 1,2 from dual);
END;

When I increase the number of columns involved to more than 1000, the SELECT INTO fails with ORA--00939 - too many arguments for function ERROR.

CREATE OR REPLACE TYPE obj_typ1 AS OBJECT (col1 number, col2 NUMBER......... col1000 NUMBER);
CREATE OR REPLACE TYPE col_typ1 AS table OF obj_typ1;
DECLARE
var_typ col_typ1;
BEGIN
  SELECT obj_typ1(1,2.....1000) INTO var_typ FROM dual;
END;

This is because when invoking the Type Constructor method the arguments cannot exceed 999 even if the Object type has more than 999 attributes. I will have to try and do a PL/SQL assignment now like

var_typ := col_typ(obj_typ(1,2,...,999,1000)); 

But my source data set that I want to load into memory is in the form of a Select Statement. Please let me know on how this could be accomplished.

Thanks!