473,890 Members | 1,373 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Writing data to file using UTL_FILE package

26 New Member
scribes
i would like to write /export the results of a query to a text file
logic
1)execute query and
2) export to a text file
Mar 12 '08
21 14624
amitpatel66
2,367 Recognized Expert Top Contributor
get :PL/SQL procedure successfully completed but the data aint there in the specified file.
Do this and let me know does it print 'Errrrrr':

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> SET SERVEROUTPUT ON
  3.  
  4.  
THen run that piece of code
Mar 12 '08 #11
amitpatel66
2,367 Recognized Expert Top Contributor
Do this and let me know does it print 'Errrrrr':

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> SET SERVEROUTPUT ON
  3.  
  4.  
THen run that piece of code
Do the following:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> CREATE OR REPLACE DIRECTORY LOGG_DIR AS 'C:\TEST';
  3.  
  4. SQL> GRANT READ,WRITE ON DIRECTORY LOGG_DIR TO PUBLIC;
  5.  
  6. SQL> SET SERVEROUTPUT ON
  7.  
  8. SQL> <The piece of Code that I have given in my previous Post>
  9.  
  10.  
Post back what happens when you do these steps?
Mar 12 '08 #12
change
26 New Member
Did As U Said And Printed Errrrrrrrrrr
Mar 12 '08 #13
change
26 New Member
Can U Get A Dummy One Working For Me Man Am In Need
Mar 12 '08 #14
amitpatel66
2,367 Recognized Expert Top Contributor
To get the exact error message, please run this code and post back the error message for reference

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3. fileID UTL_FILE.FILE_TYPE;
  4.  LINE_BUFF VARCHAR2(1000);
  5.  BEGIN
  6.  fileID := UTL_FILE.FOPEN ('C:\TEST', 'emp.dat
  7. FOR emprec IN (SELECT * FROM SO_SERVICES)
  8. LOOP
  9. LINE_BUFF :=TO_CHAR (emprec.ACCT_NO) ;
  10. UTL_FILE.PUT_LINE ( FILEID ,LINE_BUFF);
  11. END LOOP;
  12. UTL_FILE.FCLOSE (fileID);
  13. EXCEPTION
  14. WHEN OTHERS THEN
  15. DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
  16. END;
  17.  
  18.  
Mar 12 '08 #15
amitpatel66
2,367 Recognized Expert Top Contributor
You can search in this forum by typing in the keyword utl_file in the SEARCH box at the top right corner of the forum windowto get some sample codes.

But you run the code that I have provided you in my previous post to check what could be the exact error and post back the error for reference.
Mar 12 '08 #16
change
26 New Member
HI RAN THE CODE AND THIS IS THE ERROR;;;


1,User-Defined Exception
Mar 13 '08 #17
change
26 New Member
HI WAS DOING SOME REDING AND CAME ACROOS::::

Here's the format of the parameter for file access in the INIT.ORA file:

utl_file_dir = <directory>


PROBLEM IS WHERE DO I DECLARE THIS IN MY CODE
Mar 13 '08 #18
change
26 New Member
scribes

am trying to use utl_file and need to declare the directory in init.ora and i went to the oracle registry folder and i cant seem to find it....help please
Mar 13 '08 #19
amitpatel66
2,367 Recognized Expert Top Contributor
HI WAS DOING SOME REDING AND CAME ACROOS::::

Here's the format of the parameter for file access in the INIT.ORA file:

utl_file_dir = <directory>


PROBLEM IS WHERE DO I DECLARE THIS IN MY CODE
If you want to change the value of utl_file_dir, then you need to change that init.ora file and bounce the database server to take in to effect the modified value
Mar 13 '08 #20

Sign in to post your reply or Sign up for a free account.

Similar topics

4
9063
by: Igor Shulgin | last post by:
Hi! What is standard and direct way (within Oracle 9.2 stored procedures) for writing binary data from Oracle to external file on disk? We have tried to use UTL_FILE package, but it operates on files consisting out of "line" of size no more than 32767 bytes and adds new_line symbol upon closing files. Also we checked out interMedia types and packages for writing BLOB to disk but now we get stuck with some strange error messages.
5
12453
by: Siddhant Kumar | last post by:
I want to write a Text file containing data (either , or Tab delimited) using stored procedure. can any one guide me how to do this.? or point to a sample/link of code. DATABASE IS BOTH ORacle 8.x and Oracle 9
1
6842
by: Leo Martin Orfei | last post by:
Hi, Anybody know/have functions or package to create text file from postgres function? I want to execute a function to take some fields from a table and save it in a text file (or xml format). I need function like: createfile(filename); appendtofile(filename,text);
1
2109
by: tim | last post by:
Someone using Python Midi Package from http://www.mxm.dk/products/public/ lately? I want to do the following : write some note events in a midi file then after doing that, put some controllers at the beginning of the midifile (because I want to be able to make those dependant on what notes were just written)
2
3212
by: dkarthick | last post by:
How to execute UTL_FILE Package in plsql?
1
11424
by: anithas | last post by:
I need to write the o/p from an Oracle Pl/SQL to a txt file. I tried the following , but i get an error declare f utl_file.file_type; s varchar2(200) := 'this is some info'; begin f := utl_file.fopen('SAMPLEDATA','sample2.txt','W'); utl_file.put_line(f,s); utl_file.fclose(f);
1
16985
by: djramesh04 | last post by:
ORA-29280: invalid directory path ORA-06512: at "SYS.UTL_FILE", line 33 ORA-06512: at "SYS.UTL_FILE", line 436 ORA-06512: at "CPLNMGR.PARSE_STRING", line 13 ORA-06512: at line 1 CREATE OR REPLACE DIRECTORY mydir AS 'C:\CPALN\TEST\'; create or replace procedure parse_string is
2
6150
by: murthydb2 | last post by:
Hi My requirement is that i have to write a stored procedure in db2 and that will be executed in a batch file . Any system error or validation error that occurs inside the db2 sp during the execution need to be written in external flat file ( Log file to know the error ) . Is there anything similar in db2 like UTL_FILE in oracle . Please find the sample coding in oracle which will write the output to a external flat...
5
5115
by: narendrareddy | last post by:
Hi , assume a flat file name emp.txt contains the following data 10000100001 narendranathreddy tallapireddy 01-01-2007 male 10000100002 ravindranathreddy tallapireddy 01-01-2008 male 10000100003 upendranathreddy tallapireddy 01-01-2008 male 10000100004 nagalakshmireddy tallapireddy 01-01-2007 male 10000100005 ruchithareddy tallapireddy 01-01-2007 female 10000100006 narendranathreddy tallapireddy 01-01-2009 male 10000100007...
0
9980
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9826
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10925
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10468
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7172
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5855
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4682
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4276
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3283
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.