OraFAQ Forum: SQL & PL/SQL » Is it possible to fetch file path from a folder through PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Is it possible to fetch file path from a folder through PL/SQL (Windows XP, Windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Is it possible to fetch file path from a folder through PL/SQL [message #597412]

Fri, 04 October 2013 09:52

Chinmay91
Messages: 15
Registered: July 2013
Location: Mumbai

Junior Member

Hello,

We upload a file on our library on weekly basis. I wanted to fetch the latest uploaded file path/URL from a folder through PL/SQL, but not sure if it's possible.

If possible, please suggest what should I do.

Thanks,
Chinmay

Report message to a moderator

Re: Is it possible to fetch file path from a folder through PL/SQL [message #597416 is a reply to message #597412]

Fri, 04 October 2013 10:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> I wanted to fetch the latest uploaded file path/URL from a folder through PL/SQL, but not sure if it's possible.
does this folder reside on DB Server system itself?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Is it possible to fetch file path from a folder through PL/SQL [message #597417 is a reply to message #597412]

Fri, 04 October 2013 10:04

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Is it uploaded and stored in DB?

Report message to a moderator

Re: Is it possible to fetch file path from a folder through PL/SQL [message #597418 is a reply to message #597417]

Fri, 04 October 2013 10:24

Chinmay91
Messages: 15
Registered: July 2013
Location: Mumbai

Junior Member

Nope, we upload these files manually.

BlackSwan,

I read the guidelines 2 times but didn't get what you want to convey. I already did search on google but couldn't find any solution of this issue.

Thanks,
Chinmay

Report message to a moderator

Re: Is it possible to fetch file path from a folder through PL/SQL [message #597419 is a reply to message #597418]

Fri, 04 October 2013 10:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Chinmay91 wrote on Fri, 04 October 2013 08:24

Nope, we upload these files manually.

BlackSwan,

I read the guidelines 2 times but didn't get what you want to convey. I already did search on google but couldn't find any solution of this issue.

Thanks,
Chinmay

You did not answer the question I asked.

To start with what about #4 in Posting Guidelines ?

Report message to a moderator

Re: Is it possible to fetch file path from a folder through PL/SQL [message #597420 is a reply to message #597412]

Fri, 04 October 2013 10:34

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

Hi,

Chinmay91 wrote on Fri, 04 October 2013 16:52

I wanted to fetch the latest uploaded file path/URL from a folder through PL/SQL, but not sure if it's possible.

If the file is on the DB server, one possible option is using SQL*Plus to run your PL/SQL block

1- Create an OS script (for example if the OS is of Linux/Unix family you can use Kornshell) that finds the latest file in the directory. A simple version using Kornshell could be something like this (reading the list of files in the current working directory where the KornShell script is being run, so you may have to adapt it to get the proper absolute file paths)

mostRecentFile=""
fileList=$(ls ./)
for file in $fileList
do
    if [ "$file" -nt "$mostRecentFile" ]
    then
        mostRecentFile="$file"       
    fi
done
echo "the most recent file is: $mostRecentFile"

2-call your PL/SQL script with the value of $mostRecentFile

$ sqlplus <your connection/authentication method> @yourPLSQLScript.sql $mostRecentFile

And at the beginning of your PL/SQL script, you will have something like

VARIABLE fileName VARCHAR2(30);
BEGIN
    :fileName := '&1';
    . . .
END;
/

Regards,
Dariyoosh

Report message to a moderator

Re: Is it possible to fetch file path from a folder through PL/SQL [message #597421 is a reply to message #597420]

Fri, 04 October 2013 10:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> (for example if the OS is of Linux/Unix family you can use Kornshell)
OP shows running Windows, right?

Report message to a moderator

Re: Is it possible to fetch file path from a folder through PL/SQL [message #597424 is a reply to message #597420]

Fri, 04 October 2013 10:54

Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I doubt this will work on Windows.
If OP did not post his Oracle version he posted his OS.

Report message to a moderator

Re: Is it possible to fetch file path from a folder through PL/SQL [message #597430 is a reply to message #597421]

Fri, 04 October 2013 11:17

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

BlackSwan wrote on Fri, 04 October 2013 17:42

OP shows running Windows, right?

Well, I was a bit blind while I was reading his topic and I didn't see that! Confused

However, using another (windows compatible) shell language, I think it would be possible to implement a similar script based on the same idea.

[Updated on: Fri, 04 October 2013 11:17]

Report message to a moderator

Re: Is it possible to fetch file path from a folder through PL/SQL [message #597431 is a reply to message #597424]

Fri, 04 October 2013 11:18

Chinmay91
Messages: 15
Registered: July 2013
Location: Mumbai

Junior Member

Sorry for the inconvenience.

It is Windows XP, Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit

[Updated on: Fri, 04 October 2013 11:20]

Report message to a moderator

Re: Is it possible to fetch file path from a folder through PL/SQL [message #597432 is a reply to message #597431]

Fri, 04 October 2013 11:28

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

There is still one important unanswered question which BlackSwan asked you: "does this folder reside on DB Server system itself?"

Regards,
Dariyoosh

Report message to a moderator

Re: Is it possible to fetch file path from a folder through PL/SQL [message #597435 is a reply to message #597432]

Fri, 04 October 2013 11:50

Chinmay91
Messages: 15
Registered: July 2013
Location: Mumbai

Junior Member

No Dariyoosh, the folder doesn't reside on DB server at all.

Report message to a moderator

Re: Is it possible to fetch file path from a folder through PL/SQL [message #597437 is a reply to message #597435]

Fri, 04 October 2013 11:55

Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So the answer to the question: " I wanted to fetch the latest uploaded file path/URL from a folder through PL/SQL" is: no way as PL/SQL execute on the DB server.

Report message to a moderator

Previous Topic:	Calling .sql file from .ksh file
Next Topic:	Bulk collect issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 00:21:50 CDT 2024