pass value to oracle stored proc with blob datatype
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
pass value to oracle stored proc with blob datatype
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.
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.
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
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.
Can you clarify that for us, please? I'd also be curious what kind of 'failure' you are seeing.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
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.
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
If it is CLOB what datatype i need to define in datastage to pass it through SP..?
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
Can't i convert the string input data into HEX( using basic transformer stage) and pass as binary large object to SP?
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
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.
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.
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
Hi,
I am combining first 10 records in transformer.Below is the logic:-
1. running below command in sequential file stage:-
Created sequence number column in sequential file stage.
2. Created stage variable and merged all 10 records in one column.
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.
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>.*
2. Created stage variable and merged all 10 records in one column.
Code: Select all
Svar=Svar:Inputcolumn.Payload:char(10)char(13)
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.
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
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
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..??
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.
Is there any way i can handle error_code after Oracle connector stage..??
Last edited by prasson_ibm on Mon Aug 05, 2013 2:31 pm, edited 1 time in total.
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
Below is my PL/SQL block
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.
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;
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.
Last edited by prasson_ibm on Wed Aug 07, 2013 1:37 pm, edited 1 time in total.
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
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.
I asked my client to change the datatype of my input parameter of Stored Proc from BLOB to CLOB and its working fine now.
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/