You Asked
hai
how to generate a text file
i need each column of a table to be displayed in a new line. ONE ROW AT A TIME. THE NEXT ROW TO DISPLAYED IN ANOTHER FILE.....
i tried but everything is coming like for example
7369,"SMITH",7902,17/12/1980
7369|SMITH|7902|17/12/1980
i need it get displayed like :
7369
SMITH
7902
17/12/1980
the code i used is as follows (THIS IS A SAMPLE CODE I USED FOR GENERATING A FILE WITH SPACES)
begin
htp.flush;
htp.init;
-- Set the MIME type
owa_util.mime_header( 'application/octet', FALSE );
-- Set the name of the file
htp.p('Content-Disposition: attachment; filename="CU.txt"');
-- Close the HTTP Header
owa_util.http_header_close;
-- Loop through all rows in cu
for x in (select SL_NO, FIRST_NAME,REF_NO,SYSDATE from EMP)
loop
-- Print out a portion of a row,
-- separated by commas and ended by a CR
htp.prn(rpad(x.SL_NO,21,' ')|| rpad(x.FIRST_NAME,42,' ')||
rpad(x.REF_NO,4,' ')|| rpad(x.SYSDATE,56,' ')|| CHR(13)||CHR(10));
end loop;
-- Send an error code so that the
-- rest of the HTML does not render
htmldb_application.g_unrecoverable_error := true;
end;
PLEASE DO HELP ME OUT. THANKS IN ADVANCE.
and Connor said...
Here's an example that will work with most queries. Just change the DBMS_OUTPUT to use UTL_FILE and you can direct the output to a text file
SQL> set serverout on size 999999
SQL> declare
2 p_query varchar2(32767) := q'{select * from scott.emp}';
3
4 l_theCursor integer default dbms_sql.open_cursor;
5 l_columnValue varchar2(4000);
6 l_status integer;
7 l_descTbl dbms_sql.desc_tab;
8 l_colCnt number;
9 n number := 0;
10 procedure p(msg varchar2) is
11 l varchar2(4000) := msg;
12 begin
13 while length(l) > 0 loop
14 dbms_output.put_line(substr(l,1,80));
15 l := substr(l,81);
16 end loop;
17 end;
18 begin
19 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
20 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
21
22 for i in 1 .. l_colCnt loop
23 dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
24 end loop;
25
26 l_status := dbms_sql.execute(l_theCursor);
27
28 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
29 for i in 1 .. l_colCnt loop
30 dbms_sql.column_value( l_theCursor, i, l_columnValue );
31 p( rpad( l_descTbl(i).col_name, 30 )
32 || ': ' ||
33 l_columnValue );
34 end loop;
35 dbms_output.put_line( '-----------------' );
36 n := n + 1;
37 end loop;
38 if n = 0 then
39 dbms_output.put_line( chr(10)||'No data found '||chr(10) );
40 end if;
41 end;
42 /
EMPNO : 7369
ENAME : SMITH
JOB : CLERK
MGR : 7902
HIREDATE : 17-DEC-80
SAL : 800
COMM :
DEPTNO : 20
-----------------
EMPNO : 7499
ENAME : ALLEN
JOB : SALESMAN
MGR : 7698
HIREDATE : 20-FEB-81
SAL : 1600
COMM : 300
DEPTNO : 30
-----------------
EMPNO : 7521
ENAME : WARD
JOB : SALESMAN
MGR : 7698
HIREDATE : 22-FEB-81
SAL : 1250
COMM : 500
DEPTNO : 30
-----------------
EMPNO : 7566
ENAME : JONES
JOB : MANAGER
MGR : 7839
HIREDATE : 02-APR-81
SAL : 2975
COMM :
DEPTNO : 20
-----------------
EMPNO : 7654
ENAME : MARTIN
JOB : SALESMAN
MGR : 7698
HIREDATE : 28-SEP-81
SAL : 1250
COMM : 1400
DEPTNO : 30
-----------------
EMPNO : 7698
ENAME : BLAKE
JOB : MANAGER
MGR : 7839
HIREDATE : 01-MAY-81
SAL : 2850
COMM :
DEPTNO : 30
-----------------
EMPNO : 7782
ENAME : CLARK
JOB : MANAGER
MGR : 7839
HIREDATE : 09-JUN-81
SAL : 2450
COMM :
DEPTNO : 10
-----------------
EMPNO : 7788
ENAME : SCOTT
JOB : ANALYST
MGR : 7566
HIREDATE : 09-DEC-82
SAL : 3000
COMM :
DEPTNO : 20
-----------------
EMPNO : 7839
ENAME : KING
JOB : PRESIDENT
MGR :
HIREDATE : 17-NOV-81
SAL : 5000
COMM :
DEPTNO : 10
-----------------
EMPNO : 7844
ENAME : TURNER
JOB : SALESMAN
MGR : 7698
HIREDATE : 08-SEP-81
SAL : 1500
COMM : 0
DEPTNO : 30
-----------------
EMPNO : 7876
ENAME : ADAMS
JOB : CLERK
MGR : 7788
HIREDATE : 12-JAN-83
SAL : 1100
COMM :
DEPTNO : 20
-----------------
EMPNO : 7900
ENAME : JAMES
JOB : CLERK
MGR : 7698
HIREDATE : 03-DEC-81
SAL : 950
COMM :
DEPTNO : 30
-----------------
EMPNO : 7902
ENAME : FORD
JOB : ANALYST
MGR : 7566
HIREDATE : 03-DEC-81
SAL : 3000
COMM :
DEPTNO : 20
-----------------
EMPNO : 7934
ENAME : MILLER
JOB : CLERK
MGR : 7782
HIREDATE : 23-JAN-82
SAL : 1300
COMM :
DEPTNO : 10
-----------------
PL/SQL procedure successfully completed.
SQL>
SQL>
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment