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 -> Re: how pass fren extended characters to a stored procedure by odbc with sqlserver enable syntax?????????

Re: how pass fren extended characters to a stored procedure by odbc with sqlserver enable syntax?????????

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Fri, 14 Jun 2002 22:23:16 +0200
Message-ID: <ugkp87dbfb1647@corp.supernews.com>

"Sylvain Dumont" <sylvain_dumont_at_karat.com> wrote in message news:8YqO8.607$SR3.232559_at_localhost...
>
>
> error: ORA-01756: quoted string not properly terminated
>
> une chaine entre apostrophhes ne se termine pas correctement
>
> oracle Retrieving extended characters thru ODBC
>
> PL/SQL procedure parameters
>
>
> hi, i hope you can help to me.
>
> I have a problem with french and german characters.
> i have a little stored procedure than return what i'm passing to him.
>
> see these example: (the second one work fine on plsql)
>
> first exemple:
>
> 1) i created a new odbc dsn
> 2) i'm going into sqlserver migration tab to choose
> Enable Exac Syntax.
>
> 3) i'm open Winsql (this is a odbc tools)
> http://www.indus-soft.com/winsql/
> 4) i'm write
> exec ksp_test 0,'HéLLO ORACLE'
>
> i receive this error:
>
> Error: ORA-01756: quoted string not properly terminated
> (State:S1000, Native Code: 6DC)
>
> ---------
> I trying to changed too the NLS_LANG in the registry
> like FRENCH_CANADA.WE8ISO8859P1
> French_France.WE8ISO8859P1
>
> but without any success..
> --------
> i got the same problem with
> oracle 9 database with utf8 characters set.
> oracle 8.1.7 with iso8859p1 characters set.
>
>
> i trying all latest odbc driver from oracle website.
>
>
>
>
>
> second exemple:
>
> SQL> variable mytest refcursor;
> SQL> exec ksp_test (0,'HéLLO ORACLE',:MYTEST);
>
> PL/SQL procedure successfully completed.
> SQL> PRINT MYTEST;
>
> Your Database Value
> --------------------------------
> HéLLO ORACLE
>
>
>
>
>
>
>
> CREATE OR REPLACE PACKAGE KSP_PLSQLRSETPKG
> AS
> TYPE RCT1 IS REF CURSOR;
> END;
> ;
>
> CREATE OR REPLACE PROCEDURE KSP_TEST (
> PATCH INT DEFAULT 0,
> PONC VARCHAR2,
> RC1 IN OUT KSP_PLSQLRSETPkg.RCT1
> )
>
> AS
> BEGIN
> OPEN RC1 FOR
> SELECT PONC "Your Database Value" FROM DUAL;
> FROM DUAL;
> RETURN ;
> END;
>
>
>
>
> i'm trying also different nls setting but no good result.
>
> AMERICAN_AMERICA.US7ASCII
> AMERICAN_AMERICA.WE8MSWIN1252
>
> FRENCH_CANADA.WE8DEC
> FRENCH_CANADA.UTF8
> FRENCH_CANADA.WE8MSWIN1252
>
> FRENCH_FRANCE.WE8DEC
> FRENCH_FRANCE.UTF8
> FRENCH_FRANCE.WE8MSWIN1252
>
> is working well on sqlplus but not by odbc..
> also..
> i'm declare a variable and
> i set
> v_variable := 'éä'
>
> and the procedure return the good syntax...
>
>
>
>
> i think is a odbc driver problem....
> the driver don't want to accept a extended characters set by a parameters
> coming from the procedure.
>
> can you confirm to me ..this is a major bug for the driver..
> my procedure is very basic to make a little test.
> did you try my procedure to be sure you have the same problem?
>
> i try with a oracle instance utf8,WE8MSWIN1252 and
> i got always the same problem.
>
> if i write insert into test values ('äé');
>
> everything is fine...but when i call the procedure...
> the procedure don't want to accept any german..french or any extended
> characters...
>
> i need to confirm to me is a bug from the oracle odbc driver or if is
> possible to help me to find a way to pass theses characters to a stored
> procedure from odbc driver..
>
> our application is working by odbc driver.
>
>
>
> thank you very much.
>
>
>
> i'm pretty sure is a bug in the driver ...the bug is coming only when i
> select "ENABLE EXEC SYNTAX" IN THE DSN (SQLSERVER MIGRATION SECTION) ... i
> try with Shema Database and Owner and Empty and i got
> always the same problem
>
> exec KSP_TEST 0,'TéEST'
> ------------------------>>>>>>>NOT WORKING.
> BUT IF I WRITE
> CALL KSP_TEST (0,'TéEST')
> ------------------------->>>>IS WORKING
>
>
> if i select enable exec or i unselect enable exec...
>
> the CALL KSP_TEST...... is always working properly.
>
> BETWEEN THESE SYNTAX THE NLS_LANG IS NEVER CHANGED....
>
>
>
> IS WORKING.....THE NLS_LANG IS GOOD.......because i make a little
> modification in procedure to be sure the INSERT IS inside the database
> CORRECTLY.
>
> CREATE OR REPLACE PROCEDURE KSP_TEST
> (
> PATCH INT,
> PONC VARCHAR2
> )
>
> AS
> v_test varchar2(100);
>
> BEGIN
>
> v_test := 'test';
> INSERT INTO YYY VALUES (PONC);
>
> END;
>
>
>
>
>
>

Like many posters here you call on the clairvoyance capabilities of the members in this group, as nowhere you ever mentioned a version. No version of Oracle, no version of the Oracle client, no version of the ODBC driver. Instead you post many irrelevant details in a horrid English and you simply shout 'bug' at the Oracle driver.
From your post it is clear you need to isolate this on the ODBC level. You need to enable tracing at the ODBC site, using the ODBC administrators applet in Control Panel, and check whether the diacritical characters are passed correctly.
FYI: a search string on NLS ODBC on Metalink returns more than 100 hits. You don't expect anyone is going to read all those articles for you, do you? From here it is also impossible to check whether the *correct* NLS_LANG in the *correct* ORACLE_HOME registry branch has been set, you might have multiple ones or a screwed setup of different versions in one single Oracle HOME. Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address
Received on Fri Jun 14 2002 - 15:23:16 CDT

Original text of this message

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