very useful material. Thanks for the same

This is exactly what I needed to see in an example of interacting with the operating system. Trying to persuade the developers to use Java vs Perl and this is what I needed.

Thanks again.

I had never imagined that this could be done so easily. Thank you.

Great material , it's exactlly what I was looking for
10x you a lot

hi tom
your reply on extrnal procedure works
Thanks

I am also trying to run executable from java
i am getting this error

I am running Pesonal Oracle 8.1.7 on windows 98

SQL>  exec rc('c:\windows\command /c dir');
java.io.IOException
at oracle.aurora.java.lang.OracleProcess.create(OracleProcess.java)
at oracle.aurora.java.lang.OracleProcess.construct(OracleProcess.java)
at java.lang.Runtime.execInternal(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at Util.RunThis(Util:11)

PL/SQL procedure successfully completed. 

hi tom

it was dir c:\windows\command.com

when i run
exec rc('c:\windows\command.com /c dir');

it never come back

i examined udump file it looks like

Dump file d:\oracle\admin\swami\udump\ORA10653.TRC
Thu Nov 22 09:34:37 2001
ORACLE V8.1.7.0.0 - Production vsnsta=0
vsnsql=e vsnxtr=3
Windows 95 V4.10, OS V192.0, CPU type 586
Oracle8i Personal Edition Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production
Windows 95 V4.10, OS V192.0, CPU type 586
Instance name: swami

Redo thread mounted by this instance: 1

Oracle process number: 13

Windows thread id: -710243, image: ORACLE.EXE


*** 2001-11-22 09:34:37.720
*** SESSION ID:(17.10) 2001-11-22 09:34:37.610
Specified COMMAND search directory bad Microsoft(R) Windows 98
(C)Copyright Microsoft Corp 1981-1998. D:\Oracle\Ora81\DATABASE>

any idea.

Thanks

who is Steve ?

:-)

hi tom,

i ran

c:\windows\command.com /c dir

on dos prompt it works fine
and i also created do_dit.bat and it also works fine

thanks

I think 'Steve' is Tom's alter ego -- you know, the one who is answering questions and such on Thanksgiving! Thought you (Tom) were taking a break? OK, so I'm *reading* AskTom on Thanksgiving... but that's just because I haven't had the turkey yet.

hi tom
Sorry for disturbance on thanks giving but my problem is critical i have to implement this on coming MONDAY

when i run
exec rc('c:\windows\command.com /c dir');

it never come back

THIS TIME I SET SQL_TRACE = TRUE

AND LOG FILE IS AS FOLLOWS

I KILLED SESSION FOR THREE TIMES AND CHECK LOG FILE EACH TIME. IT IS SIMILER EVERY TIME

Dump file d:\oracle\admin\swami\udump\ORA51445.TRC
Thu Nov 22 11:16:57 2001
ORACLE V8.1.7.0.0 - Production vsnsta=0
vsnsql=e vsnxtr=3
Windows 95 V4.10, OS V192.0, CPU type 586
Oracle8i Personal Edition Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production
Windows 95 V4.10, OS V192.0, CPU type 586
Instance name: swami

Redo thread mounted by this instance: 1

Oracle process number: 10

Windows thread id: -341771, image: ORACLE.EXE


*** 2001-11-22 11:16:57.070
*** SESSION ID:(11.3) 2001-11-22 11:16:57.010
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=35 dep=0 uid=18 oct=42 lid=18 tim=0 hv=2767092717 ad='4332a9c'
alter session set sql_trace = true
END OF STMT
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=0
=====================
PARSING IN CURSOR #1 len=87 dep=0 uid=18 oct=47 lid=18 tim=0 hv=3739629728 ad='433c588'
BEGIN RC('c:\windows\command.COM /c exp scott/tiger file=c:\xx.dmp tables=emp'); END;
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=0
=====================
PARSING IN CURSOR #2 len=64 dep=1 uid=0 oct=2 lid=0 tim=0 hv=3686129482 ad='477a920'
insert into javasnm$(short, longname, longdbcs) values(:1,:2,:3)
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=0
=====================
PARSING IN CURSOR #3 len=46 dep=1 uid=0 oct=3 lid=0 tim=0 hv=2044415509 ad='475d6b4'
select longname from javasnm$ where short = :1
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
=====================
PARSING IN CURSOR #4 len=57 dep=1 uid=0 oct=3 lid=0 tim=0 hv=467722929 ad='46be564'
SELECT max(version) FROM "SYS"."JAVA$POLICY$SHARED$TABLE"
END OF STMT
PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=1,cu=4,mis=0,r=1,dep=1,og=4,tim=0
STAT #4 id=1 cnt=1 pid=0 pos=0 obj=0 op='SORT AGGREGATE '
STAT #4 id=2 cnt=2 pid=1 pos=1 obj=16803 op='TABLE ACCESS FULL JAVA$POLICY$SHARED$TABLE '
=====================
PARSING IN CURSOR #4 len=57 dep=1 uid=0 oct=3 lid=0 tim=0 hv=467722929 ad='46be564'
SELECT max(version) FROM "SYS"."JAVA$POLICY$SHARED$TABLE"
END OF STMT
PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=1,cu=4,mis=0,r=1,dep=1,og=4,tim=0
STAT #4 id=1 cnt=1 pid=0 pos=0 obj=0 op='SORT AGGREGATE '
STAT #4 id=2 cnt=2 pid=1 pos=1 obj=16803 op='TABLE ACCESS FULL JAVA$POLICY$SHARED$TABLE '
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
=====================
PARSING IN CURSOR #4 len=147 dep=1 uid=0 oct=3 lid=0 tim=0 hv=3013728279 ad='468ad64'
select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with (grantee#=:1 or grantee#=1) and privilege#>0
END OF STMT
PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=6,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=6,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=6,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #4 id=1 cnt=37 pid=0 pos=0 obj=0 op='CONNECT BY '
STAT #4 id=2 cnt=20 pid=1 pos=1 obj=0 op='CONCATENATION '
STAT #4 id=3 cnt=1 pid=2 pos=1 obj=102 op='INDEX RANGE SCAN '
STAT #4 id=4 cnt=20 pid=2 pos=2 obj=102 op='INDEX RANGE SCAN '
STAT #4 id=5 cnt=19 pid=1 pos=2 obj=83 op='TABLE ACCESS BY USER ROWID SYSAUTH$ '
STAT #4 id=6 cnt=55 pid=1 pos=3 obj=102 op='INDEX RANGE SCAN '
=====================
PARSING IN CURSOR #4 len=147 dep=1 uid=0 oct=3 lid=0 tim=0 hv=3013728279 ad='468ad64'
select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with (grantee#=:1 or grantee#=1) and privilege#>0
END OF STMT
PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=6,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=6,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=6,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #4 id=1 cnt=37 pid=0 pos=0 obj=0 op='CONNECT BY '
STAT #4 id=2 cnt=20 pid=1 pos=1 obj=0 op='CONCATENATION '
STAT #4 id=3 cnt=1 pid=2 pos=1 obj=102 op='INDEX RANGE SCAN '
STAT #4 id=4 cnt=20 pid=2 pos=2 obj=102 op='INDEX RANGE SCAN '
STAT #4 id=5 cnt=19 pid=1 pos=2 obj=83 op='TABLE ACCESS BY USER ROWID SYSAUTH$ '
STAT #4 id=6 cnt=55 pid=1 pos=3 obj=102 op='INDEX RANGE SCAN '
=====================
PARSING IN CURSOR #4 len=147 dep=1 uid=0 oct=3 lid=0 tim=0 hv=3013728279 ad='468ad64'
select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with (grantee#=:1 or grantee#=1) and privilege#>0
END OF STMT
PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #4 id=1 cnt=14 pid=0 pos=0 obj=0 op='CONNECT BY '
STAT #4 id=2 cnt=4 pid=1 pos=1 obj=0 op='CONCATENATION '
STAT #4 id=3 cnt=1 pid=2 pos=1 obj=102 op='INDEX RANGE SCAN '
STAT #4 id=4 cnt=4 pid=2 pos=2 obj=102 op='INDEX RANGE SCAN '
STAT #4 id=5 cnt=3 pid=1 pos=2 obj=83 op='TABLE ACCESS BY USER ROWID SYSAUTH$ '
STAT #4 id=6 cnt=25 pid=1 pos=3 obj=102 op='INDEX RANGE SCAN '
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
=====================
PARSING IN CURSOR #4 len=147 dep=1 uid=0 oct=3 lid=0 tim=0 hv=3013728279 ad='468ad64'
select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with (grantee#=:1 or grantee#=1) and privilege#>0
END OF STMT
PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=0
FETCH #4:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=0,dep=1,og=4,tim=0
STAT #4 id=1 cnt=14 pid=0 pos=0 obj=0 op='CONNECT BY '
STAT #4 id=2 cnt=4 pid=1 pos=1 obj=0 op='CONCATENATION '
STAT #4 id=3 cnt=1 pid=2 pos=1 obj=102 op='INDEX RANGE SCAN '
STAT #4 id=4 cnt=4 pid=2 pos=2 obj=102 op='INDEX RANGE SCAN '
STAT #4 id=5 cnt=3 pid=1 pos=2 obj=83 op='TABLE ACCESS BY USER ROWID SYSAUTH$ '
STAT #4 id=6 cnt=25 pid=1 pos=3 obj=102 op='INDEX RANGE SCAN '

IT HANGS HERE EVERY TIME

hi tom

finally i am able to run OS command from pl/sql.but from
Windws NT not Windows 98.

Now I can run os command using extrnal procedure
and Java stored procedure.

Thank you very much for your co-opration

Tom
Thanks for the great code, but I'm hitting limitations using '>', '|' and other metacharacters. Please could you explain why my attempts fail. I can only overcome the problem by writing the string I want to execute to a script (using utl_file, doing a chmod +x on that and then executing it). As you can see the use of '|' returns and error, the use of '>' is ignored.

1.) Granted the following privs from sys:
begin                                       
    dbms_java.grant_permission     
    ('SCOTT',                    
     'java.io.FilePermission',     
     '/tmp/*',                
     'execute');         
    dbms_java.grant_permission     
    ('SCOTT',                    
     'java.io.FilePermission',     
     '/usr/bin/*',                
     'execute');         
    dbms_java.grant_permission     
    ('SCOTT',                    
     'java.lang.RuntimePermission',
     '*',                          
     'writeFileDescriptor' );  
-- try to accomodate input redirection    
    dbms_java.grant_permission     
    ('SCOTT',                    
     'java.lang.RuntimePermission',
     '*',                          
     'readFileDescriptor' );      
-- try to accomodate input/output redirection    
    ('SCOTT',                    
     'java.io.FilePermission',     
     '/tmp/*',                
     'read,write'); 
-- commit;        
end;
/


2.) changed RC to output the return code:
CREATE OR REPLACE procedure RC(p_cmd in varchar2)
as
  x number;
begin
  x := run_cmd(p_cmd);
  DBMS_OUTPUT.PUT_LINE ( 'run_cmd returned : '||rpad(x, 3, ' ')||' for '|| p_cmd); 
end;
/


3.) the results
SQL> set serveroutput on
SQL> 
SQL> set serveroutput on
SQL> 
SQL> declare
  2    dummy varchar2(50);
  3  begin
  4    dbms_output.enable(1000000);
  5    dbms_java.set_output(1000000);
  6    
  7    -- the following work fine
  8    -- rc('/usr/bin/ps -ef');
  9    -- rc('/usr/bin/pwd');
 10    -- rc('/usr/bin/touch /tmp/t.txt');
 11    
 12    -- the following don't work 
 13    rc('/usr/bin/ps -ef | /usr/bin/grep oracle');
 14    rc('/usr/bin/pwd > /tmp/pwd.log');
 15    -- rc('/usr/bin/ksh -c "/usr/bin/pwd > /tmp/pwd.log"');
 16  end;
 17  /
run_cmd returned : 1   for /usr/bin/ps -ef | /usr/bin/grep oracle
/apps/oracle/product/8.1.7/dbs
run_cmd returned : 0   for /usr/bin/pwd > /tmp/pwd.log

PL/SQL procedure successfully completed.

4.) From Unix:
ls -ltr /tmp/pwd.log
/tmp/pwd.log: No such file or directory
 

He Tom,

Sorry, for the mixup with Steve (Feuerstein, that is) whose stuff I was studying at that time. I just saw it today, hope this clears it.

Regards
Henk-Jan

Tom,
I found this information to be very helpful. The step-by-step instructions were easy to follow. I have a question about using the ftp command instead of the ps command. When I run the ps command everything work fine, but when I run the ftp command ("/usr/bin/ftp -vin < /public/parfile.txt"), the procedure just hangs.

What's seems to be causing this problem. I did assign execute permissions via the dbms_java package.

One more thing... I'm running 8.1.7 (64-bit) on HP-UX 11.00 (64-bit).

I bet the above reader will need to do the FTP from a shell script if he wants to use '<'. I had a similar problem higher up in this thread.

Your information about using .netrc worked to some degree. I woks when I run the ftp command from the unix prompt, but it still hangs when I attempt to execute from SQL*PLUS. I hope you can help me resolve yet another issue.

Thanks!

I created shell script and I execute the shell script from with the procedure. Thanks for all the help.

Randy

In my comments higher up I referred to writing the command to a file, doing a chmod +x etc. Obviously that's only if you don't know the command you want to run. Creating a standard ftp shell script with predefined input parms is better. Maybe the way you implemented it?? From what I can remember the DBMS_PIPE solution (see this site) doesn't have the special character limitation. Java solution just seems neater.

Apologies for my total lack of java knowledge (I'm getting there...), but your example (which worked fine in 8i) appears to bomb out under my 9i version.  Could you post if the same code works under one of your 9i versions?

SQL> variable x number;
SQL> set serveroutput on
SQL> exec dbms_java.set_output(100000);
SQL> exec :x := RUN_CMD('/usr/bin/ls');
java.lang.ArrayIndexOutOfBoundsException
at Util.RunThis(Util:14)

Cheers
Connor 

I just removed the String array definition on the PL/SQL wrapper
function and the Java method. I've included all the code again (and an
example) below. I do not fully know why it worked in 8i.
My assumption is that the JVM-PL/SQL data type pickler may have
automatically promoted non-array types to arrays when required. Or 8i
was less exacting that in 9i.


ops$tkyte@ORA9I.WORLD> begin
  2    dbms_java.grant_permission
  3    ('RT_TEST',
  4     'java.io.FilePermission',
  5     '/usr/bin/ls',
  6     'execute');
  7  
  8    dbms_java.grant_permission
  9    ('RT_TEST',
 10     'java.lang.RuntimePermission',
 11     '*',
 12     'writeFileDescriptor' );
 13  end;
 14  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> connect rt_test/rt_test
Connected.
ops$tkyte@ORA9I.WORLD> @login

rt_test@ORA9I.WORLD> create or replace and compile
  2  java source named "Util"
  3  as
  4  import java.io.*;
  5  import java.lang.*;
  6  
  7  public class Util extends Object
  8  {
  9    public static int RunThis(String args)
 10    {
 11    Runtime rt = Runtime.getRuntime();
 12    int        rc = -1;
 13  
 14    try
 15    {
 16       Process p = rt.exec(args);
 17  
 18       int bufSize = 4096;
 19       BufferedInputStream bis =
 20        new BufferedInputStream(p.getInputStream(), bufSize);
 21       int len;
 22       byte buffer[] = new byte[bufSize];
 23  
 24       // Echo back what the program spit out
 25       while ((len = bis.read(buffer, 0, bufSize)) != -1)
 26          System.out.write(buffer, 0, len);
 27  
 28       rc = p.waitFor();
 29    }
 30    catch (Exception e)
 31    {
 32       e.printStackTrace();
 33       rc = -1;
 34    }
 35    finally
 36    {
 37       return rc;
 38    }
 39    }
 40  }
 41  /

Java created.

rt_test@ORA9I.WORLD> create or replace
  2  function RUN_CMD(p_cmd in varchar2) return number
  3  as
  4  language java
  5  name 'Util.RunThis(java.lang.String) return integer';
  6  /

Function created.

rt_test@ORA9I.WORLD> create or replace procedure RC(p_cmd in varchar2)
  2  as
  3    x number;
  4  begin
  5    x := run_cmd(p_cmd);
  6  end;
  7  /

Procedure created.

rt_test@ORA9I.WORLD> variable x number;
rt_test@ORA9I.WORLD> set serveroutput on
rt_test@ORA9I.WORLD> exec dbms_java.set_output(100000);

PL/SQL procedure successfully completed.

rt_test@ORA9I.WORLD> exec :x := RUN_CMD('/usr/bin/ls /tmp');
10514.ksh
10565.sql
...
xxx.dat
xxx.dbf
zz

PL/SQL procedure successfully completed.

rt_test@ORA9I.WORLD> 

 

Tom-

procedure dbms_java.grant_permission is available from 8.1.6. Is there any workaround available in 8.1.5 to achieve the same functionality. I am using 8.1.5 on windows NT. It throws the following error if i try to assign permission to any user...

PLS-00302: component 'GRANT_PERMISSION' must be declared

thanks,
senthil

I am looking at making a Java-procedure like the ones you have shown to run a program on our Unix server. The question I have is will I be able to have a user on a client machine run this java-procedure from a Form(4.5 now 6 coming) and get results back from the program on the server. I have seen a number of other news group posts regarding this with no answer and thought I would ask here where the experts are.

Hi Tom,
Content proved to be very useful.
But, I am facing a problem.
The ksh I am calling from oracle using this utility, has some parameters. Now, some of the parameters contain the spaces. While running the ksh from unix we just put these parameters in double quotes. But, when calling from oracle, it identifies them as delimiters and hence treat as separate parameters. Is there a way that I can make it avoid the spaces which are inside double quotes. Or can I specify some other delim? Pls let me know. It's really urgent.

Tom,

I cut and past your solution, then modified the procedure
names and changed the grant_permission to /usr/ucb/ps because that is what I found through the which command
on my box (/usr/bin/ps appears to be identical anyway.)
But when I execute the command, I am getting an error that
I don't know where to begin debugging... 

SQL> exec cmd( 'ps -f' );
java.lang.ArrayIndexOutOfBoundsException
at MyCommand.RunThis(MYCOMMAND:14)

PL/SQL procedure successfully completed.

.. it works the same for /usr/ucb/ps ..

You have been a great help in the past - thanks - and I
hope you will help one more time. 

I hate when the answer implies I didn't do my home work :-(
Both yesterday and today I searched for ArrayIndexOutOfBoundsException throughout the archives, but the search failed to return anything... I never thought the
answer would be right under my nose :-( I wonder why your site search failed to find this?

Anyway, it is working now and you have been most gracious - thanks.

Hi tom,
I am facing a problem of similar type.
I need to change permission in few files which are made from utl_file though oracle onli. I think this solution will help me. But when i scearch dbms_java in all objects i cannot find there. Is there anything wrong in my oracle installation. When i scearh for dbms_java in /$oracle_HOME/rdbms/admin i cant find there also so how to load this ?
But no doubt the explanation is really good
cheers,
nitin

Tom -

Your examples work great, but I'm having some trouble running my particular command using your script.

I'm trying to use scp to move files from the database to another box. I can run this from the shell a-ok, but when run via your class, it returns a 1 (failure) every time. Indeed, the file does not exist in the new location when it is all said and done. Can you help me out?

Again, the following works from the shell on the db box:
/usr/bin/scp /u01/home/cnelson/flat_files/CSV2.txt cnelson@pspdwhp1:/u01/home/cnelson/CSV2.txt

Thanks again for your sage advice.

I managed to alter your example so that it returns the command line output as a varchar2. However, some of my commands output more than 32K of data. Is there a way to pass more than 32K of data from Java to PL/SQL? Could you pass the command line output as a CLOB or break it into a varray? Thanks for any suggestions you might have.

(NOTE: For others that may try this in 8.1.7. There is a bug (#1322857) that limits the string length that may be returned by a java stored function to 4K. If you write it as a java stored procedure you may pass a string up to 32K in length)

I am running a unix script exec_cmd.sh using rc.exec as per your example. The script is simply
#!/bin/sh
$@
#finish

When a send several parameters including one that is a string with lots of white spaces , the string is being treated as several parameters rather than just one, even when I hav enclosed the string in double qoutes.

rc ('/usr/users/cdb_env/exe/exec_cmd.sh /usr/users/cdb_env/exe/
qsnd 1 triggr yrdman 1 "+000000001+000000001+000000001DC1 DL1 steve PC" ' )

When I list the parameters that exec_cmd.sh is receiving I get the following :
/usr/users/cdb_env/exe/qsnd
1
triggr
yrdman
1
"+000000001+000000001+000000001DC1
DL1
steve
PC"

It seems like the quoted string is being split into several parameters. I want the string to be a single parameter.

Can you help? I do not know what the problem is?

Hi, i use a java procedure mostly similar to this one, but when the command is terminated, a NON-EXISTANT PROCESS stays on the server (NT 4.0). Those processes seem to take some memory. So when i execute many jobs, they take a lot of memory and slow down my server. When i stop my database, they disapear, but i can't stop my database at anytime.

Do you have any idea of what might causes that?

The info is very useful. Many thanks.
I am able to follow the example and get the results as stated.

But what I needed is to run a unix script (with different unix commands), instead of a unix command.

I have a script 'tsp1.txt' in
    /u01/home/sinvb/tong

tsp1.txt
--------
ls -la


when I run
SQL> exec rc('/u01/home/sinvb/tong/tsp1.txt');
PL/SQL procedure successfully completed.

but the files are not listed.

when I run the command
'tsp1.txt' at '/u01/home/sinvb/tong'
the files get listed.

I tried different alternative
1) modified tsp1.txt to
    sh -c 'ls -la'
2) copy the file tsp1.txt to /usr/bin
   and 
    exec rc('/usr/bin/tsp1.txt');
both show the same result
PL/SQL procedure successfully completed.

pls adv what is the pbm and what must I do.
the ultimate aim is to prepare tsp1.txt as
a unix script which contains various unix command.

thanks for your help in advance.

i also noticed that if i run
exec rc('/usr/bin/ps')
nothing is displayed.

but i got results if i run
exec rc('/usr/bin/ps -ef');

but if i execute the command 'ps',
i get something displayed.
is there a reason why ???


 
 

Try to execute the following:

exec rc('/usr/bin/sh /u01/home/sinvb/tong/tsp1.txt');

Note the sh command to open a shell and your script as 'parameter'. Be sure you have execute permissions for sh (with dbms_java.grant_permission).

This should output the desired result.

Hi Tom,
- Sorry, I am interesting to use Java in Oracle, can you advice on a book or any good place to learn about Oracle Java quickly?
- I have Win2k, Oracle9 R2: Please, explain this output:

SQL> exec rc('E:\WINNT\command.* /c dir');
java.lang.ArrayIndexOutOfBoundsException
at Util.RunThis(Util.java:14)

PL/SQL procedure successfully completed. 

Hi Tom

This is excellent piece of Code.

i have one clarification. Is it possible to call a particular command in other unix server.

Ie My oracle DB is in Unix server A
My ETL tool is running in Unix server B
I want to call the ETL Process as Commandline from Java procedure. Will it need to login into Server B. If so How to do it.

hi tom,

i have oracle ee 8.1.7 on windows nt 4.0 with service pack 6a. i want to execute host command from database.

so i follow ur steps as below.

SQL> CONNECT SYSTEM@BABUL
Enter password: *******
Connected.
SQL> begin
  2  dbms_java.grant_permission
  3  ('SCOTT',
  4  'java.io.FilePermission',
  5  'C:\WINNT',
  6  'execute');
  7  
  8  dbms_java.grant_permission
  9  ('SCOTT',
 10  'java.lang.RuntimePermission',
 11  '*',
 12  'writeFileDescriptor' );
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> connect scott@babul
Enter password: *****
Connected.
SQL> 
SQL> create or replace and compile
  2  java source named "Util"
  3  as
  4  import java.io.*;
java source named "Util"
                  *
ERROR at line 2:
ORA-29536: badly formed source: Encountered "<EOF>" at line 1, column 17.

Was expecting:

";" ...

why i am geting this error.

thanks nikunj 

what version of sqlplus do you have.

This:

ops$tkyte@ORA817DEV> create or replace and compile
  2  java source named "Util"
  3  as
  4  import java.io.*;
java source named "Util"
                  *
ERROR at line 2:
ORA-29536: badly formed source: Encountered "<EOF>" at line 1, column 17.
Was expecting:
";" ...



is what I get when I use 8.0 sqlplus which didn't understand "java source".  It just sucked the code upto the first ';' and submitted it (since sqlplus didn't know back then that java source 

a) existed
b) could contain ';' like plsql can


suggest you get your client software updated. 

Hi Tom,

I just tried the answer here but I got the following messssage. What should I do? Thanks!  --steve

SQL> call testjava.run_cmd('ls') into :y;
java.security.AccessControlException: the Permission (java.io.FilePermission <<ALL FILES>> execute)
has not been granted by dbms_java.grant_permission to
SchemaProtectionDomain(SRS|PolicyTableProxy(SRS))
at java.security.AccessControlContext.checkPermission(AccessControlContext.java)
at java.security.AccessController.checkPermission(AccessController.java)
at java.lang.SecurityManager.checkPermission(SecurityManager.java)
at oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java)
at java.lang.SecurityManager.checkExec(SecurityManager.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at Util.RunThis(Util:13)

Call completed.
 

Dear Tom,
I want to run class placed in one directory. For this i am giving permissions as follows

begin
dbms_java.grant_permission
('SALES',
'java.io.FilePermission',
'/usr/java130/jre/bin/java',
'execute');

dbms_java.grant_permission
('SALES',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
end;
/

now when i am trying to execute as below
exec rc('/usr/java130/jre/bin/java -version');

it is not giving any output. but if i run like
exec rc('/usr/java130/jre/bin/java');
it is iving output.
Pls let me know why it is not accepting parameters.

Is there a way to call unix command in oracle programs/ packages?


Dawar

I was able to run your example ps -ef with no problem.

I create a simple shell script on UNIX to copy a file rfk.scr:
cp /apps/actappl/rfk.test /apps/actappl/rfk.test2

I can run this with no problem using the Oracle account on UNIX.

When I try to run it using the Java callout nothing happens, no errors no copy.

DECLARE
t NUMBER(9);
BEGIN

t := act_dw_sys_events.run_cmd('/apps/actappl/rfk.scr');
END;

Any suggestions?

Thanks Rob

I want to execute a second perl script into the perl script called by the Java procedure like:
system("/path/perl_script.pl argv");
But
But the java grant permissions seem to allow only the first perl script to be executed and this one ends when the system command is called.
here is the granted permissions
begin
dbms_java.grant_permission
('SUPER_MAN',
'java.io.FilePermission',
'/path/first_script.pl',
'execute,read ,wrtie');

dbms_java.grant_permission
('SUPER_MAN',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
end;
/
begin
dbms_java.grant_permission
('SUPER_MAN',
'java.io.FilePermission',
'/path/second_script.pl',
'execute,read ,wrtie');

dbms_java.grant_permission
('SUPER_MAN',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
end;
/
if it is not possible to call a second perl script inside the first one, I planned to call it separatly:
declare
x number;
begin
x := run_cmd('/path/first_script.pl argv');
x := run_cmd('/path/second_script.pl argv');
end;
/

Hi again Tom,
here is my part of code inside the first perl script that calls the second perl script:
...
$fichier = shift @ARGV;
print("test1 tahaa/mascot/data/make_input_txt.pl $fichier\n");
#we build a txt file here
$x = system("/tahaa/mascot/data/make_input_txt.pl $fichier");
print("x value is $x\n");
...

the dbms.output would be:
test1 /seq/murin/proteo/make_input_txt.pl /tahaa/mascot/data/20030327/F006236.dat

PL/SQL procedure successfully completed.

The x value is not printed. but when I execute the command manually (/seq/murin/proteo/make_input_txt.pl /tahaa/mascot/data/20030327/F006236.dat), the script works. So it really seems that the first script cannot execute the system command, altough it does when it is executed manually. What should I do to make it work?
thank you very much for your help

Hi Tom,

I am a dveloper.
When I try to creat a java stored procedure in database, I met the following message, what happend ? What requirement I need
to ask DBA?

Thanks!

DLIV>create or replace and compile
2 java source named "Util"
3 as
4 import java.io.*;
java source named "Util"
*
ERROR at line 2:
ORA-29547: Java system class not available: oracle/aurora/rdbms/Compiler

Tom,

I will appologize in advance because I think this might go a bit beyond the scope of the original question.

I modified your code to work on Windows and after some dumb mistakes on my part I can now execute commands at will from Oracle 9i stored procedures.

Thanks

There is one problem though that I have not been able to resolve yet. When I execute a batch file and that batch file then executes another program (not internal command) I get no output. It's like standard out has been redirected to the bit bucket. If I execute an internal command like 'dir' or 'set' I get the output in the stored procedure. I can also redirect that output to a file from within the batch file called from the stored procedure. But when I execute an external program, for example a "Hello World" java program or grep (windows version) to get some text, there is no output. The ERRORLEVEL indicates the program ran.

Any ideas what could be going on here?

Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production

SQL> connect / as sysdba

begin

   dbms_java.grant_permission
      ('CDB_TRAIN',
      'java.io.FilePermission',
      '/data4/cdb_train/exe/exec_cmd',
      'execute');

   dbms_java.grant_permission
      ('CDB_TRAIN',
       'java.lang.RuntimePermission',
       '*',
       'writeFileDescriptor');
 end;
/
PL/SQL procedure successfully completed.

exec dbms_java.grant_permission( 'CDB_TRAIN','SYS:java.io.FilePermission','/usr/cdb_train/exe/exec_cmd', 'execute' );

PL/SQL procedure successfully completed.


exec :x := pkg_utils.sp_cmd('test',3,'/data4/cdb_train/exe/db_locks');
java.security.AccessControlException: the Permission (java.io.FilePermission
/usr/cdb_train/exe/exec_cmd execute) has not been granted to CDB_TRAIN. The
PL/SQL to grant this is dbms_java.grant_permission( 'CDB_TRAIN',
'SYS:java.io.FilePermission', '/usr/c
db_train/exe/exec_cmd', 'execute' )
at java.security.AccessControlContext.checkPermission(AccessControlContext.java)
at java.security.AccessController.checkPermission(AccessController.java)
at java.lang.SecurityManager.checkPermission(SecurityManager.java)
at
oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java
)
at java.lang.SecurityManager.checkExec(SecurityManager.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at MyCommand.RunThis(MyCommand.java:14)

PL/SQL procedure successfully completed. 

Tom,
I'm getting the following error when I try to create the Util Java source:

SQL> sho err
Errors for JAVA SOURCE Util:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      Util:4: Superclass object of class Util not found.
0/0      Info: 1 errors
SQL> 

I get the same error on an 8.1.7.4 HP/UX instance, a Linux 9.2.0.3 instance and a 9.2.0.1 Windows instance.  Running the dbms_java.grant_permission, however, works fine on all three.  What is the "superclass object" of Util?  Is it one of the two imports just above the Util class declaration? 

Tom,
Here is the whole example:

SQL> connect sys@ncgd
Enter password: *****
Connected.
SQL> select version from v_$instance;

VERSION
-----------------
8.1.7.4.0

1 row selected.

SQL> CREATE OR REPLACE AND COMPILE
  2      JAVA SOURCE NAMED "Util"
  3     AS
  4      import java.io.*;
  5      import java.lang.*;
  6      
  7      public class Util extends object
  8      {
  9        public static int runthis(string args)
 10       {
 11       runtime rt = runtime.getruntime();
 12       int        rc = -1;
 13     
 14       try
 15       {
 16          process p = rt.exec(args);
 17     
 18          int bufsize = 4096;
 19          bufferedinputstream bis =
 20           new bufferedinputstream(p.getinputstream(), bufsize);
 21          int len;
 22          byte buffer[] = new byte[bufsize];
 23     
 24          // echo back what the program spit out
 25          while ((len = bis.read(buffer, 0, bufsize)) != -1)
 26             system.out.write(buffer, 0, len);
 27     
 28          rc = p.waitfor();
 29       }
 30       catch (exception e)
 31       {
 32          e.printstacktrace();
 33          rc = -1;
 34       }
 35       finally
 36       {
 37          return rc;
 38       }
 39       }
 40     }
 41  /

Warning: Java created with compilation errors.

SQL> sho err
Errors for JAVA SOURCE Util:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      Util:4: Superclass object of class Util not found.
0/0      Info: 1 errors
SQL> 

 

Oh.

After correcting that faux pax, there were a few other "spelling" mistakes I needed to correct... works like a charm now!

p.s. Note to self: don't use PL/Formatter on Java code!

I copied the same code but got an error

SQL> exec rc('/usr/bin/ps -ef');
java.lang.ArrayIndexOutOfBoundsException
at Util.RunThis(UTIL.java:14)

What is problem? 

In order to run your Java program, do I need to add entries to listener.ora and tnsnames.ora or any preparation or set-up to be done?
I'm running version 8.1.5.
Thanks in advance!

Here's what I got when I ran sql*plus:

SQL*Plus: Release 8.0.5.0.0 - Production on Tue Jun 10 14:52:27 2003

(c) Copyright 1998 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

With the phrase: With the Partitioning and Java options
Does it mean, Java is installed?

I have been using the Java Stored Procedures a lot to replace the Pro*C Codes and all the other workarounds we did in the past to execute a command on the operating system (mostly UNIX).
There's a very good article here
</code> http://www.mountainstorm.com/publications/javazine.html <code>
It helped me a lot for passing parameters to UNIX Shell.
{"/bin/sh", "-c", "Your Command") format has no surprises when executing anything on UNIX.

The username must be uppercase or it will report user or role doesnt exist error. This may need to be improved since pl/sql is not case sensitive traditionally.

Hi Tom

I've successfully run this in 8.1.7 and have put your amended code into 9.0.1.4 but get an error. Could you glance at it and tell me where I've gone wrong please?

ltop_owner@EU0084P> conn sys/eu0084psys@eu0084p as sysdba
Connected.
ltop_owner@EU0084P> ed
Wrote file afiedt.buf

1 begin
2 dbms_java.grant_permission
3 ('LTOP_OWNER',
4 'java.io.FilePermission',
5 'e:\orant_eu0084p\scripts\bandvisit.bat',
6 'execute');
7 dbms_java.grant_permission
8 ('LTOP_OWNER',
9 'java.lang.RuntimePermission',
10 '*',
11 'writeFileDescriptor' );
12* end;
ltop_owner@EU0084P> /

PL/SQL procedure successfully completed.

ltop_owner@EU0084P> conn ltop_owner/prodltop@eu0084p
Connected.
ltop_owner@EU0084P> ed
Wrote file afiedt.buf

1 create or replace and compile
2 java source named "Util"
3 as
4 import java.io.*;
5 import java.lang.*;
6 public class Util extends Object
7 {
8 public static int RunThis(String args)
9 {
10 Runtime rt = Runtime.getRuntime();
11 int rc = -1;
12 try
13 {
14 Process p = rt.exec(args);
15 int bufSize = 4096;
16 BufferedInputStream bis =
17 new BufferedInputStream(p.getInputStream(), bufSize);
18 int len;
19 byte buffer[] = new byte[bufSize];
20 // Echo back what the program spit out
21 while ((len = bis.read(buffer, 0, bufSize)) != -1)
22 System.out.write(buffer, 0, len);
23 rc = p.waitFor();
24 }
25 catch (Exception e)
26 {
27 e.printStackTrace();
28 rc = -1;
29 }
30 finally
31 {
32 return rc;
33 }
34 }
35* }
36 /

Java created.

ltop_owner@EU0084P> ed
Wrote file afiedt.buf

1 create or replace
2 function RUN_CMD(p_cmd in varchar2) return number
3 as
4 language java
5* name 'Util.RunThis(java.lang.String) return integer';
ltop_owner@EU0084P> /

Function created.

ltop_owner@EU0084P> ed
Wrote file afiedt.buf

1 create or replace procedure RC(p_cmd in varchar2)
2 as
3 x number;
4 begin
5 x := run_cmd(p_cmd);
6* end;
7 /

Procedure created.

ltop_owner@EU0084P> conn ltop_owner/prodltop@eu0084p
Connected.
ltop_owner@EU0084P> variable x number
ltop_owner@EU0084P> set serveroutput on
ltop_owner@EU0084P> exec dbms_java.set_output(100000);

PL/SQL procedure successfully completed.

ltop_owner@EU0084P> exec :x := RUN_CMD('/usr/bin/ls /tmp');
java.io.IOException: The handle is invalid.
at oracle.aurora.java.lang.OracleProcess.create(OracleProcess.java)
at oracle.aurora.java.lang.OracleProcess.construct(OracleProcess.java)
at java.lang.Runtime.execInternal(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at Util.RunThis(Util:11)

PL/SQL procedure successfully completed.

ltop_owner@EU0084P>

Hide my shame!!!! I'll just wait for the ground to open up and swallow me!

Yes the server is on Windoze and if I run the correct command it runs as it should. The power of cut 'n paste!!!

"The DBA formerly known as Steve."

Tom,

I'm having trouble passing the OS output back in a parameter to the calling PL/SQL binding function. I'd like to do this rather than rely on the dbms_output approach. I've added the following to the function call (per your instruction in your book):

public static int RunOsCmd(java.lang.String OsCmdLine,
java.lang.String[] CmdOutput)

Now I need to get the routine to stuff its output in the "CmdOutput" parameter. How would I do this?

Tom,

Here is my PL/SQL binding code and Java code and output from running the program. I can't seem to get the output in the return variable. As I'm not a Java dude, I suspect I'm not concatenating correctly or not handling the "buffer" variable correctly. What is the problem?

FUNCTION run_command_java (
fv_command IN VARCHAR2,
fv_output OUT VARCHAR2
)
RETURN NUMBER
AS
LANGUAGE JAVA
NAME 'NcgOSj.runOsCmd( java.lang.String,
java.lang.String[] ) return integer';

PROCEDURE run_command_java (
fv_command IN VARCHAR2,
fv_output OUT VARCHAR2
)
IS
ln_return_code NUMBER;
BEGIN
ln_return_code := run_command_java (fv_command, fv_output);
END run_command_java;

CREATE OR REPLACE AND COMPILE
JAVA SOURCE NAMED "NcgOSj"
AS
import java.io.*;
import java.lang.*;

public class NcgOSj extends Object
{
public static int runOsCmd(String OsCmdLine,
String[] CmdOutput)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;
String coutput = "";

if (OsCmdLine.length() < 1) {
System.out.println("USAGE: java NcgOSj.runOsCmd \'cmd\' OutputStringVariable");
System.exit(1);
}
try
{
// The exec() method in java.lang.Runtime is overloaded and
// can be called in different forms. The following use illustrates
// the simplest form of exec() which takes a string as a parameter.
//
// Execute the command using the Runtime object variable 'rt' and
// get the process 'p' which controls this command
Process p = rt.exec(OsCmdLine);

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 = bis.read(buffer, 0, bufSize)) != -1) {
System.out.write(buffer, 0, len);
coutput = coutput + buffer;
}
// Wait for the process 'p' to finish and get the return code
// from the process
rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
CmdOutput[0] = coutput;
return rc;
}
}
}
/

VARIABLE outpt VARCHAR2(2000)
SET serveroutput on
EXEC dbms_java.set_output(100000);
EXEC run_command_java ('/bin/ls /', :outpt);
bin
boot
dev
etc
home
initrd
lib
lost+found
misc
mnt
ncg01
ncg02
ncg03
ncg04
ncglx01
ncglx01_cp.log
ncglx01_cp.sh
ncglx01_mount.sh
nohup.out
opt
proc
root
sbin
tmp
tmpdir
usr
var

PL/SQL procedure successfully completed.

PRINT outpt

OUTPT
-----------------------------------------------------------
[B@e8955a6a

The byte array needs to be converted to a String before assigning to the OUT variable.
The while (read...) loop needs to be changed to the following:

// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1) {
System.out.write(buffer, 0, len);
ot = new String(buffer, 0, len) ;
coutput = coutput + ot ;
}

Where "ot" is a String variable declared in the program above like this:


Runtime rt = Runtime.getRuntime();
int rc = -1;
String coutput = " ";
String ot = " " ;


This seems to work when run on my system.

Thanks Kamal! Made those changes and it works just as expected.

Tell me, do you know how I could just convert the byte array variable "buffer" to a string inline instead of declaring the new variable "ot"? Something like:

// capture the output in a string variable
while ((len = bis.read(buffer, 0, bufSize)) != -1) {
coutput = coutput + toString (buffer, 0, len);
}

Wouldn't this use up less memory than allocating "new" memory for each iteration for the "ot" variable?

Tom,

Do you have any idea why the program above would hang on a call to sendmail? As long as I have done the dbms_java.grant_permission, the program works fine for /bin/ls, /bin/rm, etc., but when I issue the following command from SQL*Plus, it just hangs:

EXEC run_command_java (/usr/sbin/sendmail jwells@nichegroup.com < /home/oracle/smtest.txt', :outpt);

The server is on Red Hat Linux AS 2.1 and 9.2 of the database. Issuing the command line as oracle at the Unix prompt works fine. Does it work on your Linux box?

Jack,

You cannot pass more than one parameters in this case. As tom suggested: write a script and run the script. You can use pl/sql to write a script and change run mode and call the script.

BTW, why not use pl/sql send mail with attachemet?

Tom,
You were right, it was the "<" redirection... a shell thing. In my zeal to ensure only granular permissions were issued, I had avoided giving execute permission on /usr/sh. When I did, though, it worked fine. Now, the only thing that bothers me is this execute priv on /usr/sh...

Hi Tom,
We are looking to call a unix shell script from Oracle after an update/insert/delete happens on a table in the database. The above solution looks kind of neat, however, just wondering whether the procedure RC above could be called from a database trigger and made to execute a shell script (lets say test.sh) OR do you have any other suggestions which might help? We are using Oracle version 8.1.7

Thanks in advance!

Raja

Hi Tom,
In simple words, what we are trying to do here is just notify the java webserver that for example an update has occured, based on which it will initiate other necessary actions on its side on the webserver.

Actually, I do not code java. Our java developers just gave me an idea of what they want (for example to be able to execute host calls - shell scripts from Oracle which will notify the webserver for either executing something on the webserver with respect to the database update event on a table or maybe run shell script which could FTP some files or other information for each updated)

Let me know if this is enough or need more details.

Regards,
Raja

Hi Tom,
Thanks a lot for a prompt reply. This is certainly helping. Actually, just wanted to know if JMS supported in Oralce 8.1.7 as, I just happened to go to metalink (let me know if I am looking at the right article).....

</code> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=232301.1 <code>

and it mentions.....
The following requirements need to be satisfied when using JMS-AQ:

OC4J 9.0.3.0.0
Supported RDBMS version
Oracle9i Database 9.0.1.4 (Patch: 2517300)
Oracle9i Database 9.2.0.2 (Patch: 2632931)
For further information, see:

Note:230372.1

Send JMS Messages to OJMS Queue Fails with PLS-00306

You,ve been very helpful!
Regards,
Raja

Hi Tom,

Thanks for this nice solution, currently I am using it to call sqlldr to load a file into a table from plsql, since it will run sqlldr as user oracle, my boss worrys about any possible security hole here, please give me some your comments

Thanks

Tom,

I noticed that you had put the person off who asked about using triggers, but I have a similar question. OK, I have a third party app that is inserting or updating into a table, tableA. I would like to set up a trigger on these actions such that it will call a batch file (say devtest.bat, sorry I had to use Windows for these guys). Can I use your method to pass a column name (call it Project_Name) from tableA to the devtest.bat file?

The insert/update is automatically committed by the application when the click on the OK button. No rollback opportunity, and no other ad-hoc application (i.e. sqlplus) access to the database.

I have your method working calling the devtest.bat file directly, but can't get a trigger working for this.

Thanks!

Tom's code works well. Question: is it possible to expand the solution to also populate the DIR_LIST table with file attributes such as size and last-mod date? If so, any code snippets to share?

Tom,
Can you post an axample of the "Util" java that will return the output to a variable or lob instead of dbms_output.

Thank you.

Hi Tom,

I have two Questions:

1. I defined the java source and wrapper function RUN_CMD with invoker-rights AUTHID CURRENT_USER in SCHEMA1 (GLOBAL) and granted the execute right on RUN_CMD to SCHEMA2 (WP).

After that I granted the execute and writeFileDescriptor permission with DBMS_JAVA.GRANT_PERMISSION to user WP (SCHEMA2).

If I trying to execute this functiom with user WP (SCHEMA2) :

...
GLOBAL.run_cmd('/oracle/u01/app/oracle/admin/BBSE/appscripts/ls.sh')
...


FEHLER in Zeile 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
/oracle/u01/app/oracle/admin/BBSE/appscripts/ls.sh execute) has not been
granted to GLOBAL. The PL/SQL to grant this is dbms_java.grant_permission(
'GLOBAL', 'SYS:java.io.FilePermission',
'/oracle/u01/app/oracle/admin/BBSE/appscripts/ls.sh', 'execute' )
ORA-06512: at "GLOBAL.RUN_CMD", line 0
ORA-06512: at line 5

What's wrong?

2. How I can write the wrapper function based on Java-class another user?

WP@BBSE> create or replace
2 function RUN_CMD( p_cmd in varchar2) return number
3 as
4 language java
5 name 'GLOBAL:Util.RunThis(java.lang.String[]) return integer';
6 /

Warnung: Funktion wurde mit Kompilierungsfehlern erstellt.

WP@BBSE> show error
Fehler bei FUNCTION RUN_CMD:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
4/3 PLS-00311: the declaration of
"GLOBAL:Util.RunThis(java.lang.String[]) return integer" is
incomplete or malformed


Thanks

1) worked for me.  See full example below (shows that the invoker OPS$TKYTE needs the priv, not the owner RT_TEST)



2) this works:

ops$tkyte@ORA920PC> create synonym "Util" for rt_test."Util"
  2  /
 
Synonym created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace
  2  function RUN_CMD(p_cmd in varchar2) return number
  3  as
  4  language java
  5  name 'Util.RunThis(java.lang.String) return integer';
  6  /
 
Function created.


And in this case -- OPS$TKYTE is the true invoker and OPS$TKYTE would need execute on ls (not RT_TEST)

ops$tkyte@ORA920PC> begin
  2    dbms_java.grant_permission
  3    ('RT_TEST',
  4     'java.io.FilePermission',
  5     '/bin/ls',
  6     'execute');
  7
  8    dbms_java.grant_permission
  9    ('RT_TEST',
 10     'java.lang.RuntimePermission',
 11     '*',
 12     'writeFileDescriptor' );
 13  end;
 14  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> @connect rt_test/rt_test
ops$tkyte@ORA920PC> set termout off
rt_test@ORA920PC> REM GET afiedt.buf NOLIST
rt_test@ORA920PC> set termout on
rt_test@ORA920PC>
rt_test@ORA920PC> create or replace and compile
  2  java source named "Util"
  3  as
  4  import java.io.*;
  5  import java.lang.*;
  6
  7  public class Util extends Object
  8  {
  9    public static int RunThis(String args)
 10    {
 11    Runtime rt = Runtime.getRuntime();
 12    int        rc = -1;
 13
 14    try
 15    {
 16       Process p = rt.exec(args);
 17
 18       int bufSize = 4096;
 19       BufferedInputStream bis =
 20        new BufferedInputStream(p.getInputStream(), bufSize);
 21       int len;
 22       byte buffer[] = new byte[bufSize];
 23
 24       // Echo back what the program spit out
 25       while ((len = bis.read(buffer, 0, bufSize)) != -1)
 26          System.out.write(buffer, 0, len);
 27
 28       rc = p.waitFor();
 29    }
 30    catch (Exception e)
 31    {
 32       e.printStackTrace();
 33       rc = -1;
 34    }
 35    finally
 36    {
 37       return rc;
 38    }
 39    }
 40  }
 41  /
 
Java created.
 
rt_test@ORA920PC> grant execute on "Util" to public
  2  /
 
Grant succeeded.
 
rt_test@ORA920PC>
rt_test@ORA920PC> create or replace
  2  function RUN_CMD(p_cmd in varchar2) return number
  3  authid current_user
  4  as
  5  language java
  6  name 'Util.RunThis(java.lang.String) return integer';
  7  /
 
Function created.

rt_test@ORA920PC> show errors
No errors.
rt_test@ORA920PC>
rt_test@ORA920PC> create or replace procedure RC(p_cmd in varchar2)
  2  authid current_user
  3  as
  4    x number;
  5  begin
  6    x := run_cmd(p_cmd);
  7  end;
  8  /
 
Procedure created.
 
rt_test@ORA920PC> grant execute on rc to public
  2  /
 
Grant succeeded.
 
rt_test@ORA920PC>
rt_test@ORA920PC> variable x number;
rt_test@ORA920PC> set serveroutput on
rt_test@ORA920PC> exec dbms_java.set_output(100000);
 
PL/SQL procedure successfully completed.
 
rt_test@ORA920PC>
rt_test@ORA920PC>
rt_test@ORA920PC> exec :x := RUN_CMD('/bin/ls /tmp');
afiedt.buf
BEQ2113
DCE2113
DEC2113
foo.dat
hostname.txt
ISPX2113
ITCP2113
kde-tkyte
ksocket-tkyte
log
LU622113
net2113
NMP2113
nscopy.tmp
nsmail-1.tmp
nsmail-2.tmp
nsmail-3.tmp
nsmail-4.tmp
nsmail-5.tmp
nsmail.eml
nsmail.html
nsmail.tmp
orbit-tkyte
RAW2113
SPX2113
ssh-XXDMz0hl
TCP2113
TCPS2113
US2113
VI2113
 
PL/SQL procedure successfully completed.
 
rt_test@ORA920PC>
rt_test@ORA920PC> @connect /
rt_test@ORA920PC> set termout off
ops$tkyte@ORA920PC> REM GET afiedt.buf NOLIST
ops$tkyte@ORA920PC> set termout on
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create synonym "Util" for rt_test."Util"
  2  /
 
Synonym created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace
  2  function RUN_CMD(p_cmd in varchar2) return number
  3  as
  4  language java
  5  name 'Util.RunThis(java.lang.String) return integer';
  6  /
 
Function created.
 
ops$tkyte@ORA920PC> show errors
No errors.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace procedure RC(p_cmd in varchar2)
  2  as
  3    x number;
  4  begin
  5    x := run_cmd(p_cmd);
  6  end;
  7  /
 
Procedure created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> variable x number;
ops$tkyte@ORA920PC> set serveroutput on
ops$tkyte@ORA920PC> exec dbms_java.set_output(100000);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> exec :x := RUN_CMD('/bin/ls /tmp');
java.security.AccessControlException: the Permission (java.io.FilePermission /bin/ls execute) has not been granted to OPS$TKYTE.
The PL/SQL to grant this is dbms_java.grant_permission( 'OPS$TKYTE', 'SYS:java.io.FilePermission', '/bin/ls', 'execute' )
at java.security.AccessControlContext.checkPermission(AccessControlContext.java:283)
at java.security.AccessController.checkPermission(AccessController.java:399)
at java.lang.SecurityManager.checkPermission(SecurityManager.java:545)
at oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java:267)
at java.lang.SecurityManager.checkExec(SecurityManager.java:800)
at java.lang.Runtime.exec(Runtime.java:548)
at java.lang.Runtime.exec(Runtime.java:418)
at java.lang.Runtime.exec(Runtime.java:361)
at java.lang.Runtime.exec(Runtime.java:325)
at Util.RunThis(Util.java:13)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC> exec :x := rt_test.RUN_CMD('/bin/ls /tmp');
java.security.AccessControlException: the Permission (java.io.FilePermission /bin/ls execute) has not been granted to OPS$TKYTE.
The PL/SQL to grant this is dbms_java.grant_permission( 'OPS$TKYTE', 'SYS:java.io.FilePermission', '/bin/ls', 'execute' )
at java.security.AccessControlContext.checkPermission(AccessControlContext.java:283)
at java.security.AccessController.checkPermission(AccessController.java:399)
at java.lang.SecurityManager.checkPermission(SecurityManager.java:545)
at oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java:267)
at java.lang.SecurityManager.checkExec(SecurityManager.java:800)
at java.lang.Runtime.exec(Runtime.java:548)
at java.lang.Runtime.exec(Runtime.java:418)
at java.lang.Runtime.exec(Runtime.java:361)
at java.lang.Runtime.exec(Runtime.java:325)
at Util.RunThis(Util.java:13)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>




 

hi tom,

i applied this script and i have error message :

SQL> ed
Wrote file afiedt.buf

  1  begin
  2        dbms_java.grant_permission
  3        ('scott',
  4         'java.io.FilePermission',
  5         '/usr/bin/ps',
  6         'execute');
  7       dbms_java.grant_permission
  8        ('scott',
  9         'java.lang.RuntimePermission',
 10         '*',
 11         'writeFileDescriptor' );
 12*   end;
SQL> /
begin
*
ERROR at line 1:
ORA-29532: appel Java arrêté par une exception Java non interceptée :
oracle.aurora.vm.IdNotFoundException: scott is not a user or role
ORA-06512: à "SYS.DBMS_JAVA", ligne 0
ORA-06512: à ligne 2


can you help me 

thanks
 

Hi Tom,

my two Guestions in not answered:

1. It is well that I can see the invoker need the priv. But if I give the priv to invoker and revoke that from owner - i see that:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
/oracle/u01/app/oracle/admin/BBSE/appscripts/ls.sh execute) has not been
granted to GLOBAL.


The Permission of this script must be granted to the OWNER too? That is wrong.

2. Wrapper function based on Java-class another user.
That work, if java class is not in package, but need also owner execute privs(see 1.)

But, if I put the class in package, that dosn't work:

WP@BBSE> @conn global@bbse
Kennwort eingeben: *****
GLOBAL@BBSE> create or replace and compile java source named util as
2 package de.berenbergbank.global.tools;
3
4 import java.io.BufferedReader;
5 import java.io.BufferedInputStream;
6 import java.io.InputStream;
7 import java.io.InputStreamReader;
8 import java.io.IOException;
9
10 import java.util.Collections;
11 import java.util.LinkedList;
12 import java.util.List;
13
14
15 /**
16 * Created by IntelliJ IDEA.
17 * User: Terletskyy
18 * Date: 06.01.2004
19 * Time: 13:26:35
20 * To change this template use Options | File Templates.
21 */
22 public class Util {
23
24 public static String run_cmd(String command) throws Exception
25 {
26 StringBuffer myBuffer = new StringBuffer();
27 Process p = Runtime.getRuntime().exec(command);
28 int out;
29
30 int bufSize = 4096;
31 BufferedInputStream bis = new BufferedInputStream(p.getInputStream(), bufSize);
32 byte buffer[] = new byte[bufSize];
33 int len;
34
35 // Echo back what the program spit out
36 while ( (len = bis.read(buffer, 0, bufSize)) != -1) {
37 myBuffer.append(new String(buffer, 0, len));
38 }
39
40 if ((out=p.waitFor()) != 0 ){
41 throw new Exception("Error: "+out);
42 }
43
44 return myBuffer.toString();
45 }
46
47 }
48 /

Java wurde erstellt.

GLOBAL@BBSE> GRANT EXECUTE ON GLOBAL."de/berenbergbank/global/tools/Util" TO PUBLIC;

Benutzerzugriff (Grant) wurde erteilt.


WP@BBSE> create synonym "de/berenbergbank/global/tools/Util" for GLOBAL."de/berenbergbank/global/tools/Util"
2 /

Synonym wurde angelegt.

WP@BBSE> CREATE OR REPLACE FUNCTION RUN_CMD (in_command IN VARCHAR2) RETURN VARCHAR2
2 AS LANGUAGE JAVA NAME 'de.berenbergbank.global.tools.Util.run(java.lang.String) return java.lang.String';
3 /

Funktion wurde erstellt.

WP@BBSE> variable x Varchar2(4000);
WP@BBSE> set serveroutput on
WP@BBSE> exec dbms_java.set_output(100000);

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

WP@BBSE> exec :x := RUN_CMD ('/oracle/u01/app/oracle/admin/BBSE/appscripts/ls.sh');
java.lang.NoSuchMethodException: No applicable method found
at
oracle.aurora.util.JRIExtensions.getMaximallySpecificMethod(JRIExtensions.java)
at
oracle.aurora.util.JRIExtensions.getMaximallySpecificMethod(JRIExtensions.java)
BEGIN :x := RUN_CMD ('/oracle/u01/app/oracle/admin/BBSE/appscripts/ls.sh'); END;

*
FEHLER in Zeile 1:
ORA-29531: no method run in class de/berenbergbank/global/tools/Util
ORA-06512: at "WP.RUN_CMD", line 0
ORA-06512: at line 1


Thanks,
Andriy

Sorry it must be

WP@BBSE> CREATE OR REPLACE FUNCTION RUN_CMD (in_command IN VARCHAR2) RETURN VARCHAR2
2 AS LANGUAGE JAVA NAME 'de.berenbergbank.global.tools.Util.run_cmd(java.lang.String) return java.lang.String';
3 /

Funktion wurde erstellt.

WP@BBSE> show error
Keine Fehler.
WP@BBSE> variable x Varchar2(4000);
WP@BBSE> set serveroutput on
WP@BBSE> exec dbms_java.set_output(100000);

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

WP@BBSE> exec :x := RUN_CMD('/oracle/u01/app/oracle/admin/BBSE/appscripts/ls.sh');
java.security.AccessControlException: the Permission (java.io.FilePermission
/oracle/u01/app/oracle/admin/BBSE/appscripts/ls.sh execute) has not been granted
to GLOBAL. The PL/SQL to grant this is dbms_java.grant_permission( 'GLOBAL',
'SYS:java.io.FilePer
mission', '/oracle/u01/app/oracle/admin/BBSE/appscripts/ls.sh', 'execute' )
at java.security.AccessControlContext.checkPermission(AccessControlContext.java)
at java.security.AccessController.checkPermission(AccessController.java)
at java.lang.SecurityManager.checkPermission(SecurityManager.java)
at
oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java
:267)
at java.lang.SecurityManager.checkExec(SecurityManager.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at de.berenbergbank.global.tools.Util.run_cmd(UTIL.java:26)
BEGIN :x := RUN_CMD('/oracle/u01/app/oracle/admin/BBSE/appscripts/ls.sh'); END;

*
FEHLER in Zeile 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
/oracle/u01/app/oracle/admin/BBSE/appscripts/ls.sh execute) has not been
granted to GLOBAL. The PL/SQL to grant this is dbms_java.grant_permission(
'GLOBAL', 'SYS:java.io.FilePermission',
'/oracle/u01/app/oracle/admin/BBSE/appscripts/ls.sh', 'execute' )
ORA-06512: at "WP.RUN_CMD", line 0
ORA-06512: at line 1

hi tom,

thanks for your help, all the procedures are succefull, but when I execute :

SQL>  exec rc('/usr/bin/ps -ef');
java.lang.ArrayIndexOutOfBoundsException
at Util.RunThis(Util.java:11)

PL/SQL procedure successfully completed.

I have Oracle 9i database.

thx for you help.
fakhri 

In redhat linux AS 2.1  with oracle 9i-2 your code worked fine.But while I am trying the following in win-2003 with oracle 9i-2 its giving the following error. Please help.
SQL> begin
  2   dbms_java.grant_permission
  3       ('DEVEL',
  4        'java.io.FilePermission',
  5        'c:\oracle\backup\abc.bat,
  6        'execute');
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
SYS.JAVA$POLICY$SHARED$0000001d
ORA-06512: at "SYS.DBMS_JAVA", line 0
ORA-06512: at line 2


 

'c:\oracle\backup\abc.bat

Is missing the closing tick - is that correct? Just an observation...

- Mark

file is present.It just hangs & giving the following error.
Same thing is happening with '*' too.

SQL> begin
  2   dbms_java.grant_permission
  3       ('DEVEL',
  4        'java.io.FilePermission',
  5        'c:\oracle\backup\*,
  6        'execute');
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
SYS.JAVA$POLICY$SHARED$0000001d
ORA-06512: at "SYS.DBMS_JAVA", line 0
ORA-06512: at line 2
 

closing tick ' is present in the actual code. I just made a typo after pasting it here.thanks!

Is line 5 correct?

'c:\oracle\backup\abc.bat,

It looks like it is missing the closing tick:
i.e., 'c:\oracle\backup\abc.bat',

Or is it just getting dropped in the copy and paste?

- Mark

AS its my test database , I just shutdown & restarted the database & everything seems to be normal now. Thanks!

The Beauty of Windows shutdown and restart, everything works fine.

:-))))))

Tom,

Is there any way to run this so that the o/s command is run as another user besides the 'oracle' user?

We want to implement this to run users' application scripts, but would like to limit potential security risks of users' scripts running as 'oracle'.

Thanks,

Robert.

Hi Tom,

A few months ago I tried to run a program from within Oracle using the procedure you described here,
(Actually it was a shell script) and it worked great! But just recently I tried to do the same thing on another machine, and now I get an exception:

java.lang.UnsatisfiedLinkError
at oracle.aurora.java.lang.OracleProcess.create(OracleProcess.java)
at oracle.aurora.java.lang.OracleProcess.construct(OracleProcess.java)
at java.lang.Runtime.execInternal(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at UsBblLoadingWrapper.load(UsBblLoadingWrapper:13)

Until now I have not been able to find the problem. Any ideas what could be going wrong?

Thanks,
Janick


this is the setup:
------------------
 Oracle9i

- The permissions:
    dbms_java.grant_permission('AMICUS', 'java.io.FilePermission', '/home/ami35/bin/loading_script.sh', 'execute');
    dbms_java.grant_permission('AMICUS', 'java.lang.RuntimePermission', '*', 'writeFileDescriptor');

- The class:

create or replace and compile
java source named "UsBblLoadingWrapper"
as
import java.io.*;
import java.lang.*;


public class UsBblLoadingWrapper extends Object {

    public static int load() {
        Runtime rt = Runtime.getRuntime();
        int rc = -1;
        System.out.println("\n\n ---START--- \n\n");
    
        try {
            Process p = rt.exec("/home/ami35/bin/loading_script.sh");
            
            int bufSize = 4096;
            BufferedInputStream bis = new BufferedInputStream(p.getInputStream(), bufSize);
            
            
            int len;
            byte buffer[] = new byte[bufSize];
            System.out.println("\n\n--stdout--\n");
            
            // Echo back what the program spit out
            while ((len = bis.read(buffer, 0, bufSize)) != -1) {
                System.out.write(buffer, 0, len);
            }
            
            BufferedInputStream errbis = new BufferedInputStream(p.getErrorStream(), bufSize);
            
            System.out.println("\n\n--stderr--\n");
            while ((len = errbis.read(buffer, 0, bufSize)) != -1) {
                System.out.write(buffer, 0, len);
            }
                        
            rc = p.waitFor();
        }
        catch (Throwable e) {
               e.printStackTrace();
               rc = -1;
        }
        finally    {
               return rc;
        }
    }
}
/


- the function:

create or replace
function TEST_EXTERNAL_PROGRAM return number
as
language java
name 'UsBblLoadingWrapper.load() return integer';
/


- the script:

#!/bin/sh
echo "test"


- the call:

SQL> set serveroutput on size 1000000
exec dbms_java.set_output(1000000)
DECLARE
           rc NUMBER;
BEGIN
         rc := TEST_EXTERNAL_PROGRAM();
         DBMS_OUTPUT.PUT_LINE('function returncode ' || rc);
END;
/SQL>
PL/SQL procedure successfully completed.

SQL>   2    3    4    5    6    7
---START---
java.lang.UnsatisfiedLinkError
at oracle.aurora.java.lang.OracleProcess.create(OracleProcess.java)
at oracle.aurora.java.lang.OracleProcess.construct(OracleProcess.java)
at java.lang.Runtime.execInternal(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at UsBblLoadingWrapper.load(UsBblLoadingWrapper:13)
function returncode -1

PL/SQL procedure successfully completed.

SQL> 

Hi Tom,

I found this very useful, but I have a question to ask. I have a requirement wherein the Database Server, 9iAS server & Reports Server(We put all the reports output files in this after the nightly batch jobs) all in a networked environment and the Client system is web-enabled Forms9i forms. We need to build an interface wherein the user needs to see all the files available under a designated folder in the Reports Server and should be allowed to print the same. We are perplexed how to get the List of files from the Reports server. The above problem(for which you have answered) deals with the DB server itself, but this one???? Any idea? Please send in.
Thanks and Regards
Sarada Priya

Tom,
We've successfully implemented this on one database, and now I'm trying to implement it on a new one, but it seems the thing doesn't work. Is there an error log somewhere that would show me what went wrong?
Here's the code for the procedure that I implemented:
create or replace and compile java source named "Util" as
  import java.io.*;
  import java.lang.*;
  
  public class Util extends Object  {
   public static int RunThis(String[] args)    {
    Runtime rt = Runtime.getRuntime();
    int        rc = -1;
  
    try    {
       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 = bis.read(buffer, 0, bufSize)) != -1)
          System.out.write(buffer, 0, len);
  
       rc = p.waitFor();
    }
    catch (Exception e)    {
       e.printStackTrace();
       rc = -1;
    }
    finally    {
       return rc;
    }
   }
  }
  /

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


As the SYS user, I granted the following privileges:
begin
      dbms_java.grant_permission
      ('ROCQ',
       'java.io.FilePermission',
       '/tahiti/bin/test.pl',
       'execute');
  
      dbms_java.grant_permission
      ('ROCQ',
       'java.lang.RuntimePermission',
       '*',
       'writeFileDescriptor' );
  end;
  /

The procedure that executes the system call:
CREATE OR REPLACE PROCEDURE rocq.test_runcmd IS

    resultat_rp        NUMBER;

BEGIN

    dbms_output.put_line('Beginning.');
    resultat_rp := RUN_CMD('perl /tahiti/bin/test.pl');
    dbms_output.put_line(resultat_rp);
    dbms_output.put_line('Ending.');

END;
/
show errors;

And the script that is called:
#!/usr/bin/perl -w

system "echo allo >allo";

Finally, the execution:
SQL> @ test_runcmd.pls

Procedure created.

No errors.
SQL> exec test_runcmd
Beginning.
-1
Ending.

PL/SQL procedure successfully completed.

Any ideas?
Thanks for your help! 

Just tried these grants:
begin
      dbms_java.grant_permission
      ('ROCQ',
       'java.io.FilePermission',
       'perl',
       'execute');

       dbms_java.grant_permission
      ('ROCQ',
       'java.io.FilePermission',
       '/usr/bin/perl',
       'execute');
 
      dbms_java.grant_permission
      ('ROCQ',
       'java.lang.RuntimePermission',
       '*',
       'writeFileDescriptor' );
  end;
  /

But the host call still doesn't work:

SQL> set serveroutput on size 1000000
exec dbms_java.set_output(1000000)
SQL> 
PL/SQL procedure successfully completed.

SQL> exec test_runcmd
Beginning.
java.lang.ArrayIndexOutOfBoundsException
at Util.RunThis(Util:8)
-1
Ending.

PL/SQL procedure successfully completed.


 

Just did as you suggested, but now I'm running into a classic Permissions problem. 
SQL> begin
  2    dbms_java.grant_permission
  ('ROCQ',
   'SYS:java.io.FilePermission',
   'perl',
   'execute');

  dbms_java.grant_permission
  ('ROCQ',
   'SYS:java.io.FilePermission',
   '/usr/bin/perl',
   'execute');

  dbms_java.grant_permission
  ('ROCQ',
   'SYS:java.io.FilePermission',
  3    4    5    6    7     '/tahiti/bin/test.pl',
   'execute');

  dbms_java.grant_permission
  ('ROCQ',
   'SYS:java.lang.RuntimePermission',
   '*',
   'writeFileDescriptor' );
end;
/

PL/SQL procedure successfully completed.

SQL> 
SQL> exec test_runcmd
Beginning.
java.security.AccessControlException: the Permission (java.io.FilePermission
<<ALL FILES>> execute) has not been granted to ROCQ. The PL/SQL to grant this is
dbms_java.grant_permission( 'ROCQ', 'SYS:java.io.FilePermission', '<<ALL
FILES>>', 'execute' )
at
java.security.AccessControlContext.checkPermission(AccessControlContext.java:207
)
at java.security.AccessController.checkPermission(AccessController.java:403)
at java.lang.SecurityManager.checkPermission(SecurityManager.java:549)
at
oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java
:267)
at java.lang.SecurityManager.checkExec(SecurityManager.java:788)
at java.lang.Runtime.exec(Runtime.java:270)
at java.lang.Runtime.exec(Runtime.java:195)
at java.lang.Runtime.exec(Runtime.java:152)
at Util.RunThis(Util:10)
-1
Ending.

PL/SQL procedure successfully completed.
 

sorry -- i cannot reproduce on 9ir2 myself (did you log out and back in after granting?)


ops$tkyte@ORA920PC> grant dba to rt_test identified by rt_test;
 
Grant succeeded.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> begin
  2    dbms_java.grant_permission
  3    ('RT_TEST',
  4     'java.io.FilePermission',
  5     '/usr/bin/perl',
  6     'execute');
  7
  8    dbms_java.grant_permission
  9    ('RT_TEST',
 10     'java.lang.RuntimePermission',
 11     '*',
 12     'writeFileDescriptor' );
 13  end;
 14  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> @connect rt_test/rt_test
ops$tkyte@ORA920PC> set termout off
rt_test@ORA920PC> REM GET afiedt.buf NOLIST
rt_test@ORA920PC> set termout on
rt_test@ORA920PC>
rt_test@ORA920PC> create or replace and compile
  2  java source named "Util"
  3  as
  4  import java.io.*;
  5  import java.lang.*;
  6
  7  public class Util extends Object
  8  {
  9    public static int RunThis(String args)
 10    {
 11    Runtime rt = Runtime.getRuntime();
 12    int        rc = -1;
 13
 14    try
 15    {
 16       Process p = rt.exec(args);
 17
 18       int bufSize = 4096;
 19       BufferedInputStream bis =
 20        new BufferedInputStream(p.getInputStream(), bufSize);
 21       int len;
 22       byte buffer[] = new byte[bufSize];
 23
 24       // Echo back what the program spit out
 25       while ((len = bis.read(buffer, 0, bufSize)) != -1)
 26          System.out.write(buffer, 0, len);
 27
 28       rc = p.waitFor();
 29    }
 30    catch (Exception e)
 31    {
 32       e.printStackTrace();
 33       rc = -1;
 34    }
 35    finally
 36    {
 37       return rc;
 38    }
 39    }
 40  }
 41  /
 
Java created.
 
rt_test@ORA920PC>
rt_test@ORA920PC>
rt_test@ORA920PC> create or replace
  2  function RUN_CMD(p_cmd in varchar2) return number
  3  as
  4  language java
  5  name 'Util.RunThis(java.lang.String) return integer';
  6  /
 
Function created.
 
rt_test@ORA920PC>
rt_test@ORA920PC>
rt_test@ORA920PC> create or replace procedure RC(p_cmd in varchar2)
  2  as
  3    x number;
  4  begin
  5    x := run_cmd(p_cmd);
  6  end;
  7  /
 
Procedure created.
 
rt_test@ORA920PC>
rt_test@ORA920PC>
rt_test@ORA920PC> variable x number;
rt_test@ORA920PC> set serveroutput on
rt_test@ORA920PC> exec dbms_java.set_output(100000);
 
PL/SQL procedure successfully completed.
 
rt_test@ORA920PC>
rt_test@ORA920PC>
rt_test@ORA920PC> exec :x := RUN_CMD('/usr/bin/perl /home/tkyte/test.pl' );
Hello World
 
PL/SQL procedure successfully completed.
 
 

Yes - actually, the grant was in one terminal window as the SYS user, and the script execution was in another terminal, as a regular user. I just tried to disconnect/reconnect, and it still fails, but now I've lost the error messages:

@tahiti/export/home/hebben01>sqlplus

SQL*Plus: Release 9.0.1.4.0 - Production on Thu Feb 19 13:25:34 2004

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Enter user-name: rocq     
Enter password: 

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.4.0 - Production
JServer Release 9.0.1.4.0 - Production

SQL> set serveroutput on size 1000000
exec dbms_java.set_output(1000000)
SQL> 
PL/SQL procedure successfully completed.

SQL> exec test_runcmd
Beginning.
-1
Ending.

PL/SQL procedure successfully completed.

 

This is what I tried:

create or replace and compile java source named "Util" as
  import java.io.*;
  import java.lang.*;
  
  public class Util extends Object {
    public static int RunThis(String args) {
    Runtime rt = Runtime.getRuntime();
    int        rc = -1;
    try    {
        System.out.println("Hello world 1");
       Process p = rt.exec(args);
  
       int bufSize = 4096;
       BufferedInputStream bis =
        new BufferedInputStream(p.getInputStream(), bufSize);
       int len;
       byte buffer[] = new byte[bufSize];
        System.out.println("Hello world 2");
  
       // Echo back what the program spit out
       while ((len = bis.read(buffer, 0, bufSize)) != -1)
          System.out.write(buffer, 0, len);
        System.out.println("Hello world 3");
  
       rc = p.waitFor();
    }
    catch (Exception e)  {
        System.out.println("Hello world 4");
       e.printStackTrace();
       rc = -1;
    }
    finally {
       return rc;
    }
   }
  }
/

Re-granted privileges as the sys user:
SQL> begin
      dbms_java.grant_permission
      ('ROCQ',
       'SYS:java.io.FilePermission',
       '/usr/bin/perl',
       'execute');
 
     dbms_java.grant_permission
      ('ROCQ',
       'SYS:java.lang.RuntimePermission',
       '*',
       'writeFileDescriptor' );
  end;
/

PL/SQL procedure successfully completed.

Checked permissions at the OS level to make sure Oracle had the required privileges to execute the script.
But I'm still getting the same result:
SQL> exec test_runcmd
Beginning.
-1
Ending.

I'm not seeing the "hello world" messages. 

Disregard my last posting. I had neglected to logout/login again.
The new Java:
create or replace and compile java source named "Util" as
  import java.io.*;
  import java.lang.*;
  
  public class Util extends Object {
    public static int RunThis(String args) {
    Runtime rt = Runtime.getRuntime();
    int        rc = -1;
    try    {
        System.out.println("Hello world 1");
       Process p = rt.exec(args);
        System.out.println("Hello world A");
  
       int bufSize = 4096;
       BufferedInputStream bis =
        new BufferedInputStream(p.getInputStream(), bufSize);
       int len;
       byte buffer[] = new byte[bufSize];
        System.out.println("Hello world 2");
  
       // Echo back what the program spit out
       while ((len = bis.read(buffer, 0, bufSize)) != -1)
          System.out.write(buffer, 0, len);
        System.out.println("Hello world 3");
  
       rc = p.waitFor();
    }
    catch (Exception e)  {
        System.out.println("Hello world 4");
       e.printStackTrace();
       rc = -1;
    }
    finally {
       return rc;
    }
   }
  }
/


Here is what I'm seeing:
SQL> exec test_runcmd
Beginning.
Hello world 1
-1
Ending.

PL/SQL procedure successfully completed.
 

    finally {
         System.out.println("Goodbye cruel world");
      return rc;
   }


SQL> exec test_runcmd
Beginning.
Hello world 1
Goodbye cruel world
-1
Ending.
 

ok
Thanks for all your help, Tom.

Hi,
I want to send message to different users when any insert in the table. I am using Oracle 9i r2 on Windows 2003 env. Please guide me as I am trying to use

net send sikandar A record inserted....

But unable to use net send command in the trigger.

so good.
haho.

I try to submit a nohup shell script from pl/sql, but the session still wait unit the script end. How can I submit a nohup jobs in pl/sql?

Please advise!

Hi,

I had tried the following method already in the some_other_script:

1)
#!/bin/sh
some_script &

2)
#!/bin/sh
nohup some_script &

But the session in PL/SQL still wait until the last nohup job completed.

Should you have any idea? Please advise.


Thanks,
Stephen

tom, 
The code below worked fine under *nix (9i) where instead of
"c:/winnt/system32/cmd", I had "/bin/sh".
But under 8i/W2K, it's not carrying out my commands:
Can you spot whats wrong, thanks

CREATE OR REPLACE AND COMPILE
JAVA SOURCE NAMED "Util"
AS
import java.io.*;
import java.lang.*;

public class Util extends Object
{
public static int RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;

try
{
String[] cmd = {"c:/winnt/system32/cmd", "/c", args};
Process p = Runtime.getRuntime().exec(cmd);
rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}

/

CREATE OR REPLACE
FUNCTION run_host_cmd(p_cmd IN VARCHAR2) RETURN NUMBER
AS
LANGUAGE JAVA
NAME 'Util.RunThis(java.lang.String) return integer';
/

CREATE OR REPLACE PROCEDURE RC(p_cmd IN VARCHAR2)
AS
x NUMBER;
BEGIN
x := run_host_cmd(p_cmd);
END;
/

SQL> exec rc('del C:\temp\foo.pdf');

PL/SQL procedure successfully completed



----  BUT C:\temp\foo.pdf' is NOT deleted

GRANTED:
dbms_java.grant_permission('MANDA','java.io.FilePermission','c:\winnt\system32\cmd.exe','execute');
dbms_java.grant_permission('MANDA', 'java.io.FilePermission','C:/temp/*', 'read,write'); 
dbms_java.grant_permission('MANDA',  'java.lang.RuntimePermission','C:/temp/*',   'writeFileDescriptor' ); 

 

tom pls disregard my prev posting.
found out what was wrong...shoulda have a space
before the parameter...duh
Thanks

SQL> exec rc(' del C:\temp\foo.pdf'); 

Tom, does Oracle, in PL/SQL processing, wait for the host call via Java SP to end then continue ?

Thanks

We have implemented a solution for executing processes outside of the database that includes Oracle Queues, detached processes, etc. This solution looks like a much cleaner, more straightforward approach.

I have already implemented the solution mentioned here to run OS commands successfully. However in spite of my best efforts I have been unable to invoke sql loader command using this method.

my exec command is:
exec :x := RUN_CMD('/export/home/oracle/test-sqlldr/load-data-test.sh mytestctl.ctl mydatafile.dat')

load-data-test.sh looks something like:

USAGE="load-data-test.sh ctlfilename.ctl datafilename.dat"
if [ $# -lt 2 ] ; then
echo "$USAGE"
exit 1 fi
ORACLE_SID=dbtest ORACLE_HOME=/usr/app/oracle/product/8.1.7 export ORACLE_SID
export ORACLE_HOME

$ORACLE_HOME/bin/sqlldr username/pwd $1 data=$2

I have also assigned the following privilege to the user:

dbms_java.grant_permission
('USERNAME',
'java.io.FilePermission',
'/usr/bin/ls',
'execute');

dbms_java.grant_permission
('USERNAME',
'java.io.FilePermission',
'/export/home/oracle/test-sqlldr/load-data-test.sh',
'execute');

dbms_java.grant_permission
('USERNAME',
'SYS:java.io.FilePermission',
'/export/home/oracle/test-sqlldr/*',
'read,write,execute');

dbms_java.grant_permission
('USERNAME',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );

end;
/

Somehow it does not seem to create the log file or the bad file in the directory I am expecting it to create (/export/home/oracle/test-sqlldr). The response I get after executing the RUN_CMD is: "PL/SQL procedure successfully completed."

Could there be some permission that I am missing on the directory?

'cd' did the trick.
You can write all the complex code in the world, but can forget simplest of all things, that can cause undesirable results.

Your help is greatly appreciated.

Tom,

Can I use this Java approach to call a Pro*C executables
from the stored procedures. Since Pro*C needs to connect back to the database, I am not sure if this is the best way.
Currently we don't have JVM installed in our database and if calling a Pro*C executable from stored procedure is feasible by this way then I can ask the manager to have the
JVM installed in the database.

We currently using,
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

Thanks
Pushparaj

In relpy to Stephen's posting above, I've successfully used this for background execution:

#!/usr/bin/ksh
# Script: at_now.ksh
/usr/bin/echo "$1 $2 $3 $4 $5 $6 $7 $8 $9" | /usr/bin/at now

begin
RC('/full_path/at_now.ksh /usr/bin/sleep 10');
end;
/

I'm getting the following error when i run it:

SQL> exec rc('/usr/bin/ps -ef');
java.lang.ArrayIndexOutOfBoundsException
at Util.RunThis(Util.java:11)

DBA's have granted the permissions that you have specified.

Could explain me if anything else needs to be done.

Thanks
RameshG
 

hi tom.

I don´t know anything about java or unix and i would like to know if i could use this procedures whit sqlldr on windows xp and Oracle 9i. I´m not DBA an i would like to know if is obligatory anyone grant me permission with dbms_java.grant_permission.

thank you very much for your help

Hi Tom ,
I am using the procedure RC to call sqlloader in a loop in anothe rstored proc for each input file.
if the input file is small it works

even for multiple files, but its hanging and bringing the database down whne the number of records is increased to 6000 of 1column or type number.

what am I missing please help
here is the stroed proc iam Using .

CREATE OR REPLACE PROCEDURE "TKMSYS"."LG2" AS
l_file UTL_FILE.file_type;
l_location VARCHAR2(100) := 'GRADESDIR';
l_filename VARCHAR2(100) := 'fileList.txt';
l_exists BOOLEAN;
l_file_length NUMBER;
l_blocksize NUMBER;
l_text VARCHAR2(32767);
cmdStr varchar2(4000);
procId varchar2(100);
fileSeq number(10);
retVal number(10);

BEGIN
-- Open file.
l_file := UTL_FILE.fopen(l_location, l_filename, 'r', 32767);

select 'LG' ||to_char(sysdate,'mmddyyyyhhmiss') into procId from dual;


-- Read and output first line.
UTL_FILE.get_line(l_file, l_text, 32767);
DBMS_OUTPUT.put_line('First Line: |' || l_text || '|');

-- Read through the file until we reach the last line.
BEGIN
fileSeq:= 1 ;
retVal := RUN_CMD('mkdir c:\UPHS\'||procId);
LOOP
retVal := -99;
UTL_FILE.get_line(l_file, l_text, 32767);

cmdStr := ' sqlldr TKMSYS/do1od3le5@rchundi data=c:\UPHS\' ||l_text ||
' control=c:\UPHS\test.ctl log=c:\UPHS\'||procId ||'\'||replace(l_text,'.txt','.log')||
' bad=c:\UPHS\'||procId ||'\'||replace(l_text,'.txt','.bad') ||
' discard=c:\UPHS\'||procId ||'\'||replace(l_text,'.txt','.dis') ;
-- if(fileSeq =1) then
-- RC('echo ' || cmdStr || '> c:\UPHS\'||procId ||'\loadRaw.bat');
-- else
-- RC('echo ' || cmdStr || ' >> c:\UPHS\'||procId ||'\loadRaw.bat');
-- end if;
DBMS_OUTPUT.put_line( cmdStr );
retVal:= RUN_CMD(cmdStr);
--Rc('exit');
-- DBMS_OUTPUT.put_line( l_text );
fileSeq := fileSeq + 1;
END LOOP;


EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
END;
-- Rc('echo CHR(10)');
-- Output the last line.
DBMS_OUTPUT.put_line('Last Line : |' || l_text || '|');
DBMS_OUTPUT.put_line( 'c:\UPHS\'||procId ||'\loadRaw.bat' );

--RC('c:\UPHS\'||procId ||'\loadRaw.bat CHR(10)');



-- Close the file.
UTL_FILE.fclose(l_file);
END;
/

Hi Tom,

I'm running the code practically identical to yours, and it gets called several times in succession. And sometimes I'm getting OutOfMemoryException, which gets thrown from

Process p = Runtime.getRuntime().exec(cmd);

Once I catch the first OutOfMemoryException, all the next attempts also fail, until I logout/re-login again.
Do you have any idea what could be causing it?

The command itself is a shell script that calls chmod.

The oracle version is 9.2 and it's running under AIX.

Any ideas would be much appreciated.

thanks,
ilya

OK, here's the java code:

public static void executeCommand(String cmd)
throws IOException, java.sql.SQLException {
Process p = Runtime.getRuntime().exec(cmd);
int res;

int bufSize = 4096;
int len;
byte[] buffer = new byte[bufSize];

try {

BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);

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

res = p.waitFor();
p = null;
bis.close();
bis = null;
} catch (InterruptedException ex) {
throw new RuntimeException(
"exception while waiting for the process to complete: "
+ ex.getMessage());
}

}


and here's pl/sql test code:

begin
dbms_java.set_output(1000000);
dbms_output.enable(1000000);

for i in 1 .. 100
loop
fileutils_pkg.executecommand('/homedev/ilyam/giftsedd/chmod_edd.sh');
end loop;
end;

and there's also a package fileutils_pkg with
PROCEDURE executecommand(
cmd IN VARCHAR2
)
AS
LANGUAGE JAVA
NAME 'gifts.edd.FileUtils.executeCommand(java.lang.String)';

This runs fine the first time (calling the program 100 times in succession), but usually bombs the second time with this error:

ORA-04030: out of process memory when trying to allocate 4032 bytes (ioc_make_sub2,ioc_allocate_pal)
and the printstack is
java.lang.OutOfMemoryError
at java.lang.Thread.start(Thread.java)
at oracle.aurora.java.lang.OracleProcess.newOutputStream(OracleProcess.java)
at oracle.aurora.java.lang.OracleProcess.construct(OracleProcess.java)
at java.lang.Runtime.execInternal(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at gifts.edd.FileUtils.executeCommand(FileUtils.java:241)

where line 241 is

Process p = Runtime.getRuntime().exec(cmd);

I also tried commenting out all the BufferedInputStream stuff, so that it's simply
public static void executeCommand(String cmd)
throws IOException, java.sql.SQLException {
Process p = Runtime.getRuntime().exec(cmd);
int res;

//int bufSize = 4096;
//int len;
//byte[] buffer = new byte[bufSize];

try {
/*
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);

//Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1) {
System.out.write(buffer, 0, len);
}
*/
res = p.waitFor();
p = null;
//bis.close();
//bis = null;
} catch (InterruptedException ex) {
throw new RuntimeException(
"exception while waiting for the process to complete: "
+ ex.getMessage());
}

}


but the result is exactly the same: succeeds on the first run, fails with OutOfMemoryException the second time.

thanks,
ilya

sorry, I don't know much about java pool size. What do I need to check?

SQL> show parameter java 

NAME TYPE VALUE
------------------------------------ ----------- ----------
java_max_sessionspace_size integer 0
java_pool_size big integer 218103808
java_soft_sessionspace_limit integer 0

while in the process of creating a tar, I found a similar report of memory leak problem with oracle's JVM, reproducible in 9.2.0.2; it was fixed in 10.0.0

I've read all the suggestions of going back and doing a ctrl-f to find the comment about this exception.

Here's my problem though. The documented construct for passing an array of strings to a java method in a pl/sql wrapper is to have it declared like this:

public class EchoInput {
public static void main (String[] args) {
for (int i = 0; i < args.length; i++)
System.out.println(args[i]);
}
}


To publish method main, you might write the following call spec:

CREATE OR REPLACE PROCEDURE echo_input (
s1 VARCHAR2,
s2 VARCHAR2,
s3 VARCHAR2)
AS LANGUAGE JAVA
NAME 'EchoInput.main(java.lang.String[])';

This is taken directly from the 9iR2 Java Stored Procedure Developers Guide.

In addition, I have an 8i app that I'm trying to upgrade to 9i, and all my calls to methods similarly fail with ArrayIndexOutOfBounds. I can't believe that Oracle would make a change to the callspec in such a way as to BREAK everyone's app? Oh, I guess everyone being the 5 foolish people to actually USE java in the database.

I am using the following code to run unix commands. I have minimised the memory issues by including a call to the garbage collector, but occasionally it is returning a rc of 3. However I do not know how to output details of the exception. I would like to output the error details to a file. Can anyone help with that?

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "function_os_command" AS

import java.io.*;

import java.util.*;

public class function_os_command

{

public static int Run(String Command)

{

int rc = 0;

try

{

Runtime rt = Runtime.getRuntime();

Process p = rt.exec(Command);

try {

rc = p.waitFor();

} catch (InterruptedException intexc) { rc = 2; }

rt.gc();

} catch (Throwable t) { rc = 3; }

return(rc);

}

}

/

I have tried this example on Oracle 9i R2 Windows.

java.lang.ArrayIndexOutOfBoundsException
at Util.RunThis(Util:14).

What Should change in code so It can works on Windows Platform. Please give any example .
Thanks

Harshad

Tom,

I'm having a problem using run_cmd in my package for starting dynamic SQL*Plus sessions; I want to pass a connection string and PL/SQL block to a shell script for execution. Everything works fine when calling exp or others, which I've included below. I think it's a quoting problem but I can't seem to find a solution other than completely reformatting the SQL and using sed. If you would provide any insight or advice, I'd seriously appreciate it. :)  RDBMS/OS --> 9.2.0.4/Solaris 8

My code:
--
-- TEST TABLE
--
create table exp_test(id number(22));

--
-- THIS PROC WORKS SHOWING JAVA AND PERMS ARE CORRECT,
-- I USED YOUR LATEST CODE.
--
create or replace procedure run_exp(
    p_exp_conn     in  varchar2,                          -- USERID= 
    p_table_name   in  varchar2,                          -- TABLES= 
    p_dmp_file     in  varchar2,                          -- FILE= 
    p_log_file     in  varchar2 )                         -- LOG= 
is
--
-- Example call: 
--     exec run_exp('user/pass@sid',
--                  'exp_test',
--                  '/tmp/exp_test.dmp',
--                  '/tmp/exp_test.log');  
--
    l_exp_cmd      varchar2(2000) := '/tmp/run_exp.sh';
    l_return       number         := 0;
    
begin
    l_exp_cmd := l_exp_cmd ||'  "'|| p_exp_conn
                           ||'" "'|| p_table_name
                           ||'" "'|| p_dmp_file
                           ||'" "'|| p_log_file
                           ||'"'; 

    l_return := run_cmd(l_exp_cmd);
     
    if l_return = 0 then
        dbms_output.put_line('Exp session succeeded.');
    else
        dbms_output.put_line('Exp session failed.');
    end if;

exception
when others then
    raise;
end run_exp;
/
--
-- THIS IS THE PROBLEM PROC OR QUOTING PROBLEM
--
create or replace procedure run_sqlplus(
    p_sqlplus_conn     in  varchar2,
    p_sqlplus_qry      in  varchar2,
    p_spool_file       in  varchar2 )
is
--
-- Sample call: 
--     exec run_sqlplus('user/pass@sid',
--                      'begin dbms_output.put_line(''Hello''); end;',   
--                      '/tmp/run_sqlplus.log');  
--
    l_sqlplus_cmd      varchar2(2000) := '/tmp/run_sqlplus.sh';
    l_return           number         := 0;
    
begin
    l_sqlplus_cmd := l_sqlplus_cmd ||'   '|| p_sqlplus_conn
                                   ||'  "'|| p_sqlplus_qry
                                   ||'" "'|| p_spool_file
                                   ||'"';
                                   
    dbms_output.put_line(l_sqlplus_cmd);
                  
    l_return := run_cmd(l_sqlplus_cmd);
     
    if l_return = 0 then
        dbms_output.put_line('SQL*Plus session succeeded.');
    else
        dbms_output.put_line('SQL*Plus session failed.');
    end if;

exception
when others then
    raise;
end run_sqlplus;
/

--
-- SAMPLE SHELL SCRIPTS
--
#!/bin/sh
#
# run_exp.sh
# -------------------------
# 
echo userid = $1
echo tables = $2
echo file = $3
echo log = $4

exp userid=$1 tables=$2 file=$3 log=$4 statistics="none" direct="yes"

#!/bin/sh
#
# run_sqlplus.sh
# -------------------------
#
echo conn = $1
echo query = $2
echo spoolfile = $3

sqlplus -s /NOLOG << EOF
connect $1
set serverout on
spool $3
set timing on
begin
    $2
end;
/
spool off
exit
EOF

--
-- TEST sh SCRIPTS
--
$ /tmp/run_exp.sh "user/pass@sid" "exp_test" "/tmp/exp_test.dmp" "/tmp/exp_test.log"

result:
-------
. . exporting table                       EXP_TEST          0 rows exported
Export terminated successfully without warnings.

$ /tmp/run_sqlplus.sh "user/pass@sid" "begin dbms_output.put_line('Hello'); end;" "/tmp/run_sqlplus.log"

result:
-------
conn = user/pass@sid
query = begin dbms_output.put_line('Hello'); end;
spoolfile = /tmp/run_sqlplus.log
Connected.

Hello

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.16

--
-- TEST PROCS
--
set serveroutput on size 1000000
exec dbms_java.set_output(1000000);

SQL> exec run_exp('user/pass@sid', 'exp_test', '/tmp/exp_test.dmp', '/tmp/exp_test.log');

PL/SQL procedure successfully completed. (new dmp file and log were created)

SQL> exec run_sqlplus('user/pass@sid','begin dbms_output.put_line(''Hello''); end;','/tmp/run_sqlplus.log');
/tmp/run_sqlplus.sh   user/pass@sid  "begin dbms_output.put_line('Hello'); end;" "/tmp/run_sqlplus.log"
conn = user/pass@sid
query = "begin
spoolfile = dbms_output.put_line('Hello');
Connected.
ERROR:
ORA-01740: missing double quote in identifier
Elapsed: 00:00:00.00
SQL*Plus session succeeded.

PL/SQL procedure successfully completed. 

whitespace will kill you everytime when you have sh's calling shells and so on.

easiest to just "avoid the issue"

user/pass@connect should never have whitespace
we'll make the reasonable assumption that your spool file name won't either

but the query will....

so arg1 and arg2 are "one word"
arg3 is the rest.  

we change the script to be:

#!/bin/sh
#
# run_sqlplus.sh
                                                                                                   
conn=$1
shift
spoolfile=$1
shift
query=$*
                                                                                                   
echo conn = $conn
echo query = $query
echo spool = $spoolfile
                                                                                                   
sqlplus -s /NOLOG << EOF
connect $conn
set serverout on
spool $spoolfile
set timing on
begin
    $query
end;
/
spool off
exit
EOF


and the plsql to be:

ops$tkyte@ORA9IR2> create or replace procedure run_sqlplus(
  2      p_sqlplus_conn     in  varchar2,
  3      p_sqlplus_qry      in  varchar2,
  4      p_spool_file       in  varchar2 )
  5  is
  6  --
  7  -- Sample call:
  8  --     exec run_sqlplus('user/pass@sid',
  9  --                      'begin dbms_output.put_line(''Hello''); end;',
 10  --                      '/tmp/run_sqlplus.log');
 11  --
 12      l_sqlplus_cmd      varchar2(2000) := '/tmp/run_sqlplus.sh';
 13      l_return           number         := 0;
 14
 15  begin<b>
 16      l_sqlplus_cmd := l_sqlplus_cmd ||' '|| p_sqlplus_conn
 17                                     ||' '|| p_spool_file
 18                                     ||' '|| p_sqlplus_qry;
 19</b>
 20      dbms_output.put_line(l_sqlplus_cmd);
 21
 22      l_return := run_cmd(l_sqlplus_cmd);
 23
 24      if l_return = 0 then
 25          dbms_output.put_line('SQL*Plus session succeeded.');
 26      else
 27          dbms_output.put_line('SQL*Plus session failed.');
 28      end if;
 29
 30  exception
 31  when others then
 32      raise;
 33  end run_sqlplus;
 34  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set serveroutput on
ops$tkyte@ORA9IR2> exec dbms_java.set_output(100000);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec run_sqlplus('/', 'begin dbms_output.put_line(''Hello''); end;', '/tmp/run_sqlplus.log' );
/tmp/run_sqlplus.sh / /tmp/run_sqlplus.log begin dbms_output.put_line('Hello');
end;
conn = /
query = begin dbms_output.put_line('Hello'); end;
spool = /tmp/run_sqlplus.log
Connected.
Hello
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL*Plus session succeeded.
 
PL/SQL procedure successfully completed.
 





 

Tom, this is great stuff. As usual. However, I've read all the various permissions solutions in the article so far, but am still having problems.

Calling ls, ps, etc all works great. However, I cannot get any user scripts to run. I created a test script in the Oracle users home directory, owned by Oracle (who also owns and started the database) and checked the execute permissions. Script runs fine at the command line as Oracle, but I get nothing in SQL*Plus and nothing in my debug.txt file when I run it via pl/sql. I granted permissions on /usr/bin/whoami just to verify that Oracle is the user executing these scripts and it is.

Here's my test script:
ls
echo "This is a test..."
date > debug.txt

Any additional thoughts? I'm stumped.

#!/bin/sh solves the problem. Obviously didn't even think of that one. Sorry to bug you with something so simple.

It runs fine locally, but the result does not show up when running remotely through a db_link.  did I miss sth?

LOCAL:
SQL> exec rc('/bin/sar -u 3 3');
....
23:54:34    %usr    %sys    %wio   %idle
23:54:37       0       2       2      96
23:54:40       0       0       2      98
23:54:43       0       0       1      99
Average        0       1       1      98
PL/SQL procedure successfully completed.

REMOTE:
SQL> set serveroutput on size 1000000
SQL> exec dbms_java.set_output(1000000)
PL/SQL procedure successfully completed.
SQL> exec rc@xxx('/bin/sar -u 3 3');
PL/SQL procedure successfully completed.
SQL>

 

In reference to the original question and examples in this thread. Is this a safe and reliable way of communicating with the OS? I'm not that familiar with JVM in general and the way Oracle runs it, but if JVM is a part of Oracle kernel, then allowing the application to talk almost directly to the OS seems to be strange. For example, what if i call a shell script that changes the structure of the database, or something else that can affects the entire instance? Is there any protection against such operations?

Could you please explain why running os calls from Java sp is more secure and safe than running pro*c on the server as Oracle?

Hello Tom,

This topic has given me an idea how I can execute host calls from PL/SQL in Oracle server. Finally, I am using like this:

SET SERVEROUTPUT ON SIZE 1000000
CALL DBMS_JAVA.SET_OUTPUT(1000000);
BEGIN
Host_Command (p_command => 'sdelayer -o delete -l TRM_RP_GEOMETRICS_MV,GEOMETRY -i esri_sde9i -s jj00WB316 -u gisteam -p gisteam -N -q');
END;
/

It does SDE registration in Oracle Spatial database.

Table name: TRM_RP_GEOMETRICS_MV
Column Name: Geometry
Username:GISTEAM
Pwd:GISTEAM
Database: JJ00WB316

It is executed successfully. I have to execute such Host_Command procedure 12 times in part of a PL/SQL script for different purposes.

Like that, I have 40 PL/SQL scripts with different table name,column name etc...

Now, I would like to create a procedure which will accept the parameters like tablename,columnname etc....pass them to HOST_COMMAND procedure to execute the DOS command. So, that I don't need to maintain in those 40 scripts.

So, how can I make the HOST_COMMAND procedure accept these parameters??

Example:
CREATE OR REPLACE PROCEDURE A (TABLENAME,COLUMNNAME)
AS
BEGIN

Host_Command (p_command => 'sdelayer -o delete -l <TABLENAME>,<COLUMNNAME> -i esri_sde9i -s jj00WB316 -u gisteam -p gisteam -N -q');


Host_Command (...);
Host_Command (...);
..
..
End;



It would be very helpful to me, if you could give me a solution.

regards,
-raman

Thankyou verymuch ...

I created a java program and wrapped it in PL/SQL. I would like to use it to unzip a certain file:

In SQLPLUS >
exec host.run('/bin/unzip -L /app/dphrmo/pvc/f0016741.113 -d /app/dphrmo/pvc/');

Archive: /app/dphrmo/pvc/f0016741.113

PL/SQL-procedure succesfully completed.

But no extraction is made. When I execute the complete command at the Unix prompt the unzip is succesful. When I try to unzip a file - in PLSQL - that already exists, the program 'hangs' logically, because it's waiting to enter a Yes/No/Cancel action for overwriting.

But why is the unzip-action not succesfull?

The entire (java) program is:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "Host" AS
import java.io.*;
import java.sql.*;
import java.util.*;

public class Host
{
public static void Dir(String directory)
throws SQLException
{
File path = new File( directory );
String[] list = path.list();
String element;

for(int i = 0; i < list.length; i++)
{
element = list[i];
#sql { INSERT INTO DIR_LIST (FILENAME)
VALUES (:element) };
}
}

public static int Run(String Command)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;
try
{
Process p = rt.exec(Command);

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 = bis.read(buffer, 0, bufSize)) != -1 )
System.out.write(buffer, 0, len);
rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/


So (IMHO) the errors are catched but not hided...
Before running the PL/SQL command I also ran:
>exec dbms_java.set_output(1000000)

So what can be wrong?

I moved the p.waitFor() direct after the exec(Command) and read out the ErrorStream. Apparently the user (Oracle?) was not allowed to write in the specified directory....

Now it works! Thanx a lot!

I tried the same steps mention by you and grant permissions to all the files.

I am able to run a shell script which contains unix commands, but I am not able to run a small HelloWorld Java program from the shell script.

I want to run a java program from the shell script and call that shell script from the stored procedure in question.

SQL> exec rc('/usr/bin/sh /home/dhscrp/pass.sh');
run_cmd returned : 1   for /usr/bin/sh /home/dhscrp/pass.sh

PL/SQL procedure successfully completed.
------------------------------------------------
create or replace procedure RC(p_cmd in varchar2)
as
x number;
begin
    x := run_cmd(p_cmd);
    --execute immediate('set serveroutput on size 1000000');
    --dbms_java.set_output(1000000);
    DBMS_OUTPUT.PUT_LINE ( 'run_cmd returned : '||rpad(x, 3, ' ')||' for '||p_cmd);
end;
/

----------------------------------------------------
SQL> create or replace function RUN_CMD( p_cmd  in varchar2) return number
  2  AUTHID CURRENT_USER as  language java
  3  name 'OSUtil.RunThis(java.lang.String) return integer';
  4  /


-------------------------------------------------------

create or replace and compile java source named "OSUtil" as
  import java.io.*;
  import java.lang.*;
  public class OSUtil extends Object
  {
    public static int RunThis(String args)
    {
         Runtime rt = Runtime.getRuntime();
         int        rc = -1;
         try
         {
            Process p = rt.exec(args);
            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 = bis.read(buffer, 0, bufSize)) != -1)
           System.out.write(buffer, 0, len);
              rc = p.waitFor();
      }
      catch (Exception e)
      {
        e.printStackTrace();
        rc = -1;
      }
      finally
      {
         return rc;
      }
     }
   }
/
-------------
I used the same to call a shell script contains java commands to run one java class from Unix OS. It is not doing any thing though it is working for the shell script which contains unix commands only.
 

Tom,
Host command via java stored procedures works fine for us.But each time it generates a .trc (trace files) in user dump destination like show below.How we can stop generating this sort of trace files?

dump file c:\oracle\admin\reds\udump\reds_ora_11844.trc
Thu Dec 16 13:10:56 2004
ORACLE V9.2.0.5.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 , CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Windows 2000 Version 5.2 , CPU type 586
Instance name: reds

Redo thread mounted by this instance: 1

Oracle process number: 33

Windows thread id: 11844, image: ORACLE.EXE


*** 2004-12-16 13:10:56.590
*** SESSION ID:(35.2353) 2004-12-16 13:10:56.497


C:\oracle\ora92\DATABASE>cmd /k "c:\reds_lims_labels\print_commands\warehouse_medium.cmd C:\reds_lims_labels\13105656CHENNK.lbl > c:\reds_lims_labels\warehouse_medium.log"

.cmd file is working via this java procedure.Can we turn off the trace file generations?

Thanks! We didn't give permission on cmd /k .
We only gave execute permission on the following file.
c:\reds_lims_labels\print_commands\warehouse_medium.cmd .

Let me explain again.Sorry I din't make it clear.

We didn't give direct permission to cmd /k .

We gave permission to execute the following file

c:\reds_lims_labels\print_commands\submit_warehouse_medium.cmd

submit_warehouse_medium.cmd has the following lines :

cmd /k "c:\reds_lims_labels\print_commands\warehouse_medium.cmd %1 > c:\reds_lims_labels\warehouse_medium.log"

Thanks Tom! Do we have anyway to turn off these trace file generations.

SQL> execute dbms_java.set_output(99999);

PL/SQL procedure successfully completed.

SQL> set serverout on;
SQL> variable a number;

SQL> execute :a := java_mail_stuff.run_cmd('c:\reds_lims_labels\print_commands\call_ware
.cmd c:\reds_lims_labels\13032121AGARWALAR.lbl');
C:\oracle\ora92\DATABASE>cmd /k
"c:\reds_lims_labels\print_commands\warehouse_medium.cmd
c:\reds_lims_labels\13032121AGARWALAR.lbl >
c:\reds_lims_labels\warehouse_medium.log"

PL/SQL procedure successfully completed.


It didn't generate the trace file when I execute it from the sqlplus. Thanks! 

Did you mean that if we do "2>>&1 " like shown below then it would not generate the trace files?

cmd /k "c:\reds_lims_labels\print_commands\warehouse_medium.cmd %1 > c:\reds_lims_labels\warehouse_medium.log" 2>>&1

I have writen a Java SP to run a host script as mentioned above, and this scripts starts my backup script (since SP waits for the first script to end I wrote an intermadiate script). I have scheduled the SP to start my backup script using DBMS_JOB. All works fine.
Before going to distribute it to 2046 Oracle sites, I want to ask that, is this a good idea for starting backup using this technique?

Thank you for your response.

We have more than 2000 oracle remote sites. Operators of these sites even don't know (and dont care) if they have a DB. We have to perform backups on behalf of the site users.
The users' creativity on destruction made the simple backup script to a backup and one click recovery program.

Most of the sites have only one computer, so rman cannot be used. Using third party backup tools is not feasable for small sites.

Therefore we have to use the backup scripts. The problem comes out managing start time of backup (If we dont start backups, clients never take a backup). Scheduling backup using OS tools (eg. at command) needs administrator password that users dont know, even if they know it is hard to force them to schedule. But scheduling using Oracle job is as simple as running a sql script.

I know scheduling backup using oracle jobs is unusual, that is why I am asking if it is a good idea.

I don't know if here is the right place for the story. Any way here is the code that I use.

------------------------------------------------------------
the java code
------------------------------------------------------------
import java.io.*;

public class Dba {
public Dba() {
}

public static void main(String[] args) {
Dba dba = new Dba();
String arg="";
System.out.println(args[0]);
if (args.length>0) arg=args[0];
runDba(arg);
}

public static void runProcess (String program){
Process proc;
Runtime rt;

rt = Runtime.getRuntime();
try {
proc = rt.exec(program);
Thread.sleep(1000);
}
catch (Exception ex) {
ex.printStackTrace();
}

}

public static void runDba(String param){
runProcess(param);
}

}
------------------------------------------------------------
load java -user <username>/<password> Dba.class
------------------------------------------------------------

create or replace procedure jrun(param varchar2)
as language java
name 'Dba.runDba(java.lang.String)';
/

------------------------------------------------------------
startbackup.bat
------------------------------------------------------------
c:
cd c:\
start %1 %2 %3 (%2 & %3 are optional parameters)
exit
------------------------------------------------------------
backup.bat
------------------------------------------------------------

c:
cd c:\
@setlocal
set CommonProgramFiles=C:\Program Files\Common Files
set COMPUTERNAME=IBRAHIM
set HOMEDRIVE=C:
set HOMEPATH=\Documents and Settings\iozturk
set LOGONSERVER=\\IBRAHIM
set OS=Windows_NT
set Os2LibPath=C:\WINNT\system32\os2\dll;
set Path=C:\Program Files\Java\jdk1.5.0\bin;D:\oracle\product\10.1.0\db\bin;D:\oracle\product\10.1.0\db\jre\1.4.2\bin\client;D:\oracle\product\10.1.0\db\jre\1.4.2\bin;C:\jdk1.5.0\jre\bin;C:\j2sdk1.4.2_05\jre\bin;D:\JBuilderX\jdk1.4\bin;C:\WINNT\system32;C:\WINNT;C:\WINNT\System32\Wbem;C:\Program Files\Symantec\pcAnywhere\;D:\JBuilderX\jdk1.4\bin;D:\Program Files\Adabas\bin;D:\Program Files\Adabas\pgm; D:\Program Files\StarOffice7\program;
set PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH
set ProgramFiles=C:\Program Files
set SystemDrive=C:
set SystemRoot=C:\WINNT
set TEMP=C:\DOCUME~1\iozturk\LOCALS~1\Temp
set TMP=C:\DOCUME~1\iozturk\LOCALS~1\Temp
set USERDOMAIN=IBRAHIM
set USERNAME=iozturk
set USERPROFILE=C:\Documents and Settings\iozturk
C:\j2sdk1.4.2_05\bin\java -cp "%HOMEPATH%\dba_scripts\bin;%HOMEPATH%\dba_scripts\bin\lib\jbcl.jar;%HOMEPATH%\dba_scripts\bin\lib\classes12.jar;%HOMEPATH%\dba_scripts\bin\lib\jdom.jar" dbaController.DbaController backup
REM C:\j2sdk1.4.2_05\bin\java -cp C:\dba_scripts\bin;c:\dba_scripts\bin\lib\jbcl.jar;C:\dba_scripts\bin\lib\classes12.jar;c:\dba_scripts\bin\lib\jdom.jar dbaController.DbaController backup
@endlocal
exit

------------------------------------------------------------

finally schedule it to every friday at 19 PM

VARIABLE jobno NUMBER
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'jrun(''c:\startbackup.bat c:\backup.bat OraLisener stop'');',
SYSDATE, 'NEXT_DAY(TRUNC(SYSDATE), ''CUMA'') + 19/24');
COMMIT;
END;
/
------------------------------------------------------------

In the past I have successfully implemented the solution mentioned here, now I am trying to take it to the next level.

I am trying to generate Sql loader control file and shell script (on Unix platform) based on parameters passed to a sp and then call the solution mentioned here to launch the shell script.

At first I was running into the following problem:
java.security.AccessControlException: the Permission (java.io.FilePermission <<ALL FILES>> execute has not been granted.

I resolved this by granting the following permision:
dbms_java.grant_permission
('IMPORTUSER',
'SYS:java.io.FilePermission',
'<<ALL FILES>>',
'read,write,execute')

In order to execute the generated shell script, I need to assign the 'x' privilege at the os level, so I granted the following privilege:

dbms_java.grant_permission
('IMPORTUSER',
'java.io.FilePermission',
'/usr/bin/chmod',
'execute');

In spite of all this when I issue the chmod command, and try to launch the shell script, it does nothing:

cmd_return_code := osCommandRun('chmod 744 ' || v_FileDirectory || '/' || v_ShFileName );

cmd_return_code := osCommandRun(v_FileDirectory || '/' || v_ShFileName || ' ' ||
v_CtlFileName || ' ' || a_Data_File_Name );

Sqlplus simply reports "PL/SQL procedure successfully completed", but the permission is neither changed nor the shell script is executed.

Am I missing something here?

I would have loved to use external tables, but using external tables is not an options.

Damn Corporate security standards. External tables are not on the approved list.

In the meantime I added some debugging statements right as follows:

cmd_return_code := osCommandRun('chmod 744 ' || v_FileDirectory || '/' || v_ShFileName );


DBMS_OUTPUT.PUT_LINE('chMod Command= ' || to_char(cmd_return_code));

cmd_return_code := osCommandRun(v_FileDirectory || '/' || v_ShFileName || ' ' ||
v_CtlFileName || ' ' || a_Data_File_Name );

DBMS_OUTPUT.PUT_LINE('SqlLoader Launch Command= ' || to_char(cmd_return_code));


both return the code of 255.

I will again try to convince the security team about external tables.

I can successfully execute any shell script from within a PL/Sql procedure as mentioned here as long as it has the ‘x’ (execute) privilege set for oracle.

My problem is that I have to create shell script and the control file on the fly (within the stored procedure), I need to first make it ‘x’ executable to be able to run it.

In spite of granting exclusive ‘x’ privilege on ‘chmod’ it does not change the permission and hence cannot execute the shell script. I get a return code of 255 as opposed to 0.

cmd_return_code := osCommandRun('chmod 744 ' || v_FileDirectory || '/' || v_ShFileName );

If I change the permission to 744 for the same file manually at the os level, and re-run the stored procedure, asking for the same file name to be generated, it preserves the execute permission and the successfully executes the shell script to launch the sql loader.

This makes me believe that I am having some trouble running ‘chmod’ command due to some file permissions.

Any ideas?

I finally figured out my problem:

I had granted the following java permission:

dbms_java.grant_permission
('IMPORTUSER',
'java.io.FilePermission',
'/usr/bin/chmod',
'execute');

but the command to change the permission would not work.

cmd_return_code := osCommandRun('chmod 744 ' || v_FileDirectory || '/' || v_ShFileName );

so I changed the command to:

cmd_return_code := osCommandRun('/usr/bin/chmod 744 ' || v_FileDirectory || '/' || v_ShFileName );

All I did was to prefix chmod with /usr/bin/ and it started working. Even though 'oracle' path was setup correctly with /usr/bin:/usr/ccs/bin:/usl/local/bin: and so on.

I am thinking that when an OS command is executed from within PL/Sql Procedure, it runs in it's own shell and does not inherit any environment/path variables of the parent (in this case 'oracle' user).

Please correct me if I am wrong here.

Tom,
I am using your procedure here with some success.
I have tested with a simple batch file that does time /t>> c:\temp\test.log. That works fine. 

What I want to do is to schedule a job to run on another server 
through windows AT. For this my batch file contents looks like 'at \\AAAAAAAA 10:34 C:\TEMP\STOP_APP_SERVER.BAT'. I can run this through the command line and also double click the batch file to schedule the job. When Oracle however executes the batch file it does not get scheduled - the procedure completes to success but no joy on the scheduling. 

I execute it through Oracle like so


SQL> variable x number;
SQL> set serveroutput on
SQL> exec dbms_java.set_output(100000);

PL/SQL procedure successfully completed.

SQL> exec :x := RUN_CMD('C:\TEMP\SCHEDULE_APP_SERVER_SHUT.BAT');
D:\oracle\ora92\DATABASE>at \\orlq1aza 14:26 C:\TEMP\STOP_APP_SERVER.BAT

PL/SQL procedure successfully completed.

SQL> commit; 

Tom,

I am trying to map a network drive from within a stored proc.

Here is what I have done and what I get at the end:

1) Run these as sys

exec dbms_java.grant_permission('PHAME','java.io.FilePermission','C:\WINDOWS\system32\net.exe','execute');

exec dbms_java.grant_permission('PHAME','java.lang.RuntimePermission','*','writeFileDescriptor' );

2) run this as our schema owner

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

public class Util extends Object
{
public static int RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;

try
{
Process p = rt.exec(args);

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 = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);

rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/

3) run this as our schema owner
create or replace procedure call_net_use(p_cmd in varchar2)
as
x number;
begin
x := NET_USE(p_cmd);
end;
/

4) run this as our schema owner
exec call_net_use('net use j: \\servername\d$ password /user:domain\username');

( The net use command works at the command line of the database server (I have changed the parms to be generic))

I get:

java.security.AccessControlException: the Permission (java.io.FilePermission <<ALL FILES>> execute) has not been granted to PHAME. The PL/SQL to grant this is dbms_java.grant_permission( 'PHAME',
'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execute' )

Do you see the problem?

Thanks very much!

Hi Tom,

I have been using the code from this thread. It works great!!!

We have a tmp folder from which we want to delete all the files after certain operation.

When I use

exec :x := RUN_CMD('/usr/bin/rm /tmp/xyz.txt') ;

if works fine.

But what I really like to run is
exec :x := RUN_CMD('/usr/bin/rm /tmp/*.txt') ;

to delete all the files from the tmp foler. This does not work.

Any suggestions!!!

when I meant by "does not work" is that '/usr/bin/rm *.txt' passed as an argument to RUN_CMD does not delete a single file from /tmp folder. Although the procedure successfully completes and i do not get anything in DBMS JAVA OUTPUT.

If I pass the '/usr/bin/rm xyz.txt' then the procedure deletes the file from xyz.txt from the tmp folder.

Thanks

This link from Erdal (above) might help Deepak: </code> http://www.mountainstorm.com/publications/javazine.html <code>

Hi Tom

I read you book and this page and wanted to create Java OS command to do export and landed up in many errors.

This one is the one I can not figure out and need your help.
Oracel 9R2 on Redhat Server2.1 and client from where I ran this Windows XP.

I search this page for dbms_java.grant_permission and unable to figure out my own mistake.


scott@TUNE> connect sys/xxxx@TUNE as sysdba;
Connected.
scott@TUNE> begin
2 dbms_java.revoke_permission
3 ('SCOTT',
4 'java.io.FilePermission',
5 '/opt/oracle/9.2.0.1.0/bin/exp',
6 'execute');
7
8 dbms_java.revoke_permission
9 ('SCOTT',
10 'java.lang.RuntimePermission',
11 '*',
12 'writeFileDescriptor' );
13 end;
14 /

PL/SQL procedure successfully completed.

scott@TUNE> rem show err
scott@TUNE> show err
No errors.
scott@TUNE> begin
2 dbms_java.grant_permission
3 ('SCOTT',
4 'java.io.FilePermission',
5 '/opt/oracle/9.2.0.1.0/bin/exp',
6 'execute');
7
8 dbms_java.grant_permission
9 ('SCOTT',
10 'java.lang.RuntimePermission',
11 '*',
12 'writeFileDescriptor' );
13 end;
14 /

PL/SQL procedure successfully completed.

scott@TUNE> show err
No errors.
scott@TUNE> connect scott/tiger@TUNE
Connected.
scott@TUNE> create or replace and compile
2 java source named "Util"
3 as
4 import java.io.*;
5 import java.lang.*;
6
7 public class Util extends Object
8 {
9 public static int RunThis(String args)
10 {
11 Runtime rt = Runtime.getRuntime();
12 int rc = -1;
13
14 try
15 {
16 Process p = rt.exec(args);
17
18 int bufSize = 4096;
19 BufferedInputStream bis =
20 new BufferedInputStream(p.getInputStream(), bufSize);
21 int len;
22 byte buffer[] = new byte[bufSize];
23
24 // Echo back what the program spit out
25 while ((len = bis.read(buffer, 0, bufSize)) != -1)
26 System.out.write(buffer, 0, len);
27
28 rc = p.waitFor();
29 }
30 catch (Exception e)
31 {
32 e.printStackTrace();
33 rc = -1;
34 }
35 finally
36 {
37 return rc;
38 }
39 }
40 }
41 /

Java created.

scott@TUNE> show err
No errors.
scott@TUNE> create or replace
2 function RUN_CMD( p_cmd in varchar2) return number
3 as
4 language java
5 name 'Util.RunThis(java.lang.String) return integer';
6 /

Function created.

scott@TUNE> show err
No errors.
scott@TUNE> create or replace procedure RC(p_cmd in varchar2)
2 as
3 x number;
4 begin
5 x := run_cmd(p_cmd);
6 end;
7 /

Procedure created.

scott@TUNE> show err
No errors.
scott@TUNE> create or replace procedure run_exp(p_exp_cmd varchar2)
2 is
3
4 l_exp_cmd varchar2(500);
5 l_return number := 0;
6
7 begin
8 --
9 l_return := run_cmd(p_exp_cmd);
10 --
11 if l_return = 0 then
12 dbms_output.put_line('Export session succeeded.');
13 else
14 dbms_output.put_line('Export session failed.');
15 end if;
16
17 exception
18 when others then
19 raise;
20 end run_exp;
21 /

Procedure created.

scott@TUNE> show err
No errors.
scott@TUNE> set serveroutput on size 1000000
scott@TUNE> exec dbms_java.set_output(1000000)

PL/SQL procedure successfully completed.

scott@TUNE>
scott@TUNE> exec run_exp('exp scott/tiger@TUNE, TABLES=(emp, dept), FILE=/tmp/exp_test.dmp, LOG=/tmp/exp_test.log');
java.security.AccessControlException: the Permission (java.io.FilePermission <<ALL FILES>> execute) has not been granted
to SCOTT. The PL/SQL to grant this is dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execute' )
at java.security.AccessControlContext.checkPermission(AccessControlContext.java)
at java.security.AccessController.checkPermission(AccessController.java)
at java.lang.SecurityManager.checkPermission(SecurityManager.java)
at oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java)
at java.lang.SecurityManager.checkExec(SecurityManager.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at Util.RunThis(Util.java:13)
Export session failed.

PL/SQL procedure successfully completed.

scott@TUNE> show user
USER is "SCOTT"
scott@TUNE> begin
2 dbms_java.grant_permission
3 ('SCOTT',
4 'java.io.FilePermission',
5 '/opt/oracle/9.2.0.1.0/bin/exp',
6 'execute');
7
8 dbms_java.grant_permission
9 ('SCOTT',
10 'java.lang.RuntimePermission',
11 '*',
12 'writeFileDescriptor' );
13 end;
14 /

PL/SQL procedure successfully completed.

scott@TUNE> show err
No errors.
scott@TUNE> connect scott/tiger@tune
Connected.
scott@TUNE> set serveroutput on size 1000000
scott@TUNE> exec dbms_java.set_output(1000000)

PL/SQL procedure successfully completed.

scott@TUNE> exec run_exp('exp scott/tiger@TUNE, TABLES=(emp, dept), FILE=/tmp/exp_test.dmp, LOG=/tmp/exp_test.log');
java.security.AccessControlException: the Permission (java.io.FilePermission <<ALL FILES>> execute) has not been granted
to SCOTT. The PL/SQL to grant this is dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execute' )
at java.security.AccessControlContext.checkPermission(AccessControlContext.java)
at java.security.AccessController.checkPermission(AccessController.java)
at java.lang.SecurityManager.checkPermission(SecurityManager.java)
at oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java)
at java.lang.SecurityManager.checkExec(SecurityManager.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at Util.RunThis(Util.java:13)
Export session failed.

PL/SQL procedure successfully completed.
scott@TUNE> select * from dba_java_policy where grantee = 'SCOTT';

KIND GRANT TYPE_SCHEM TYPE_NAME NAME ACTION ENABLED SEQ
-------- ----- ---------- ---------------------- ------------------------- --------------- -------- ---
GRANT SCOTT SYS java.io.FilePermission /opt/oracle/9.2.0.1.0/bin execute ENABLED 75
/exp

GRANT SCOTT SYS java.io.FilePermission /usr/bin/exp execute ENABLED 77
GRANT SCOTT SYS java.lang.RuntimePermi * writeFileDescri ENABLED 76
ssion ptor
GRANT SCOTT SYS java.lang.RuntimePermi /tmp/ writeFileDescri ENABLED 79
ssion ptor


4 rows selected.

scott@TUNE>

What I am doing wrong is obviously I can not find.
Thanks for all the help.

Regards.

Hi Tom

This was my first Java program and one mistakle I found was that path was not correct and then reconnected and worked fine.

begin
dbms_java.grant_permission
('SCOTT',
'java.io.FilePermission',
'/opt/oracle/product/9.2.0.1.0/bin/exp',
'execute');

dbms_java.grant_permission
('SCOTT',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
end;

I orginally missed /product/ in path.

I will appriciate if you could help me to make it better or your any suggestion.

Thanks

Regards.

Hi Tom

Thanks for reply. I have few ....

In one of your follow-up you said

"did you use dbms_java grant permission to grant yourself the needed privs? like my example does?"

If sys as sysdba has granted 'java.lang.RuntimePermission', and execute permission then why do I need to grant it to me again?

Is it required for all users to grant permission to ownself?

After granting privs what’s the reason for reconnecting again to execute? Java cannot see those grant/prives?

Once JAVA SOURCE NAMED "Util" is compiled where can I find compiled code in oracle?

I could not find dbms_java.grant_permission on any document, where is it?

Thanks for you help and wonderful source of knowledge.

Regards.

I am trying to use a java stored procedure in solaris environment. The java code is accessing a xml file and inserts the data within it into a table in oracle. I am using java.io.File class to create the file object. I am not able to create the file object using following code.

InputSource input = new InputSource(new FileReader(new File("/export/home/stniitd1/interface/inbound/a55progsched/a55_episode_sample.xml")));

when I used System.getProperty("user.dir"), I got following string
/nas/orasoft/baracuda/product/9.2.0/

Hello Tom,

i implemented the java code you've shown above, and it works great.

however, when i tried to modify it slightly to this:

CREATE OR REPLACE AND COMPILE JAVA SOURCE named "Util_zip"
as
import java.io.*;
import java.lang.*;

public class Util_zip extends Object
{

public static int RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;

try
{
Process p = rt.exec(args);

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 = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer,0,len);

#sql {
CALL scanning.insert_file (:buffer) };

rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/
show errors;

oracle throws 01461, 00000, "can bind a LONG value only for insert into a LONG column".
as the byte[] apparently maps to a LONG RAW (?). What i am trying to do is insert the data that the unix command spits back out into a table for later processing.

The procedure being called is very straight forward:

PROCEDURE insert_file (p_file IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ziplist (file_name) VALUES (p_file);
COMMIT;
END;

i use autonomous_transaction pragma, because this whole thing is being called inside a function. the data integrity should be fine, though as there's no concurrent accesses to this table at all.

The operating system command being called is also working fine, it's a simple shell script that does:
#!/bin/sh
/usr/local/bin/unzip -l $1 | /bin/awk '{ print $4 }' | /bin/grep '\.'

i tried to "fix" the above java to convert this to a datatype oracle would understand, but after 2h of failures (i know enough java to read the source and java.sun.com, but that's not enough anymore here :| ) ... i am hoping you might be able to tell me how to adjust the source to accomplish this task. or maybe you even see a much easier way to do the same trick.

looking forward to your reply, and thanks in advance

Stefan

Hi Tom, I try use this example but when i execute the procedure rc, failure
SQL> exec rc('ls')
java.lang.ArrayIndexOutOfBoundsException
at Util.RunThis(UTIL.java:15) 

I see the other questions about the error java.lang.ArrayIndexOutOfBoundsException, but all have UTIL.java:14 and for me show UTIL.java.15 (change the number)

It's the same error!?

Thank's 

Out of all the examples of java calling a host command - this one has to be the best. I needed to use logic like this and it works great.

hi Tom again

When i try execute the RUN_CMD function, show me the next error:

B@db9i> exec :x := RUN_CMD('/usr/lib ls')
java.security.AccessControlException: the Permission (java.io.FilePermission /usr/lib execute) has
not been granted to HT. The PL/SQL to grant this is dbms_java.grant_permission( 'HT',
'SYS:java.io.FilePermission', '/usr/lib', 'execute' )
at java.security.AccessControlContext.checkPermission(AccessControlContext.java)
at java.security.AccessController.checkPermission(AccessController.java)
at java.lang.SecurityManager.checkPermission(SecurityManager.java)
at oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java)
at java.lang.SecurityManager.checkExec(SecurityManager.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at Util.RunThis(UTIL.java:13)

I saw the solution for this error: use 2 users, User A give the grants to user B and user B create all the programns (Util,RUN_CMD, RC).

But show me the same error!!! please help me!!

I tryed use SYS user for give grants too

forgets the previous question, already arrange it...


thanks

Tom:

This is great for us - we need to call a script from pl/sql, and the external proc thing was giving us trouble. This is cleaner.

One question though - I can get a script to run, such as:

echo Hello world.

But... if the script does something to a file - or creates a directory or anything - I see the echo output, but the file operation does not actually happen.

E.G.

echo Hello World
mv /home/oracle/this /home/oracle/that

I see Hello World (so I know it ran) but file "this" does not become "that".

Is this a permissions thing? Do I need a DIRECTORY object created and have perms on it or something?

Thanks.

That fixed it. It was the path. The user was indeed oracle, but the path was not setup in the script.

Thanks again.

Dear Tom,
I want from client side (Windows XP,Plus80w) to execute a dos command like (Copy file) using PL/SQL and kindly note that client connect to database 9i on unix server.

Hi Tom

I need your help to solve this export/import from java class which I got from this site. It works fine and its realy great and when I wanted to test it with wrong username/password hangs, waitng for username/password.

Since I am calling from pl/sql there is no way I can kill this.

Could you please help me to figure out how to fix this.

Thanks

Hi Tom
Thanks for reply.

We did kill it from OS but I was trying to find if any way java code can be alterd to cater for this eventually.

Anoter alternate was to hard code username/pwd which may not be desirable.

Thanks and regards.

Tom - what's going on today? The 'last updated'for 20 May 2005 8am appears on one line for questions 1 to 10, and straddles 2 lines for questions 11 to 20?

hi tom,
execellent stuff. what i'm trying to do is execute a .bat file that calls a vbscript that prints a ms word doc to pdf. everything works great from the commandline. when i try and run it using your proc, the word doc opens and gets sent to the pdf writer, but hangs there. in other tests, i have been able to sucessfully print to a regular printer using the proc. any ideas on what could be preventing me from printing using the pdf writer?

The initial solution is very cool! But what would I do, if I have to call a binary on a different server, that is not running an Oracle database? (Otherwise I could call a procedure on the remote oracle server, which then calls the program locally....)

The company's policy is to not allow anything else on the Oracle server (which is okay with me).

The environment is Oracle 9i (9.2.0.6.0) on Windows 2000 Server.
The "remote" program is on a different Windows 2000 Server.

So, how can I call a program that is on a different server in this environment?

Do you have any ideas or hints (it seems to be a windows specific problem) how do I do a "remote call" from the Oracle database to the other server?

Sincerly, Rene

Tom,

I've used the Util class since ver 817 ->10.1.0.2 with no issue at all.
When running same code after the upgrade I'm getting
ERROR:
ORA-03114: not connected to ORACLE

BEGIN rc('/bin/ps -ef'); END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

No trace files to find in udump/bdump. I also tried putting dbms_output and java system.out.. but the connection aborts and never finishes and I'm not getting any debug info at all.
In addition to the specific grants I've also granted javauserpriv, javasyspriv but no different result.
Is anyone aware of anything that has changed in the ojvm that makes this non backward compatible ?

exec dbms_java.grant_permission('IRIS', 'java.io.FilePermission', '/bin/ps','execute');
exec dbms_java.grant_permission('IRIS', 'java.io.FilePermission', '*','read,write');


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

public class Util extends Object
{
public static int RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;

try
{
Process p = rt.exec(args);

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 = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);

rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/
create or replace
function RUN_CMD( p_cmd in varchar2) return number
as
language java
name 'Util.RunThis(java.lang.String) return integer';
/
create or replace procedure RC(p_cmd in varchar2)
as
x number;
begin
x := run_cmd(p_cmd);
end;
/

set serveroutput on size 10000
exec dbms_java.set_output(1000)
exec rc('/bin/ps -ef');