DSXchange

Hi,
I am getting following error while updating table. I have only two columns. I know its Oracle error, could anybody help me to resolve this.

OakUpdateMagent..Trx_AgtCode.lnk_Magent: DSD.BCIPut call to SQLExecute failed.
SQL statement:UPDATE ANIL.DM_AGENT_M SET STRBOOKINGREFNBR = :2 WHERE STRAGENTCD = :1
SQLSTATE=60, DBMS.CODE=1008
[DataStage][SQL Client][ODBC][DataDirect][ODBC 20101 driver][Oracle]ORA-01008: not all variables bound

STRAGENTCD = "1012257"
STRBOOKINGREFNBR = "37"

Thanks

Girish Oak

This is a OCI column versus SQL statement (columns) mismatch type of error.
I would say that the column STRBOOKINGREFNBR is not defined as a key field in the column definition. In other words, your bind variables :1, :2 are bound positionally to your OCI stage column definitions. When they are in the WHERE clause, they are key fields and have to be defined as such. You'll get a ORA-1008 when they aren't.
The other error you can get when the columns and SQL are mismatched is a ORA-24334: no descriptor for this position. Usually when the columns in the select statement don't match the columns in the column definitions.

Hi,

I defined both columns in the ODBC stage. But still gives the same error. Can anybody help me out. Its really urgent. Special request for Kim, Kenneth and Ray to look into the matter.

I am using ODBC stage

Thanks in advance

Girish Oak

trobinson wrote:This is a OCI column versus SQL statement (columns) mismatch type of error.
I would say that the column STRBOOKINGREFNBR is not defined as a key field in the column definition. In other words, your bind variables :1, :2 are bound positionally to your OCI stage column definitions. When they are in the WHERE clause, they are key fields and have to be defined as such. You'll get a ORA-1008 when they aren't.
The other error you can get when the columns and SQL are mismatched is a ORA-24334: no descriptor for this position. Usually when the columns in the select statement don't match the columns in the column definitions.

trobinson wrote:This is a OCI column versus SQL statement (columns) mismatch type of error.
I would say that the column STRBOOKINGREFNBR is not defined as a key field in the column definition. In other words, your bind variables :1, :2 are bound positionally to your OCI stage column definitions. When they are in the WHERE clause, they are key fields and have to be defined as such. You'll get a ORA-1008 when they aren't.

You're right except for the second to last sentence. What you have defined in the metadata as the primary key is what forms the WHERE condition in the SQL. The columns do not have to be the primary key in the target table.

trobinson wrote: The other error you can get when the columns and SQL are mismatched is a ORA-24334: no descriptor for this position. Usually when the columns in the select statement don't match the columns in the column definitions.

Bingo.

How was this SQL statement generated? Are you using auto-generated SQL? What did you do to this SQL? Can you do a view data (it should show you these two columns in the target)? Not all variables bound usually indicates that more or less columns are being passed to the SQL. So, for instance, if you're streaming 6 columns of data, but are using user-defined SQL with only two bind variables, then viola! This message pops up.

Hi Kenneth,

I am trying to use user-defined sql. I have only two columns coming as an input for the stage. I want to update second column's value when first column's value is value from column one.

hence I wrote the sql like this And this error appeared.

After that I also tried to use Update exists only.

That works but raises errors when condition doesnt satisfies. That causes numerous warnings which could lead to termination of job.

Please guide for the correct approach

Thanks

Girish Oak

While using Update existing rows only got following warning messages

OakUpdateMagent..Trx_AgtCode.lnk_Magent: DSD.BCIPut call to SQLExecute failed.
SQL statement:UPDATE ANIL.DM_AGENT_M SET STRBOOKINGREFNBR = ? WHERE (STRAGENTCD = ?)
0 Rows affected.

STRAGENTCD = "1009617"
STRBOOKINGREFNBR = "136"

-- Girish Oak

Okay, you're streaming two columns of data. Column 1 is the WHERE column, and column 2 is the data value. If you use the AUTO-GENERATED SQL for update existing rows only, why doesn't that work? What is the error message? That should be your preference, rather than user-defined SQL.

Make sure you use the Get SQL Info button on the ODBC stage so that the stage adjusts to the data target type and generates the appropriate SQL. I don't use the ODBC stages with Oracle, as the OCI is my preference in all situations. Please try the auto-generated and paste the SQL and error messages that appear.

In Oracle, update existing rows only should not generate any messages for rows not found. If this is the case with ODBC, well, I learned something new. Otherwise, please paste the messaging for that and let's work that out.

Hi Ken,

Actually I also want to use OCI stage only but currently facing a small problem while using that. I am going to post a topic for the same. Currently, as mentioned above ODBC also works fine but throws warning if row to be updated doesnt found. I am worring about that. If you could focus on that, it would be great.

Thanks

Girish Oak

The "bleedingly obvious" question; if the row doesn't exist, why are you trying to update it?
Assuming the target table has a primary key, you can pre-load a hashed file with the key values, and use this as a means to determine whether the row exists or not and, thereby, handle updates and inserts on separate streams.
Alternately, and not as performant, you can change your update rule to "update existing rows or insert new rows". This tries the update then, if the update fails because the row does not exists, inserts a new row. The cost of the failed update is why the first approach is to be preferred.