Home » SQL & PL/SQL » SQL & PL/SQL » Java Stored Procedure (Oracle 9i)
Java Stored Procedure [message #447253] Fri, 12 March 2010 15:20 Go to next message
Amine
Messages: 301
Registered: March 2010
Senior Member

Hi all,
I've written a Java stored procedure that deletes some provided file. The PL/SQL procedure looks like :

procedure delete (file in varchar);

The procedure does the work correctly when the provided file name exists on the DB server, but doesn't when the file is ou of the DB server.

Is there a way to resolve this ?

Best regards
Re: Java Stored Procedure [message #447255 is a reply to message #447253] Fri, 12 March 2010 15:24 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What do you mean by "how to resolve this?"

Do you mean "how to delete a non-existent file"?

Well, if it doesn't exist don't try to delete it.

Re: Java Stored Procedure [message #447256 is a reply to message #447255] Fri, 12 March 2010 15:26 Go to previous messageGo to next message
Amine
Messages: 301
Registered: March 2010
Senior Member

How to delete a file that exists out of the DB server using the PL/SQL procedure ???
Re: Java Stored Procedure [message #447257 is a reply to message #447253] Fri, 12 March 2010 15:27 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/


>The procedure does the work correctly when the provided file name exists on the DB server, but doesn't when the file is ou of the DB server.
>Is there a way to resolve this ?

The code runs on the DB server.
Code can only impact file local to DB server.
There is nothing to resolve.
It is what it is.
Re: Java Stored Procedure [message #447258 is a reply to message #447257] Fri, 12 March 2010 15:31 Go to previous messageGo to next message
Amine
Messages: 301
Registered: March 2010
Senior Member

Sorry for the little first spam...

So is there a way to launch a command prompt and launch a program that do the job ?
Re: Java Stored Procedure [message #447259 is a reply to message #447258] Fri, 12 March 2010 15:36 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>So is there a way to launch a command prompt and launch a program that do the job ?

You can try, but exactly how to delete file on a remote system?
What about security & permissions?

Would it be OK with you for code on DB server to DELETE files from your PC & you have NO control over which files go missing?
Re: Java Stored Procedure [message #447260 is a reply to message #447259] Fri, 12 March 2010 15:40 Go to previous messageGo to next message
Amine
Messages: 301
Registered: March 2010
Senior Member

A Java class does the job correctly.

For example, we can imagine that after a call to a PL/SQL procedure, these Java class is executed on the DB server to act on a remote PC. And for permissions, we can say, as a rule, that the DB server has enough permissions on the remote PC to delete the file...So for now, the question is can we launch a command prompt from a PL/SQL procedure ?
Re: Java Stored Procedure [message #447261 is a reply to message #447260] Fri, 12 March 2010 15:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The most common way to launch a command prompt form PL/SQL is to do it with a Java stored procedure.

But I would imagine it is pretty unlikely you will ever get it working the way you imagine it.

Or, if you get it working, some hacker will destroy your network pretty quick because of all the security holes you just opened up.

icon7.gif  Re: Java Stored Procedure [message #447263 is a reply to message #447261] Fri, 12 March 2010 15:55 Go to previous messageGo to next message
Amine
Messages: 301
Registered: March 2010
Senior Member

So what do you advise me ? Smile

A network mapped drive is not accessible from PL/SQL, so I can't use it in my procedure...
Re: Java Stored Procedure [message #447265 is a reply to message #447263] Fri, 12 March 2010 16:15 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Depends on what you are actually trying to achieve. Smile

In general, if you are working with files on client PCs handle that files from your client program, not from server procedures.
Re: Java Stored Procedure [message #447266 is a reply to message #447265] Fri, 12 March 2010 16:22 Go to previous messageGo to next message
Amine
Messages: 301
Registered: March 2010
Senior Member

Here is my real problem :
Clients have access to a File server that contains some *txt files. But they have only the read permission on that File server.

There's a need to delete/rename some files. The files names are values retrieved from a DB server.

So, I thought about that solution, to perform these task (delete/rename from the DB server)...
Re: Java Stored Procedure [message #447267 is a reply to message #447266] Fri, 12 March 2010 16:32 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And in what sort of client software are the users working?

And how are the files shared? CIFS, NFS, etc...?

If the client is Java and the share CIFS you can probably connect to the DB to get the values and then use JCIFS to rename the files with an appropriate login.

Or just give the users write access and use a "normal" rename.



Re: Java Stored Procedure [message #447274 is a reply to message #447267] Fri, 12 March 2010 16:59 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Read up on rsh & rexec, both of which can be run as an external job from dbms_scheduler, or just use FTP from PL/SQL.

http://www.oracle-base.com/articles/misc/FTPFromPLSQL.php
Re: Java Stored Procedure [message #447330 is a reply to message #447274] Sat, 13 March 2010 13:07 Go to previous messageGo to next message
Amine
Messages: 301
Registered: March 2010
Senior Member

Clients (Windows hosts) are using Forms Runtime to run fmx programs and the server that hosts these files is also a Windows server.

I think the best way is to run :

rename file1.txt file2


through a PL/SQL procedure that encapsulates a Java class.

Do you agree with me ??
Re: Java Stored Procedure [message #447333 is a reply to message #447330] Sat, 13 March 2010 14:24 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>Do you agree with me ??
It does not matter what we think.
The only thing that matters is what you can implement to meet the requirements.
Re: Java Stored Procedure [message #447351 is a reply to message #447330] Sat, 13 March 2010 18:27 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I think that

Forms -> Oracle -> PL/SQL -> Java -> Command Line -> Rename

With multiple interdependent network connections in between is probably a lot more prone to errors than just

Forms -> Rename

The first thing I would probably try in your place:

Can the process that creates those files be amended so that the files are created with the "correct names" in the first place

OR

Can the process that further processes the files be amended that it first renames the files, or just uses the original file names.

Previous Topic: Trim Column Data
Next Topic: function "dump" in oracle
Goto Forum:
  


Current Time: Wed Dec 07 18:24:41 CST 2016

Total time taken to generate the page: 0.21394 seconds