DSXchange

Hi,

I wanted to create a stored procedure (within a parallel job) to write the reject output to a dedicated Oracle table by means of an Oracle stored procedure. Although the invoked procedure will eventually contain several parameters (the rerect outpuy being one of them), it has been stripped to a lean 1-argument procedure (to narrow down the conceivable error causes). I've tried this and that but did not get it working... Hereunder you'll find more details:

1. The stored procedure argument is specified as a RAW datatype; the corresponding field in the target Oracle table was also defined as RAW. The latter has been done to correspond with the VarBinary data type in which the DS reject output automatically comes.

The stored procedure stage is set to use the automatically generated procedure call, and contains one parameter, i.e. param1, that maps to the rejected column (reject output) and is defined as input. The error I'm getting in this scenario runs as follows:

APT_CombinedOperatorController,0: Fatal Error: Fatal: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to '<procedure name>' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

2. When I generate procedure call manually in the stored procedure stage, thereby specifying the aformentioned param as:

BEGIN <procedure name>(#param1#); END;

I get following error:
Stored_Procedure_37,0: Error: ORA-01036: illegal variable name/number

3. When I change the binary data type to a varchar (in datastage -> input link -> "rejected" field, Oracle stored procedure & target table), I get following errors:
- APT_CombinedOperatorController,0: Fatal Error: Fatal: Must have an output link to declare this type of parameter(B or O).

- Trs_PROD_MAPPING,1: Fatal Error: waitForWriteSignal(): Premature EOF on node <server_name>No such file or directory

Anyone a clue on how to best achieve my goal, i.e. export the reject output via a stored procedure with a manually generated procedure?

Thanks in advance

Regards,

1. What type of procedure did you set the stage to? Sounds like 'target' would be appropriate here. Getting it wrong can generate the error you posted.

2. You don't bind in the parameter name from the proc but rather that field from the job that should be passed to each positional parameter.

Hi Chulett

Thanks for your reply!

1. I've checked and the procedure type was currently set to "transform"; I've reset this to "Target" but that did not solve the issue for the first scenario.

More specifically, I'm still receiving following errors when I define the reject output as binary data (varbinary in DS & RAW in Oracle procedure/target table):

APT_CombinedOperatorController,0: Invalid character(s) ([xE8]) found converting string (code point(s): Warning: <job name>.Stored_Procedure_51: DSTAGE-STP-0095`:`...) from codepage UTF-8 to Unicode, substituting.

APT_CombinedOperatorController,0: Invalid character(s) ([xFFFD]) found converting Unicode string (code point(s): Warning: <job name>.Stored_Procedure_51: DSTAGE-STP-0095`:`...) to codepage ISO-8859-1, substituting.

APT_CombinedOperatorController,0: Fatal Error: Fatal: ORA-01480: trailing null missing from STR bind value

<job name>: Fatal Error: waitForWriteSignal(): Premature EOF on node <server name>No such file or directory

Is there not an issue with data types in this scenario? I've anyhow tried to convert the reject output to varchar (datastage -> input link -> "rejected" field: VARCHAR; Oracle stored procedure & target table: VARCHAR) but that resulted in comparable errors:

Stored_Procedure_51,0: Warning: <job name>_extract.Stored_Procedure_51: The value of the row is: rejected = 02k(ICAN

APT_CombinedOperatorController,0: Fatal Error: Fatal: ORA-01480: trailing null missing from STR bind value

Trs_STD_UNIT_CONV,1: Fatal Error: waitForWriteSignal(): Premature EOF on node <server name>No such file or directory

2. I'm not sure whether I understand you correctly but my final objective is to create a manual procedure statement where I can call the "rejected" field that is automatically generated by the reject output (next to several other parameters). Since this param can/will contain different data every time the job is run, job parameters seems to be of no use for me (after all, the latter will not be able to capture the rejected fields). Moreover, when I - in the stored procedure stage - map a param to the automatically generated "rejected" field, this seems to be an accepted field (this input is validated and random columns will not be accepted) so this makes me think it can be. Please correct me if I oversee sth.

Thanks already!

Regards,

FYI, in the last attempts, I did NOT foresee a parameter in the procedure stage and had the procedure statements generated automatically so here's what appears shaded:

BEGIN <procedure name>(:1); END;

1. Well, your proc is being called now, something it wasn't doing before so the correction to the type helped. Now you've got other errors to work through. The 'invalid characters' errors are fairly common and you may find some answers by searching the forums here but since all of your errors seem to be related to passing raw data into the proc you may be firmly into support territory here. Best to open a case with your official support provider IMHO.

2. Never mentioned job parameters. I was just trying to respond to the anonymous block you posted and how you passed in a parameter to the function and which generated the ORA-01036 error:

BEGIN <procedure name>(#param1#); END;

As you've seen, you need a positional bind variable that binds a column in the stage to each passed argument:

BEGIN <procedure name>(:1); END;

1. Well, your proc is being called now, something it wasn't doing before so the correction to the type helped. Now you've got other errors to work through. The 'invalid characters' errors are fairly common and you may find some answers by searching the forums here but since all of your errors seem to be related to passing raw data into the proc you may be firmly into support territory here. Best to open a case with your official support provider IMHO.

2. Never mentioned job parameters. I was just trying to respond to the anonymous block you posted and how you passed in a #parameter# to the function and which generated the ORA-01036 error:

BEGIN <procedure name>(#param1#); END;

As you've seen, you need a positional bind variable that binds a column in the stage to each passed argument:

BEGIN <procedure name>(:1); END;