Oracle PL/SQL code running from UX shell script ???

[Henrique Silva_3]

Hello everyone, and happy new year.

I have a PL/SQL block, which runs a cursor, which works fine from a sqlplus session, however, when I wrap it in a ux shell script ( sh ), it just sites there and nothing seems to happen.

Is there anything especifically I need to do within the shell, to get this to run from the script ?

example of script !!!

#!/bin/sh

echo "Starting on `date`"; echo ""

. ~oracle/.profile

sqlplus -s /nolog @/home/oracle/update.sql
sqlplus -s /nolog @/home/oracle/cursor.sql

echo ""; echo "Ending on `date`"; echo ""

exit 0

the first sql, which is a simple update, works fine, but the cursor.sql just sits there, from the shell, and from cron, I am not sure if it is just creating oracle sessions, or not ( need to investigate it ), and not disconnecting them.

In any case, the cursor goes something like this ( changed table names and columns to protect the inocent :_) ). In any case, it is a simple cursor that updates a column if a condition is true. Again, it works fine from a sqlplus session, not from script !!! :

spool /tmp/UID.txt

CONNECT user/passwd@DB

set echo off
set serveroutput on size 1000000 format wrapped

column v1format 999999999999999999
column v2 format 999999999999999999

DECLARE

CURSOR c_TEST IS

select v1, v2
from table1 P
, table2 C
where P.T1_oid = C.T2_oid
and P.T1_email is not null
and C.T2_uid is null
and P.T1_oid != ( Select org_oid from T3
where T3.code = 'SOMETHING');

i number := 0;

BEGIN

FOR c_TEST_rec IN c_TEST

LOOP

i := i +1;
update T3
set column = c_TEST_rec.email
where t3_oid = c_TEST_rec.oid;

commit;

END LOOP;

DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('Records updated = '||i );
DBMS_OUTPUT.PUT_LINE('End of Program');

commit;BMS_OUTPUT.PUT_LINE('.');

spool off

output from sqlplus session

Records updated = 0
End of Program
.

PL/SQL procedure successfully completed.

ANY IDEAS HERE ?????

Thanks,

Henrique

[rtdewi0]

Henrique,

It may be as simple as adding an "exit" at the end of the sql script. Because SQL*Plus is an interactive program, it could be waiting for your next command.

Hope this helps.

Robert

[Henrique Silva_3]

Thanks.

Actually exit is there :-( I forgot to add that last line on the example above :-(

But I am thinking it has to be something else, because the file I am spooling this info to, has connected as the only entry in it. It never goes through the code and displays the output expected.

Cheers,

[Sandman!]

Hello Henrique,

Could you attach your PL/SQL code (NOT paste) so that it is more understandable.

Atleast from the code you have pasted here it looks like you are missing the "end" statement followed by the forward slash "/" terminator for your PL/SQL block.

Might be more obvious once you have attached your code.

cheers!

[Henrique Silva_3]

Here is the PL/SQL code !!

Again, it works fine on a sql plus session, not wrapped on the script. And I would not pay attention to what it is actually doing, but why it does not work on the ux script.

I did have the END and EXIT on it, not on the copy and past, my bad , but I am attaching it it here so that you can see it and let me know what is wrong !!

Henrique

PS.: I added the "/" at the end and that did not it either :-(

PS2.: The unix wrapper is on the original message, just say that this file is called cursor.sql instead of the attachment name. Again, the update.sql part works fine, the cursor does not :-(

[Muthukumar_5]

You have to execute this script as sql user only? Are you executing the scripting with normal or super user? Then It will not work. Try to log with sql user or use as,

su - sqluser -c "full script path"

or

use sudo to execute the script as sql user without password.

--
Muthu

[Yogeeraj_1]

hi,

are you sure the first script does not lock any record that the second script is waiting for?

if yes, can you try to set sql_trace on in your PLSQL code and see what's happenning?

e.g. alter session set sql_trace=true;

let us know what happens.

kind regards
yogeeraj

[Frank de Vries]

Maybe it is better to keep things basic and simple.

Leaving out the pl/sql and
just come back to a simple
query

What happens when you run this in
your unix script ?

spool /tmp/UID.txt

CONNECT user/passwd@instance

set echo off
set serveroutput on size 1000000 format wrapped

select * from dual;
spool off
exit;

---------
This has three purposes for troubleshooting logic , first
we test your unix environment,
secondly we test the CONNECT
and thirdly if it works we know
it is located in the pl/sql block,
(and then the sqltrace=true has to be
used and more debugging)
if not it is dependent of pl/sql
we need to look furhter at the
environment or CONNECT instance
maybe even sql*net

Get cooking :)

[Henrique Silva_3]

Thanks for all the replies. Maybe, the example above is misleading a bit.

Forget about the update.sql script. That one WORKS fine via the wrapped unix script. It does not block anything on the second script. They are completely detached, and for purposes of troubleshooting here, lets assume that we are only dealing with the PL/SQL block one !!!

Now, I am running this script from root cron, that is why I was setting up the oracle profile on the script. So, I went ahead and ran it as oracle, via the
su - oracle -c full path, after commenting out the .profile call, and same thing. It just sits there.

I added the alter session right after the connect string ( the user and passwd are for the application schema, a non-privileged schema ), and when I try to run it from the shell, again, this is what I get :

Starting on Tue Jan 10 09:26:42 EST 2006

Connected.

Session altered.

and nothing else !!! :-(

I ran the same exact code from a sqlplus window, just now, and this is what I got :

Session altered.

.
Records changed = 73
End of Program
.

PL/SQL procedure successfully completed.


Commit complete.


What gives ? :-)

Henrique

[Sandman!]

Hi Henrique,

What happens when you run the PL/SQL block by itself within a script i.e. without using the Unix wrapper script.

It would be easy to troubleshoot if the root cause is narrowed down to either the shell script or the SQL embedded within the PL/SQL anonymous block.

Could you attach the wrapper shell script as well.

cheers!

[Henrique Silva_3]

GOT IT :-)

Thanks everyone !!!

It turns out that the issue was the slash ( "/" )missing after the END; block :-)

I am not sure why this did not work before, unless all I did was put the "/" after the exit !!!

This was driving me nuts, and I really do appreciate all the help here. Without your insight, I would be pulling all my hair out :-)

Thanks a lot,

Henrique

[Sandman! · Accepted solution]

Hi Henrique,

I'm glad you got the solution. I had mentioned this in my very first post.

>> ...you are missing the "end" statement followed by the forward slash "/" terminator for your PL/SQL block. <<

cheers!

[Henrique Silva_3]

You are right !!

I was putting the "/" after the exit, as opposed to after the END; block :-)

Thanks a lot guys,

Henrique

[Frank de Vries]

That is bonkers,
you know what I had this at least a couple
of times, just seemed to obvious.
that shows me !!!!
End well , all well