Home » Infrastructure » Unix » Run shell Script from a procedure
Run shell Script from a procedure [message #202145] Wed, 08 November 2006 06:52 Go to next message
kanis
Messages: 61
Registered: November 2006
Member
How we can run shell Script from a pl/sql procedure???
any pointer ??
Re: Run shell Script from a procedure [message #202150 is a reply to message #202145] Wed, 08 November 2006 07:08 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Ok. This one can stay. Don't cross post: one thread per subject.

MHE
Re: Run shell Script from a procedure [message #202158 is a reply to message #202145] Wed, 08 November 2006 07:37 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Depends on which version of Oracle you are running.

In Oracle 7 , I think dbms_pipe is the only way.
Oracle 8 and 9, use Java stored procedures
Oracle 10g, Java stored procedures or DBMS_SCHEDULER

That's the way I would go.

[Updated on: Wed, 08 November 2006 07:38]

Report message to a moderator

Re: Run shell Script from a procedure [message #202282 is a reply to message #202158] Wed, 08 November 2006 23:10 Go to previous messageGo to next message
kanis
Messages: 61
Registered: November 2006
Member
Thanx for the reply , but in my oracle 9i server jserver is not loaded , at that time it is bit difficult to load jserver in oracle 9i.
can the java stored proc be run with out jserver(with out loading initjvm.sql)???

any pointer & can you provide some samle code of the same???
Re: Run shell Script from a procedure [message #202352 is a reply to message #202282] Thu, 09 November 2006 04:44 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

You can build java stored proc from sqlplus providning you have the priviliges, java pool setup correctly.

Go http://asktom.oracle.com a do a search for java stored procedure.

He has provided some excellent examples.

Good Luck

[Updated on: Thu, 09 November 2006 04:46]

Report message to a moderator

Re: Run shell Script from a procedure [message #202359 is a reply to message #202352] Thu, 09 November 2006 06:03 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
And how would you run a java stored procedure without jserver?
Re: Run shell Script from a procedure [message #202384 is a reply to message #202359] Thu, 09 November 2006 07:52 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

So sorry for my very lame answer....Obviously need a few days of :=)

"And how would you run a java stored procedure without jserver?"

Havent got a clue Razz

..........

Anyways then I reckon dbms_pipe with some host commands ?
I would avoid it thou, and try another approach.


But Frank may have some ideas ? ........


Re: Run shell Script from a procedure [message #202402 is a reply to message #202384] Thu, 09 November 2006 10:10 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
No, sorry, I do not have any ideas.
Except that I think that starting OS-scripts from a database is, in general, kind of the wrong way around. (exceptions granted)
Re: Run shell Script from a procedure [message #400512 is a reply to message #202145] Tue, 28 April 2009 09:35 Go to previous messageGo to next message
nagashreeb
Messages: 6
Registered: April 2009
Location: Bangalore
Junior Member
Hi,
I have problem executing a sql command from my shell script on HP-UX.

I have test.sh containing
#!/usr/bin/sh
echo HELLO > /test/log.txt
sqlplus test/test@testdb @/test/upload/test.sql

test.sql contains
BEGIN
INSERT INTO msg_tbl values('hi there');
commit;
END;
/
quit;

when I run the script from the command prompt, the record gets inserted.

But when I run the script from a java stored procedure using runtime.exec

...................
runtime.exec("/test/script/test.sh");
//read stream for output messages
................

Strangely, the output on sqlplus for the java stored proc shows

HELLO
PL/SQL procedure executed successfully

I also find log.txt contianing 'HELLO'

But no rows are inserted. any pointers on what could be the problem?

thanks.
Re: Run shell Script from a procedure [message #400515 is a reply to message #202145] Tue, 28 April 2009 09:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But no rows are inserted. any pointers on what could be the problem?
The "environment" is not correct & therefore sqlplus does not actually run.

Add the following just after echo command
env | sort -o /tmp/my.env

Compare results from interactive & via JAVA
Re: Run shell Script from a procedure [message #400581 is a reply to message #400515] Tue, 28 April 2009 23:48 Go to previous messageGo to next message
nagashreeb
Messages: 6
Registered: April 2009
Location: Bangalore
Junior Member
Hi,
Thanks for your reply.
But I get the message "PL/SQL procedure successfully completed".
If sqlplus is not called, I would not be getting this message right?

I will any try the env | sort -o /tmp/my.env
and post the results
Re: Run shell Script from a procedure [message #400584 is a reply to message #202145] Tue, 28 April 2009 23:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>If sqlplus is not called, I would not be getting this message right?
Provide reproducible proof including timestamp your observations are valid.
Re: Run shell Script from a procedure [message #400587 is a reply to message #202145] Wed, 29 April 2009 00:06 Go to previous messageGo to next message
nagashreeb
Messages: 6
Registered: April 2009
Location: Bangalore
Junior Member
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set serveroutput on
SQL> call dbms_java.set_output(2000);
Call completed.
SQL> EXEC executecmd('/TESToracle/AASEJBAPP/UPLOAD/test.sh');
In main
Return code from process127
hello
Done executing
PL/SQL procedure successfully completed.

However I checked the table MSG_TBL, it has no rows inserted.
Re: Run shell Script from a procedure [message #400648 is a reply to message #400587] Wed, 29 April 2009 03:55 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Now isn't that weird?
Java is able to manipulate sqlplus' output and change the case of its output!
HELLO changed into hello
Re: Run shell Script from a procedure [message #400705 is a reply to message #202145] Wed, 29 April 2009 05:43 Go to previous messageGo to next message
nagashreeb
Messages: 6
Registered: April 2009
Location: Bangalore
Junior Member
echo HELLO was a typing mistake from me. the script has
echo hello
its not a change case pbm.
Re: Run shell Script from a procedure [message #400774 is a reply to message #400705] Wed, 29 April 2009 08:19 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
If you made a typo with that, how do we know you didn't make a typo with everything else?

Why do people insist on retyping their code and output when it is SO much easier to just cut and paste?
Re: Run shell Script from a procedure [message #400793 is a reply to message #202145] Wed, 29 April 2009 10:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>If sqlplus is not called, I would not be getting this message right?
WRONG!
>PL/SQL procedure successfully completed.
This means "executecmd" procedure was successfully invoked.
It says NOTHING about what happened with sqlplus in the script.

>I will any try the env | sort -o /tmp/my.env
>and post the results
We are still waiting for this.
Re: Run shell Script from a procedure [message #401305 is a reply to message #400793] Mon, 04 May 2009 00:29 Go to previous messageGo to next message
nagashreeb
Messages: 6
Registered: April 2009
Location: Bangalore
Junior Member
hi,
env | sort -o /tmp/my.env
I included the following line in my shell script, it returned nothing.

I tried it on the $ prompt as well. It just returned back to the $ prompt

$env | sort -o /tmp/my.env
$

file my.env has this content

COLUMNS=80
EDITOR=vi
ERASE=^H
HOME=/home/oracle
LINES=46
LOGNAME=oracle
MAIL=/var/mail/oracle
MANPATH=/usr/share/man/%L:/usr/share/man:/usr/contrib/man/%L:/usr/contrib/man:/usr/local/man/%L:/usr/local/man:/opt/ldapux/share/man/ %L:/opt/ldapux/share/man:/opt/ipf/man:/opt/ldapux/ypldapd/man:/opt/samba/man:/opt/samba/WTEC_Support_Tools/man:/opt/samba/cfsm_man:/o pt/cifsclient/share/man:/opt/openssl/man:/opt/openssl/prngd/man:/opt/wbem/share/man:/opt/graphics/common/man:/opt/amgr/man:/opt/amgr/ man/%L:/opt/sec_mgmt/share/man:/opt/drd/share/man/%L:/opt/drd/share/man:/opt/dsau/man:/opt/resmon/share/man/%L:/opt/resmon/share/man: /opt/gnome/man:/usr/contrib/kwdb/share/man:/opt/perl_32/man:/opt/perl_64/man:/opt/sfmdb/pgsql/man:/opt/sfm/share/man:/opt/swm/share/m an/%L:/opt/swm/share/man:/opt/sec_mgmt/share/man/%L:/opt/ssh/share/man:/opt/swa/share/man/%L:/opt/swa/share/man:/opt/VRTS/man:/opt/gw lm/man/%L:/opt/gwlm/man:/opt/ignite/share/man/%L:/opt/ignite/share/man:/usr/dt/share/man:/opt/hpsmc/shc/man:/opt/omni/lib/man
PATH=/usr/bin:/usr/ccs/bin:/usr/contrib/bin:/usr/contrib/Q4/bin:/opt/perl/bin:/opt/ipf/bin:/opt/nettladm/bin:/opt/fcms/bin:/opt/wbem/ bin:/opt/wbem/sbin:/opt/sas/bin:/opt/graphics/common/bin:/opt/atok/bin:/usr/bin/X11:/usr/contrib/bin/X11:/opt/sec_mgmt/bastille/bin:/ opt/drd/bin:/opt/dsau/bin:/opt/dsau/sbin:/opt/resmon/bin:/opt/firefox:/opt/gnome/bin:/usr/contrib/kwdb/bin:/opt/mozilla:/opt/perl_32/ bin:/opt/perl_64/bin:/opt/sfm/bin:/opt/swm/bin:/opt/sec_mgmt/spc/bin:/opt/ssh/bin:/opt/swa/bin:/opt/hpsmh/bin:/opt/thunderbird:/opt/g wlm/bin:/opt/ignite/bin:/opt/omni/bin:/EKPoracle/perf:/EKPoracle/orahome/product/10.2.x/db_1/bin:.
PWD=/TESToracle/AASEJBAPP/UPLOAD
SHELL=/sbin/sh
TERM=ansi
TZ=IST-5:30
_=/usr/bin/env
Re: Run shell Script from a procedure [message #401358 is a reply to message #401305] Mon, 04 May 2009 04:30 Go to previous message
nagashreeb
Messages: 6
Registered: April 2009
Location: Bangalore
Junior Member
One more input I would like to give. I had no problems running a batch file [containing same commands as in the shell script] using the same java stored procedure on oracle installed on windows.

I have problems trying to run it in HP-UX environment Sad.
Previous Topic: High iowait on AIX
Next Topic: NFS Inquery?
Goto Forum:
  


Current Time: Fri Mar 29 07:01:19 CDT 2024