Home » SQL & PL/SQL » SQL & PL/SQL » Dump table data in text files
Dump table data in text files [message #207651] Wed, 06 December 2006 07:33 Go to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Hi pals,

I have 2 scripts for two specific purposes.
But i am facing problem with the second script.
Can anyone help us. the problem is that, i must able to create a ROWTYPE var dynamically.

Can anyone help me out.

script 1) is used to generate SELECT STMTS Used to generate data into flat files

set serveroutput on
set linesize 1000
set pagesize 1000
declare
l_tname varchar2(100);
l_colname varchar2(100);
l_qry varchar2(32000):='SELECT ';
begin
for i in (select tname from tab) loop
l_tname:=i.tname;
for j in (select column_name from user_tab_columns where table_name=l_tname) loop
l_colname:=j.column_name;
l_qry:=l_qry||l_colname||'|';
end loop;
-- l_qry:=substr(l_qry,1,length(l_qry)-1);
l_qry:=l_qry||' FROM '||l_tname||';';
dbms_output.put_line(l_qry);
l_qry:='SELECT ';
end loop; -- eof outterloop
end;
/


output
-------------
SELECT ENAME|JOB|SAL|COMM| FROM BONUS;
SELECT DEPTNO|DNAME|LOC| FROM DEPT;
SELECT EMPNO|ENAME|JOB|MGR|HIREDATE|SAL|COMM|DEPTNO| FROM EMP;
SELECT GRADE|LOSAL|HISAL| FROM SALGRADE;



******************************************************************************************************
script 2 : This below script must need to create datafiles with filename as table_name concatinated with sysdate.




step 1)create or replace directory temp as 'c:\temp'; -- Assuming that he has create directory
step 2)now i'll create a directory at OS level on Windows
step 3)Now i'll execute the below PL/SQL script
which will create files while Tablename concatinated with sysdate in that dir

The Problem i am facing in the below script is that i am not able create the a rowtype var.
The error line is appended with astrisks symbols '********************'


declare
l_tname varchar2(100);
l_colname varchar2(100);
l_qry varchar2(32000):='SELECT ';
p_file utl_file.file_type;
p_row p_cursr%ROWTYPE;
p_dt VARCHAR2(20);
p_filename VARCHAR2(50);
begin
for i in (select tname from tab) loop
l_tname:=i.tname;
for j in (select column_name from user_tab_columns where table_name=l_tname) loop
l_colname:=j.column_name;
l_qry:=l_qry||l_colname||',';
end loop;
l_qry:=substr(l_qry,1,length(l_qry)-1);
l_qry:=l_qry||' FROM '||l_tname||';';
dbms_output.put_line(l_qry);

FOR k IN l_qry LOOP
p_filename:=l_tname;
p_row k%ROWTYPE; ****************************
SELECT TO_CHAR(SYSDATE,'MMDDYYYY') INTO p_dt FROM dual;
p_filename:=p_filename||p_dt||'.txt';
p_file:= utl_file.fopen ('TMP_DIR',p_filename,'W');
FETCH p_cursr INTO p_row;
utl_file.put_line(p_file,p_row.str);
END LOOP;
utl_file.fclose(p_file);
CLOSE p_cursr;
l_qry:='SELECT ';
end loop; -- eof outterloop
exception
when others then
dbms_output.put_line(SQLERRM);
end;


Thanks and Regards,
frank
Re: Dump table data in text files [message #207653 is a reply to message #207651] Wed, 06 December 2006 07:43 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You'll have to DECLARE it - not as 'K', but 'L_QRY' rowtype. Here is a short example:
DECLARE
  CURSOR l_qry IS SELECT dname FROM DEPT;
BEGIN
  FOR k IN l_qry LOOP
    DECLARE
      p_row l_qry%ROWTYPE;
    BEGIN
      dbms_output.put_line(k.dname);
    END;	  
  END LOOP;
END;
/
Re: Dump table data in text files [message #208035 is a reply to message #207653] Thu, 07 December 2006 22:30 Go to previous messageGo to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Thanks Buddy
Re: Dump table data in text files [message #208061 is a reply to message #207651] Thu, 07 December 2006 23:57 Go to previous message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Hi pals,

Hi bruno,

Cant we associate a SELECT stmt dynamically to a cursor.
I am getting the problem at the line appended with --- Here it is ----.

Can anyone provide some input.


declare
l_tname varchar2(100);
l_colname varchar2(100);
l_qry varchar2(32000):='SELECT ';
p_file utl_file.file_type;
p_row p_cursr%ROWTYPE;
p_dt VARCHAR2(20);
p_filename VARCHAR2(50);
begin
for i in (select tname from tab) loop
l_tname:=i.tname;
for j in (select column_name from user_tab_columns where table_name=l_tname) loop
l_colname:=j.column_name;
l_qry:=l_qry||l_colname||',';
end loop; -- eof inner loop
l_qry:=substr(l_qry,1,length(l_qry)-1);
l_qry:=l_qry||' FROM '||l_tname||';';
dbms_output.put_line(l_qry);
declare
CURSOR p_cursr IS l_qry; ------- Here it is -- p_row p_cursr%ROWTYPE;
begin
p_filename:=l_tname;
SELECT TO_CHAR(SYSDATE,'MMDDYYYY') INTO p_dt FROM dual;
p_filename:=p_filename||p_dt||'.txt';
p_file:= utl_file.fopen ('TMP_DIR',p_filename,'W');
open p_cursr;
loop
FETCH p_cursr INTO p_row;
IF(p_cursr%NOTFOUND) THEN
EXIT;
END IF;
utl_file.put_line(p_file,p_row.str);
end loop;
end; -- eof declare - begin end
utl_file.fclose(p_file);
CLOSE p_cursr;
l_qry:='SELECT ';
end loop; -- eof outterloop
Exception
when others then
dbms_output.put_line(SQLERRM);
end;


Thanks in advance,
franky
Previous Topic: create index on a table with VARRAY data type
Next Topic: How to get data from another table (if its there)
Goto Forum:
  


Current Time: Sun Dec 11 04:02:29 CST 2016

Total time taken to generate the page: 0.21721 seconds