Call an Oracle Function by using Stored Procedure stage

[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

Code: Select all

DsInput:   ID 
           PERSON_ID

Can you help me to configure Stored Procedure stage?

[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

[vietnq254]

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.

[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:

Code: Select all

PERSON_ID                      Decimal       3,0

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?

[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.

[chulett]

Functions are not stored procedures. You 'call' them by including them in a SQL select statement.

[vietnq254]

You 'call' them by including them in a SQL select statement.

Can you help me to give the SQL statement?

[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.

[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;

[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;

[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?

[vietnq254]

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.

[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.

[vietnq254]

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.