using sql function in xml sql statement

[Former Member]

Hello,

I have following statement:

select field1, field2,....,field10 from table( cast (get_mhdata(:1,:2) as mhdata))

where mhdata is an oracle type with 10 fields.

It works good, but I don't know how can I use this statement in xml sql statement.

Please, can you tell me if it is really possible.

Thanks in regards.

[Former Member]

Hi

You cant pass the parameters if you are executing writing this select function from the communication channel.

You have 3 options:

1) Use a stored procedure and execute from the communication channel

2) Use a stored procedure and execute via message mapping in the IR - here you can pass parameters

3) Same as above but call the query via SQL select - you can also pass parameters.

sincerely,

--NM

[Former Member]

cannot anyone help me with this problem?

[ranjit_deshmukh]

Hi

I think in order to pass parameters you need to write a stored procedure

to which you can call and can pass arguments.

Ranjit

[Former Member]

repost

[Former Member]

repost

[justin_santhanam]

Farit,

Do u want to use this in Sender comm.channel. Then no need to change anything juz give the same SQL in the comm. channel, If I understood wrong, reply back.

Best regards,

raj.

[Former Member]

thanks Raj for quick reply,

yes, i'd like to use this in sender comm.channel, but how can I pass parameters to sql function using that?

[justin_santhanam]

Farit,

Give the same in the SELECT Statement in comm.channel and give a try,I think surely it will work.

Best regards,

raj.

[Former Member]

Raj,

could you please give me an example of how to pass parameters inside function when it is used in 'from' clause..