Skip to Main Content
  • Questions

  • Access operating system command output from SQL or PL/SQL

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Mark.

Asked: November 30, 2009 - 1:13 pm UTC

Last updated: May 06, 2012 - 2:24 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Tom:

Suppose I want to get some information from the output from an operating system command, and use that information in a SQL statement. Is there a way to do that totally within SQL?

Here is a sample work-around, this is a somewhat contrived example, but this gets the point across. This Solaris Unix sample is to determine if I have the processes parameter in init.ora value set high enough. And I know that this example doesn't take into account various other processes that might spring up from time to time, user processes, etc.

#!/usr/bin/ksh

# Get number of current oracle processes for current SID
NUM_PROCS_CURRENT=`ps -fu oracle | grep -c $ORACLE_SID`

sqlplus  / << xxSPENDxx \

define NUM_PROCS_CURRENT=$NUM_PROCS_CURRENT
col processes_param format a15

select
  to_number((select value from v\$parameter 
             where name ='parallel_max_servers')) *
  to_number((select value from v\$parameter 
             where name ='parallel_threads_per_cpu')) +
  &&NUM_PROCS_CURRENT as min_processes,
  (select value from v\$parameter 
             where name ='processes') as processes_param 
from dual
;

exit

xxSPENDxx


Output:

$  db_unix_info.sh
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 30 12:21:53 2009
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

OPS$XXXPRF1@xxxprf1> 
old   6:   &&NUM_PROCS_CURRENT as min_processes,
new   6:   127 as min_processes,

MIN_PROCESSES PROCESSES_PARAM
------------- ---------------
          607 500


So for this example, the shell script figured out there were 127 oracle processes currently running for the SID, and the SQL added that to the value of parallel_max_servers * parallel_threads_per_cpu.

I know that the processes parameter of 500 is too low, and would need to be at least 607 to be safe.

and Tom said...

Not until 11g Release 2

Prior to that, you need to use a java stored procedure or you can use dbms_scheduler.

A java stored procedure can execute the program, read stdout/stderr, and return that to you:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:952229840241#2717585194628

dbms_scheduler can run an external process, which would create a file, which you could read using bfile's or utl_file.



In 11g, you can use an external table to run a program and read the output of that program. For example:


ops$tkyte%ORA11GR2> CREATE TABLE EMP_ET
  2  (
  3    "EMPNO" NUMBER(4),
  4    "ENAME" VARCHAR2(10),
  5    "JOB" VARCHAR2(9),
  6    "MGR" NUMBER(4),
  7    "HIREDATE" DATE,
  8    "SAL" NUMBER(7,2),
  9    "COMM" NUMBER(7,2),
 10    "DEPTNO" NUMBER(2)
 11  )
 12  ORGANIZATION external
 13  ( TYPE oracle_loader
 14    DEFAULT DIRECTORY load_dir
 15    ACCESS PARAMETERS
 16    ( RECORDS DELIMITED BY NEWLINE
<b> 17          preprocessor  exec_dir:'run_gunzip.sh'
</b> 18      FIELDS TERMINATED BY "|" LDRTRIM
 19    )
 20    location ( 'emp.dat.gz')
 21  )
 22  /
Table created.


that'll run the script run_gunzip.sh with the input of "emp.dat.gz"

so

ops$tkyte%ORA11GR2> !file emp.dat.gz
emp.dat.gz: gzip compressed data, was "emp.dat", from Unix, last modified: Wed Oct  7 12:48:53 2009

ops$tkyte%ORA11GR2> !cat run_gunzip.sh
#!/bin/bash

/usr/bin/gunzip -c $*

ops$tkyte%ORA11GR2> select empno, ename from emp_et where rownum <= 5;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN




Taking that a step further....


ops$tkyte%ORA11GR2> CREATE TABLE ls
  2  (
  3    line varchar2(255)
  4  )
  5  ORGANIZATION external
  6  ( TYPE oracle_loader
  7    DEFAULT DIRECTORY load_dir
  8    ACCESS PARAMETERS
  9    ( RECORDS DELIMITED BY NEWLINE
 10          preprocessor  exec_dir:'run_ls.sh'
 11      FIELDS TERMINATED BY "|" LDRTRIM
 12    )
 13    location ( 'run_ls.sh')
 14  )
 15  /

Table created.





ops$tkyte%ORA11GR2> select * from ls;

LINE
---------------------------------------------------------------
11 things about 11gr2.ppt
diyp.sql
ebr.old.sql
ebr.sql
emp.ctl
emp.dat.gz
EMP_ET_26122.log
emp_et.sql
LS_26122.log
run_gunzip.sh
run_ls.sh

11 rows selected.


run_ls.sh was just:

$ cat run_ls.sh
#/bin/bash
cd /mnt/hgfs/docs/Presentations/Seminar/11gr2
/bin/ls


so, we just read the output of ls - you can use this to read the output of anything..

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

More than one way to skin a cat

Mark Stewart, December 01, 2009 - 10:15 am UTC

Thanks, Tom! I appreciate the three different approaches; and one of them will give me another reason to go to 11g rel 2. I saw your mention of executable permissions for directories in it in your magazine column, but I didn't make the connection.

very nice

Sokrates, December 01, 2009 - 10:31 am UTC

... usage of new feature

error in preprocessor

Ernesto Villarruel, May 03, 2012 - 10:25 pm UTC

I would greatly appreciate your help on this one.

I have the next error :

SQL> select * from ls;
select * from ls
*
ERROR at line 1:
ORA-29913: error al ejecutar la llamada de ODCIEXTTABLEFETCH
ORA-29400: error de cartucho de datos
KUP-04095: preprocessor command /tmp/run_ls.sh encountered error
"/tmp/run_ls.sh: line 2: cd: /home/oracle/test_dir: Permission denied
"


SQL> host
uxdxdb01{dbm1}: /home/oracle $ pwd
/home/oracle

uxdxdb01{dbm1}: /home/oracle $ ls -ltrd */
drwxr-xr-x 3 oracle oinstall 4096 Mar 27 10:33 oradiag_oracle/
drwxr-xr-x 2 oracle oinstall 4096 Apr 11 18:15 data/
drwxr-xr-x 2 oracle oinstall 4096 Apr 20 18:10 tnsadmin/
drwxrwxrwx 2 oracle oinstall 4096 May 3 21:47 test_dir/
drwxr-xr-x 3 root root 0 May 3 22:10 dbfs/
uxdxdb01{dbm1}: /home/oracle $ ls -l /tmp/run_ls.sh
-rwxrwxrwx 1 oracle oinstall 46 May 3 21:48 /tmp/run_ls.sh

uxdxdb01{dbm1}: /home/oracle $ cat /tmp/run_ls.sh
#!/bin/bash
cd /home/oracle/test_dir
/bin/ls

uxdxdb01{dbm1}: /home/oracle $ id
uid=1001(oracle) gid=1001(oinstall) groups=101(fuse),1001(oinstall),1002(dba),1003(racoper),1004(asmdba),1006(asmadmin)
uxdxdb01{dbm1}: /home/oracle $ cd test_dir/

uxdxdb01{dbm1}: /home/oracle/test_dir $ /bin/ls
1.dat 2.dat 3.dat

How can I read this directory from table ls ?,I need any permission ?

Thanks in advance for taking and answering my question.



Tom Kyte
May 06, 2012 - 2:24 pm UTC

whomever the script is running as would likewise need execute on /home and /home/oracle

put an "/usr/bin/id" call in there instead and see exactly who the script is running as.

If you are connecting to the database using a direct connection - the script will likely be running as "you"

If you are connecting via a listener, the script will likely be running as "the OS account that started the listener"

If you are connecting via shared server, the script will likely be running as "the OS account that started the database"


I think you are likely connecting directly and you cannot actually 'see' that directory.

Here is the problem in reverse - I have a script run_ls.sh that cd's to MY (tkyte) directory /home/tkyte/tmp and does an ls.

[tkyte@localhost 11gr2]$ sqlplus /

SQL*Plus: Release 11.2.0.3.0 Production on Sun May 6 15:22:10 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ops$tkyte%ORA11GR2> select * from ls;

LINE
-------------------------------------------------------------------------------
test.sql
tk.prf
xxx

ops$tkyte%ORA11GR2>



but, if I connect over sqlnet:

[tkyte@localhost 11gr2]$ sqlplus 'ops$tkyte/foobar@ora11gr2_2'

SQL*Plus: Release 11.2.0.3.0 Production on Sun May 6 15:22:38 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ops$tkyte%ORA11GR2> select * from ls;
select * from ls
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04095: preprocessor command /tmp/run_ls.sh encountered error
"/tmp/run_ls.sh: line 2: cd: /home/tkyte/tmp: Not a directory
"


ops$tkyte%ORA11GR2>


If I replace the cd/ls with /usr/bin/id - you can see what is happening:



ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> select * from ls;

LINE
-------------------------------------------------------------------------------
uid=500(tkyte) gid=500(tkyte) groups=500(tkyte),501(ora11gr2) context=user_u:sy
stem_r:unconfined_t


ops$tkyte%ORA11GR2> connect ops$tkyte/foobar@ora11gr2_2
Connected.
ops$tkyte%ORA11GR2> select * from ls;

LINE
-------------------------------------------------------------------------------
uid=501(ora11gr2) gid=501(ora11gr2) groups=501(ora11gr2) context=user_u:system_
r:unconfined_t


ops$tkyte%ORA11GR2>

Thanks

Ernesto Villarruel, May 08, 2012 - 8:08 pm UTC


Excelent !!!!! Thanks Tom !!!

The database is ORACLE RAC (Exadata)

I connect to node 1 (uxdxdb01) of cluster.

This is my string of connection.
I use the scan DBM.


uxdxdb01{dbm1}: /tmp $ sqlplus vicxx96@dbm

SQL*Plus: Release 11.2.0.3.0 Production on Mar May 8 19:56:39 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>
SQL> select * from ls;

LINE
--------------------------------------------------------------------------------
uid=1000(grid) gid=1001(oinstall) groups=1001(oinstall),1002(dba),1003(racoper),
1004(asmdba),1006(asmadmin)


and Again , Thank you so much, Tom.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here