stored procedure error
Moderators: chulett, rschirm, roy
stored procedure error
Hi,
I have a scenario where I need to call an transform stored procedure(oracle) with In and Out parameters.These parameters are CLOB type. But Datastage 8.7 has the longvarchar equivalent to CLOB. If I use lonavarchar it gives an error datatype not supported.. If I use varchar It gives me value/numeric error in the value..Can any one please give me a direction if DataStage can handle this scenario.
Thanks
I have a scenario where I need to call an transform stored procedure(oracle) with In and Out parameters.These parameters are CLOB type. But Datastage 8.7 has the longvarchar equivalent to CLOB. If I use lonavarchar it gives an error datatype not supported.. If I use varchar It gives me value/numeric error in the value..Can any one please give me a direction if DataStage can handle this scenario.
Thanks
-
- Participant
- Posts: 117
- Joined: Wed Feb 06, 2013 9:24 am
- Location: Chennai,TN, India
CLOB can hold up tp 4gb but can you judge what will be the length of input column, Varchar should hold good. Check whether the error is during call of procedure or within procedure
Try with Unknown datatype.
Try with Unknown datatype.
Last edited by prasannakumarkk on Wed Mar 20, 2013 9:04 pm, edited 1 time in total.
Thanks,
Prasanna
Prasanna
I get the following errosr fr varchar in datastage
Stored_Procedure_0,0: Fatal Error: Fatal: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "Store_proc",
If I use unknown datatype:
Stored_Procedure_0,0: Error: Output link column definitions failed validation. Refer to previous messages for details.
DSTAGE-STP-0133`:`The SQL type (VarChar) of input link column Request must match the SQL type (Char) of output link column Request.
Store_proc is already defined with IN and OUT parameters as CLOB. I cannot change the store proc as its the existing one..
Since there is a priviege to change the datae type for IN and OUT.. I tried with varchar
if run the store proc directly with varchar in and out.. it gives the error
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Stored_Procedure_0,0: Fatal Error: Fatal: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "Store_proc",
If I use unknown datatype:
Stored_Procedure_0,0: Error: Output link column definitions failed validation. Refer to previous messages for details.
DSTAGE-STP-0133`:`The SQL type (VarChar) of input link column Request must match the SQL type (Char) of output link column Request.
Store_proc is already defined with IN and OUT parameters as CLOB. I cannot change the store proc as its the existing one..
Since there is a priviege to change the datae type for IN and OUT.. I tried with varchar
if run the store proc directly with varchar in and out.. it gives the error
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Last edited by India2000 on Sat Mar 23, 2013 8:18 pm, edited 2 times in total.
-
- Participant
- Posts: 117
- Joined: Wed Feb 06, 2013 9:24 am
- Location: Chennai,TN, India
-
- Participant
- Posts: 117
- Joined: Wed Feb 06, 2013 9:24 am
- Location: Chennai,TN, India
-
- Participant
- Posts: 117
- Joined: Wed Feb 06, 2013 9:24 am
- Location: Chennai,TN, India
-
- Participant
- Posts: 117
- Joined: Wed Feb 06, 2013 9:24 am
- Location: Chennai,TN, India
-
- Participant
- Posts: 117
- Joined: Wed Feb 06, 2013 9:24 am
- Location: Chennai,TN, India
-
- Participant
- Posts: 117
- Joined: Wed Feb 06, 2013 9:24 am
- Location: Chennai,TN, India