Home » SQL & PL/SQL » SQL & PL/SQL » Accessing Sub directory under Oracle directory (11g)
Accessing Sub directory under Oracle directory [message #651492] Thu, 19 May 2016 02:16 Go to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

Hello-
I have created a oracle directory WCPOLS_DIR and which looks in dba_directories like this:-
OWNER	DIRECTORY_NAME	DIRECTORY_PATH
SYS	WCPOLS_DIR	/mnt/WCPOLS/PROCESSWCPOLS


Now I need to have a subfolder under this path where I am placing one input file to read in pl/sql code. So I created a directory 'test' under this path but when I run my code its giving an error invalid directory path. This is how I gave path to this test folder in plsql code.
declare
  w_dir                   VARCHAR2(100);
  w_input_file            VARCHAR2(50);
  input_file      utl_file.file_type;
  
  
BEGIN  
    
    w_dir            := 'WCPOLS_DIR\test'; -- This is how I am accessing test folder under the oracle directory
    w_input_file     := 'wcpols_04262016_05032016_CA.TXT';        
    input_file    := utl_file.fopen(w_dir, w_input_file, 'r',32000);
   
    
    IF utl_file.is_open(input_file) THEN
     LOOP


I was assuming when we have created a directory in oracle, we could access the sub directory under this path. Could you please advise where I am making miskate?

Thanks!
Re: Accessing Sub directory under Oracle directory [message #651494 is a reply to message #651492] Thu, 19 May 2016 02:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
harshadsp wrote on Thu, 19 May 2016 00:16
Hello-
I have created a oracle directory WCPOLS_DIR and which looks in dba_directories like this:-
OWNER	DIRECTORY_NAME	DIRECTORY_PATH
SYS	WCPOLS_DIR	/mnt/WCPOLS/PROCESSWCPOLS


Now I need to have a subfolder under this path where I am placing one input file to read in pl/sql code. So I created a directory 'test' under this path but when I run my code its giving an error invalid directory path. This is how I gave path to this test folder in plsql code.
declare
  w_dir                   VARCHAR2(100);
  w_input_file            VARCHAR2(50);
  input_file      utl_file.file_type;
  
  
BEGIN  
    
    w_dir            := 'WCPOLS_DIR\test'; -- This is how I am accessing test folder under the oracle directory
    w_input_file     := 'wcpols_04262016_05032016_CA.TXT';        
    input_file    := utl_file.fopen(w_dir, w_input_file, 'r',32000);
   
    
    IF utl_file.is_open(input_file) THEN
     LOOP


I was assuming when we have created a directory in oracle, we could access the sub directory under this path. Could you please advise where I am making miskate?

Thanks!


Yes, you are making a mistake. You need to create another Oracle Directory object for your sub-directory, like:

CREATE OR REPLACE DIRECTORY test AS '/mnt/WCPOLS/PROCESSWCPOLS/test';

Re: Accessing Sub directory under Oracle directory [message #651495 is a reply to message #651494] Thu, 19 May 2016 02:33 Go to previous messageGo to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

Thanks Barbara for your quick reply. So if the requirement is to have 11 folders then I need to create 11 oracle directories for those 11 folders? Can't I place all the subfolders in just one original name like we specify in UTL_FILE_DIR?

CREATE OR REPLACE DIRECTORY test AS '/mnt/WCPOLS/PROCESSWCPOLS , /mnt/WCPOLS/PROCESSWCPOLS/test';
Re: Accessing Sub directory under Oracle directory [message #651497 is a reply to message #651495] Thu, 19 May 2016 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No you can't.

Re: Accessing Sub directory under Oracle directory [message #651499 is a reply to message #651497] Thu, 19 May 2016 03:36 Go to previous messageGo to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

okay, thank you for all your help! I have created all the separate directories for each sub-directory and its working now. Thank you.
Re: Accessing Sub directory under Oracle directory [message #651518 is a reply to message #651495] Thu, 19 May 2016 07:12 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
harshadsp wrote on Thu, 19 May 2016 02:33
Thanks Barbara for your quick reply. So if the requirement is to have 11 folders


Why is the requirement to have 11 "folders" (directories)? Cannot all the files just as easily reside in the same directory? I can think of some legitimate reasons for having all those multiple directories, but I can also very easily imagine that being a "requirement" simply because no one thought to ask "why?".
Re: Accessing Sub directory under Oracle directory [message #651532 is a reply to message #651518] Thu, 19 May 2016 13:08 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Agreed. Since you are using different file names, what possible reason is there to group everything into multiple sub directories?
Re: Accessing Sub directory under Oracle directory [message #663596 is a reply to message #651532] Sat, 10 June 2017 05:22 Go to previous messageGo to next message
rickyrocker
Messages: 2
Registered: June 2017
Junior Member
I don't want to be rude but the question was asked and answered. Subsequently asking "why" simply bloats the thread and adds no tangible value, as even if in this person's use case it is not 100% necessary, there are many use cases in which it obviously is (otherwise folder trees would not exist)
Re: Accessing Sub directory under Oracle directory [message #663597 is a reply to message #663596] Sat, 10 June 2017 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Subsequently asking "why" simply bloats the thread and adds no tangible value,
Agree to the very specified question but it just may help OP to review his original design so that the problem disappears which is much better than having a direct solution for a problem that is not necessary to have.

Saying that your post does not add anything to the topic and, not wanting to be rude, registering to just post that is somewhat silly.

[Updated on: Sat, 10 June 2017 05:58]

Report message to a moderator

Re: Accessing Sub directory under Oracle directory [message #664179 is a reply to message #663597] Thu, 06 July 2017 05:39 Go to previous messageGo to next message
rickyrocker
Messages: 2
Registered: June 2017
Junior Member
In my opinion what is silly is:

People with 0 visibility into an OPs use-case making comments like "Why on earth would you need that" (with the often implied "...you fool - don't you know anything" tacked on the end)

I say this as at best such comments are myopic, ill informed and narrow minded, and at worst they dilute from the real question and answer wasting the time of potentially thousands or millions of people looking for a real answer (as I am sadly doing right now due your response)

What is also silly is a site which ostensibly vets user comments before putting them out there entertaining such useless input.

I thus raised my annoyance in the hope that the "vetter" would take a more judicious approach to comments that are published - in the interests of both the quality of content and the many people who read it.

(as mentioned my comments are targetting the site vetters so I really don't care if this gets published on the site or not)

Re: Accessing Sub directory under Oracle directory [message #664181 is a reply to message #664179] Thu, 06 July 2017 06:00 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
There is zero implied or explicit criticism of the OP. I have been an oracle DBA and developer since oracle 5 and was suggesting that they could simplify their code by using one directory. Having the file names with specific prefixes would separate the files into separate silos. It was a suggestion only and I have an application currently where I am using separate subfolders with different directory objects but that is for separating the files so each folder can only be accessed by specific users. Please don't read into comments intentions that aren't there.

[Updated on: Thu, 06 July 2017 06:01]

Report message to a moderator

Re: Accessing Sub directory under Oracle directory [message #664184 is a reply to message #664181] Thu, 06 July 2017 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Bill, to wait a month before posting again, he is just a troll.

Re: Accessing Sub directory under Oracle directory [message #664192 is a reply to message #664184] Thu, 06 July 2017 09:52 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
your right and I took the hook. Shame on me.
Previous Topic: Search in concatenated string (merged)
Next Topic: Report hangs (possibly query related).. is this possible cause?
Goto Forum:
  


Current Time: Fri Mar 29 00:42:43 CDT 2024