Why simple Update statement does not work.

[moepower]

I'm trying to run a very simple Update statement against an Oracle8i database on Sun Solaris using SQL*Plus Release 8.1.6.0.0 on Windows 2000. The SQL statement is:

Update table_a
set column_a = (select sequence_name.nextval from dual)
where column_a is null;

I keep getting the ORA-03113: end-of-file on communication channel error message. Why is this command not working?

Thanks,

 

[MikeLacey]

are you correctly connected to the database first?
Mike
michael.j.lacey@ntlworld.com

 

[moepower]

I finally figured out that the statement should be:

Update table_a
set column_a = sequence_name.nextval
where column_a is not null;

I should not have put the Select statement sub-query in there. However, it is funny that Oracle gave me an end-of-file commnunication error message then did a core dump on the server though.

 

[MikeLacey]

Ok... weird, thanks for posting that, good to know.
Mike
michael.j.lacey@ntlworld.com

 

[samg58]

You ran into a PROFILE limit and timed out.

Your session died and the 03113 recorded the loss.

We're digging into a similar problem with Accrue....

 

[Guest_imported]

I have a similar problem at present, where the given command worked fine under Oracle 7.3.4, but doesn't under Oracle 8.1.7. There are a few levels involved and I'll explain them:

(1) a PERL script executes a command like

$rc = system("sqlplus -s DBLOGIN_INFO @ sql_script.sql > outfile.out"

(2) the sql_script.sql is merely:
update COMMIT_RESTART_560 set FILE_POS = 0 ;
exit;

This worked fine under Oracle 7.3.4; there are two glitches, one of which we can get around, with Oracle 8.1.7. First, we need to change "@ sql_script.sql" to "\@sql_script.sql", which is fine. Secondly, though, by the time it boiled back up to PERL, $rc should have gotten some sort of error return code; the log file (before making the noted fix) states "SP2-0310: unable to open file ..." (where ... is a bunch of garbage).

It also seems to hang the system, I need to hit Ctrl-D as the PERL script runs before it will even get to a simple printf statement I placed after this one.

I would question this in PERL discussion, but given that the only changes were on the Oracle side, I'll pose it here...

Thanks.

 

[Guest_imported]

I should note more clearly: the log file contains that error, but $rc still gets a value of 0, which means all is well... even though it isn't. I'd like to ensure that, when hitting this glitch, it returns something else.
Thanks - John

 

[ljspop]

Hi John,

Try adding a "whenever sqlerror" statement within your sql_script.sql file.

The syntax from the 8.1.5 docs is:
WHENEVER SQLERROR
{EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
[COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}

At least than your $rc can be made aware of trouble in paradise.

Bob Bob Lowell
ljspop@yahoo.com

 

[schneids99]

Strangely enough, that doesn't work... using a begin/exception/end didn't catch it either... it's as if everything's just fine even though it couldn't open the file and even knew enough to tell me so in the log file... I suppose I could parse the log file, but that seems like overkill; I posted the question at the Oracle site too - if there's an answer, I'll bring it back; (or vice versa).
- John

 

[karluk]

Another way to return an error code to the O/S is in the exit statement. If the last line of your sqlplus script is

exit sql.sqlcode;

then the error code, if any, of the last sql statement should be returned.

 

[schneids99]

Thanks for the suggestion - already tried it... I'm becoming pretty convinced that sql really isn't seeing the error.

 

[karluk]

It's hard to avoid the conclusion that the update statement never got executed - no update, no sqlerror.

If you can, please post the exact contents of the log file, both before and after the fix you made. I would be interested in seeing the name of the file that couldn't be opened.

 

[schneids99]

Just to get back to this... the "error" is just a message generated by sqlplus, but it won't return an error code... once we found that, they put me elsewhere, since I'm working on an Oracle 8i conversion and this evidently isn't specific to 8i.

It was pretty trivial to parse the log file for any such "SP" error, though, and if I end up looking at it again, that's what I'll do.

Anyway, prior to the fix, the log file was empty; after the fix it contained that message: "SP2-0310: unable to open file "filename""

Thanks,
John