This discussion is archived
2 Replies Latest reply: Feb 26, 2013 5:25 AM by 992851 RSS

XML select issue strangeness - pro*c

992851 Newbie
Currently Being Moderated
I've had a pro*c problem that I can't figure out. Hopefully, one of you might be able to do so. I am trying to create a XML output from a select statement. I having several problems, but the first one is that I can't get a simple XMLElement to work.

Depending on how I do it, it either does not pre-compile or compiles - but does not work. Both queries work correctly in SQL*Plus.

Pro*c Code >>>>>>>>>>>>
EXEC SQL BEGIN DECLARE SECTION ;
OCIClobLocator *xmltype_lob;
EXEC SQL END DECLARE SECTION ;
EXEC SQL ALLOCATE :xmltype_lob;
EXEC SQL LOB CREATE TEMPORARY :xmltype_lob;

EXEC SQL
SELECT XMLElement("eligVerification", dummy ).getClobVal() <--line 125
INTO :xmltype_lob
FROM dual;

if ( sqlca.sqlcode != 0 )
{
fprintf (stderr, "Failed to get XML sqlca.sqlcode = %d\n" , sqlca.sqlcode ) ;
fflush(stderr);
exit ( EXIT_FAILURE ) ;
}
<<<<<<<<<<<<

This fails on the pre-compile:

Syntax error at line 125, column 55, file tester.sc:
Error at line 125, column 55 in file tester.sc
XMLElement("eligVerification", dummy ).getClobVal()
......................................................1
PCC-S-02201, Encountered the symbol "." when expecting one of the following:

, * + - / | an identifier, a quoted string, at, date, into,
as, count, from, day, extract, hour, interval, minute, month,
second, time, timestamp, treat, trim, year, avg, max, min,
sum, stddev, variance,

======================

If I remove the .getClobVal(), the SELECT now looks like:

Changed >>>>>>
EXEC SQL
SELECT XMLElement("eligVerification", dummy )
INTO :xmltype_lob
FROM dual;
<<<<<<<<<<<<

This does get thru the pre-compile and compiles just fine. At run time, I get this:
Failed to get XML sqlca.sqlcode = -932
From oerr, that is: 00932, 00000, "inconsistent datatypes: expected %s got %s"

Any thoughts?

We are using:
SunOS 5.10 Generic_147440-27 sun4u sparc SUNW,SPARC-Enterprise
Sun C 5.12 SunOS_sparc 2011/11/16
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Pro*C/C++: Release 11.2.0.3.0
  • 1. Re: XML select issue strangeness - pro*c
    943610 Newbie
    Currently Being Moderated
    You need the precompiler option: common_parser=yes
    (it was added in version 11)

    I don't know how to work with OCIClobLocator, but I was able to select some XML into a string:
    EXEC SQL BEGIN DECLARE SECTION ;
    OCIClobLocator *xmltype_lob;
    char s[500+1];
    EXEC SQL VAR s is STRING;
    EXEC SQL END DECLARE SECTION ;
    
    EXEC SQL ALLOCATE :xmltype_lob;
    EXEC SQL LOB CREATE TEMPORARY :xmltype_lob;
    
    EXEC SQL
    SELECT
         /*
         NULL
         */
         XMLElement("eligVerification", dummy ).getClobVal()
    INTO
         :s
         /*
         :xmltype_lob
         */
    FROM dual;
    Edited by: 940607 on Feb 25, 2013 10:57 AM
  • 2. Re: XML select issue strangeness - pro*c
    992851 Newbie
    Currently Being Moderated
    Thank you! This worked like a charm.

    Onto the other errors as soon as I have time :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points