I am having a table with number , float, double of 100 columns
need to read the table value and return as blob
so i have used utl_raw.CAST_FROM_
something like this
i have used loop to read each column data type and used bind variable to insert
----------------------------------------------------------------------------------------
TYPE varColtypelist is varray(100) of NUMBER(20);
collist varColtypelist;
TYPE varColLenlist is varray(100) of number(20);
byteLenList varColLenlist;
Select CASE WHEN data_type ='BINARY_FLOAT' THEN 1
WHEN data_type ='BINARY_DOUBLE' THEN 2
WHEN data_type ='NUMBER' THEN 3 END bulk collect into collist
from all_tab_columns where table_name=UPPER(Table_Name)
ORDER BY COLUMN_ID ;
v_cursor := DBMS_SQL.OPEN_CURSOR;
statment :='Select all columns from mytable order by c1 desc ' and rownum between 5000 to 10000
execute immediate 'Select byte_info from my_byte_info where id=1'
bulk collect into byteLenList ;
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.parse (v_cursor, statment, DBMS_SQL.native);
FOR col_ind IN 1 .. 100--(100 is 100 columns in that table)
LOOP
data_type := collist(col_ind);
if data_type =1 THEN
DBMS_SQL.define_column (v_cursor, col_ind, flid);
ELSIF data_type =2 THEN
DBMS_SQL.define_column (v_cursor, col_ind, dblid);
ELSIF data_type =3 THEN
DBMS_SQL.define_column (v_cursor, col_ind, nid);
END IF;
END LOOP;
dumy := DBMS_SQL.Execute (v_cursor);
LOOP
EXIT WHEN DBMS_SQL.FETCH_ROWS (v_cursor) = 0;
FOR i IN 1..l_max LOOP
data_type := collist(i);
ncollength := byteLenList(i);
if data_type =1 THEN
BEGIN
DBMS_SQL.column_value (v_cursor, i, flid);
value := utl_raw.cast_from_BINARY_FLOAT( flid);
END;
end if;
if data_type =2 THEN
BEGIN
DBMS_SQL.column_value (v_cursor, i, dblid);
value := utl_raw.cast_from_BINARY_DOUBLE( dblid);
END;
end if;
if data_type =3 THEN
BEGIN
DBMS_SQL.column_value (v_cursor, i, nid);
value := utl_raw.CAST_FROM_BINARY_INTEGER( nid);
END;
end if;
IF nNewRecord = 0 then
buffer1 := utl_raw.cast_to_varchar2(dbms_lob.substr(value));
dbms_lob.writeappend( l_out,ncollength,buffer1 );
End if;
IF nNewRecord = 1 then
nNewRecord := 0;
Select (utl_raw.cast_to_varchar2(dbms_lob.substr(var))) into l_out from dual;
end if;
end loop;
END LOOP;
DBMS_SQL.CLOSE_CURSOR (v_cursor);
--------------------------------------------------
it is something like
SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.CAST_FROM_BINARY_INTEGER(int_column))) from mytable
SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.cast_from_BINARY_DOUBLE(double_coulmn))) from mytable
SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.cast_from_BINARY_FLOAT(float_column))) from mytable
SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.CAST_FROM_BINARY_INTEGER(int_column))) from mytable
SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.cast_from_BINARY_DOUBLE(double_coulmn))) from mytable
SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.cast_from_BINARY_FLOAT(float_column))) from mytable
SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.cast_from_BINARY_FLOAT(float_column))) from mytable
SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.CAST_FROM_BINARY_INTEGER(int_column))) from mytable
SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.cast_from_BINARY_FLOAT(float_column))) from mytable
SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.CAST_FROM_BINARY_INTEGER(int_column))) from mytable
SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.cast_from_BINARY_DOUBLE(double_coulmn))) from mytable
SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.cast_from_BINARY_FLOAT(float_column))) from mytable
but it is time consuming taking more time to write and apennd
can we do it in alternate way.