Home » SQL & PL/SQL » SQL & PL/SQL » Files names and structure (Oracle 9i)
Files names and structure [message #345922] Fri, 05 September 2008 05:08 Go to next message
scotty_uk
Messages: 4
Registered: September 2008
Junior Member
I need to create files using UTL_FILE, but the naming of the directory structure is dynamic, as below

\Job Sheet\<depot>\<job number>\<job number>-<nnn>.tif

Where
a) <depot> is name obtained from the databas
b) <job number> is number obtained from the database
c) <nnn> is a system generated sequential number

Can this be done ?

Cheers
Re: Files names and structure [message #345924 is a reply to message #345922] Fri, 05 September 2008 05:12 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

naming of the directory structure is dynamic



Oracle is not a directory/file Manager.
In oracle you have to define directory object first
where utl_file will write file.

the file names can be dynamic.But directory name
is not.

Regards,
Rajat

[Updated on: Fri, 05 September 2008 05:13]

Report message to a moderator

Re: Files names and structure [message #345957 is a reply to message #345924] Fri, 05 September 2008 07:06 Go to previous messageGo to next message
gkbiswal
Messages: 5
Registered: August 2008
Location: Mumbai
Junior Member
Hi,

You need do create the DIRECTORY object dynamically by using execute immediate.

Then you can create file in the dynamically created directories.

Regards,
GK
Re: Files names and structure [message #345960 is a reply to message #345957] Fri, 05 September 2008 07:15 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

Hi,

You need do create the DIRECTORY object dynamically by using execute immediate.

Then you can create file in the dynamically created directories.

Regards,
GK



Great Do you Know directory object don't check
whether directory is available or not.

It does not physically create directory at OS.


Regards,
Rajat

[Updated on: Fri, 05 September 2008 07:15]

Report message to a moderator

Re: Files names and structure [message #345972 is a reply to message #345922] Fri, 05 September 2008 07:39 Go to previous messageGo to next message
gkbiswal
Messages: 5
Registered: August 2008
Location: Mumbai
Junior Member
create directory command create a directory database object that points to a OS directory on the server.

What I understand from scotty_uk's query is that phycical directory is already there in the OS by the time he's writing to this directory though the name of the OS directory is obtained dynamically.

Regards,
GK

Re: Files names and structure [message #346255 is a reply to message #345972] Sun, 07 September 2008 23:23 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

create directory command create a directory database
object that points to a OS directory on the server.



No.

You write anything it will take.Oracle will not check
whether it exists or not at OS.

Regards,
Rajat
Re: Files names and structure [message #346308 is a reply to message #346255] Mon, 08 September 2008 02:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As far as I'm aware, UTL_FILE will user either Directories, or the old init.ora parameter Utl_File_Dir.
If you set Utl_file_Dir to '\Jobsheet\*' then utl_file should be able to access any subdirectory below the Jobsheet directory.
Re: Files names and structure [message #346460 is a reply to message #346308] Mon, 08 September 2008 11:10 Go to previous message
gkbiswal
Messages: 5
Registered: August 2008
Location: Mumbai
Junior Member
Hi Rajat,

I understand create directory command will create directory whether the OS directory is present or not.

Here the requirement is
1- Create files by UTL_FILE
2- The direcotry names are obtained from Database.What is understandable from here is that the OS directory(physical directory) is already available to the program by the time URL_FILE writes to the file.Hence the directory object can be created dynamically before UTL_FILE writes to the file.

Regards,
Gk
Previous Topic: Database Triggers (merged)
Next Topic: SQL Problem
Goto Forum:
  


Current Time: Thu Feb 13 23:49:23 CST 2025