| Batch file to run sql script [message #651226] |
Fri, 13 May 2016 04:18  |
harshadsp
Messages: 100
Registered: August 2008
|
Senior Member |
|
|
Hello Experts, need your thoughts on this!
I have created a .sql script which is currently taking a file at oracle directory and execute the sql code to split the input file on multiple files using utl_file commands(as we have discussed previously). But when I give this code to users they need some another way to execute this.
Users will have the input files(say 10) in their windows directory and they want a utility which will select one file from those 10 files and then run my sql program and they will get the multiple output files in same directory.
I was thinking to create a batch file (which I honestly don't know how it works) which will prompt the input file and then user will select the required file and then the sql program will split it into multiple output files. I am not sure how I could point the windows directory instead of my utl/oracle directory or even I am not sure which is the best way to implement this functionality.
Could you please advise how I can proceed for this?
Thanks!
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Batch file to run sql script [message #651294 is a reply to message #651270] |
Sat, 14 May 2016 19:01  |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
harshadsp wrote on Sat, 14 May 2016 01:25The Oracle is at another Unix server and windows is at another server. By better way, I mean to say if there is any oracles predefined library which I can use to ftp files from windows to Unix oracle server and vice versa. When I goggled I found some ftp.login, xutl_ftp etc but when I use those inside my code its giving error that identifier must be declared. So just want to know if I can use whatever 11g has already without installing anything more.
Did you consider asking for help with that code? Most likely it does NOT involve installing anything more, but simply debugging your code and possibly granting some permissions.
Quote:@Michael sorry but I didn't understand "Do NOT use UTL_DIR it is a security hole. Use Oracle DIRECTORY object.". Is it advisable that I could mount the windows share drive to Unix and then map this drive in utl directories of oracle and run my sql program?
Another way I am thinking is (If our Unix admin say NO to mount the windows drive on Unix), create a batch file, place this in users windows directory. Batch file will
1) first ftp the input files to utl directory path
2) then it will call sql script and prompt user to enter input file to be processed
If you're asking for the name of the file in step 2, how did step 1 know what file to ftp?
Quote:
3) run the sql script for that input parameter(file)
4) sql will generate output files in utl directory
5) ftp output files again back to users windows directory
All this will be done through batch file when user run the same.
I am not sure if this is correct way. Basically user should drive all the process so I thought to make a batch file like above.
Please advise your opinions, thanks!
I can imagine writing a batch file to do this but you'd have to take a lot of other things into consideration. And at the top of the list is having a fool-proof, 100% guaranteed method on making sure that two users running at the same time don't end up colliding on file names.
And while I can imagine it being technically feasible within the broad outlines of what you describe, I can't imagine it being the best approach, even after tweaking the details. Actually, I'd prefer a web-based app written in java or .net. Then you don't have to install ANYTHING on the user's PC, AND you avoid having to ship the d*** files back and forth.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Batch file to run sql script [message #651307 is a reply to message #651306] |
Sun, 15 May 2016 09:54 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:The CENGROUP_OUTPUT directory has entries in both UTL_FILE_DIR and DBA_DIRECTORIES whereas the newly created WCPOLS_DIR is just a oracle directory and not set in UTL_FILE_DIRECTORY and I think that's the reason utl_file package is not working with this directory. Could this will be the reason?
No unless your code is wrong. You do not use both directories in the same way.
Post the code which calls UTL_FILE.FOPEN.
Edit: with the value of the variables.
[Updated on: Sun, 15 May 2016 09:54] Report message to a moderator |
|
|
|
| Re: Batch file to run sql script [message #651338 is a reply to message #651307] |
Mon, 16 May 2016 01:43 |
harshadsp
Messages: 100
Registered: August 2008
|
Senior Member |
|
|
Here is the code with new directory WCPOLS_DIR-
DECLARE
w_dir VARCHAR2(100);
w_input_file VARCHAR2(50);
w_newline VARCHAR2(2000);
input_file utl_file.file_type;
BEGIN
w_dir := 'WCPOLS_DIR';
w_input_file := '&FileName';
input_file := utl_file.fopen(w_dir, w_input_file, 'r',32000);
IF utl_file.is_open(input_file) THEN
LOOP
BEGIN
utl_file.get_line(input_file, w_newline);
EXCEPTION WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
END IF;
COMMIT;
utl_file.fclose(input_file);
END;
This gives error Ora-29283 Invalid file operation
And this is the code with CENGROUP_OUTPUT directory which works perfect
DECLARE
w_dir VARCHAR2(100);
w_input_file VARCHAR2(50);
w_newline VARCHAR2(2000);
input_file utl_file.file_type;
BEGIN
w_dir := 'WCPOLS_DIR';
w_input_file := '&FileName';
input_file := utl_file.fopen(w_dir, w_input_file, 'r',32000);
IF utl_file.is_open(input_file) THEN
LOOP
BEGIN
utl_file.get_line(input_file, w_newline);
EXCEPTION WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
END IF;
COMMIT;
utl_file.fclose(input_file);
END;
The entries in dba_directories
OWNER DIRECTORY_NAME DIRECTORY_PATH
1 SYS WCPOLS_DIR /home/hpatil/WCPOLS
5 SYS CENGROUP_OUTPUT /home/cengroup/cent/dev0/batch/output
The entry on CENGROUP_OUTPUT in UTL_FILE_DIR
NUM NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE ISINSTANCE_MODIFIABLE ISMODIFIED ISADJUSTED ISDEPRECATED ISBASIC DESCRIPTION UPDATE_COMMENT HASH
2 1656 utl_file_dir 2 /cent/dev0/batch/output /home/arggroup/argt/dv0/batch/output, /cent/dev0/batch/output FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE utl_file accessible directories list 379739569[/b][
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
So the only option for me is do with Oracle only 
I may write shell to ftp the files.