Доброй ночи уважаемые знатоки Oracle !
Oracle 9I
Пытаюсь из динамического курсора вернуть строки через функцию pipelined
и тип AnyDataSet
Функция в целом работает (если возвращать другой тип, скажем Integer)
Но с диррективой
pipe row(RowSave) - дает ошибку
Error: PLS-00801: внутр.ошибка [74303]Подскажите кто-чем может !
Заранее благодарен и признателен !
create or replace function SqlView(iText in String:=Null) return /*Integer*/ AnyDataSet pipelined
as
type tRowRead is record
(Chr_Column Varchar2(32767),
Num_Column Number,
Date_Column Date,
Clob_Column Clob,
Raw_Column Raw(32767),
Raw_Error Number,
Raw_Length Integer,
Ids_Column Interval Day To Second,
Iym_Column Interval Year To Month,
Ts_Column TimeStamp,
Tstz_Column TimeStamp with time zone,
Tsltz_Column TimeStamp with local time zone,
Cvl_Offset Integer:=0,
Cvl_Length Integer
);
I Integer;
SqlExe Integer;
DbType Integer;
Fields AnyType;
RowRead tRowRead;
RowSave AnyDataSet;
Describe Dbms_Sql.Desc_Tab2;
begin
SqlExe:=Dbms_Sql.Open_Cursor;
Dbms_Sql.Parse(SqlExe,iText,Dbms_Sql.Native);
Dbms_Sql.Describe_Columns2(SqlExe,I,Describe);
AnyType.BeginCreate(Dbms_Types.TypeCode_Object,Fields);
For I in Describe.First .. Describe.Last loop
Case when Describe(I).Col_Type in(1,96,11,208) then
Dbms_Sql.Define_Column(SqlExe,I,'',32767);
DbType:=Dbms_Types.TypeCode_Varchar2;
when Describe(I).Col_Type= 2 then
Dbms_Sql.Define_Column(SqlExe,I,To_Number(Null));
DbType:=Dbms_Types.TypeCode_Number;
when Describe(I).Col_Type= 8 then
Dbms_Sql.Define_Column_Long(SqlExe,I);
DbType:=Dbms_Types.TypeCode_Clob;
when Describe(I).Col_Type=112 then
Dbms_Sql.Define_Column(SqlExe,I,To_Clob(Null));
DbType:=Dbms_Types.TypeCode_Clob;
when Describe(I).Col_Type= 12 then
Dbms_Sql.Define_Column(SqlExe,I,To_Date(Null));
DbType:=Dbms_Types.TypeCode_Date;
when Describe(I).Col_Type= 23 then
Dbms_Sql.Define_Column_Raw(SqlExe,I,To_Raw(Null),Describe(I).Col_Max_Len);
DbType:=Dbms_Types.TypeCode_Raw;
when Describe(I).Col_Type=180 then
Dbms_Sql.Define_Column(SqlExe,I,To_TimeStamp(Null));
DbType:=Dbms_Types.TypeCode_TimeStamp;
when Describe(I).Col_Type=181 then
Dbms_Sql.Define_Column(SqlExe,I,To_TimeStamp_Tz(Null));
DbType:=Dbms_Types.TypeCode_TimeStamp_Tz;
when Describe(I).Col_Type=231 then
Dbms_Sql.Define_Column(SqlExe,I,To_TimeStamp_Tz(Null));
DbType:=Dbms_Types.TypeCode_TimeStamp_Ltz;
when Describe(I).Col_Type=182 then
Dbms_Sql.Define_Column(SqlExe,I,To_YmInterval(Null));
DbType:=Dbms_Types.TypeCode_Interval_Ym;
when Describe(I).Col_Type=183 then
Dbms_Sql.Define_Column(SqlExe,I,To_DsInterval(Null));
DbType:=Dbms_Types.TypeCode_Interval_Ds;
End case;
Fields.AddAttr(Describe(I).Col_Name,DbType,Describe(I).Col_Precision,Describe(I).Col_Scale,
Case when Describe(I).Col_Type=11 then 32 else Describe(I).Col_Max_Len End,
Describe(I).Col_Charsetid,Describe(I).Col_CharSetForm);
End loop;
Fields.EndCreate;
I:=Dbms_Sql.Execute(SqlExe);
While Dbms_Sql.Fetch_Rows(SqlExe)>0 Loop
AnyDataSet.BeginCreate(Dbms_Types.TypeCode_Object,Fields,RowSave);
RowSave.AddInstance();
RowSave.PieceWise();
For I in Describe.First .. Describe.Last loop
Case when Describe(I).Col_Type in(1,96,11,208) then
Dbms_Sql.Column_Value(SqlExe,I,RowRead.Chr_Column);
RowSave.SetVarchar2(RowRead.Chr_Column);
when Describe(I).Col_Type= 2 then
Dbms_Sql.Column_Value(SqlExe,I,RowRead.Num_Column);
RowSave.SetNumber(RowRead.Num_Column);
when Describe(I).Col_Type= 8 then
Loop
Dbms_Sql.Column_Value_Long(SqlExe,I,32767,RowRead.Cvl_Offset,RowRead.Chr_Column,RowRead.Cvl_Length);
RowRead.Clob_Column:=RowRead.Clob_Column||RowRead.Chr_Column;
RowRead.Cvl_Offset:=RowRead.Cvl_Offset+32767;
exit when RowRead.Cvl_Length<32767;
End loop;
RowSave.SetClob(RowRead.Clob_Column);
when Describe(I).Col_Type=112 then
Dbms_Sql.Column_Value(SqlExe,I,RowRead.Clob_Column);
RowSave.SetClob(RowRead.Clob_Column);
when Describe(I).Col_Type= 12 then
Dbms_Sql.Column_Value(SqlExe,I,RowRead.Date_Column);
RowSave.SetDate(RowRead.Date_Column);
when Describe(I).Col_Type= 23 then
Dbms_Sql.Column_Value_Raw(SqlExe,I,RowRead.Raw_Column,RowRead.Raw_Error,RowRead.Raw_Length);
RowSave.SetRaw(RowRead.Raw_Column);
when Describe(I).Col_Type=180 then
Dbms_Sql.Column_Value(SqlExe,I,RowRead.Ts_Column);
RowSave.SetTimeStamp(RowRead.Ts_Column);
when Describe(I).Col_Type=181 then
Dbms_Sql.Column_Value(SqlExe,I,RowRead.Tstz_Column);
RowSave.SetTimeStampTz(RowRead.Tstz_Column);
when Describe(I).Col_Type=231 then
Dbms_Sql.Column_Value(SqlExe,I,RowRead.Tsltz_Column);
RowSave.SetTimeStampLTz(RowRead.Tsltz_Column);
when Describe(I).Col_Type=182 then
Dbms_Sql.Column_Value(SqlExe,I,RowRead.Iym_Column);
RowSave.SetIntervalYM(RowRead.Iym_Column);
when Describe(I).Col_Type=183 then
Dbms_Sql.Column_Value(SqlExe,I,RowRead.Ids_Column);
RowSave.SetIntervalDs(RowRead.Ids_Column);
End case;
End Loop;
RowSave.EndCreate();
pipe row (RowSave);
End Loop;
Dbms_Sql.Close_Cursor(SqlExe);
dbms_output.put_line('Quit');
return /*Describe.Last*/;
end SqlView;