pass value to oracle stored proc with blob datatype

[prasson_ibm]

Hi,
I am in datastage 8.7 and i have requirement where i need to pass value to stored procedure which is defined as BLOB. I have source as sequential file and i need to pass first 10 lines of sequential file to stored procedure whose parameter is blob define.I tried giving input to procedure as longvarbinary but its execution is failing.

Any help will be appriciated.

[chulett]

Yikes. So, what exactly is in your sequential file - each records holds a BLOB and you need to call the procedure 10 times? Or you need to call the procedure once and somehow combine the values from the first 10 lines into the BLOB?

Can you clarify that for us, please? I'd also be curious what kind of 'failure' you are seeing.

[prasson_ibm]

I am combining all 10 records(which is not blob rather a pipe delimited or fixed width file) in transformer and want to pass it to stored procedure as a blob.

[ray.wurlod]

No you don't. If you have pipe-delimited then at best it would be CLOB.

[prasson_ibm]

If it is CLOB what datatype i need to define in datastage to pass it through SP..?

[chulett]

Longvarchar. BLOB = binary large object, CLOB = character large object.

[prasson_ibm]

Can't i convert the string input data into HEX( using basic transformer stage) and pass as binary large object to SP?

[chulett]

You need to verify what exactly it is you are passing in - is it a BLOB or a CLOB - and pass it appropriately. I don't see any circumstances where converting it to hex would be a good idea... unless you've been specifically told to do that by whomever created the stored procedure.

[prasson_ibm]

Hi Chullet,

Thanks for your input,as far as i know it is BLOB column and they are asking me to pass top ten rows from file to this blob.

[chulett]

Then we're all the way back to your first post and the question you haven't answered yet - detail for us how you are combining the first 10 records and then what failure do you get with the procedure.

[prasson_ibm]

Hi,
I am combining first 10 records in transformer.Below is the logic:-

1. running below command in sequential file stage:-

Code: Select all

 head -10 <FileName>.* 

Created sequence number column in sequential file stage.

2. Created stage variable and merged all 10 records in one column.

Code: Select all

Svar=Svar:Inputcolumn.Payload:char(10)char(13)

3.Define constraint as Seqno=10 and pass the Svar to output derivation.

Output column defined as LongVarchar
Running the transformer stage sequentially.

I am getting below error:-

Stored_Procedure_37,0: Error: Datatype not presently supported

Please let me know if my approach is wrong.

[chulett]

Two things.

1) "Datatype not presently supported" should have stopped you right there. If passing a BLOB to the SP isn't supported, this whole approach goes down the drain. You'll need to look into alternates like PL/SQL I would imagine.

2) I don't see the need to add the LF/CR between what (I assume) are pieces of a BLOB that has been chopped up and written to a file. You probably should just concatenate them together but whomever created the file you are sourcing from should be able to help with that.

[prasson_ibm]

Hi Chullet,

First i have done it in PL/SQL code and i have called in Oracle connector stage.But their requirement is something different.There are two output variables Error_Code and Error_value and they want me to handle in datastage like

Code: Select all

If (Error_Code<0 and Error_Code=-1006) dup check is failed

else if ( Error_Code<0 and error_code<>-1006) then proc or pl/sql block failed.

else success.

So i asked them to give me Stored proc instead of PL/SQL code so that i can perform transformation after getting Error_code and Error_value.

Is there any way i can handle error_code after Oracle connector stage..??

[prasson_ibm]

Below is my PL/SQL block

Code: Select all

DECLARE 
  RetVal NUMBER;
  P_PAYLOADUUID VARCHAR2(40);
  P_PAYLOAD AITBROKER.STR_PAYLOADS.PAYLOAD%type;
  P_TRANSTYPE VARCHAR2(20);
  P_STATUS VARCHAR2(15);
  P_TIMESTAMP TIMESTAMP;
  P_ADAPTERTYPE VARCHAR2(20);
  P_PAYLOADSIZE NUMBER;
  P_ERRORMSG VARCHAR2(32767);


  RetVal := TRANSMISSION_ETL.RECEIVE_MESSAGE ( P_PAYLOADUUID, 
                                                                        P_PAYLOAD, 
                                                                        P_TRANSTYPE, 
                                                                        P_STATUS, 
                                                                        P_TIMESTAMP, 
                                                                        P_ADAPTERTYPE, 
                                                                        P_PAYLOADSIZE, 
                                                                        P_ERRORMSG );
  IF(RetVal < 0) THEN
    IF(RetVal <> -1006) THEN
        RAISE_APPLICATION_ERROR(-20003,'DUPLICATE CHECK FAILED');
     ELSE
       RAISE_APPLICATION_ERROR(-200001,'PL/SQL BLOCK FAILED');    
    END IF;
  ELSE
    COMMIT; 
  END IF;
END; 

How can i handle RetVal in transformer?

IF(RetVal < 0) THEN
IF(RetVal = -1006) THEN
RAISE_APPLICATION_ERROR(-20003,'DUPLICATE CHECK FAILED');
ELSE
RAISE_APPLICATION_ERROR(-200001,'PL/SQL BLOCK FAILED');
END IF;
ELSE
COMMIT;
END IF;

Can i call this PL/SQL block in STP stage.

[prasson_ibm]

Hi,
I asked my client to change the datatype of my input parameter of Stored Proc from BLOB to CLOB and its working fine now.