Skip to Main Content
  • Questions
  • Special character constant package after deployment

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question.

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

Answered by: Connor McDonald - Last updated: July 07, 2020 - 1:20 pm UTC

Category: PL/SQL - Version: 11g

Viewed 100+ 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 we 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


and you rated our response

  (2 ratings)

Reviews

July 07, 2020 - 7:29 am UTC

Reviewer: A reader

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

Followup  

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;
/

£

July 07, 2020 - 7:51 pm UTC

Reviewer: Umashankar from india

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 to Explore

Administration

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