Home » SQL & PL/SQL » SQL & PL/SQL » Different CharcterSet are not recongnisable
Different CharcterSet are not recongnisable [message #401997] Thu, 07 May 2009 04:10 Go to next message
ind9
Messages: 65
Registered: January 2009
Member
Hi,

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

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

Below is my database details.

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

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 how to solve this issue.
Re: Different CharcterSet are not recongnisable [message #402131 is a reply to message #401997] Thu, 07 May 2009 14:11 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>i have some of the Arabic characters in one of the table.
OK, it is a nice place to start.

I suspect the lack of response is due to the fact the problem statement is not intuitively obvious.

>And i am querying from the table and writing the query output to the flat file.
Above is reasonable & straight forward.

>Now the problem whenever a different charcter set is encountered
Different than what? What is this new/different character set?

> l_line := convert(l_line, 'AR8MSWIN1256'); --Arabic Charcter Set
If Arabic characters already exist (see top line of this post), then why do they need to be converted?

What is the content of the file when CONVERT() is not used?

Re: Different CharcterSet are not recongnisable [message #402156 is a reply to message #402131] Thu, 07 May 2009 21:56 Go to previous messageGo to next message
ind9
Messages: 65
Registered: January 2009
Member
Thanks for your response.

Quote:
Different than what? What is this new/different character set?


Different in the sense the Arabic characters are stored in the encoded format in the table.

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

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

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

EMP_Name
--------------------------------------------------
¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿¿¿¿





Quote:
If Arabic characters already exist (see top line of this post), then why do they need to be converted?


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

Quote:

When Convert Function is used flat file is containing data as shown below
Adam ????? ??????

If the convert function is not used then data in the flat file is
Adam ¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿¿¿¿




I hope you are more clear with the problem. Please let me know if more details are required.
Re: Different CharcterSet are not recongnisable [message #402158 is a reply to message #402156] Thu, 07 May 2009 22:44 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
is this data generated through unicode transliteration. if so placing the required font on default directory and invoking through short key may solve the problem, since it does with tamil character set. if not, i could not understand the problem and let me also wait for some more responses
yours
dr.s.raghunathan
Re: Different CharcterSet are not recongnisable [message #402160 is a reply to message #401997] Thu, 07 May 2009 23:00 Go to previous message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
I am not sure I understand the problem now.
Perhaps ACSIISTR or UNISTR functions might assist or provide visibility.
I am withdrawing from this thread before I make a bad situation worse.
Previous Topic: Insert with Select Inner Join
Next Topic: table partitioning
Goto Forum:
  


Current Time: Tue Dec 06 12:24:36 CST 2016

Total time taken to generate the page: 0.25930 seconds