ORA-01036 in job..

woosy · Post by **woosy** » Wed Mar 03, 2004 7:07 pm

Hi !

In my project, a job have two source oci stage and a target oci stage.

One source stage has user-defined sql and used by lookup.

I could search data by "View Data" in stage's properties window.

But while job running, ORA-01036 error is in the stage.

Could I know which is problem??

Thank!!

ray.wurlod · Post by **ray.wurlod** » Wed Mar 03, 2004 7:35 pm

Sure you can.

Use oerr -01036 to find out, or consult the Oracle codes and messages manual. It's an Oracle error, not a DataStage error.

kcbland · Post by **kcbland** » Wed Mar 03, 2004 8:45 pm

In a source OCI stage, viewing the data actually runs the SQL statement. In a reference OCI/ODBC stage, viewing the data runs an altered SQL statement.

Think about it a minute, the query for a reference is used to being handed specific values to use in the select. For example, your reference link is being asked to "go find this(these) row(s) where the following columns have this set of values". When you view data, the query can't exercise the filtering WHERE conditions, because what data is it supposed to go get? The answer is that it basically does a "SELECT columnsdefined FROM TABLE_X".

Therefore, if you have a syntactical issue, check your query. I suspect that it does not work either syntactically, or the values being passed by the job either (1) require conversion/casting or (2) you don't have the appropriate number of values defined in the query to be passed (bound variables).

chulett · Post by **chulett** » Wed Mar 03, 2004 11:22 pm

Also note that just because you can "View" the data doesn't mean the syntax is absolutely correct and the job will actually run!

One thing I've found, for instance, is that prototyping SQL in another tool and then pasting it into DataStage can sometimes bite you in the rear. That lowly little trailing semi-colon that your other tool might want doesn't affect your ability to View the data but it will cause an error when the job runs. Sure would be nice if the Running and the Viewing worked exactly the same.

When your ORA-01036 error was logged, it should have included some descriptive text as well, something very similar to what shows up when using the command Ray mentioned. "ORA-01036: illegal variable name/number" in this case. Is this Custom SQL, perhaps? [checks original post] Ah, yes, so it is. Please check your Custom SQL as there seems to be something amiss with it. If you can't figure it out, post the entire SQL statement here and your Column definitions and we'll see about getting you straightened out.

woosy · Post by **woosy** » Wed Mar 03, 2004 11:52 pm

Thanks very much KCBLAND !!

Thanks everyone!!

trobinson · Post by **trobinson** » Thu Mar 04, 2004 8:09 am

We requested an enhancement that would prompt when viewing data from an OCI stage for a set of values corresponding to the where clause. That would be nice from a troubleshooting point of view.

kcbland · Post by **kcbland** » Thu Mar 04, 2004 8:32 am

trobinson wrote:We requested an enhancement that would prompt when viewing data from an OCI stage for a set of values corresponding to the where clause. That would be nice from a troubleshooting point of view.

Hey, make sure you ask for a "CANCEL" button for when the query takes too long. It's great fun when you view data and realize the query is doing a cartesian product or full table scan and the row you want is the last row. Your only choice is to either kill the query from the DB side, or kill your client session, which probably leaves your job locked.