5 Replies Latest reply on Jun 6, 2011 10:57 AM by Pandeesh

    return code 139

      Hi i am executingthe below script in unix:

      sqlplus -s ${SQL_USERID} <<EOF
      whenever sqlerror exit sql.sqlcode
      UPDATE statement

      echo $rc

      i am getting the return code as 139.

      What does it mean?

      How to make the query to be successful?

        • 1. Re: return code 139

          rc=`sqlplus -s ${SQL_USERID} <<EOF
          whenever sqlerror exit sql.sqlcode
          UPDATE statement


          when others then
          echo $rc; #Use value in rc for further processing.


          Edited by: Vinodh2 on Jun 2, 2011 5:57 AM
          • 2. Re: return code 139
            William Robertson
            My wild guess would be:
            ORA-00907: missing right parenthesis

            Whose idea was it to return sql.sqlcode though? The return code of Unix commands is an 8 bit number and can only go up to 255 (i.e. binary 11111111), which is fine for distinguishing between 0 (success) and 1 (failure) etc, after which it wraps around to 0 again. This means your 139 is mod(actual_error_code,256), and my guess is 907:
            $> sqlplus username/pass
            SQL*Plus: Release Production on Thu Jun 2 18:59:41 2011
            Copyright (c) 1982, 2010, Oracle.  All rights reserved.
            Connected to:
            Oracle Database 10g Enterprise Edition Release - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options
            SQL> select (1 from dual;
            select (1 from dual
            ERROR at line 1:
            ORA-00907: missing right parenthesis
            SQL> exit sql.sqlcode
            Disconnected from Oracle Database 10g Enterprise Edition Release - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options
            $> print $?
            Just for fun, here are the valid error codes that could give you 139:
               for r in (
                  select rownum as oracle_error
                       , mod(rownum,256) as return_code
                       , cast(null as varchar2(200)) as message
                  from   dual
                  connect by rownum <= 20000
                  if r.return_code = 139 then
                     r.message := sqlerrm(r.oracle_error * -1);
                     if r.message not like 'ORA-%: Message ' || r.oracle_error || ' not found;%' then
                     end if; 
                  end if; 
               end loop; 
            ORA-00139: duplicate attribute 
            ORA-00395: online logs for the clone database must be renamed
            ORA-00907: missing right parenthesis
            ORA-01163: SIZE clause indicates  (blocks), but should match header 
            ORA-01419: datdts: illegal format code
            ORA-01675: max_commit_propagation_delay inconsistent with other instances
            ORA-01931: cannot grant  to a role
            ORA-02187: invalid quota specification
            ORA-02443: Cannot drop constraint  - nonexistent constraint
            ORA-03211: The segment does not exist or is not in a valid state
            ORA-06027: NETASY: channel close failure
            ORA-06539: target of OPEN must be a query
            ORA-07563: sldext: $PARSE failure
            ORA-08331: Wait operation timed out
            ORA-10635: Invalid segment or tablespace type
            ORA-10891: disable column pruning in ANSI join transformation
            ORA-12171: TNS:could not resolve connect identifier: 
            ORA-12427: invalid input value for  parameter
            ORA-12683: encryption/crypto-checksumming: no Diffie-Hellman seed
            ORA-13195: failed to generate maximum tile value
            ORA-13451: GeoRaster metadata scaling function error
            ORA-13707: Either the start snapshot  or the end snapshot  is incomplete or missing key statistics.
            ORA-15243:  is not a valid version number
            ORA-16011: Remote File Server process is in Error state.
            ORA-16267: Cannot instantiate a Logical Standby during a switchover
            ORA-16523: operation requires the client to connect to instance ""
            ORA-16779: the destination parameter of a database is set incorrectly
            ORA-19595: archived log  already included in backup conversation
            ORA-19851: OS error while managing auxiliary database
            By the same logic, you will notice that ORA-00256, ORA-01024, ORA-01280, ORA-01536 etc return 0, making you think your script succeeded when it actually failed with 'space quota exceeded for tablespace' etc.

            Edited by: William Robertson on Jun 2, 2011 7:13 PM
            • 3. Re: return code 139
              Actually, while executing a script it throws the error " segmentation fault.Core dump" in our job.

              While looking into unix , it simply throws failed to update and return code is 139.

              What the error " segmentation fault.Core dump" means?

              Whether this error is related to oracle or unix?

              • 4. Re: return code 139
                William Robertson
                Sounds like you need to debug the script a bit more. As I said, all return code 139 is telling you is that you are hitting an exception where mod(sqlcode,256) = 139, which as you have found out is not very helpful. Can you add a spool statement to the script and see what the resulting logfile says? Do the SQL*Plus commands work when executed directly (not through a shellscript here-document construction)?

                "Segmentation fault - core dump" is a Unix error.
                • 5. Re: return code 139
                  But the same script executed fine without any error after 2 hrs.
                  I am wondering how that error suddenly went away.