Home » SQL & PL/SQL » SQL & PL/SQL » use varchar2 value as cursor elements .. (10gXE, windowsXP)
icon9.gif  use varchar2 value as cursor elements .. [message #359682] Tue, 18 November 2008 00:29 Go to next message
elliekim
Messages: 8
Registered: November 2008
Junior Member
Good afternoon, everyone.
I'm a newbie in oracle & pl/sql, since last week, and I'm trying to create a code, export the whole tables in a DB.
So I've got the table names, column names... but I can't write out the data to the csv file.
Is there anyone can solve this problem?

below is my code.



SET SERVEROUTPUT ON

CREATE OR REPLACE PACKAGE PKG AS
	PROCEDURE EXPT;
	TYPE mycursor IS REF CURSOR;
	PROCEDURE GETREC(tmpfile IN UTL_FILE.FILE_TYPE, tmp_num IN NUMBER, colname IN VARCHAR2, curname IN VARCHAR2, tblname IN VARCHAR2);
	PROCEDURE GETTBL(tblname IN VARCHAR2);

END PKG;
/

CREATE OR REPLACE PACKAGE BODY PKG AS

	/*****************************************************************************************************
		# EXPORT THE WHOLE TABLES IN THIS DB TO CSV FILES # 
	*****************************************************************************************************/

	PROCEDURE EXPT AS

		tmp_tblname VARCHAR2(200);
		CURSOR tblname IS
			SELECT TABLE_NAME FROM TABS ORDER BY TABLE_NAME ASC;

	BEGIN
		FOR cur_tbl IN tblname LOOP
		
			tmp_tblname := cur_tbl.TABLE_NAME;
			GETTBL(tmp_tblname);

		END LOOP;

	EXCEPTION 
		WHEN OTHERS THEN 
			DBMS_OUTPUT.PUT_LINE('ERROR : TBL ERROR');
	END EXPT;

	/*****************************************************************************************************
		# GET TABLE NAME AND EXPORT TO CSV FILE # 
	*****************************************************************************************************/

	PROCEDURE GETTBL(tblname IN VARCHAR2) AS

		tmpfile UTL_FILE.FILE_TYPE;
		tmp_colname VARCHAR2(1000);
		del_colname VARCHAR2(50);
		first_colname VARCHAR2(500);
		tmp_curname VARCHAR2(1000);
		del_curname VARCHAR2(50);
		first_curname VARCHAR2(500);
		CURSOR colname IS
			SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = tblname;
		cur_rec mycursor;
		cur_cnt mycursor;
		tmp_rec clob;
		tmp_num number;

	BEGIN
			
		-- OPEN FILE
		tmpfile := UTL_FILE.fopen('ARK_DIR', tblname || '.csv', 'W', 32767);
		/*tmpfile := UTL_FILE.FOPEN(location => 'ARK_DIR', 
		filename => cur_tbl.TABLE_NAME || '.csv', 
		open_mode => 'w', max_linesize => 32767); */

		tmp_colname := 'TESTTEST'; -- meanless
		tmp_curname := 'TESTTEST'; -- meanless

		FOR cur_col IN colname LOOP
			tmp_colname := tmp_colname || ',' || cur_col.COLUMN_NAME;
			tmp_curname := tmp_curname || ',cur_rec.' || cur_col.COLUMN_NAME;
		END LOOP;

		del_colname := SUBSTR(tmp_colname, 1, INSTR(tmp_colname,',', 1, 1) );
		del_curname := SUBSTR(tmp_curname, 1, INSTR(tmp_curname,',', 1, 1) );
		tmp_colname := REPLACE(tmp_colname, del_colname);
		tmp_curname := REPLACE(tmp_curname, del_curname);

		UTL_FILE.PUT_LINE(tmpfile, tmp_colname);

		OPEN cur_cnt FOR 'SELECT COUNT(*) CNTS FROM ' || tblname;

			FETCH cur_cnt INTO tmp_num;

			IF tmp_num = 0 THEN
				UTL_FILE.FCLOSE(tmpfile);
				DBMS_OUTPUT.PUT_LINE('SELECT ' || tmp_colname || ' FROM ' || tblname || ' (NO RECORDS)');
			
			ELSIF tmp_num > 0 THEN
				GETREC(tmpfile, tmp_num, tmp_colname, tmp_curname, tblname);
				UTL_FILE.FCLOSE(tmpfile);
			
			ELSIF cur_cnt%NOTFOUND THEN
				UTL_FILE.FCLOSE(tmpfile);
				DBMS_OUTPUT.PUT_LINE('ERROR : NOT FOUND');

			ELSE
				DBMS_OUTPUT.PUT_LINE('tmp_num : ' || tmp_num);

			END IF; -- END OF [ IF tmp_num = ... ]

		CLOSE cur_cnt;

	-- EXCEPTIONS
	EXCEPTION 
		WHEN OTHERS THEN 
			UTL_FILE.FCLOSE(tmpfile);
			DBMS_OUTPUT.PUT_LINE('ERROR : getTBL ERROR');

	END GETTBL;

	/*****************************************************************************************************
		# GET RECORDS AND EXPORT TO CSV FILE # 
	*****************************************************************************************************/

	PROCEDURE GETREC(tmpfile IN UTL_FILE.FILE_TYPE, tmp_num IN NUMBER, colname IN VARCHAR2, curname IN VARCHAR2, tblname IN VARCHAR2) IS
		cur_rec mycursor;
	BEGIN
		OPEN cur_rec FOR 'SELECT ' || colname || ' FROM ' || tblname;
		-- REPEAT WRITING OUT TO CSV FILE
		FOR i IN 1 .. tmp_num LOOP
			-- CHECKING PRINTOUT
			DBMS_OUTPUT.PUT_LINE(tblname || ' : SELECT ' || colname || ' FROM ' || tblname);
			-- WRITEOUT TO THE CSV FILE
			UTL_FILE.PUT_LINE(tmpfile, curname);
			----------------------------------------------------------------------
			----------------------------------------------------------------------
			--- Here,  I wans to writeout datas to the file.
			----------------------------------------------------------------------
			----------------------------------------------------------------------
			--EXIT WHEN cur_rec%NOTFOUND;
		END LOOP; -- END OF [FOR i IN 1 .. tmp_num LOOP]
		CLOSE cur_rec;

	-- EXCEPTIONS
	EXCEPTION 
		WHEN OTHERS THEN 
			DBMS_OUTPUT.PUT_LINE('ERROR : GETREC ERROR');

	END GETREC;

END PKG;
/

[Updated on: Tue, 18 November 2008 00:33]

Report message to a moderator

Re: use varchar2 value as cursor elements .. [message #359693 is a reply to message #359682] Tue, 18 November 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why can't you?

Regards
Michel
Re: use varchar2 value as cursor elements .. [message #359694 is a reply to message #359693] Tue, 18 November 2008 00:51 Go to previous messageGo to next message
elliekim
Messages: 8
Registered: November 2008
Junior Member
I wanted to write out datas, but the code can only write out the headers to the file. T_T
Re: use varchar2 value as cursor elements .. [message #359724 is a reply to message #359694] Tue, 18 November 2008 01:30 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You never actually fetch your data.
Previous Topic: checking the flag and comparing
Next Topic: how to impose the check constraint
Goto Forum:
  


Current Time: Sat Dec 03 07:46:28 CST 2016

Total time taken to generate the page: 0.10543 seconds