|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
June 18th, 2004, 06:11 AM
|
|||
|
|||
Alternative to UTL_FILE
I have an issue where i need to create two fixed length interface files which in itself is not an issue and i have done this many times.
I do however have the restriction of being on 9i and there being no directories set in all_directories which is preventing me from using the UTL_FILE package. This is a vendor database and i cannot create any tables an although i could spool the files this is not what i wanted to use anyone have any ideas of an alternative , the best i can do is use an anonomous block.
|
|
#2
June 18th, 2004, 09:18 AM
|
|||
|
|||
|
Most OS support some type of redirection - on Unix we do this kind of thing to get around the problem, using DBMS_OUTPUT which lets you format stream output
Code:
VALUE="13"
UIPW=username/password
echo "$UIPW
SET SERVEROUT ON SIZE 1000000
DECLARE
cursor whatever is
SELECT
usbsubm_status stat
from usbsubm
WHERE usbsubm_job = 'UZERTEX'
AND usbsubm_number = $VALUE;
BEGIN
FOR X IN whatever
LOOP
DBMS_OUTPUT.put_line('Record status:'|| x.stat);
END LOOP;
END;
/" |
$ORACLE_HOME/bin/sqlplus -s > /path/to/output/file
|
|
#3
June 18th, 2004, 09:35 AM
|
|||
|
|||
|
Thanks Jim ,
Am on a Unix box so this should be ok however any idea on how i can check the files exists ? In your opinion would the only way to be to have this in a shell script or can you think off a clever sql/plsql method ? Barry
|
|
#4
June 18th, 2004, 11:54 AM
|
|||
|
|||
|
I assume you mean the output file already exists
Code:
if [ -s /path/to/output/file ] ; then #--- do stuff with cat, mv or rm on the old file here #--- example mv /path/to/output/file /path/to/another/file.old fi PL/SQL allows output to: 1.stdout (dbms_output package) 2.files in specifed directories (utl_file package) 3.spool dbms_output to a file, but you said that spool won't work. That's it. All I know about. Except - if you have Oracle, you may have Pro*C installed. You can write C code that opens the database and writes a file anywhere - using embedded PL/SQL blocks to get the data. Code:
EXEC SQL EXECUTE ..PL/SQL BLOCK here END-EXEC; The $ORACLE_HOME path has pro*c code examples - the files end in .pc
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > Alternative to UTL_FILE |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
