Topics

1

PSOUG / Different Character set are not recognisable in PLSQL Programe

« on: May 13, 2009, 01:27:46 am »

 have some of the Arabic characters in one of the table (EMP).

Actual characters for the employee name is اللغة العربيةاللغة

Whereas in the database those characters are stored like as shown below

Code: [Select]

SQL> Select EMP_Name
  2   From EMP
  3    Where EMpID = 100;

EMP_Name
--------------------------------------------------
żżżżż żżżżżżżżżżżż



And i am querying from the table and writing the query output to the flat file.
 
Now the problem whenever a different character set is encountered then in the file is writing those characters in inverted question marks.

i have used convert function to convert the value into Arabic character set before writing into file. but there is of no use.




Characters in the flat file are not readable. Below are the details.

When Convert Function is used in the plsql program then output of the flat file is containing data as shown below
Adam ??

If the convert function is not used in the plsql program then output of the then data in the flat file is
Adam żżżżż żżżżżżżżżżżż

Please find the attachment which contains database information and the program unit which writes the information to flat file
Tags:

Below are the database details

Code: [Select]

select * from NLS_DATABASE_PARAMETERS
/

PARAMETER                      VALUE
------------------------------ ---------------------------------------------------------------------
NLS_NCHAR_characterSET         UTF8
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_characterS         .,
NLS_characterSET               UTF8
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_RDBMS_VERSION              9.2.0.8.0

20 rows selected.


   
And i have a program unit as shown below which is used to write the contents of the file to a table

Code: [Select]


Declare
 Cursor C1 Is
    Select *
        From Employees
   Where EMpID = 100;
 L_Emp Employees%Rowtype;
 l_file           UTL_FILE.FILE_TYPE;
 l_line           VARCHAR2(2500);
Begin
  Open C1;
  Fetch C1
   Into L_Emp;
  Close C1;

  L_Line := L_Emp.EmpName;
 l_file := UTL_FILE.FOPEN('dir', 'sample', 'w');
  IF UTL_FILE.IS_OPEN(l_file) THEN
     IF l_line IS NOT NULL THEN
       l_line := convert(l_line, 'AR8MSWIN1256'); --Arabic Charcter Set
       UTL_FILE.PUT_LINE(l_file, l_line);
       
     End If;
  End If;
 UTL_FILE.FCLOSE(l_file);
 Exception
  When Others Then
   Dbms_Output.Put_Line (Substr(Sqlerrm,1,254));
End;


Kindly assist