Re: Possible Oracle bug - spot the mistake

Might be the fact that your string goes onto the next line (that is, it's terminated by a line return, not a close quote). Try this:

insert into USER_PENDING (UP_USER_NO, UP_TIME_STAMP, UP_EMAIL_ADDRESS, UP_LAST_NAME, UP_FIRST_NAME, UP_SCMB_CLIENT, UP_ACCOUNT_NO) values (USER_PENDING_SEQ.nextval,
TO_DATE('21/12/1997 15:05:11','DD/MM/YYYY, H24:MI:SS'), 'jarred_at_vwv.com', 'gfg','fgsdfgs','O','id number')

SQL doesn't let you embed line returns in strings, at least not that way.
  --Bob Muller

L120bj wrote:
>
> >Subject: Possible Oracle bug - spot the mistake
> >From: Graham Leggett <graham_at_vwv.com>
> >Date: 12/21/97 2:22PM GMT
> >Message-id: <349D2616.14AF707D_at_vwv.com>
> >
> >Hi all.
> >
> >I have a wierd problem.
> >
> >I have an insert statement that looks like this:
> >
> >insert into USER_PENDING (UP_USER_NO, UP_TIME_STAMP, UP_EMAIL_ADDRESS,
> >UP_LAST_NAME, UP_FIRST_NAME, UP_SCMB_CLIENT) values
> >(USER_PENDING_SEQ.nextval,TO_DATE('21/12/1997 15:05:11','DD/MM/YYYY,
> >HH24:MI:SS'),'jarred_at_vwv.com', 'gfg','fgsdfgs','O')
> >Statement executed.
> >
> >This works 100%.
> >
> >I add one more column to the insert, called UP_IDENTITY_NO, a
> >varchar2(15), and get the following result:
> >
> >insert into USER_PENDING (UP_USER_NO, UP_TIME_STAMP, UP_EMAIL_ADDRESS,
> >UP_LAST_NAME, UP_FIRST_NAME, UP_SCMB_CLIENT, UP_IDENTITY_NO) values
> >(USER_PENDING_SEQ.nextval,TO_DATE('21/12/1997 15:05:11','DD/MM/YYYY,
> >HH24:MI:SS'),'jarred_at_vwv.com', 'gfg','fgsdfgs','O','id number')
> >[OpenLink][ODBC][Oracle Server]ORA-01756: quoted string not properly
> >terminated, SQLSTATE=S1000
> >[OpenLink][ODBC][Driver]Syntax error or access, SQLSTATE=37000
> >
> >Umm - when last I looked, 'id number' was a properly terminated quoted
> >string. Anyway, it gets better. I substitute a different column, this
> >time called UP_ACCOUNT_NO, a varchar2(50).
> >
> >insert into USER_PENDING (UP_USER_NO, UP_TIME_STAMP, UP_EMAIL_ADDRESS,
> >UP_LAST_NAME, UP_FIRST_NAME, UP_SCMB_CLIENT, UP_ACCOUNT_NO) values
> >(USER_PENDING_SEQ.nextval,TO_DATE('21/12/1997 15:05:11','DD/MM/YYYY,
> >HH24:MI:SS'),'jarred_at_vwv.com', 'gfg','fgsdfgs','O','id number')
> >[OpenLink][ODBC][Oracle Server]ORA-00900: invalid SQL statement,
> >SQLSTATE=S1000
> >[OpenLink][ODBC][Driver]Syntax error or access, SQLSTATE=37000
> >
> >Now as can be seen, the SQL statement is invalid.
> >
> >Anyone shed any ideas as to why?
> >
> >System is an Oracle 7-3-3 server running under NT v4.0. Client is a
> >Solaris 2-5-1 machine running Openlink ODBC driver suite.
> >
> >Regards,
> >Graham
> >--
> >-----------------------------------------
> >graham_at_vwv.com "There's a moon
> >VWV Interactive over Bourbon Street
> > tonight...
> >
> >
> >
> >
> >
> >
> >
> I am not absolutely 100% certain about this, but it may be that your ODBC
> driver is not ORACLE 7.3 compatible. We upgraded our ORACLE database from 7.2
> to 7.3 a while back and sql which had previously run successfully via ODBC
> started to fail with similar error messages to those you are getting. It may
> be worth contacting ORACLE to see what ODBC drivers are certified as 7.3
> compatible. Hope this helps.
> Rob
Received on Mon Dec 22 1997 - 00:00:00 CST