Loading ...
Sorry, an error occurred while loading the content.
 

RE: PeopleSoft DBA Forum Insert via database link from Ora 9i to Ora 8i

Expand Messages
  • Baird, Andrew (ICT) (HK)
    Hi Stephan, Try looking at this article and set a trace on before you run the sql. This might let you see what is being passed through the link........ PURPOSE
    Message 1 of 9 , Nov 5, 2004
        Hi Stephan,
       
      Try looking at this article and set a trace on before you run the sql. This might let you see what is being passed through the link........
       
      PURPOSE 

      This article describes a process that can be used to debug the error

      ORA-1400 Mandatory (NOT NULL) column is missing or NULL during insert.

      The debug process is generic and can be used to debug any ORA-1400, even

      though this article talks about the specific problem when trying to create

      a procedure or package body.

      SCOPE & APPLICATION

      For users receiving the ora-1400 error.

      DESCRIPTION

      While running an application, the following error is generated:

      ERROR at line 1:

      ORA-604: error occurred at recursive SQL level 1

      ORA-1400: mandatory (NOT NULL) column is missing or NULL during insert

      For Oracle8, the ORA-1400 error reports the schema, table and column

      causing the problem. If this error is occuring within an application, then

      the full error is sometimes not reported. In this case, an event can be set

      either for the instance or for the session which will cause a tracefile

      to be generated in user_dump_dest directory with the full error.

      For the instance, edit the init<SID>.ora and add:

      event="1400 trace name errorstack level 3"

      and restart the instance.

      For the session, issue:

      alter session set events '1400 trace name errorstack level 3';

      Run the application to cause the error.

      For Oracle7, the ora-1400 error does not report the schema, table and

      column. In this case, setting the 10046 event will produce a tracefile

      which can then be used to identify the table and column causing the

      error.

      From the SQL*Plus session in which the error is occuring:

      alter session set events '10046 trace name context forever, level 4';

      Then cause the error.

      A tracefile will have been generated in the directory defined by the

      initialisation parameter USER_DUMP_DEST. Normally the last cursor

      in the tracefile is the offending SQL statement.

      An example is:

      insert into obj$

      (owner#, name, namespace, obj#, type, ctime, mtime, stime, status,

      remoteowner, linkname)

      values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11)

      END OF STMT

      PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=0

      BINDS #4:

      bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08

      bfp=400ae928 bln=22 avl=02 flg=0d

      value=11

      bind 1: dty=1 mxl=32(08) mal=00 scl=00 pre=00 oacflg=08

      bfp=400a2908 bln=08 avl=08 flg=0d

      value=""

      bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08

      bfp=400ae900 bln=22 avl=02 flg=0d

      value=1

      bind 3: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08

      bfp=400ae8d8 bln=22 avl=03 flg=0d

      value=7350

      bind 4: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08

      bfp=400ae8b0 bln=22 avl=02 flg=0d

      value=10

      bind 5: dty=12 mxl=07(07) mal=00 scl=00 pre=00 oacflg=08

      bfp=400a3238 bln=07 avl=07 flg=0d

      value="2/10/1999 11:16:29"

      bind 6: dty=12 mxl=07(07) mal=00 scl=00 pre=00 oacflg=08

      bfp=400a0870 bln=07 avl=07 flg=0d

      value="2/10/1999 11:16:29"

      bind 7: dty=12 mxl=07(07) mal=00 scl=00 pre=00 oacflg=08

      bfp=400a1fd8 bln=07 avl=07 flg=0d

      value="12/31/4712 23:59:59"

      bind 8: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08

      bfp=400ae888 bln=22 avl=02 flg=0d

      value=1

      bind 9: dty=1 mxl=32(00) mal=00 scl=00 pre=00 oacflg=00

      bfp=00000000 bln=00 avl=00 flg=01

      bind 10: dty=1 mxl=32(00) mal=00 scl=00 pre=00 oacflg=00

      bfp=00000000 bln=00 avl=00 flg=01

      The above lists all the bind variable values used by the insert statement.

      For example, bind 0: value=11

      bind 1: value=""

      bind 8: value=1

      Describing the table SYS.OBJ$ is as follows:

      SQL> desc sys.obj$

      Name Null? Type

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

      OBJ# NOT NULL NUMBER

      OWNER# NOT NULL NUMBER

      NAME NOT NULL VARCHAR2(30)

      NAMESPACE NOT NULL NUMBER

      TYPE NOT NULL NUMBER

      CTIME NOT NULL DATE

      MTIME NOT NULL DATE

      STIME NOT NULL DATE

      STATUS NOT NULL NUMBER

      REMOTEOWNER VARCHAR2(30)

      LINKNAME VARCHAR2(128)

      In this case the column NAME is NOT NULL, and bind 1 does not have a value.

      This is causing the ORA-1400.

       

      Regards

       

      Andy Baird
      _____________________________
      Database Administrator, ICT
      Holland Casino
      Hoofdweg 640
      P.O. Box 355
      2130 AJ Hoofddorp
      Netherlands

      (Tel            +31(0)23-565 94 07
      4 Fax           +31(0)23-565 93 77
      ÈMobile +31(0)653310617
      8  Email        abaird@...

       
       
      -----Original Message-----
      From: Stephan Fuegner [mailto:stephan.fuegner@...]
      Sent: vrijdag 5 november 2004 10:20
      To: abaird@...
      Cc: psftdba@yahoogroups.com
      Subject: RE: PeopleSoft DBA Forum Insert via database link from Ora 9i to Ora 8i


      Hi Andrew,

      many thanks for the fast answer. That was my first idea, too. To make sure I provide data for each row, I only use rows where a.oprid is not null (see where condition). The strange thing is, that "a.oprid is not null" is true, but on the same time Oracle replaces a.oprid by something when using nvl(a.oprid, 'something').

      INSERT INTO Table8@... (D_DATABASE, USER_OPRID)
      SELECT 'TEST', a.OPRID FROM PSOPRDEFN a
      WHERE a.oprid is not null


      Mit freundlichen Grüßen / kind regards
      Stephan Fügner


      Deutsche Bank AG
      -- HR Systems --

      Frankfurter Str. 84, Raum 2-30
      Phone    +49  69 91 03 40 97
      Mobile   +49 174  3 11 02 26
      Fax      +49  69 91 08 23 15





      "Baird, Andrew (ICT) (HK)" <abaird@...>

      05.11.2004 10:12

             
              To:        Stephan Fuegner/OuB/Zentrale/DeuBa@dbcom
              cc:        <psftdba@yahoogroups.com>
              Subject:        RE: PeopleSoft DBA Forum Insert via database link from Ora 9i to Ora 8i



      Hi Stephan,

      You will probably find that the field OPRID in the table PSOPRDEFN cannot be
      NULL (see below). That means you will have to supply data for each row you
      are inserting for the OPRID in the table.

      DESC PSOPRDEFN
       OPRID           VARCHAR2(30)                  NOT NULL,
       VERSION         INTEGER                       NOT NULL,
       OPRDEFNDESC     VARCHAR2(30)                  NOT NULL,
       EMPLID          VARCHAR2(11)                  NOT NULL,
       EMAILID         VARCHAR2(70)                  NOT NULL,
       OPRCLASS        VARCHAR2(8)                   NOT NULL,
       ROWSECCLASS     VARCHAR2(8)                   NOT NULL,
       CLASSCOUNT      INTEGER                       NOT NULL,
       OPERPSWD        VARCHAR2(32)                  NOT NULL,
       ENCRYPTED       INTEGER                       NOT NULL,
       SYMBOLICID      VARCHAR2(8)                   NOT NULL,
       LANGUAGE_CD     VARCHAR2(3)                   NOT NULL,
       MULTILANG       INTEGER                       NOT NULL,
       CURRENCY_CD     VARCHAR2(3)                   NOT NULL,
       LASTPSWDCHANGE  DATE                          NOT NULL,
       ACCTLOCK        INTEGER                       NOT NULL,
       PRCSPRFLCLS     VARCHAR2(8)                   NOT NULL,
       DEFAULTNAVHP    VARCHAR2(8)                   NOT NULL,
       LASTUPDDTTM     DATE,
       LASTUPDOPRID    VARCHAR2(30)                  NOT NULL,
       FAILEDLOGINS    INTEGER                       NOT NULL

      Regards

      Andy Baird
      _____________________________
      Database Administrator, ICT
      Holland Casino
      Hoofdweg 640
      P.O. Box 355
      2130 AJ Hoofddorp
      Netherlands

      Tel                                  +31(0)23-565 94 07
      Fax                                  +31(0)23-565 93 77
      Mobile                 +31(0)653310617
      Email                       abaird@...

      -----Original Message-----
      From: Stephan Fügner [mailto:stephan.fuegner@...]
      Sent: vrijdag 5 november 2004 8:32
      To: psftdba@yahoogroups.com
      Subject: PeopleSoft DBA Forum Insert via database link from Ora 9i to
      Ora 8i





      Hello,

      I'm running the following sql-statement on a Oracle 9i database, GBL
      is a database link pointing to an oracle 8i database.

      INSERT INTO Table8@... (D_DATABASE, USER_OPRID)
      SELECT 'TEST', a.OPRID FROM PSOPRDEFN a
      WHERE a.oprid is not null
      /

      And get the strange errormessage
      INSERT INTO Table@... (D_DATABASE, USER_OPRID)
      *
      FEHLER in Zeile 1:
      ORA-01400: cannot insert NULL into ("SYSADM"."Table8"."USER_OPRID")
      ORA-02063: preceding line from GBL.WORLD

      If I modify the query to nvl(a.oprid, 'something'), "something" will
      be successfully written to the table.

      Has anybody an idea what I'm doing wrong?

      kind regards
      Stephan









      ------------------------ Yahoo! Groups Sponsor --------------------~-->
      $9.95 domain names from Yahoo!. Register anything.
      http://us.click.yahoo.com/J8kdrA/y20IAA/yQLSAA/JqWylB/TM
      --------------------------------------------------------------------~->


      PeopleSoft for the Oracle DBA will be published by Apress at the end of
      November - see www.psft.dba.com.
      The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk.

      Yahoo! Groups Links

      <*> To visit your group on the web, go to:
         http://groups.yahoo.com/group/psftdba/

      <*> To unsubscribe from this group, send an email to:
         psftdba-unsubscribe@yahoogroups.com

      <*> Your use of Yahoo! Groups is subject to:
         http://docs.yahoo.com/info/terms/





      This communication is intended for the exclusive use of the addressee. It
      may contain confidential or privileged information. You may not divulge this
      information to any unauthorized third party. Please inform us immediately if
      this communication was received unintentionally. Thank you.






      This communication is intended for the exclusive use of the addressee. It may contain confidential or privileged information. You may not divulge this information to any unauthorized third party. Please inform us immediately if this communication was received unintentionally. Thank you.



    • Goulet, Dick
      Is the not null constraint on the table differed ? If the constraint was applied after an initial data load and deferred there is the possibility that you
      Message 2 of 9 , Nov 5, 2004
        Is the not null constraint on the table "differed"?  If the constraint was applied after an initial data load and deferred there is the possibility that you got an old data record.
         

        Dick Goulet
        Senior Oracle DBA
        Oracle Certified 8i DBA

         


        From: Stephan Fuegner [mailto:stephan.fuegner@...]
        Sent: Friday, November 05, 2004 4:47 AM
        To: abaird@...
        Cc: psftdba@yahoogroups.com
        Subject: RE: PeopleSoft DBA Forum Insert via database link from Ora 9i to Ora 8i


        Hi Andy,

        sure - however a.oprid is not null according to the where condition. My question is just why is oprid NOT NULL in the where condition but NULL in the select part of the statement?

        Mit freundlichen Grüßen / kind regards
        Stephan Fügner


        Deutsche Bank AG
        -- HR Systems --

        Frankfurter Str. 84, Raum 2-30
        Phone    +49  69 91 03 40 97
        Mobile   +49 174  3 11 02 26
        Fax      +49  69 91 08 23 15





        "Baird, Andrew (ICT) (HK)" <abaird@...>

        05.11.2004 10:42

               
                To:        Stephan Fuegner/OuB/Zentrale/DeuBa@dbcom
                cc:        <psftdba@yahoogroups.com>
                Subject:        RE: PeopleSoft DBA Forum Insert via database link from Ora 9i to Ora 8i



        Hi Stephan,
         
        This is correct. The NVL() function replaces all occurances of oprid with 'something', where the oprid is null. Basically the function replace the NULL value with 'something'.
         
        Regards
         
        Andy
        -----Original Message-----
        From:
        Stephan Fuegner [mailto:stephan.fuegner@...]
        Sent:
        vrijdag 5 november 2004 10:20
        To:
        abaird@...
        Cc:
        psftdba@yahoogroups.com
        Subject:
        RE: PeopleSoft DBA Forum Insert via database link from Ora 9i to Ora 8i


        Hi Andrew,


        many thanks for the fast answer. That was my first idea, too. To make sure I provide data for each row, I only use rows where a.oprid is not null (see where condition). The strange thing is, that "a.oprid is not null" is true, but on the same time Oracle replaces a.oprid by something when using nvl(a.oprid, 'something').


        INSERT INTO Table8@... (D_DATABASE, USER_OPRID)
        SELECT 'TEST', a.OPRID FROM PSOPRDEFN a
        WHERE a.oprid is not null


        Mit freundlichen Grüßen / kind regards

        Stephan Fügner


        Deutsche Bank AG
        -- HR Systems --

        Frankfurter Str. 84, Raum 2-30
        Phone    +49  69 91 03 40 97
        Mobile   +49 174  3 11 02 26
        Fax      +49  69 91 08 23 15





        "Baird, Andrew (ICT) (HK)" <abaird@...>

        05.11.2004 10:12

               
               To:        Stephan Fuegner/OuB/Zentrale/DeuBa@dbcom

               cc:        <psftdba@yahoogroups.com>

               Subject:        RE: PeopleSoft DBA Forum Insert via database link from Ora 9i to Ora 8i




        Hi Stephan,

        You will probably find that the field OPRID in the table PSOPRDEFN cannot be
        NULL (see below). That means you will have to supply data for each row you
        are inserting for the OPRID in the table.

        DESC PSOPRDEFN
        OPRID           VARCHAR2(30)                  NOT NULL,
        VERSION         INTEGER                       NOT NULL,
        OPRDEFNDESC     VARCHAR2(30)                  NOT NULL,
        EMPLID          VARCHAR2(11)                  NOT NULL,
        EMAILID         VARCHAR2(70)                  NOT NULL,
        OPRCLASS        VARCHAR2(8)                   NOT NULL,
        ROWSECCLASS     VARCHAR2(8)                   NOT NULL,
        CLASSCOUNT      INTEGER                       NOT NULL,
        OPERPSWD        VARCHAR2(32)                  NOT NULL,
        ENCRYPTED       INTEGER                       NOT NULL,
        SYMBOLICID      VARCHAR2(8)                   NOT NULL,
        LANGUAGE_CD     VARCHAR2(3)                   NOT NULL,
        MULTILANG       INTEGER                       NOT NULL,
        CURRENCY_CD     VARCHAR2(3)                   NOT NULL,
        LASTPSWDCHANGE  DATE                          NOT NULL,
        ACCTLOCK        INTEGER                       NOT NULL,
        PRCSPRFLCLS     VARCHAR2(8)                   NOT NULL,
        DEFAULTNAVHP    VARCHAR2(8)                   NOT NULL,
        LASTUPDDTTM     DATE,
        LASTUPDOPRID    VARCHAR2(30)                  NOT NULL,
        FAILEDLOGINS    INTEGER                       NOT NULL

        Regards

        Andy Baird
        _____________________________
        Database Administrator, ICT
        Holland Casino
        Hoofdweg 640
        P.O. Box 355
        2130 AJ Hoofddorp
        Netherlands

        Tel                                  +31(0)23-565 94 07
        Fax                                  +31(0)23-565 93 77
        Mobile                 +31(0)653310617
        Email                       abaird@...

        -----Original Message-----
        From: Stephan Fügner [mailto:stephan.fuegner@...]
        Sent: vrijdag 5 november 2004 8:32
        To: psftdba@yahoogroups.com
        Subject: PeopleSoft DBA Forum Insert via database link from Ora 9i to
        Ora 8i





        Hello,

        I'm running the following sql-statement on a Oracle 9i database, GBL
        is a database link pointing to an oracle 8i database.


        INSERT INTO Table8@... (D_DATABASE, USER_OPRID)
        SELECT 'TEST', a.OPRID FROM PSOPRDEFN a
        WHERE a.oprid is not null
        /

        And get the strange errormessage
        INSERT INTO Table@... (D_DATABASE, USER_OPRID)
        *
        FEHLER in Zeile 1:
        ORA-01400: cannot insert NULL into ("SYSADM"."Table8"."USER_OPRID")
        ORA-02063: preceding line from GBL.WORLD

        If I modify the query to nvl(a.oprid, 'something'), "something" will
        be successfully written to the table.

        Has anybody an idea what I'm doing wrong?

        kind regards
        Stephan









        ------------------------ Yahoo! Groups Sponsor --------------------~-->
        $9.95 domain names from Yahoo!. Register anything.
        http://us.click.yahoo.com/J8kdrA/y20IAA/yQLSAA/JqWylB/TM
        --------------------------------------------------------------------~->

        PeopleSoft for the Oracle DBA will be published by Apress at the end of
        November - see www.psft.dba.com.
        The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk.

        Yahoo! Groups Links

        <*> To visit your group on the web, go to:
          http://groups.yahoo.com/group/psftdba/

        <*> To unsubscribe from this group, send an email to:
          psftdba-unsubscribe@yahoogroups.com

        <*> Your use of Yahoo! Groups is subject to:
          http://docs.yahoo.com/info/terms/





        This communication is intended for the exclusive use of the addressee. It
        may contain confidential or privileged information. You may not divulge this
        information to any unauthorized third party. Please inform us immediately if
        this communication was received unintentionally. Thank you.





        This communication is intended for the exclusive use of the addressee. It may contain confidential or privileged information. You may not divulge this information to any unauthorized third party. Please inform us immediately if this communication was received unintentionally. Thank you.


      • David Kurtz
        What happens if you run the insert into a table without the null constraint. How many rows have NULL operator IDs? regards _________________________ David
        Message 3 of 9 , Nov 5, 2004
          What happens if you run the insert into a table without the null constraint.
          How many rows have NULL operator IDs?
           

          regards
          _________________________
          David Kurtz
          Go-Faster Consultancy Ltd.
          tel: +44 (0)7771 760660
          fax: +44 (0)7092 348865
          web: www.go-faster.co.uk
          mailto:david.kurtz@...
          Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
          PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

          -----Original Message-----
          From: Stephan Fuegner [mailto:stephan.fuegner@...]
          Sent: 05 November 2004 09:47
          To: abaird@...
          Cc: psftdba@yahoogroups.com
          Subject: RE: PeopleSoft DBA Forum Insert via database link from Ora 9i to Ora 8i


          Hi Andy,

          sure - however a.oprid is not null according to the where condition. My question is just why is oprid NOT NULL in the where condition but NULL in the select part of the statement?

          Mit freundlichen Grüßen / kind regards
          Stephan Fügner


          Deutsche Bank AG
          -- HR Systems --

          Frankfurter Str. 84, Raum 2-30
          Phone    +49  69 91 03 40 97
          Mobile   +49 174  3 11 02 26
          Fax      +49  69 91 08 23 15





          "Baird, Andrew (ICT) (HK)" <abaird@...>

          05.11.2004 10:42

                 
                  To:        Stephan Fuegner/OuB/Zentrale/DeuBa@dbcom
                  cc:        <psftdba@yahoogroups.com>
                  Subject:        RE: PeopleSoft DBA Forum Insert via database link from Ora 9i to Ora 8i



          Hi Stephan,
           
          This is correct. The NVL() function replaces all occurances of oprid with 'something', where the oprid is null. Basically the function replace the NULL value with 'something'.
           
          Regards
           
          Andy
          -----Original Message-----
          From:
          Stephan Fuegner [mailto:stephan.fuegner@...]
          Sent:
          vrijdag 5 november 2004 10:20
          To:
          abaird@...
          Cc:
          psftdba@yahoogroups.com
          Subject:
          RE: PeopleSoft DBA Forum Insert via database link from Ora 9i to Ora 8i


          Hi Andrew,


          many thanks for the fast answer. That was my first idea, too. To make sure I provide data for each row, I only use rows where a.oprid is not null (see where condition). The strange thing is, that "a.oprid is not null" is true, but on the same time Oracle replaces a.oprid by something when using nvl(a.oprid, 'something').


          INSERT INTO Table8@... (D_DATABASE, USER_OPRID)
          SELECT 'TEST', a.OPRID FROM PSOPRDEFN a
          WHERE a.oprid is not null


          Mit freundlichen Grüßen / kind regards

          Stephan Fügner


          Deutsche Bank AG
          -- HR Systems --

          Frankfurter Str. 84, Raum 2-30
          Phone    +49  69 91 03 40 97
          Mobile   +49 174  3 11 02 26
          Fax      +49  69 91 08 23 15





          "Baird, Andrew (ICT) (HK)" <abaird@...>

          05.11.2004 10:12

                 
                 To:        Stephan Fuegner/OuB/Zentrale/DeuBa@dbcom

                 cc:        <psftdba@yahoogroups.com>

                 Subject:        RE: PeopleSoft DBA Forum Insert via database link from Ora 9i to Ora 8i




          Hi Stephan,

          You will probably find that the field OPRID in the table PSOPRDEFN cannot be
          NULL (see below). That means you will have to supply data for each row you
          are inserting for the OPRID in the table.

          DESC PSOPRDEFN
          OPRID           VARCHAR2(30)                  NOT NULL,
          VERSION         INTEGER                       NOT NULL,
          OPRDEFNDESC     VARCHAR2(30)                  NOT NULL,
          EMPLID          VARCHAR2(11)                  NOT NULL,
          EMAILID         VARCHAR2(70)                  NOT NULL,
          OPRCLASS        VARCHAR2(8)                   NOT NULL,
          ROWSECCLASS     VARCHAR2(8)                   NOT NULL,
          CLASSCOUNT      INTEGER                       NOT NULL,
          OPERPSWD        VARCHAR2(32)                  NOT NULL,
          ENCRYPTED       INTEGER                       NOT NULL,
          SYMBOLICID      VARCHAR2(8)                   NOT NULL,
          LANGUAGE_CD     VARCHAR2(3)                   NOT NULL,
          MULTILANG       INTEGER                       NOT NULL,
          CURRENCY_CD     VARCHAR2(3)                   NOT NULL,
          LASTPSWDCHANGE  DATE                          NOT NULL,
          ACCTLOCK        INTEGER                       NOT NULL,
          PRCSPRFLCLS     VARCHAR2(8)                   NOT NULL,
          DEFAULTNAVHP    VARCHAR2(8)                   NOT NULL,
          LASTUPDDTTM     DATE,
          LASTUPDOPRID    VARCHAR2(30)                  NOT NULL,
          FAILEDLOGINS    INTEGER                       NOT NULL

          Regards

          Andy Baird
          _____________________________
          Database Administrator, ICT
          Holland Casino
          Hoofdweg 640
          P.O. Box 355
          2130 AJ Hoofddorp
          Netherlands

          Tel                                  +31(0)23-565 94 07
          Fax                                  +31(0)23-565 93 77
          Mobile                 +31(0)653310617
          Email                       abaird@...

          -----Original Message-----
          From: Stephan Fügner [mailto:stephan.fuegner@...]
          Sent: vrijdag 5 november 2004 8:32
          To: psftdba@yahoogroups.com
          Subject: PeopleSoft DBA Forum Insert via database link from Ora 9i to
          Ora 8i





          Hello,

          I'm running the following sql-statement on a Oracle 9i database, GBL
          is a database link pointing to an oracle 8i database.


          INSERT INTO Table8@... (D_DATABASE, USER_OPRID)
          SELECT 'TEST', a.OPRID FROM PSOPRDEFN a
          WHERE a.oprid is not null
          /

          And get the strange errormessage
          INSERT INTO Table@... (D_DATABASE, USER_OPRID)
          *
          FEHLER in Zeile 1:
          ORA-01400: cannot insert NULL into ("SYSADM"."Table8"."USER_OPRID")
          ORA-02063: preceding line from GBL.WORLD

          If I modify the query to nvl(a.oprid, 'something'), "something" will
          be successfully written to the table.

          Has anybody an idea what I'm doing wrong?

          kind regards
          Stephan









          ------------------------ Yahoo! Groups Sponsor --------------------~-->
          $9.95 domain names from Yahoo!. Register anything.
          http://us.click.yahoo.com/J8kdrA/y20IAA/yQLSAA/JqWylB/TM
          --------------------------------------------------------------------~->

          PeopleSoft for the Oracle DBA will be published by Apress at the end of
          November - see www.psft.dba.com.
          The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk.

          Yahoo! Groups Links

          <*> To visit your group on the web, go to:
            http://groups.yahoo.com/group/psftdba/

          <*> To unsubscribe from this group, send an email to:
            psftdba-unsubscribe@yahoogroups.com

          <*> Your use of Yahoo! Groups is subject to:
            http://docs.yahoo.com/info/terms/





          This communication is intended for the exclusive use of the addressee. It
          may contain confidential or privileged information. You may not divulge this
          information to any unauthorized third party. Please inform us immediately if
          this communication was received unintentionally. Thank you.





          This communication is intended for the exclusive use of the addressee. It may contain confidential or privileged information. You may not divulge this information to any unauthorized third party. Please inform us immediately if this communication was received unintentionally. Thank you.


        • Graham Bowden
          This is a corker! It reminds me of that email 101 things you hope your DBA doesn t say - e.g.: Hmmm that s interesting It shouldn t do that Another
          Message 4 of 9 , Nov 6, 2004
            This is a corker! It reminds me of that email "101 things you hope your DBA doesn't say" - e.g.:
             
            "Hmmm that's interesting"
            "It shouldn't do that"
             
            Another curious SQL to explore this would perhaps to try it in the same DB, just to establish that it is the link that causes the issue. Then to try to a DB on same machine (and therefore OS) and same Oracle version...
             
            Keep us all posted on what happens next...

            Regards
             
             
            Try out our really useful page: www.homemarketeer.com/home
            ----- Original Message -----
            Sent: Friday, November 05, 2004 6:01 PM
            Subject: RE: PeopleSoft DBA Forum Insert via database link from Ora 9i to Ora 8i

            What happens if you run the insert into a table without the null constraint.
            How many rows have NULL operator IDs?
             

            regards
            _________________________
            David Kurtz
            Go-Faster Consultancy Ltd.
            tel: +44 (0)7771 760660
            fax: +44 (0)7092 348865
            web: www.go-faster.co.uk
            mailto:david.kurtz@...
            Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
            PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

            -----Original Message-----
            From: Stephan Fuegner [mailto:stephan.fuegner@...]
            Sent: 05 November 2004 09:47
            To: abaird@...
            Cc: psftdba@yahoogroups.com
            Subject: RE: PeopleSoft DBA Forum Insert via database link from Ora 9i to Ora 8i


            Hi Andy,

            sure - however a.oprid is not null according to the where condition. My question is just why is oprid NOT NULL in the where condition but NULL in the select part of the statement?

            Mit freundlichen Grüßen / kind regards
            Stephan Fügner


            Deutsche Bank AG
            -- HR Systems --

            Frankfurter Str. 84, Raum 2-30
            Phone    +49  69 91 03 40 97
            Mobile   +49 174  3 11 02 26
            Fax      +49  69 91 08 23 15





            "Baird, Andrew (ICT) (HK)" <abaird@...>

            05.11.2004 10:42

                   
                    To:        Stephan Fuegner/OuB/Zentrale/DeuBa@dbcom
                    cc:        <psftdba@yahoogroups.com>
                    Subject:        RE: PeopleSoft DBA Forum Insert via database link from Ora 9i to Ora 8i



            Hi Stephan,
             
            This is correct. The NVL() function replaces all occurances of oprid with 'something', where the oprid is null. Basically the function replace the NULL value with 'something'.
             
            Regards
             
            Andy
            -----Original Message-----
            From:
            Stephan Fuegner [mailto:stephan.fuegner@...]
            Sent:
            vrijdag 5 november 2004 10:20
            To:
            abaird@...
            Cc:
            psftdba@yahoogroups.com
            Subject:
            RE: PeopleSoft DBA Forum Insert via database link from Ora 9i to Ora 8i


            Hi Andrew,


            many thanks for the fast answer. That was my first idea, too. To make sure I provide data for each row, I only use rows where a.oprid is not null (see where condition). The strange thing is, that "a.oprid is not null" is true, but on the same time Oracle replaces a.oprid by something when using nvl(a.oprid, 'something').


            INSERT INTO Table8@... (D_DATABASE, USER_OPRID)
            SELECT 'TEST', a.OPRID FROM PSOPRDEFN a
            WHERE a.oprid is not null


            Mit freundlichen Grüßen / kind regards

            Stephan Fügner


            Deutsche Bank AG
            -- HR Systems --

            Frankfurter Str. 84, Raum 2-30
            Phone    +49  69 91 03 40 97
            Mobile   +49 174  3 11 02 26
            Fax      +49  69 91 08 23 15





            "Baird, Andrew (ICT) (HK)" <abaird@...>

            05.11.2004 10:12

                   
                   To:        Stephan Fuegner/OuB/Zentrale/DeuBa@dbcom

                   cc:        <psftdba@yahoogroups.com>

                   Subject:        RE: PeopleSoft DBA Forum Insert via database link from Ora 9i to Ora 8i




            Hi Stephan,

            You will probably find that the field OPRID in the table PSOPRDEFN cannot be
            NULL (see below). That means you will have to supply data for each row you
            are inserting for the OPRID in the table.

            DESC PSOPRDEFN
            OPRID           VARCHAR2(30)                  NOT NULL,
            VERSION         INTEGER                       NOT NULL,
            OPRDEFNDESC     VARCHAR2(30)                  NOT NULL,
            EMPLID          VARCHAR2(11)                  NOT NULL,
            EMAILID         VARCHAR2(70)                  NOT NULL,
            OPRCLASS        VARCHAR2(8)                   NOT NULL,
            ROWSECCLASS     VARCHAR2(8)                   NOT NULL,
            CLASSCOUNT      INTEGER                       NOT NULL,
            OPERPSWD        VARCHAR2(32)                  NOT NULL,
            ENCRYPTED       INTEGER                       NOT NULL,
            SYMBOLICID      VARCHAR2(8)                   NOT NULL,
            LANGUAGE_CD     VARCHAR2(3)                   NOT NULL,
            MULTILANG       INTEGER                       NOT NULL,
            CURRENCY_CD     VARCHAR2(3)                   NOT NULL,
            LASTPSWDCHANGE  DATE                          NOT NULL,
            ACCTLOCK        INTEGER                       NOT NULL,
            PRCSPRFLCLS     VARCHAR2(8)                   NOT NULL,
            DEFAULTNAVHP    VARCHAR2(8)                   NOT NULL,
            LASTUPDDTTM     DATE,
            LASTUPDOPRID    VARCHAR2(30)                  NOT NULL,
            FAILEDLOGINS    INTEGER                       NOT NULL

            Regards

            Andy Baird
            _____________________________
            Database Administrator, ICT
            Holland Casino
            Hoofdweg 640
            P.O. Box 355
            2130 AJ Hoofddorp
            Netherlands

            Tel                                  +31(0)23-565 94 07
            Fax                                  +31(0)23-565 93 77
            Mobile                 +31(0)653310617
            Email                       abaird@...

            -----Original Message-----
            From: Stephan Fügner [mailto:stephan.fuegner@...]
            Sent: vrijdag 5 november 2004 8:32
            To: psftdba@yahoogroups.com
            Subject: PeopleSoft DBA Forum Insert via database link from Ora 9i to
            Ora 8i





            Hello,

            I'm running the following sql-statement on a Oracle 9i database, GBL
            is a database link pointing to an oracle 8i database.


            INSERT INTO Table8@... (D_DATABASE, USER_OPRID)
            SELECT 'TEST', a.OPRID FROM PSOPRDEFN a
            WHERE a.oprid is not null
            /

            And get the strange errormessage
            INSERT INTO Table@... (D_DATABASE, USER_OPRID)
            *
            FEHLER in Zeile 1:
            ORA-01400: cannot insert NULL into ("SYSADM"."Table8"."USER_OPRID")
            ORA-02063: preceding line from GBL.WORLD

            If I modify the query to nvl(a.oprid, 'something'), "something" will
            be successfully written to the table.

            Has anybody an idea what I'm doing wrong?

            kind regards
            Stephan









            ------------------------ Yahoo! Groups Sponsor --------------------~-->
            $9.95 domain names from Yahoo!. Register anything.
            http://us.click.yahoo.com/J8kdrA/y20IAA/yQLSAA/JqWylB/TM
            --------------------------------------------------------------------~->

            PeopleSoft for the Oracle DBA will be published by Apress at the end of
            November - see www.psft.dba.com.
            The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk.

            Yahoo! Groups Links

            <*> To visit your group on the web, go to:
              http://groups.yahoo.com/group/psftdba/

            <*> To unsubscribe from this group, send an email to:
              psftdba-unsubscribe@yahoogroups.com

            <*> Your use of Yahoo! Groups is subject to:
              http://docs.yahoo.com/info/terms/





            This communication is intended for the exclusive use of the addressee. It
            may contain confidential or privileged information. You may not divulge this
            information to any unauthorized third party. Please inform us immediately if
            this communication was received unintentionally. Thank you.





            This communication is intended for the exclusive use of the addressee. It may contain confidential or privileged information. You may not divulge this information to any unauthorized third party. Please inform us immediately if this communication was received unintentionally. Thank you.




            PeopleSoft for the Oracle DBA will be published by Apress at the end of November - see www.psft.dba.com.
            The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk.



          Your message has been successfully submitted and would be delivered to recipients shortly.