hi all,
this is the code to get the details of a table with comma delimiter.
create or replace procedure tname(table_name in varchar2) is
l_bool BOOLEAN;
irows INTEGER := 1;
buffer clob;
f utl_file.file_type;
BEGIN
f := utl_file.fopen('SAMPLE','tablename.txt','W');
OWA.cgi_var_name (1) := 1;
OWA.cgi_var_val (1) := 1;
OWA.init_cgi_env (1, OWA.cgi_var_name, OWA.cgi_var_val);
HTP.adddefaulthtmlhdr (FALSE);
l_bool :=
OWA_UTIL.tableprint (ctable => table_name,
cattributes => '1',
ntable_type => OWA_UTIL.pre_table,
ccolumns => '*',
cclauses => '',
ccol_aliases => '//////',
nrow_min => 0,
nrow_max => 50
);
WHILE (irows != 0)
LOOP
buffer := HTP.get_line (irows);
buffer := LTRIM (RTRIM (buffer, '|' || CHR (10)), '|');
IF buffer NOT LIKE '%-----------------%'
AND buffer NOT LIKE '%//////%'
AND REPLACE (buffer, CHR (10)) NOT IN ('<PRE>', '</PRE>')
THEN
buffer := TRIM (REPLACE (buffer, '|', ','));
OWA_PATTERN.CHANGE (buffer, ' {0,}, {0,}', ',', 'ig');
utl_file.put_line(f,buffer);
DBMS_OUTPUT.put_line (buffer);
END IF;
END LOOP;
END;
this procedure is working fine with tables small in size.
when i go for tables with huge no .of columns, the output is not in desired shape as follows.
INTRODUCER,TEXT,LEGAL.ID,REVIEW.FREQUENCY,BIRTH.INCORP.DATE,GLOBAL.CUSTOMER,CUST
OMER.LIABILITY,LANGUAGE,POSTING.RESTRICT,DISPO.OFFICER,COMPANY.BOOK,CONFID.TXT,D
ISPO.EXEMPT,ISSUE.CHEQUES,CLS.CPARTY,FX.COMM.GROUP.ID,RESIDEN
CE.REGION,ASSET.CLASS,CUSTOMER.RATING,NO.UPDATE.CRM,TITLE,GIVEN.NAMES,FAMILY.NAM
E,GENDER,DATE.OF.BIRTH,MARITAL.STATUS,NO.OF.DEPENDENTS,PHONE.1,SMS.1,EMAIL.1,EMP
LOYMENT.STAT,OCCUPATION,EMPLOYERS.NAME,EMPLOYERS.AD,EMPLOY
ERS.BUSS,EMPLOYMENT.STAR,CUSTOMER.CURREN,SALARY,ANNUAL.BONUS,SALARY.DATE.FRE,NET
.MONTHLY.IN,NET.MONTHLY.OUT,RESIDENCE.STATU,RESIDENCE.TYPE,RESIDENCE.SINCE,RESID
ENCE.VALUE,MORTGAGE.AMT,OTHER.FIN.REL,OTHER.FIN.INST,COMM.TYPE,
PREF.CHANNEL,INTERESTS,FAX.1,PREVIOUS.NAME,CHANGE.DATE,CHANGE.REASON,CUSTOMER.SI
NCE,CUST.GENDER,CUST.RES.PHO,CUST.EMAIL.I,CUST.TITLE,LEGAL.DOC.NA,LEGAL.DOC.ID,L
EGAL.ISS.AU,LEGAL.ISS.DT,LEGAL.EXP.DT,CUST.OFF.PHO,CUST.MOB.P
HO,OVERRIDE,RECORD.STATUS,CURR.NO,INPUTTER,DATE.TIME,AUTHORISER,CO.CODE,DEPT.COD
E,AUDITOR.CODE,AUDIT.DATE.TIME
11121,AVINASH,AVIN,NASH,,,,,,,1001,6,60,1000,999,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,
,,,,,,,,,,
11122,SRSK,SENTHIL,KUMAR,,,,,,,1001,7,70,1000,999,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,
,,,,,,,,,,
11115,KART,JKARTHIK,JAYKAY,,,,,,,1001,1,10,1000,999,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,
,,,,,,,,,,
11123,DEVARAJAN,DEVA,RAJ,,,,,,,1001,8,80,1000,999,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,
,,,,,,,,,,
11124,GLENN,RICKY,RICKYMAN,,,,,,,1001,9,90,1000,999,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,
,,,,,,,,,,
11125,SELVA,PERUMAL,MAAL,,,,,,,1001,10,11,1000,999,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,
,,,,,,,,,,
11116,HARRY,HARISH,HARRYBOY,,,,,,,1001,2,20,1000,999,,,,
BEGIN tname('customer'); END;
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at "XMLUSER.TNAME", line 35
ORA-06512: at line 1
expected output:
11121,AVINASH,AVIN,NASH,,,,,,,1001,6,60,1000,999,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
11122,SRSK,SENTHIL,KUMAR,,,,,,,1001,7,70,1000,999,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
11115,KART,JKARTHIK,JAYKAY,,,,,,,1001,1,10,1000,999,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
the variable which carries the records is of CLOB datatype,but still buffer overflow takes place.
can anybody help me.
thanks in advance
karthik.J