[o:XML] oXml DB Question

[Michael Wilson]

Sorry to say that my function CODEX.MAIN.ADD_SEARCH_FOLDER performs DML
(adds a record w/ commit) and because of security considerations Oracle
doesn't allow this type of function to be called from a SELECT
statement, it must be called within an PL/SQL block or an error is
thrown.

Thanks for the reply though.  As it stands the code works I just can't
get a return code.  I've worked around the problem before so it's not
that big of a deal.  You just have the function write a transaction
record with a key that you pass into the function as an identifier.  The
client code calls the function with a millisecond/hash key and after the
insert immediately performs a select on the audit table.

Mike

-----Original Message-----
From: Martin Klang [mailto:martin at o-xml.org] 
Sent: Tuesday, February 07, 2006 6:51 AM
To: Michael Wilson
Cc: o-xml at lists.pingdynasty.com
Subject: Re: [o:XML] oXml DB Question

Michael,

I've not tried using cursors, I'll give it a go with MySQL later to see
what results I get.
OUT and INOUT parameters aren't supported currently with the db
extensions.

Functions can be used as part of normal select statements, so if you can
do  > select codex.main.add_search_folder(a, b, c) from dual; then you
should be able to do:

   <db:query name="createSearchFolder" connection="dev1">
     <db:param name="in_show" type="String"/>
     <db:param name="in_folder" type="String"/>
     <db:sql>
        select codex.main.add_search_folder({$in_show},
{$in_folder},NULL) as pk_search, {$in_show} as out_show, {$in_folder} as
out_folder from dual
     </db:sql>
     <db:result>
       <add_folder in_show="{$out_show}" in_folder="{$out_folder}">
{$pk_search}</add_folder>
     </db:result>
   </db:query>

what you can try to do is first figure out what the right Oracle SQL
query is and check the result column names. You can then use the column
names in the db:result template. That should usually work, since
db:result operates on the result set regardless how it was produced
(select query, procedure call or functions).

hth,

/m