Page 1 of 1
Call an Oracle Function by using Stored Procedure stage
Posted: Tue Sep 07, 2010 11:11 pm
by vietnq254
Hello everybody,
I have a function in Oracle like below:
Code: Select all
FUNCTION GET_INFORMATION(
O_error_message OUT VARCHAR2,
O_NAME OUT VARCHAR2,
O_ADDRESS OUT VARCHAR2,
I_PERSON_ID IN PERSONS.PERSON_ID%TYPE )
RETURN BOOLEAN;
I use a Stored Procedure stage to call this function and get names, addresses.
Job flow like below:
Code: Select all
Dataset DsInput => Stored Procedure stage => Sequential File stage
with
Can you help me to configure Stored Procedure stage?
Posted: Wed Sep 08, 2010 12:18 am
by Sreenivasulu
It would be better if you convert the function a SP and use the StoredProcedure stage. I did like that in one the projects since i felt that the function was not working as desired using the SP stage
Regards
Sreeni
Posted: Wed Sep 08, 2010 12:29 am
by vietnq254
Sreenivasulu wrote:It would be better if you convert the function a SP and use the StoredProcedure stage.
Thank you but I don't have permission to modify this function in Oracle.
Posted: Wed Sep 08, 2010 2:23 am
by vietnq254
In Parameters tab, I set:
Code: Select all
O_error_message = ERROR_MESSAGE Output
O_NAME = NAME Output
O_ADDRESS = ADDRESS Output
I_PERSON_ID = PERSON_ID Input
v_Return = v_Return Function
In Input Columns tab:
In Output Columns tab:
Code: Select all
ERROR_MESSAGE VARCHAR
NAME VARCHAR
ADDRESS VARCHAR
v_Return VARCHAR
In Oracle, the type of PERSONS.PERSON_ID is NUMBER(3,0).
So I have warning log below:
Code: Select all
PLS-00382: expression is of wrong type. ORA-06550: line 1, column 7: PL/SQL: Statement ignored
Can anyone help me?
Posted: Wed Sep 08, 2010 3:12 am
by HariK
Assuming the problem is with reutrn type. In function it is Boolean, in DS job it is Varchar. Not sure if Boolean is supported in DS 7 version for Oracle.
Posted: Wed Sep 08, 2010 7:04 am
by chulett
Functions are not stored procedures. You 'call' them by including them in a SQL select statement.
Posted: Wed Sep 08, 2010 8:54 pm
by vietnq254
chulett wrote:You 'call' them by including them in a SQL select statement.
Can you help me to give the SQL statement?
Posted: Wed Sep 08, 2010 8:58 pm
by chulett
The function is simply referenced as if it were a column name. Test it outside of DataStage in a tool like Toad first to ensure you have it working.
Posted: Thu Sep 09, 2010 12:30 am
by vietnq254
How do you think about this script below?
Code: Select all
DECLARE
O_ERROR_MESSAGE VARCHAR2(200);
O_NAME VARCHAR2(200);
O_ADDRESS VARCHAR2(200);
I_PERSON_ID NUMBER;
v_Return BOOLEAN;
BEGIN
I_PERSON_ID:= 304; --Set input value
v_Return:= GET_INFORMATION (O_ERROR_MESSAGE, O_NAME, O_ADDRESS,I_PERSON_ID);
execute immediate 'CREATE GLOBAL TEMPORARY TABLE INFORMATION( NAME VARCHAR2(200),
ADDRESS VARCHAR2(200)) ON COMMIT DELETE ROWS';
execute immediate 'insert into INFORMATION values (O_NAME,O_ADDRESS)';
END;
select * from GET_INFORMATION;
drop table GET_INFORMATION;
Posted: Thu Sep 09, 2010 6:40 am
by chulett
That's PL/SQL, not a script... and I have no idea what this has to do with your original question.
Is 'GET_INFORMATION' your stored function? If so, as noted, you would include it in a normal SQL select as if it were a column in a table. If you don't have a need for a table, use DUAL. For example:
SELECT GET_INFORMATION(X,Y,Z) FROM DUAL;
Posted: Thu Sep 09, 2010 9:02 pm
by vietnq254
Thank you, but it can be performed because of 2 reasons:
+ PERSON_ID isn't a fixed value, it is the output of dataset file
+ Function doesn't return columns values, it only return boolean value
With procedure, it is easy to call it in Datastage. Otherwise with function, you can also call it when it doesn't return a boolean value (can be replaced by VARCHAR, NUMBER..)
Does anyone has a clear solution?
Posted: Thu Sep 09, 2010 9:06 pm
by vietnq254
HariK wrote:Assuming the problem is with reutrn type. In function it is Boolean, in DS job it is Varchar.
Yes it is. So if function doesn't return a Boolean value, I can use Stored Procedure stage to call it like above.
Posted: Thu Sep 09, 2010 10:04 pm
by chulett
If you insist on treating it like a stored procedure, why not make it a stored procedure? Otherwise, study up on how to properly leverage functions, there are lots of examples out there -
here as one example.
Posted: Fri Sep 10, 2010 12:28 am
by vietnq254
chulett wrote:If you insist on treating it like a stored procedure, why not make it a stored procedure? Otherwise, study up on how to properly leverage functions, there are lots of examples out there -
here as one example.
Thank you.
It is a function in Oracle sever that I only can view. I tried get SELECT from this function but I was fail. Of course, because it return a unrelated value (True or False).
In my opinion, it is impossible to resolve this problem.