Use Pivot Stage and receive Ora 1002 error

louise · Post by **louise** » Fri Oct 17, 2003 4:45 am

Hi all,

I've been trying to use Pivot stage to take an input Oracle table record, go through a transformer stage and output to another oracle table with multiple records.

THe input table has 19000 records to be output to 19000 x 3 records in output. However after processing till the end, I get an abort message 'ORA-1002 fetch out of sequence'.

Can someone tell me is there something i need to set for transaction handling, array size etc ?

Thanks,

Louise

chulett · Post by **chulett** » Fri Oct 17, 2003 10:17 am

Well... can't say for sure but unless you are doing something funky, like a stored procedure in there or anything else that might be cursor based, this may be a bug. "Fetch out of sequence" implies an attempt to fetch from a cursor that is no longer valid, which could be for a variety of reasons.

I'd report this to Ascential Support. Is it reproducable?

Teej · Post by **Teej** » Fri Oct 17, 2003 1:54 pm

Just to help you understand this particular error (all points to the Oracle Stage's internals):

dsadm> oerr ora 1002
01002, 00000, "fetch out of sequence"
// *Cause: This error means that a fetch has been attempted from a cursor
// which is no longer valid. Note that a PL/SQL cursor loop
// implicitly does fetches, and thus may also cause this error.
// There are a number of possible causes for this error, including:
// 1) Fetching from a cursor after the last row has been retrieved
// and the ORA-1403 error returned.
// 2) If the cursor has been opened with the FOR UPDATE clause,
// fetching after a COMMIT has been issued will return the error.
// 3) Rebinding any placeholders in the SQL statement, then issuing
// a fetch before reexecuting the statement.
// *Action: 1) Do not issue a fetch statement after the last row has been
// retrieved - there are no more rows to fetch.
// 2) Do not issue a COMMIT inside a fetch loop for a cursor
// that has been opened FOR UPDATE.
// 3) Reexecute the statement after rebinding, then attempt to
// fetch again.

ray.wurlod · Post by **ray.wurlod** » Fri Oct 17, 2003 8:25 pm

You might try increasing the transaction isolation level in the stage doing the SELECT to at least Read Committed. No guarantees, but I've seen this work in a similar situation (years ago, so details are hazy).

kcbland · Post by **kcbland** » Fri Oct 17, 2003 8:37 pm

This is a classic example of why jobs should be broken into multiple isolated steps. What I recommend is to never maintain live connections between source and target databases. You potentially are holding source selects for longer than you should, because you are busy transforming and loading. Had this developer simply written the select output to a sequential file, she would have achieved an audit snapshot, as well as a restart point. She also would have maximized her ability to quickly extract data, especially if it was in a volatile OLTP environment. If you have limited rollback and temp space in a source system, the original design has a larger exposure to problems such as this.

Next, I would have taken the source data sequential file and transformed it to another sequential file. This gives you the ability to instantiate this job naturally. If you have 4 cpus, then run 4 instances. Afterwards concatenate the results together. You now have a ready to load file. That should be a no-brainer.

louise · Post by **louise** » Fri Oct 17, 2003 8:46 pm

I was able to simulate the problem with 2 different jobs.

I have 2 jobs that use pivot stage and both gave me the same error.

One job has input oracle stage -> pivot stage -> transformer stage.
Another job has input oracle stage -> pivot stage -> output oracle table.

In both cases, the job aborted at the end of the pivot stage.

I hope i am defining the pivot stage correctly :

e.g input oracle table has columns : A, B1, B2,B3,C

For pivot stage input, I define columns A,B1,B2,B3,C
For pivot stage output, i define columns, A,B1,B2,B3,C, BB (I specified a multiple derivation as : B1,B2,B3). I didn't specify any group or key.

My expected result is for each input, i will get 3 output records with columns A,B1,B2,B3,C,BB.

Thanks for the feedback, i will change to sequential file and try again.

rgds,

Louise

ray.wurlod · Post by **ray.wurlod** » Fri Oct 17, 2003 8:55 pm

Can you do the same task using text files as source? This will isolate whether it's in the Pivot stage or not. If nothing else it will convince you that the Pivot stage works OK!

louise · Post by **louise** » Fri Oct 17, 2003 9:40 pm

I turn all oracle tables to sequential files and the pivot stage works fine...

Thanks for the help.

louise · Post by **louise** » Fri Oct 17, 2003 11:26 pm

Hi,

Pardon my ignorance but just another question :

Is there a way to stop writing to a output record from the pivot stage if one of the 3 fields is NULL instead of filtering them out later in the process ?

i.e input A, B1,B2,B3,C , if B3 is empty, I want only 2 records :

A,B1,C
A,B2,C

degraciavg · Post by **degraciavg** » Sun Oct 19, 2003 12:28 am

hi louise,

you can't place a conditional statement (to filter the null inputs) inside the derivation column in the output tab. you can't add a constraint in this stage either. you need to let your output go through a Transformer stage and add a constraint to filter out the null output in BB.

hth,
vladimir

ray.wurlod · Post by **ray.wurlod** » Sun Oct 19, 2003 12:28 am

There's nothing I know of in the Pivot stage itself. After all, the purpose of the stage is to pivot, not to constrain. That would be a much more complex design (of the stage)!