Skip to Main Content
  • Questions

  • Special character constant package after deployment

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: July 05, 2020 - 6:00 pm UTC

Last updated: July 07, 2020 - 1:20 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi TOM

i have a constant package where i define a variable constant whose value is '£' (pound),

Now, running the package script in dev environment, package created with no errors and did not show any special character in constant variable,
but running same package script in UT environment package created with no errors but constant value has 'Ä£'.

how can i resolve this issues ? came to know its different character set issue

thanks in advance.


Code:

create or replace package pk_constants is
lv_pound constant char(1) :='£'; -- for germany and europe countries
......
.....
end pk_constants;
/



and Connor said...

There are a few potential causes here.

Normally the database will automatically convert charactersets *assuming* the client settings have been correctly set.

But also this can depend on the client tools being used to both edit and run the file.

For example, if I cut/paste your code on my *windows* machine, the code and the result are fine:

create or replace package pk_constants is
  lv_pound constant char(1) :='£'; -- for germany and europe countries
end pk_constants;
/

SQL> select text from user_source
  2  where name like 'PK_CON%';

TEXT
---------------------------------------------
package pk_constants is
  lv_pound constant char(1) :='£'; -- for germany and europe countries
end pk_constants;


but if that character was (say) arabic, then the code is this:

create or replace package pk_constants is
  lv_pound constant char(20) :='بعض المف'; 
end pk_constants;
/


but pasted into windows, the command prompt doesn't support arabic and I get garbage

SQL> select text from user_source
  2  where name like 'PK_CON%';

TEXT
---------------------------------------------
package pk_constants is
  lv_pound constant char(20) :='??? ????';
end pk_constants;


So check that

- the file was saved correctly
- the run time environment was correct for nls settings
- the database charactersets are in alignment


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 07, 2020 - 7:29 am UTC

Use this and it will always be pound.

DECLARE
lv_pound CONSTANT VARCHAR2(1) := unistr('\00A3');
BEGIN
dbms_output.put_line(lv_pound);
END;

Chris Saxon
July 07, 2020 - 1:20 pm UTC

Neat idea - though you may hit:

DECLARE
  lv_pound CONSTANT VARCHAR2(1) := unistr('\00A3');
BEGIN
  dbms_output.put_line(lv_pound);
END;
/

ORA-06502: PL/SQL: numeric or value error: character string buffer too small


Ensure the variable uses character semantics to avoid this:

DECLARE
  lv_pound CONSTANT VARCHAR2(1 char) := unistr('\00A3');
BEGIN
  dbms_output.put_line(lv_pound);
END;
/

£

Umashankar, July 07, 2020 - 7:51 pm UTC

Thank you very much connor & chris .

so i need to prepate one more addition script/statement to change the client session nlssettings before running the deployment script.
correct me if i am wrong.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database