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)
    I have also tried and I canot reproduce this either. I have looked at metalink and the only thing I could find that matched this problem is using Bulk inserts.
    Message 1 of 9 , Nov 5, 2004
    • 0 Attachment
      I have also tried and I canot reproduce this either. I have looked at
      metalink and the only thing I could find that matched this problem is using
      Bulk inserts. The "Bulk Insert across Database Link in Oracle 8i ", DOC ID
      452902.999. This also says that nulls are sent accross the link wether you
      want them or not. I havenot managed to find anything else that comes close
      except for ora-01400 for copy across dblinks.

      Andy

      -----Original Message-----
      From: David Kurtz [mailto:info2@...]
      Sent: vrijdag 5 november 2004 12:47
      To: psftdba@yahoogroups.com
      Subject: RE: PeopleSoft DBA Forum Insert via database link from Ora 9i
      to Ora 8i




      I can't reproduce this on 8.1.7.4 and 9.2.0.5 on Windows in either
      direction.

      It can't be a null being selected from the table because of the IS NOT NULL
      condition and because OPRID is a NOT NULL column in the first place.

      I suspect this is something funny about database links from 9i down to 8i,
      there are various bugs
      What operating system you using?

      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 Fügner [mailto:stephan.fuegner@...]
      Sent: 05 November 2004 07: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










      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















      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








      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.
    • 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 2 of 9 , Nov 5, 2004
      • 0 Attachment
          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 3 of 9 , Nov 5, 2004
        • 0 Attachment
          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 4 of 9 , Nov 5, 2004
          • 0 Attachment
            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 5 of 9 , Nov 6, 2004
            • 0 Attachment
              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.