Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL and Unicode (Oracle 10g)
icon5.gif  Dynamic SQL and Unicode [message #331686] Fri, 04 July 2008 10:15 Go to next message
cristoir
Messages: 3
Registered: July 2008
Junior Member
Hi everyone,

I have a problem with dynamic SQL and unicode and was wondering if anyone could help?

One of my stored procedures contains a line like this;

EXECUTE IMMEDIATE someSQL;

Where someSQL has to be NVARCHAR2 because it will contain some unicode literals. This throws an error because NVARCHAR2 is not supported by EXECUTE IMMEDIATE as far as I am aware.

I realise that an alternative to this is to do something like (pseudo code);

someSQL VARCHAR2;
someSQL := 'SELECT * FROM some_table WHERE some_value = :1';

myUnicodeValue NVARCHAR2;
myUnicodeValue := 'Some unicode string';

EXECUTE IMMEDIATE someSQL USING myUnicodeValue;

However, this approach is very unsuitable for my application, due to the highly variable nature of the SQL statement that is passed in for execution. It would be extremely impractical to fill the statements with Mad binding placeholders.

Is there another way around this problem that I am not aware of?

Thanks for your help.
Re: Dynamic SQL and Unicode [message #331689 is a reply to message #331686] Fri, 04 July 2008 10:23 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow Posting Guidelines below
http://www.orafaq.com/forum/t/88153/0/

Hmmm.....
At my last job, I had an application supporting "unicode" (UTF-8) characters in VARCHAR2 variables.

I am/was unaware of any requirement for NVARCHAR2 use for unicode data.

I don't know about any restriction involving EXECUTE IMMEDIATE & NVARCHAR data.

You might be able to work around restrictions by using nested ASCIISTR & UNISTR functions
Re: Dynamic SQL and Unicode [message #331694 is a reply to message #331686] Fri, 04 July 2008 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (col nvarchar2(10));

Table created.

SQL> insert into t values (n'a');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;
COL
----------
a

1 row selected.

SQL> declare
  2    val nvarchar2(10) := n'a';
  3    ret integer;
  4  begin
  5    execute immediate 'select count(*) from t where col = :1'
  6    into ret
  7    using val;
  8    dbms_output.put_line('cnt='||ret);
  9  end;
 10  /
cnt=1

PL/SQL procedure successfully completed.

What is your test?

Regards
Michel
Re: Dynamic SQL and Unicode [message #331695 is a reply to message #331686] Fri, 04 July 2008 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I understand now what you meant now but bind variables ARE the way to do it.
It is almost ALWAYS the way to do it.
Literals are almost always BAD, VERY bad, VERY VERY bad.
You can kill your server with literals and this whatever is its power.

Regards
Michel

[Updated on: Fri, 04 July 2008 11:40]

Report message to a moderator

Re: Dynamic SQL and Unicode [message #332006 is a reply to message #331695] Mon, 07 July 2008 04:01 Go to previous messageGo to next message
cristoir
Messages: 3
Registered: July 2008
Junior Member
OK, thanks for the information, I will have to take a look again at how I'm doing things.

Can I ask you though, is there a better approach to binding variables than explicitly matching every binding literal to every parameter of the USING clause?

For example, if I try to execute this code;

declare
  val nvarchar2(10) := 'a';
  ret integer;
begin
  execute immediate 'select count(*) from a_bind_test where col1 = :1 AND col2 = :1'
  into ret
  using val;
  dbms_output.put_line('cnt='||ret);
end;


Then I get an error saying;

Error report:
ORA-01008: not all variables bound
ORA-06512: at line 5
01008. 00000 -  "not all variables bound"
*Cause:    
*Action:


I know I can put "USING val, val" in, but this seems pretty clunky to me. It makes it particularly difficult when very long SQL statements are generated and there are lots of variables to be inserted into them by the code. Is there anyway that I can just bind all occurances of a particular identifier to one "USING" parameter when executing the dynamic SQL?

Hope this makes sense, cheers.
Re: Dynamic SQL and Unicode [message #332007 is a reply to message #332006] Mon, 07 July 2008 04:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You can a form of naming your bind-variables by using sys_context. This is especially handy for code where you don't know the number of binds upfront.
Re: Dynamic SQL and Unicode [message #332126 is a reply to message #332007] Mon, 07 July 2008 10:14 Go to previous messageGo to next message
cristoir
Messages: 3
Registered: July 2008
Junior Member
Hi Frank,

Could you clarify what you mean? I looked up about sys_context, but could only find information about getting environment variables for your Oracle session - nothing about dynamic SQL or binding variables.

Cheers!
Re: Dynamic SQL and Unicode [message #332134 is a reply to message #332126] Mon, 07 July 2008 10:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Check Tom's reply to this follow-up

Edit: Found an example that explains better what I mean: http://www.orafaq.com/forum/m/330457/42935/?srch=sys_context#msg_330457

Google for sys_context in combination with "create context" or "create or replace context" for more examples.
And of course come back if you still can't find what you need Smile

[Updated on: Mon, 07 July 2008 10:32]

Report message to a moderator

Re: Dynamic SQL and Unicode [message #332140 is a reply to message #332126] Mon, 07 July 2008 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace package myPkg as
  2    procedure set_var (varname varchar2, value varchar2);
  3  end;
  4  /

Package created.

SQL> create or replace package body myPkg as
  2    procedure set_var (varname varchar2, value varchar2) is
  3    begin
  4      dbms_session.set_context('MyApp', varname, value);
  5    end;
  6  end;
  7  /

Package body created.

SQL> create or replace context MyApp using MyPkg;

Context created.

SQL> drop table t purge;

Table dropped.

SQL> create table t (col1 varchar2(10), col2 varchar2(10));

Table created.

SQL> insert into t values ('A','B');

1 row created.

SQL> insert into t values ('B','C');

1 row created.

SQL> begin
  2    myPkg.set_var('val1','B');
  3    for r in (select col1, col2 from t where col1 = sys_context('MyApp','val1'))
  4    loop
  5      dbms_output.put_line ('Query 1, found: '||r.col1||','||r.col2);
  6    end loop;
  7    for r in (select col1, col2 from t 
  8              where col1 = sys_context('MyApp','val1')
  9                 or col2 = sys_context('MyApp','val1'))
 10    loop
 11      dbms_output.put_line ('Query 2, found: '||r.col1||','||r.col2);
 12    end loop;
 13  end;
 14  /
Query 1, found: B,C
Query 2, found: A,B
Query 2, found: B,C

PL/SQL procedure successfully completed.

You set all variables in your context and you use these variables in the query as you want.

Regards
Michel
Re: Dynamic SQL and Unicode [message #332178 is a reply to message #332140] Mon, 07 July 2008 14:50 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
The argument against literals often gets way over-blown. It's all about avoiding unnecessary hard-parsing of statements, where the only difference between the statements is the value of the literals, and you execute LOTS of such statements. If you only run a few, it's fine to use litterals, especially if you're queries are going to take a long time to execute (say data warehousing). I'm unaware of any dynamic query builder tool (QBE) that goes to elaborate lengths to avoid bind variables. I've coded solutions using the sys_context() approach and it's definitely not trivial to code, debug & deploy. How much is your coding time worth...

AskTom fas a funky query to assess the situation.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1163635055580

Your issue may be related to more to what your database characterset and NCHAR characterset is as well as your client environment settings are. I'd suggest:
1) check your codepage settings
-- Whoops - sorry about using a litteral here...
select * from nls_database_parameters 
where parameter like '%CHARACTERSET'
order by 1;

PARAMETER               VALUE
----------------------- -----------
NLS_CHARACTERSET        AL32UTF8
NLS_NCHAR_CHARACTERSET  AL16UTF16


2) If using a DOS sqlplus, set the appropriate codepage first
C:\>chcp 1252
Active code page: 1252
C:\>sqlplus...

3) check your sqlplus NLS_LANG (client code page of interest)
NLS_LANG codepage needs to be compatibale with database and NCHAR
characterset if you want to use it.
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jul 7 11:44:52 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> @%NLS_LANG%
SP2-0310: unable to open file "AMERICAN_AMERICA.WE8MSWIN1252"
SQL>


4) ensure that your "unicode character" is actually getting to the Oracle server in tact.
(just because you have something that looks like a special character on your client and looks like the same character when you select it back using the same client, doesn't mean it's being correctly stored in the database if your NLS_LANG is wrong). Notice the difference in data type (1 vs 96) between selecting a litteral compared to a value actually stored in a column.
SQL> create table abc(col1 varchar2(1 char));

Table created.

SQL> insert into abc values ('a');

1 row created.

SQL> select dump(col1) from abc;

DUMP(COL1)
------------------------------------------------------
Typ=1 Len=1: 97

SQL> select dump('a', 1010) from dual;

DUMP('A',1010)
--------------------------------------
Typ=96 Len=1 CharacterSet=AL32UTF8: 97

SQL> select dump(col1, 1010) from abc;

DUMP(COL1,1010)
------------------------------------------------------
Typ=1 Len=1 CharacterSet=AL32UTF8: 97

SQL>


I don't fully understand how client-side pl/sql litterals tie up to NLS_LANG/charactersets. You're probably aware of database storage semantic settings (char vs byte). I don't know what's used in pl/sql variables - need to read up more...
Previous Topic: Display Result at specific or first row
Next Topic: error in where clause
Goto Forum:
  


Current Time: Mon Dec 05 10:37:17 CST 2016

Total time taken to generate the page: 0.09316 seconds