Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

Perl, Oracle, and Xquery

by jozxyqk (Initiate)
on Mar 07, 2007 at 18:15 UTC ( [id://603661]=perlquestion: print w/replies, xml ) Need Help??

jozxyqk has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks,

I am working with an Oracle database and accessing it through Xquery. So far, I have been doing this just through the SQLPlus tool. But now I need to programmatically access it, and I don't want to have to resort to Java.

In SQLPlus, I simply have to enter the line "xquery", and then insert my xquery script below that.

But when I try to use Perl DBI to "prepare" such a statement, I get an "Invalid SQL" error.

How can I access an Oracle database using Xquery in Perl?


Replies are listed 'Best First'.
Re: Perl, Oracle, and Xquery
by Moron (Curate) on Mar 07, 2007 at 18:37 UTC
    Maybe I am misunderstanding this, but are you trying to prepare more than one SQL statement in one go? You have to split it up and get rid of any delimiters (statement-terminating semicolons?) that might be in some way special to Xquery as opposed to obeying the pure SQL syntax.


    Free your mind

      Even if I just prepare the statement "xquery" on a line alone (which, at least in the SQLPlus program, is an indicator that xquery follows), I get the "no such SQL command" error.

      DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute) [for Statement "xquery"] at line 62.

        "XQUERY" is a command to set a mode in the SQL interpreter SQL*Plus. So it is not send to the backend database engine.
        Once you have set XQUERY mode then the following statement ('.....') will be embedded in a SELECT XQUERY statement by SQL*Plus before it is send to the database engine.

        So if you would want to do something similar from within Perl you would need to enter in your prepare method a text like this:

        NB: xquery SQL extensions are not supported by all databases
        It ain't lying. xquery is not an SQL command. As I already explained, you have to extract each PURE SQL statement from your XQuery script before submitting them individually via DBI.


        Free your mind

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://603661]
Approved by Paladin
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others chanting in the Monastery: (7)
As of 2024-06-18 13:39 GMT
Find Nodes?
    Voting Booth?

    No recent polls found

    erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.