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
  • andyv_psdba
    I wonder if you have an OPRID value of a space and the space is trimmed. Try this: insert into table@dblinkd (..) select ..... from PSOPRDEFN where OPRID
    Message 1 of 5 , Dec 2, 2004
    • 0 Attachment
      I wonder if you have an OPRID value of a space ' ' and the space is
      trimmed. Try this:

      insert into table@dblinkd (..)
      select ..... from PSOPRDEFN
      where OPRID <> ' '
      /

      OPRID IS NOT NULLABLE IN PSOPRDEFN but can have a space.

      hope this helps,

      AndyV.


      >
      > --- In psftdba@yahoogroups.com, "David Kurtz" <info2@g...> wrote:
      > >
      > > 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@g...
      > > 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@d...]
      > > 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@g... (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@g... (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
    Your message has been successfully submitted and would be delivered to recipients shortly.