Home » SQL & PL/SQL » SQL & PL/SQL » utl_file
utl_file [message #289740] Tue, 25 December 2007 23:47 Go to next message
piscean_n
Messages: 36
Registered: December 2007
Member
I want to display columname datatype and datalength of table..
into a flat file.
so i'am using utl_file package for it..

i used query:
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM cols WHERE table_name = 'RQ_SERVICES'
ORDER BY column_id

Since it is a view so m not getting any outputs written to file.
can u please help me how to write data from view to a file using utl_file or
is there any other way for display meta details of a table like columnname,data type,data length.

Thanks in advance.
Re: utl_file [message #289742 is a reply to message #289740] Tue, 25 December 2007 23:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
Please review the forum guide at the top of the forum page or using the link below, then provide a copy and paste of your code and error message and/or results. It is unclear what you are trying to do and what the problem is. Are you saying that the query does not return any results? If so, then is there a table or view namesd "RQ_SERVICES" and do you have sufficient privileges to see it? Or are you saying that although you can get results from the select statement they are not output to a file using utl_file (with what code?) Are you just looking for any way to output the results to a flat file, not necessarily utl_file?


http://www.orafaq.com/forum/t/88153/0/
Re: utl_file [message #289743 is a reply to message #289742] Wed, 26 December 2007 00:03 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
Query is returning a result but those results are ot displaying in the file.

CREATE OR REPLACE PROCEDURE write_demo IS
  
  OutMetaFile UTL_FILE.FILE_TYPE;
    FileMeta  VARCHAR2(400);
 i        PLS_INTEGER;
 j        PLS_INTEGER := 0;
 SeekFlag BOOLEAN := TRUE;
 BEGIN
   	FileMeta  :=  ('00000001_' || TO_CHAR(SYSDATE,'yyyymmddhhmiss') || '_RQ_SERVICES2.txt');
    	
	OutMetaFile := UTL_FILE.FOPEN('ORALOAD', FileMeta, 'w');
	
	
	

 FOR meta IN(SELECT  COLUMN_NAME INTO col_nm  FROM cols
                              WHERE table_name = 'RQ_SERVICES' AND column_id = 1 ) 
										
LOOP

UTL_FILE.PUT_LINE(OutMetaFile,col_nm,FALSE);


END LOOP;

UTL_FILE.FCLOSE (OutMetaFile);

EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error '||SUBSTR(SQLERRM,1,200));
END WRITE_demo;
/




[code tags added by moderator; next time please add them yourself]

[Updated on: Wed, 26 December 2007 00:12] by Moderator

Report message to a moderator

Re: utl_file [message #289744 is a reply to message #289743] Wed, 26 December 2007 00:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
Your procedure does not even compile. You have an into clause in your select statement that your for loop uses that selects into a variable that has not been declared. You need to use SHOW ERRORS after compiling your procedure and fix the bugs one at a time.
Re: utl_file [message #289745 is a reply to message #289743] Wed, 26 December 2007 00:21 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
Please try with this.
file is getting created with no text.

CREATE OR REPLACE PROCEDURE write_demo IS

col_nm cols.column_name%TYPE ;
OutMetaFile UTL_FILE.FILE_TYPE;
FileMeta VARCHAR2(400);
i PLS_INTEGER;
j PLS_INTEGER := 0;
SeekFlag BOOLEAN := TRUE;
BEGIN
FileMeta := ('00000001_' || TO_CHAR(SYSDATE,'yyyymmddhhmiss') || '_RQ_SERVICES2.txt');

OutMetaFile := UTL_FILE.FOPEN('ORALOAD', FileMeta, 'w');




FOR meta IN(SELECT COLUMN_NAME INTO col_nm FROM cols
WHERE table_name = 'RQ_SERVICES' AND column_id = 1 )

LOOP

UTL_FILE.PUT_LINE(OutMetaFile,col_nm,FALSE);


END LOOP;

UTL_FILE.FCLOSE (OutMetaFile);

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error '||SUBSTR(SQLERRM,1,200));
END WRITE_demo;
/
Re: utl_file [message #289747 is a reply to message #289745] Wed, 26 December 2007 00:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
Try this and please remember to format your code with code tags.

CREATE OR REPLACE PROCEDURE write_demo 
IS
  OutMetaFile UTL_FILE.FILE_TYPE;
  FileMeta    VARCHAR2(400);
BEGIN
  FileMeta  :=  ('00000001_' || TO_CHAR(SYSDATE,'yyyymmddhhmiss') || '_RQ_SERVICES2.txt');
  OutMetaFile := UTL_FILE.FOPEN('ORACLE11G', FileMeta, 'w');
  FOR meta IN
    (SELECT  COLUMN_NAME AS col_nm 
     FROM    cols
     WHERE   table_name = 'DEPT' AND column_id = 1 ) 
  LOOP
    UTL_FILE.PUT_LINE(OutMetaFile,meat.col_nm,FALSE);
  END LOOP;
  UTL_FILE.FCLOSE (OutMetaFile);
END WRITE_demo;
/
SHOW ERRORS
EXEC write_demo

Re: utl_file [message #289749 is a reply to message #289747] Wed, 26 December 2007 00:35 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
No again that data is not coming into file.

Can you suggest me any other idea to get table structure into a file.

Re: utl_file [message #289751 is a reply to message #289749] Wed, 26 December 2007 00:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
I changed the table name and directory name for testing on my system and forgot to change them back. Please copy and paste the code below. Notice that I used AS instead of INTO and meta.col_nm.

CREATE OR REPLACE PROCEDURE write_demo 
IS
  OutMetaFile UTL_FILE.FILE_TYPE;
  FileMeta    VARCHAR2(400);
BEGIN
  FileMeta  :=  ('00000001_' || TO_CHAR(SYSDATE,'yyyymmddhhmiss') || '_RQ_SERVICES2.txt');
  OutMetaFile := UTL_FILE.FOPEN('ORALOAD', FileMeta, 'w');
  FOR meta IN
    (SELECT  COLUMN_NAME AS col_nm 
     FROM    cols
     WHERE   table_name = 'RQ_SERVICES' AND column_id = 1 ) 
  LOOP
    UTL_FILE.PUT_LINE(OutMetaFile,meta.col_nm,FALSE);
  END LOOP;
  UTL_FILE.FCLOSE (OutMetaFile);
END WRITE_demo;
/
SHOW ERRORS
EXEC write_demo

Re: utl_file [message #289752 is a reply to message #289751] Wed, 26 December 2007 00:52 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
I tried with both as and into..
but none of them is working.

do i have to change any permissions on views.
Re: utl_file [message #289754 is a reply to message #289752] Wed, 26 December 2007 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is RQ_SERVICES yours?
COLS only gives columns of YOUR objects.
"YOU" is here the owner of the procedure.

Regards
Michel

[Updated on: Wed, 26 December 2007 01:01]

Report message to a moderator

Re: utl_file [message #289755 is a reply to message #289752] Wed, 26 December 2007 01:01 Go to previous message
piscean_n
Messages: 36
Registered: December 2007
Member
Ok.
Thanks for the help but finaly i got the table name only.
previousl i was querying with view.
Now with the table only.
so now data is coming.

Thanks.
Previous Topic: sql script is not working in unix box
Next Topic: ORA-29284
Goto Forum:
  


Current Time: Mon Dec 05 15:14:48 CST 2016

Total time taken to generate the page: 0.05679 seconds