Uisng DBQuery to get value from Sequence

[jackcool]

Hi,

I was trying to access SEQUENCE.NEXTVAL using DBQUERY function. My Target is Oracle Database.I tried following syntax ,For some reason none of them seems to work.

TEXTTONUMBER(DBQUERY("SELECT TEST_SEQ.NEXTVAL FROM DUAL","-MDQ C:/test.mdq -DBNAME test" ))


TEXTTONUMBER(DBQUERY("SELECT TEST_SEQ.NEXTVAL FROM DUAL","C:/test.mdq","test" ))

Iam getting error code -1009 [Failed to execute the SQL statement]

Am i doing something wrong ? Can we use DBQUERY when Target for Output Card is Database ?

Iam running out of options any help is appreciated .


Thanks
Jack

[janhess]

Set the trace parameter -T and look in the resulting .dbl file for further information.

[jackcool]

Thanks for the reply.Iam very to new to the product.where do i set the Trace [-T] option.

[DSguru2B]

Try just selecting from dual and see what does it do?
SELECT COLUMN_NAME.NEXTVAL FROM dual;

[jackcool]

I tried using the "SELECT COLUMN_NAME.NEXTVAL FROM dual" it doesn't work.
Enabled the trace .Here is what i see in dbl file


*************************************************************
<4184-3696>: Validating the adapter command...
<4184-3696>: Database type is Oracle
<4184-3340>: Participating in active transaction.
<4184-3340>: Interface library version 8.0(81)
<4184-3340>: Map: C:\Ascential\xxx\testMap.mmc, Timestamp: 04/14/06 13:24:15.
<4184-3340>: Loading data for output card 1.
<4184-3340>: Database adapter: Oracle9i Version 8.0(81)
<4184-3340>: Starting database load to table test...
<4184-3340>: Host string: testdb
<4184-3340>: UserID : *****
<4184-3340>: Password : *****
<4184-3340>: Context: Output Card, Transaction scope: Map
<4184-3340>: On Failure: Rollback, On Success: Create
<4184-3340>: TRACE command specified, file: C:\Ascential\xxx\TestMap.dbl
<4184-3340>: Table name: test
<4184-3340>: Update mode is off.
<4184-3340>: The columns are of the following types:
<4184-3340>: Column 1 (TEST_ID) type is NUMBER(10).
<4184-3340>: Column 2 (*****) type is VARCHAR(255).
<4184-3340>: Column 3 (*****) type is DATE.
<4184-3340>: Column 4 (*****) type is NUMBER(5).
<4184-3340>: Column 5 (*****) type is NUMBER(8).
<4184-3340>: Column 6 (*****) type is NUMBER(12,2).
<4184-3340>: Column 7 (*****) type is VARCHAR(32).
<4184-3340>: Column 8 (*****) type is NUMBER(5).
<4184-3340>: Column 9 (*****) type is NUMBER(10).
<4184-3340>: The insert statement to be executed is:
<4184-3340>: INSERT INTO transmission VALUES (:a00, :a01, :a02, :a03, :a04, :a05, :a06, :a07, :a08)
<4184-3340>: Error in: OCIStmtExecute

Message : ORA-01400: cannot insert NULL into ("****"."TEST"."TEST_ID")

<4184-3340>: The following values were being inserted:
<4184-3340>: Column 1 TEST_ID : NULL
<4184-3340>: Column 2 **** : 6
<4184-3340>: Column 3 **** : xi-16
<4184-3340>: Column 4 **** : 2
<4184-3340>: Column 5 **** : 55
<4184-3340>: Column 6 **** : 51776
<4184-3340>: Column 7 **** : This is a Test
<4184-3340>: Column 8 **** : 1
<4184-3340>: Column 9 **** : 102
<4184-3340>: Failed to insert a row (rc = -1009).
<4184-3340>: Failed after 0 rows inserted.
<4184-3340>: Database load complete.
<4184-3340>: Returned status: (-1009) Failed to execute the SQL statement
*************************************************************

[jackcool]

Atlast it worked !!!
for some reason
TEXTTONUMBER(DBQUERY(XXX)) is evaluating to NONE

when i changed it to
LEAVENUM( DBQUERY("SELECT TEST_SEQ.NEXTVAL FROM dual","C:\Ascential\test.mdq","test"))

it worked.Thanks guys for your help in resloving this.