Using PI - Calling a Function in Oracle

[Former Member]

Hi,

I understand that the receiver JDBC adapter is capable of calling a stored procedure or a function in Oracle. I would like to call an Oracle function using the Receiver JDBC Adapter. How should my structure look like.

Thanks.

Krishnan

[Former Member · Accepted solution]

Hi,

From PI, How do I call a function in oracle. I am sure, it's not the same how we call a Stored Procedure. I am getting an error as stated above.

Appreciate your help.

Regards.

Krishnan

[GabrielSagaya · Accepted solution]

http://www.psoug.org/reference/functions.html

http://www.techonthenet.com/oracle/functions.php

http://www.dba-oracle.com/t_functions.htm

[Former Member · Accepted solution]

Hi Krishnan,

Oracle function are executed like a normal Select query, if we can use to_date function you should be able to invioke user define function as well. Try creating the sql structure and it should work.

Also send me the function name with full query that you want to execute. You cannot use Execute with function, you can use it only for executing stored procedure.

thanks

amit

[Former Member · Accepted solution]

hi amit,

The name of my function in oracle is validate_serialNo and the table name is serialTab.

my function has two input parameters and one output parameter. The two input parameters are the 1) serialNo and 2) date.

please let me know how should my target structure look like.

Thanks.

Krishnan

[Former Member · Accepted solution]

Hi,

I just want to know what is the exact sql query you are trying to execute in database. Is it insert, update, delete or select. Oracle function always return a value, how and what you want to do with that value, Can you tell me your complete scenario so that I can help you out.

If you want to get some value form that function using select statement why you are going for receiver jdbc channel, you can go with sender jdbc channel as well.

thanks

amit

[Former Member · Accepted solution]

Hi Amit,

the oracle function is executed on the target system. It's not a select query nor an insert or an update.

I tried using SQL_DML and SQL_QUERY in the 'action' and sent this piece of code within the target structure:-

execute declare a number; begin a:=check_serialNo(TO_DATE('01-01-2008 07:55:00','MM-DD-YYYY HH24:MI:SS'), 45987); END;

I get an error which states:-

'com.sap.engine.interfaces.messaging.api.exception.MessagingException: No response available.'

But the same piece of code when executed in oracle sql-query editor works fine.

Do you have any idea on this....

Thanks.

Krishnan

[Former Member · Accepted solution]

Did you find the solution to your problem of using BEGIN , I am trying to do the same ?

[Former Member]

either you can create a stored proc. and call the function in it.

rest is well known to you

[Former Member]

Hi,

Try creating the structure same way as you create for Select query. Not sure it will work or not. You can try giving the function name as described in the thread link provided below. Here predefine Oracle function (to_date) is called, similarly you should be able to call your function as well.

The other solution is to call a Stored Procedure which inturn will call your function.

thanks

amit

reward point if solution is useful

[Former Member]

Hi Amit,

You cannot create a structure similar to the SELECT because for a SELECT structure you need to specify the SQLDataType attribute whereas for a StoredProcedure or a Function you need a different structure.

Any inputs on how to overcome the error message.

Thanks.

Krishnan

[Former Member]

Hi,

Can you please post your sql query which you want to execute in db with function name.

thanks

amit

[Former Member]

Hi,

Does any one have idea of how to invoke or execute an oracle function using JDBC Receiver Adapter.

Thanks.

Krishnan

[Former Member]

Hi,

It may helps you,

http://help.sap.com/saphelp_nw2004s/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm

Thanks,

Boopathi

[Former Member]

Hi,

I have created a structure like:-

<STATEMENT>

<FUNCTION action="EXECUTE">

<FunctionName>myFunction</FunctionName>

<SerialNo type="NUMERIC">1000001</record_count>

<SerialDate hasQuot="NO" type="STRING">TO_DATE(&apos;07-10-2008 04:59:43&apos;,&apos;MM-DD-YYYY HH24:MI:SS&apos;)</SerialDate>

</FUNCTION>

</STATEMENT>

I am getting the following error in the JDBC receiver adapter:-

-

---

com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'myFunction' (structure 'STATEMENT'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00221: 'myFunction' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored

-

---

Thanks.

Krishnan