Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> nclob and nvarchar2 oddity with Pro*C

nclob and nvarchar2 oddity with Pro*C

From: clach04 <clach04_at_gmail.com>
Date: 4 Oct 2006 18:00:16 -0700
Message-ID: <1160010016.011894.154370@k70g2000cwa.googlegroups.com> 





Forgive the long posting, but I think the detail requires it.



Short summary though:






I'm using the default AL16UTF16 encoding and this is with 10g (I've
even tested 9i).



Using:



    OCIClobLocator CHARACTER SET IS NCHAR_CS *a_nclob;
    char    CHARACTER SET IS NCHAR_CS  nclobbuf[2000];



Does not work as documented, you can't use utext[] with the Clob
locator. But utext and "CHARACTER SET IS NCHAR_CS" (provided "export
NLS_NCHAR=AL16UTF16" is issued) does work for nvarchar2.




Background/longer version:



I'm using ProC (in an existing application so I'm stuck with ProC) and
I'm attempting to access nclob column(s). I'm able to access nvarchar2
columns successfully (but I have seen some problems there too). I've
put together a simple test case (our application is way to big to use
as a demo for the problem). The test case is a seriously hacked up
small version of the Oracle provided ansi dynamic SQL example program
1. It is using the "ANSI" ProC switch as you can't determine if a
column is Unicode with dynamic SQL with the older Oracle style, the
older Oracle approach just doesn't have indicators for that.



>From various docs around the place it appears that you have 2 options
for dealing with Unicode data (irrespective of whether the column is
nvarchar or a nclob) as UTF16 (I'm ignoring utf8):



    
  
  1.   use utext - which gives you direct access to the code points
  
  2.   use char (or long_varchar, etc.) with "CHARACTER SET IS NCHAR_CS"
(or the proc command line argument "nls_char=variable_name") which
gives you the code points broken across 2 bytes. Using "CHARACTER SET
IS NCHAR_CS" has a documented restriction that you must set the
Operating System variable NLS_NCHAR set to the character set you need
(typically 'AL16UTF16').






My experience has been that you can NOT use utext with nclobs (proc
errors out), the documents I've found say to use the regular CLOB
locator but to use the "CHARACTER SET IS NCHAR_CS" declaration and the
os variable NLS_NCHAR.



I've tried using nclob with clob locators (and nchar....) in both
static and dynamic SQL and I'm getting back single byte characters, for
real Unicode data that can not be coerced into ascii I get back '?'
characters.



I do have nvarchar2 columns working in both static and dynamic SQL so I
don't think I'm doing anything wrong.



    Side note; I have seen a difference in behavior between dynamic and
static SQL with regard to both utext and char (as nchar):



    
  
  1.   if you use utext; in dynamic SQL the code point comes back
byte swapped if using SQL*Net from a Linux box, in static SQL it is not
byte swapped!
  
  2.   if you use char with the "CHARACTER SET IS NCHAR_CS"; you do
need the os variable NLS_NCHAR set to "AL16UTF16" (if it is not set you
get back single bytes and characters that can not be represented in
ASCII come back as '?'), however with dynamic sql NLS_NCHAR has no
relevance.







My current fear is that these are bugs in Oracle ProC (and SQL*Net for
the byte swap problem) and that no one has seen them before as everyone
uses api's based on OCI; either oci directly or wrappers like jdbc,
odbc, perl-dbi, cx_oracle_dbi, etc.



I've attached inline the ProC code (which is pretty messy, as I wanted
to get a standalone test case up and running quickly - please forgive
the mess), a setup script to create and populate a table for the test,
a simple make file (again, messy as we use internal scripts for
building that are not suitable for small test cases).



The test creates table with an nvarchar2 and an nclob column, they only
contain a single character which is the Chinese symbol/glyph for liquor
(read in to that what you will :-) ) I deliberately choose a code point
that can not be represented in ASCII. See the setup.sh for more
information.



The nclob problem is what I need help with, the nvarchar2
inconsistencies are not a big deal but I'd appreciate any
ideas/feedback.



Help!



Chris








### <-------------- Cut here: setup.nclob_demo.pc START -------------->

/*


vim:set filetype=c:



NOTE this test does NOT create tables or import data.



Schema and data is populated via setup.sh script.
*/





#include <stdio.h>

#include <string.h>

#include <setjmp.h>

#include <stdlib.h>

#include <sqlcpr.h>

#include <oci.h>




#include <oratypes.h> /* for utext */





#define MAX_OCCURENCES 40




#ifndef NULL

#define NULL  0

#endif





/* Prototypes */


#if defined(__STDC__)



  void sql_error(void);


#else



  void sql_error(/*_ void _*/);


#endif




EXEC SQL INCLUDE sqlca;




/* global variables */


EXEC SQL BEGIN DECLARE SECTION;



 char    dyn_statement[1024];


 char    SQLSTATE[6];


 OCIClobLocator CHARACTER SET IS NCHAR_CS *a_nclob;
 char    CHARACTER SET IS NCHAR_CS  nclobbuf[2000];
 char encoding_name[50];


 char simple_str[50];


 utext ncharbuf[2000];


 char    CHARACTER SET IS NCHAR_CS   fake_ncharbuf[2000];


#define NAME_LENGTH 30



 char    user[NAME_LENGTH], pwd[NAME_LENGTH], db[NAME_LENGTH];
 int     amount, loblen, lob_offset, buflen, length;
EXEC SQL END DECLARE SECTION;



 char    outbuf[128];


 int     i;




/* Define a buffer to hold longjmp state info. */
jmp_buf jmp_continue;



/* A global flag for the error routine. */
int parse_flag = 0;


/* A global flag to indicate statement is a select */
int select_found;



/* test query using static SQL */


static_sql_test()


{


/* local variables */


EXEC SQL BEGIN DECLARE SECTION;



 OCIClobLocator CHARACTER SET IS NCHAR_CS *a_nclob;
 char    CHARACTER SET IS NCHAR_CS  nclobbuf[2000];
 char encoding_name[50];


 char simple_str[50];


 utext ncharbuf[2000];


 char    CHARACTER SET IS NCHAR_CS   fake_ncharbuf[2000];
 int     amount, loblen, lob_offset, buflen, length;
EXEC SQL END DECLARE SECTION;



    printf("\n\nstatic select test\n\n");



/*


"SELECT col1, col2, col3 FROM clach04_nclob");
*/




    printf("select col3 into a 'real' unicode variable and a 'fake'
unicode variable\n");


    EXEC SQL SELECT col1, col3, col3 into :simple_str, :ncharbuf,
:fake_ncharbuf from clach04_nclob;


    printf("1st column - should just work >%s<\n", simple_str);


    printf("dump out first two items of utext array\n");
    printf("col3[0] =%x\n", (int) ncharbuf[0]);
    printf("col3[1] =%x\n", (int) ncharbuf[1]);

    printf("dump out first two items of char array\n");
    printf("col3[0] =%x\n", (int) fake_ncharbuf[0]);


    printf("col3[1] =%x\n", (int) fake_ncharbuf[1]);
    printf("NOTE! this is a static SQL test and the OS environment
variable: NLS_NCHAR impacts behavior!\n");


    printf("      test with both NLS_NCHAR not set and set to
'AL16UTF16'\n");


    printf(" for this to work MUST: setenv NLS_NCHAR AL16UTF16 \n");




    printf("select nclob into a 'fake' unicode variable\n");
    printf("can NOT select into utext, etc. not supported in ProC\n");



    EXEC SQL ALLOCATE :a_nclob;


    EXEC SQL SELECT col2 into :a_nclob from clach04_nclob;
    EXEC SQL LOB DESCRIBE :a_nclob GET LENGTH INTO :loblen;



    /* read all of the lob in */


    amount = loblen;


/*


   EXEC SQL LOB READ :amount FROM :a_nclob AT :lob_offset


        INTO :nclobbuf WITH LENGTH :buflen;
*/


    EXEC SQL LOB READ :amount FROM :a_nclob INTO :nclobbuf  WITH LENGTH
:loblen;

    printf("\ndump out first two items of char array\n");
    printf("nclob[0] =%x\n", (int) nclobbuf[0]);
    printf("nclob[1] =%x\n", (int) nclobbuf[1]);






    printf ("read %d bytes\n", loblen);



}



int main(int argc, char * argv[])


{


    if (argc != 3+1)


    {

        printf("sorry this tool is hard coded to take in:\n");
        printf("    USERNAME PASSWORD TNSNAME\n");
        exit(-1);




    }

    strcpy(user, argv[1]);
    strcpy(pwd, argv[2]);
    strcpy(db, argv[3]);





    EXEC SQL WHENEVER SQLERROR DO sql_error();



    /* Connect to the database. */


    EXEC SQL CONNECT :user IDENTIFIED BY :pwd


         USING :db;



/*


 These have no effect on behavior:



    printf("set fail on nchar conv error\n");
    EXEC SQL ALTER SESSION SET NLS_NCHAR_CONV_EXCP = 'TRUE';

    EXEC SQL ALTER SESSION SET NLS_NCHAR_CONV_EXCP = TRUE;

    printf("ALTER SESSION completed\n");
*/



    /* Allocate the input and output descriptors. */
    EXEC SQL ALLOCATE DESCRIPTOR LOCAL 'execute_desc';



    /* Process SQL statements. */


    (void) setjmp(jmp_continue);



    /* Get the statement.  Break on "exit". */
    sprintf(dyn_statement, "SELECT col1, col2, col3 FROM
clach04_nclob");



    /* Prepare the statement and declare a cursor. */
    parse_flag = 1;     /* Set a flag for sql_error(). */

    printf("SQL Query:\n%s\n", dyn_statement);
    printf("----------------\n");
    printf(" prepare\n");
    EXEC SQL PREPARE sql_stmt FROM :dyn_statement;
    parse_flag = 0;     /* Unset the flag. */


    printf(" describe\n");



    EXEC SQL DESCRIBE OUTPUT sql_stmt USING SQL DESCRIPTOR


        LOCAL 'execute_desc';



    EXEC SQL GET DESCRIPTOR LOCAL 'execute_desc' VALUE 1 :encoding_name
= CHARACTER_SET_NAME;



    printf ("1st column (varchar) is encoded in=%s\n", encoding_name);



    EXEC SQL GET DESCRIPTOR LOCAL 'execute_desc' VALUE 2 :encoding_name
= CHARACTER_SET_NAME;



    printf ("2nd column (nclob) is encoded in=%s\n", encoding_name);



    EXEC SQL GET DESCRIPTOR LOCAL 'execute_desc' VALUE 3 :encoding_name
= CHARACTER_SET_NAME;



    printf ("3rd column (nvarchar2) is encoded in=%s\n",
encoding_name);



    EXEC SQL ALLOCATE :a_nclob;


    length = sizeof(OCIClobLocator *);


    EXEC SQL SET DESCRIPTOR LOCAL 'execute_desc'


        VALUE 2  DATA = :a_nclob ;



    printf(" declare\n");


    EXEC SQL DECLARE my_cursor CURSOR FOR sql_stmt;



    /* Open the cursor and execute the statement. */
    printf(" open\n");


    EXEC SQL OPEN my_cursor;



    printf(" fetch\n");


    EXEC SQL FETCH my_cursor INTO DESCRIPTOR LOCAL 'execute_desc';



    EXEC SQL GET DESCRIPTOR LOCAL 'execute_desc' VALUE 1 :simple_str =
DATA;



    printf("1st column - should just work >%s<\n", simple_str);



    printf("\nIn this example using simple basic Unicode code
points,\ni.e. no combining characters\n");


    printf("\nfetch nvarchar2 into utext array (i.e. each element is a
codepoint)\n");


    EXEC SQL GET DESCRIPTOR LOCAL 'execute_desc' VALUE 3 :ncharbuf =
DATA;


    printf("dump out first two items of utext array\n");
    printf("col3[0] =%x\n", (int) ncharbuf[0]);
    printf("col3[1] =%x\n", (int) ncharbuf[1]);





    printf("\nfetch nvarchar2 into char array (i.e. each element is
half a codepoint)\n");


    EXEC SQL GET DESCRIPTOR LOCAL 'execute_desc' VALUE 3 :fake_ncharbuf
= DATA;


    printf("dump out first two items of char array\n");
    printf("col3[0] =%x\n", (int) fake_ncharbuf[0]);
    printf("col3[1] =%x\n", (int) fake_ncharbuf[1]);
    printf("NOTE! if using dynamic (ansi) SQL, the OS environment


variable: NLS_NCHAR appears to NOT be relevant!\n");


    printf("this is not the case for static sql. Docs do NOT mention
this.\n");



    printf("\n");




    /*
     ** now the second item in the descriptor is an nclob locator
     ** use it to nclob data.  With test data we use in setup.sh
     ** we expect this to be encoded in 2 bytes as UCS2
     ** (as UTF16 is used and this is a base char/glyph)
     ** value of hex 9152.
     */




    printf(" describe NCLOB\n");




    EXEC SQL GET DESCRIPTOR LOCAL 'execute_desc' VALUE 2 :encoding_name
= CHARACTER_SET_NAME;



    printf ("2nd column (nclob) is still encoded in=%s\n",
encoding_name);



    EXEC SQL LOB DESCRIBE :a_nclob GET LENGTH INTO :loblen;



    printf("described loblen = %d\n", loblen);
    lob_offset = 1;


    loblen *= 2;


    amount = buflen = loblen;


    amount=2; /* various attempts to set to byte and "character" length
*/


    buflen = sizeof(nclobbuf); /* various attempts to set to byte and
"character" length */


    printf(" Read NCLOB\n");


    EXEC SQL LOB READ :amount FROM :a_nclob AT :lob_offset


        INTO :nclobbuf WITH LENGTH :buflen;
    /* you can not use utext as destination buffer for NCLOB - get
errors */



    EXEC SQL GET DESCRIPTOR LOCAL 'execute_desc' VALUE 2 :encoding_name
= CHARACTER_SET_NAME;



    printf ("encoding_name=%s\n", encoding_name);



    sprintf(outbuf, "a_nclob data = ");


    for (i = 0; i < loblen +2; i++ )


    {

        char hex_data[8];
        sprintf( hex_data, "%02x ", nclobbuf[i] );
        strcat( outbuf, hex_data);




    }


    strcat( outbuf, "\n");


    printf( outbuf );


    /* Close the cursor. */


    printf(" close\n");



    EXEC SQL CLOSE my_cursor;





    /* Deallocate the descriptors */


    EXEC SQL DEALLOCATE DESCRIPTOR LOCAL 'execute_desc';



    static_sql_test();



    EXEC SQL WHENEVER SQLERROR CONTINUE;

    EXEC SQL COMMIT WORK;


    EXEC SQL WHENEVER SQLERROR DO sql_error();
    return 0;


}





void sql_error()


{


    /* ORACLE error handler */


    if ((int) SQLSTATE[0] != (int) NULL )


        printf("\n\nANSI sqlstate: %s", SQLSTATE);
    printf ("\n\n%.70s\n",sqlca.sqlerrm.sqlerrmc);



    if (parse_flag)


        printf("Parse error at character offset %d in SQL
statement.\n",


           sqlca.sqlerrd[4]);



    EXEC SQL WHENEVER SQLERROR CONTINUE;

    EXEC SQL ROLLBACK WORK;


    exit(-1);


/*


    longjmp(jmp_continue, 1);


*/


}



### <-------------- Cut here: setup.nclob_demo.pc END -------------->




### <-------------- Cut here: setup.sh START -------------->

#!/bin/sh

# Setup up demo table and data

# in this example using Chinese character for liquor

# i.e. code point U+9152

#  - see http://www.unicode.org/charts/PDF/U4E00.pdf (page 69)

#




usage()


{

    echo ""
    echo "Usage:"
    echo "  $0 username password tnsname"
    echo ""
    echo "E.g.:"
    echo ""
    echo " $0 fred secret mytnsname"
    echo ""




}



if [ $# != 3 ]


then


    usage


    exit 10


fi


u_name=$1
u_pwd=$2
u_dbname=$3





make nclob_demo



# assumes remote tns



sqlplus ${u_name}/${u_pwd}@${u_dbname} <<EOF
drop table clach04_nclob;


create table clach04_nclob(


    col1 varchar(10),


    col2 nclob,


    col3 nvarchar2(10)


);



insert into clach04_nclob(col1, col2, col3)
values ('liquor', unistr('\9152'), unistr('\9152'));



commit;



select col1, rawtohex(to_nchar(col2)), rawtohex(to_nchar(col3)) from
clach04_nclob ;



EOF



echo ""
echo "now build and run nclob_demo with arguments, e.g.:"
echo ""
echo "  ./nclob_demo ${u_name} ${u_pwd} ${u_dbname}"
echo ""




### <-------------- Cut here: setup.sh END -------------->




### <-------------- Cut here: Makefile START -------------->




# Make file for a SINGLE ProC (*.pc) source files

# Save as "Makefile" (or modify "$(MAKE) -f ..." line

# to use issue:

#	make exe_name

# E.g:

#	make nclob_demo

#

# vim: set noexpandtab:

#




include $(ORACLE_HOME)/precomp/lib/env_precomp.mk



CC=cc



build: $(OBJS)


        $(DEMO_PROC_BUILD_SHARED)


MYFILE=nclob_demo


PROCFLAGS=DYNAMIC=ANSI type_code=ANSI UTF16_CHARSET=NCHAR_CHARSET
sqlcheck=syntax parse=none


MYORACINC=-I$(ORACLE_HOME)/precomp/public -I$(ORACLE_HOME)/rdbms/public
-I$(ORACLE_HOME)/rdbms/demo



$(MYFILE):



        $(MAKE) -f Makefile OBJS=$@.o EXE=$@ build



.SUFFIXES: .pc .c .o



.pc.c:


        $(PROC) $(PROCFLAGS) iname=$*



.pc.o:

	 $(PROC) $(PROCFLAGS) iname=$*
	 $(C2O) $(MYORACINC)

.c.o:
	 $(C2O) $(MYORACINC)


#	$(C2O) -I$(ORACLE_HOME)/precomp/public -I$(ORACLE_HOME)/rdbms/public



-I$(ORACLE_HOME)/rdbms/demo


### <-------------- Cut here: Makefile END -------------->

Received on Wed Oct 04 2006 - 20:00:16 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US