2 Replies Latest reply: Feb 26, 2013 7:25 AM by 992851 RSS

    XML select issue strangeness - pro*c

    992851
      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
          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
            Thank you! This worked like a charm.

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