Solved

regarding the utl_file concept in oracle db

Posted on 2011-03-12
11
411 Views
Last Modified: 2012-05-11
hi all,
i have one select query which fetching recods from 4-5 tables and i am getting 60000 records in that query, so i would like to use utl file and getting those records in the text file. for that what accesss do i need on the server,

i think can i use existing path /apps/orarpt file with read and write prilileges or do we need to bounce the db for utl file parameter.
0
Comment
Question by:nagavenuiluri
  • 5
  • 4
  • 2
11 Comments
 

Author Comment

by:nagavenuiluri
ID: 35120235
hi all,
after building procedure do i need to login to db and execute that, how should i proceedd.

pls help me on this
0
 
LVL 4

Expert Comment

by:subratabiswas
ID: 35120398
Relevant information about how to create, read, write files using utl_file, can be found at http://www.adp-gmbh.ch/ora/plsql/utl_file.html . It has two small but useful examples at the end.

There is no need to log out and log back in after creating a procedure / package; after creation or update, new versions of package / procedure are available immediately for execution in the same session. But if your account is assigned a new privilege (e.g. to create directory, or access to an existing directory), then it would be required to log out and reconnect in order for those new privileges to be effective.
0
 
LVL 4

Expert Comment

by:subratabiswas
ID: 35120423
It should be clear from the link above that you will need access to an existing directory, or create directory privilege. Files to be read / written are located on the server.
0
 

Author Comment

by:nagavenuiluri
ID: 35120534
hi all,
do we need to bounce the database to setup utl file or it is aleady exists in the db, we are using 10g oracle.
0
 
LVL 4

Expert Comment

by:subratabiswas
ID: 35120881
Package UTL_FILE is part of the base installation of Oracle. If the database is running, it should be available for execution, no need to bounce any services.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Expert Comment

by:subratabiswas
ID: 35120917
UTL_FILE has been available since Oracle 7.3; there has been some enhancement to include support for DIRECTORY from Oracle 9i. prior to 9i, files could exist only in physical directories pointed to by parameter utl_file_dir; 9i onwards, it is possible to define a DIRECTORY object pointing to a physical directory and create / access files there using URL_FILE.
0
 
LVL 2

Expert Comment

by:swadhinray
ID: 35124785
As subratabiswas: said follow it and you dont need to bounce your DB is under SYS user you will find the UTL packages.

You need write a procedure which will execute the sql statement and export it into a txt or other file format that you want.

You can also use do spool to your query and save it for an example check this link:
http://www.experts-exchange.com/Database/Software/CRM/Oracle/Q_24107028.html

Else if you want you can also implement the UTL file concept.

0
 

Author Comment

by:nagavenuiluri
ID: 35124925
if i need to execute the procedure in sql so i need the schema privilges with create option right?
0
 

Author Comment

by:nagavenuiluri
ID: 35124940
Thanks for ur responces.
i mean schema credentials and for that schema it should have create priviliges am i right
0
 
LVL 2

Expert Comment

by:swadhinray
ID: 35125166
if you need to execute then  you need to have execute privileges to on user  and if you wan to create then you need create privileges to you user.

As a sysdba you can grant all the required privileges to your user.
0
 
LVL 4

Accepted Solution

by:
subratabiswas earned 500 total points
ID: 35125517
One thing to be highlighted, if not clear already by now, it is not enough to have the path /apps/orarpt existing at the OS level; it is also required to have that path included in parameter UTL_FILE_DIR or to create a DIRECTORY database object pointing to that directory.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Read only access to a Procedure in oracle? 4 48
SQL Query 34 82
How do I call MySQL Stored Procedure from oracle using HS link ? 5 28
Oracle sql query 7 51
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now