Showing results for 
Search instead for 
Did you mean: 

printing with Oracle PL/SQL utility UTL_FILE, using /etc/lp files

Stuart Abramson_2
Honored Contributor

printing with Oracle PL/SQL utility UTL_FILE, using /etc/lp files

One of our users is trying to use the /etc/lp printer definition files to print from Oracle PL/SQL using the UTL_FILE utility:

/etc/lp/cinterface/wablxm001 Cancel interface
/etc/lp/interface/wablxm001 Interface script (210 lines)
/etc/lp/member/wablxm001 device file name
/etc/lp/sinterface/wablxm001 Status interface

He says that UTL_FILE is designed to use these files.

I don't want to have to support this. I told him to format the file and just:

lp -d wablxm001 file_name

Is he correct here? Can UTL_FILE use these /etc/lp files?
Stuart Abramson_2
Honored Contributor

Re: printing with Oracle PL/SQL utility UTL_FILE, using /etc/lp files

What should the correct permissions for these files be? Ours are executable by users:

# find /etc -name wablxm001 -exec ll {} \;

-rwxr-xr-x 1 lp lp 112 Jul 2 09:30 /etc/lp/cinterface/wablxm001
-rwxr-xr-x 1 lp lp 5263 Jul 2 09:30 /etc/lp/interface/wablxm001
-rw-r--r-- 1 lp lp 10 Jul 2 09:30 /etc/lp/member/wablxm001
-rwxr-xr-x 1 lp lp 112 Jul 2 09:30 /etc/lp/sinterface/wablxm001

A. Clay Stephenson
Acclaimed Contributor

Re: printing with Oracle PL/SQL utility UTL_FILE, using /etc/lp files

This is Looney Tunes. The interface files are for the EXCLUSIVE use of the lp system and are never intended for use with applications. An interface file expects 7 args:
1) printer name
2) id - lp spooler request id
3) user
4) title
5) number of copies
6) options (the -o stuff)
7) name of file

Applications use terminfo, termcap, and/or printcap (if available) to handle printer specific stuff. A few applications have other methods using their own printer definition files but output formatting in UNIX is the sole responsibility of the application.

Your instructions about using lp (possibly with some custom -o options added to an existing interface) is the correct method.

If you need a bit more ammunition consider this problem: Lp is currently printing to a parallel or directly attached printer. Your user then starts sending to the same device - it's perfectly legal. You now have output data interleaved from two sources which makes for some rather tricky reading by us dumb humans. This normally wouldn't happen with network printers because they block until the port is agian free.
If it ain't broke, I can fix that.
Graham Cameron_1
Honored Contributor

Re: printing with Oracle PL/SQL utility UTL_FILE, using /etc/lp files

To echo what Clay has said.
UTL_FILE is not designed to write direct to interface files. Absolutely not.

It is to write (and read) flat files.
The only directories which are accessible to the UTL_FILE package are those specified in the UTL_FILE_DIR parameter in the init.ora file (or SPFILE if you're on 9i).

This parameter (there can be several entries) should be used to clamp down access to just a few, controlled directories, and not to system files such as your user suggests.

If you have
then you are asking for trouble, Oracle spefically recommend against this.

I see from the 9i docs that Oracle now recommend the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR:

Whatever, you cannot and should not allow access to your system files except through the supported interface (in this case the lp system).

-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Honored Contributor

Re: printing with Oracle PL/SQL utility UTL_FILE, using /etc/lp files


If you want to execute a script from procedure PLSQL, here is how to do this in java.

STEP1: granting some priviledges.
here, to demonstrate the execution of the program /usr/bin/ps, hence only granting as little as possible.
As SYS or some appropriately priveleged user, we need to execute:


'writeFileDescriptor' );

That allows user YD_TEST to successfully execute that program. We could have allowed it to execute /usr/bin/* or * or whatever -- Here it is restricted to only one program.

STEP2: Now, YD_TEST would create in its schema:

create or replace and compile
java source named "Util"
import java.lang.*;

public class Util extends Object
public static int RunThis(String[] args)
Runtime rt = Runtime.getRuntime();
int rc = -1;
Process p = rt.exec(args[0]);
int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];

// Echo back what the program spit out
while ((len =, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);

rc = p.waitFor();
catch (Exception e)
rc = -1;
return rc;

create or replace
function RUN_CMD( p_cmd in varchar2) return number
language java
name 'Util.RunThis(java.lang.String[]) return integer';

STEP3: To make this callable as a procedure (ignoring the return code), we need to create a procedure:

create or replace procedure RC(p_cmd in varchar2)
x number;
x := run_cmd(p_cmd);

STEP4: And now to run it:
set serveroutput on size 1000000
exec dbms_java.set_output(1000000)
exec rc('/usr/bin/ps -ef');

Hope this helps!

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)