| Help needed very badly!!!! [message #7387] |
Wed, 11 June 2003 02:12  |
sachin kumar gupta
Messages: 157
Registered: March 2003
|
Senior Member |
|
|
Hi,
Kindly go through this script & suggest me the optimal solution:
set head off
set pause on
set pagesize 0
set linesize 500
set recsep OFF
clear buffer
clear breaks
clear columns
set serveroutput ON size 1000000
declare
ans varchar2(32767);
cursor col_cursor is
select column_name from dba_tab_columns where
table_name='RT12' and column_id <=22 order by column_id;
var_col_cursor col_cursor%ROWTYPE;
begin
ans:='';
open col_cursor;
loop
fetch col_cursor into var_col_cursor;
exit when col_cursor%NOTFOUND;
/* delete from query1; */
/* insert into query1 select colnames || 'A' from query1;*/
ans := ans || var_col_cursor.column_name || ',';
end loop;
dbms_output.put_line(ans);
close col_cursor;
end;
/
set head on
set pagesize 23
set linesize 80
set recsep WRAP
set serveroutput OFF
Now O/P is:
SQL> @query1
ECC,YYYYMM,CETH,UQC,MAJOR_PROD_CODE,MINOR_PROD_CODE,DATA_PRD_TYPE,BREAKUP_RECORD_NO,RT_TYPE,RET_NO,RET_PRD_TYPE,INPUT_CLEARANCE,PROD_DESC,QTY_OB,QTY_MNF,QTY_RCVD_TYPE_D_A,QTY_RCVD_TYPE,QTY_RCVD,QTY_CLR_DP_TYPE_D_A,QTY_CLR_DP_TYPE,QTY_CLR_DP,ASS_VAL_DP,
PL/SQL procedure successfully completed.
In above script, if I change the column_id<=23, as shown below:
cursor col_cursor is
select column_name from dba_tab_columns where
table_name='RT12' and column_id <=23 order by column_id;
then it gives following error:
SQL> @query1
declare
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 99
ORA-06512: at "SYS.DBMS_OUTPUT", line 65
ORA-06512: at line 18
I think 'ans' variable's storage limit is exceeding, even long datatype for 'ans' is
also not working.
My goal is to retrieve large information in strictly a single row & display it as O/P.
The 'ans' variable that I am using is unable to hold this large row, that's what I think!!
Even I m trying for clob datatype for 'ans'. But unfortunately the data type for
'ans' variable is mismatching with that of retrieving column.
Kindly suggest any solution for displaying larger information in a single row.
I am using Oracle 8i(8.1.5.0.0).
Any help highly appreciated. Thanks in Advance.
Sachin Kumar Gupta.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
