Functions

[Former Member]

I want to call a function on an Oracle Database using the JDBC Receiver adapter.

Could anyone tell me if this is possible and if so then how and what sort of structure is required.

I have found alot of info on Stored Procedures and Standard calls to a database but nothing on how to call a function... any help would be greatly appreciated!!!!!

[Former Member]

Is it possible for clarification on this... how do I include inout and output parameters?

What should the structure then look like?

Has anyone actually successfully implemented a scenario where you call an Oracle function with input and/or output parameters using the receiver JDBC adapter?

Message was edited by:

Alex

[Former Member]

Does anyone have any experience calling Database functions from XI?

[Former Member]

Hi Alex,

We have already used Oracle functions from XI. It is almost equal using procedures... The fundamental difference is on the types you are using.

<Statement>
  <functionName action=” EXECUTE”>
    <table>FunctionName</table>
    <param1 [isInput=”true”] [isOutput=true] type=SQLDatatype>value</param1>
  </functionName >  
</Statement>

If you want, you can try using Native Oracle SQL. The sql instruction can be like this

SELECT YOUR_FUNCTION(PARAM1, PARAM2) FROM DUAL

It depends what you are trying to do with the function.

hope this helps.

regards!

roberti

[Former Member]

Hi maybe you could elaborate on this....

we have already tried something that looks nearly identicle to what you have... please see below.

<?xml version="1.0" encoding="UTF-8" ?> 
<ns0:MT_F_ZMETADATA_DELETE xmlns:ns0="http://qimonda.com/CMD/MetaData">
 <Statement>
  <f_delete_sca action="execute">
  <table>f_delete_sca</table> 
  <p_return type="Integer" isOutput="true" /> 
  <p_retmsg type="Varchar" isOutput="true" /> 
  </f_delete_sca>
 </Statement>
</ns0:MT_F_ZMETADATA_DELETE>

Could you please tell me what the problem is with the above structure?

We are also in the middle of trying the SQL instruction and it is also not successful

[Former Member]

Roberti the problem with the SQL instruction is that there is no way of using Out parameters... or am I mistaken?

[Former Member]

Could you please clarify this please?

<b>The fundamental difference is on the types you are using.</b>

and this

<param1 [isInput=”true”] [isOutput=true] type=SQLDatatype>value</param1>

[Former Member]

Sorry Alex,

We started using Oracle Function in this structure, but now we are using only Procedures by this way (don't know why). Functions are been used here when we have only one output, using a select as I posted before.

But, it seems you need to proceed DML statements (delete) in your function, am I right? By this way the "select function from dual" will not work because you can't perform DML into queries...

Why can't you use a procedure for this action? I think it would be the faster way of solving this issue... Tell me if you have a requirement for not using procedure and we can try working around it.

regards.

roberti

[Former Member]

Hello Roberti,

yes, currently we have the requirement to use the given functions as switching to stored procedures would cause lots of effort and would delay the overall implementation.

So we have to think about a workaround. You are right, using the mentioned function there is a delete statement inside and two parameters are returned indicating success or failure.

Do you have any idea how to solve this?

Thanks a lot!

[Former Member]

Ok,

well, as I can see, the only way of executing Oracle Functions is by queries, as we discussed before, and will not work in this case.

you can create a Procedure as a "wrapper" to the function, then you call the procedure from XI and procedure calls the function. I think it will work fine. Have you already tried it?

regards.

roberti

[Former Member]

Hey

yes,its possible to call functions as well from receiver JDBC.please have a look at the following thread

Thanx

Aamir