store_speciial_character_into_oracle

I have an oracle 12c database with UTF8 character set running on Red Hat Linux 5.8.

I need to update a erroneous character representation in some database records for phono character defined in this link.
The character is stored with other regular ascii text in a VARCHA2 column.

https://www.compart.com/en/unicode/U+2117

I use SQL*plus on windows 7 desktop or I also have TOAD and Sql Developer
I need to replace those character with correct uff8 encoding for phono character.

The string to replace is "&#738471" in VArChar2 field.

Would you a replace statement (source string,target string) with a target of "U+2117" or the bytes 0xE2 0x84 0x97?
sam15Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
You should be able to use SQL Developer and just paste the character in.  sqlplus might not work depending on the characterset of the environment you are running it in.

Check your string to replace.  Normally encoded Unicode ends with a ';'.  I added a closing semi-colon to my example.  If your data doesn't have it, just change my example.

I would try a replace in SQL Developer:
update some_table replace(some_column,'򴒧','℗');
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam15Author Commented:
Did you use windows character map to type the phono letter or some keyboard combination?
Cant you type the actual Unicode representation for that pohno letter in the update statement?
0
slightwv (䄆 Netminder) Commented:
>>Did you use windows character map to type the phono letter or some keyboard combination?

No.  I copied and pasted it directly from the link you provided.

>>Cant you type the actual Unicode representation for that pohno letter in the update statement?

You might be able to use CHR or some other character encoding function but why can't you just use SQL Developer and the raw character itself?

Possibly UNISTR:  https://docs.oracle.com/database/122/SQLRF/UNISTR.htm#SQLRF06154

I don't use SQL Developer and I don't have the proper code page set up in my sqlplus configuration to test anything right now where I can give you a 100% copy/paste answer.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Geert GOracle dbaCommented:
you need to set your oracle client to UTF8 too in NLS_LANG in the registry (or environment).
sample: AMERICAN_AMERICA.UTF8

otherwise SQLDeveloper/Toad statements get translated
0
sam15Author Commented:
I did set the SQL Developer client encoding to UTF-8.
I ran the update using replace function and copy/pasted the phono character as you have above. It looked fine in the after selecting record from database using sql developer but not correct using sql*plus for windows.

I used the web application to generate the XML and I got this translation which does not seem correct.

â„—

The xml should have ℗ for this phono character.

Any ideas why?
0
sam15Author Commented:
Here is the function that encodes the data selected from database into XML

 FUNCTION xml_entity_encode( str1 IN VARCHAR2 ) RETURN VARCHAR2
        IS
                local_str       VARCHAR2(32000) := str1;
        BEGIN


                local_str := REPLACE (local_str, '&', '&');
                local_str := REPLACE (local_str, '<', '&lt;');

                local_str := REPLACE (local_str, '>', '&gt;');
                local_str := REPLACE (local_str, '''', '&apos;');
                local_str := REPLACE (local_str, '"', '&quot;');
                local_str := REPLACE (local_str, '|', '');
        RETURN local_str;
        END xml_entity_encode;
0
slightwv (䄆 Netminder) Commented:
>>after selecting record from database using sql developer but not correct using sql*plus for windows.
>>Any ideas why?

sqlplus has never dealt well with Unicode characters.  You can have limited success if you can find the correct code page in the window/OS where you run sqlplus.  sqlplus hasn't really been updated over the years and Oracle has been trying to get rid of it for a LONG time.

Just because it doesn't look right in sqlplus doesn't mean it isn't right.

If you really need command line and need to work with unicodeI would try the new SQLCL:
http://www.oracle.com/technetwork/developer-tools/sqlcl/overview/index.html

I've never used it but I hear some people like it and it should do a better job with Unicode.

>>I used the web application to generate the XML

If you already have the XML, why are you needing to encode things using the function you posted?  Oracle has all the XML functions and procedures you will likely ever need.

>>Here is the function that encodes the data selected from database into XML

Use the one provided by Oracle:
https://docs.oracle.com/database/122/ARPLS/DBMS_XMLGEN.htm#ARPLS69854

select dbms_xmlgen.convert('Convert XML: &"<>',0) from dual;

DBMS_XMLGEN.CONVERT('CONVERTXML:&"<>',0)
------------------------------------------------------------------------------------------------------------------------
Convert XML: &amp;&quot;&lt;&gt;
0
sam15Author Commented:
This is old codes and references the custom package I referenced 100s of times. It would take a long time to replace that with oracle package and retest everything, The package has been working fine for years. I am not using sql*plus command for the update. I used sql developer after setting client encoding to UTF-8.

Do you know how to verify if the phono character is stored correctly (I. check the Unicode representation value) and why the html entity in XML file is not correct?
0
slightwv (䄆 Netminder) Commented:
>>Do you know how to verify if the phono character is stored correctly

Query it using a tool that can properly display that particular Unicode character.  I would probably use SQL Developer.

If you use sqlplus and it doesn't "look" right, copy what it does return and paste it into something that can display Unicode like Word.  I've seen it paste correctly even though sqlplus didn't have that character in the code page to display it correctly.

Did you try UNISTR that I suggested earlier?

select UNISTR('try this: \2117') from dual;

>>and why the html entity in XML file is not correct?

If can be many things.

-Whatever program received the data encoded it incorrectly.
-Oracle can and does rewrite/encode XML for you.  If you insert one thing, Oracle XML might re-encode it differently as it processes it.
-If the data moves between systems before getting to the database, something in the middle might have messed with it.

>>The package has been working fine for years

and now you know it no longer works.  I'm betting this isn't the last time you will have a Unicode encoding/decoding problem.

I rewrite code all the time because when I first wrote it, there are things I didn't know.  As I learn better and newer ways of doing things, I adjust.
0
slightwv (䄆 Netminder) Commented:
I'm on Windows and here is an example of getting it to work for me using sqlplus.

I need to set the correct code page in my CMD window and set NLS_LANG for Oracle.

C:\>sqlplus user/pass

SQL*Plus: Release 12.1.0.1.0 Production on Fri Nov 3 12:15:03 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Fri Nov 03 2017 12:14:12 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select UNISTR('try this: \2117') from dual;

UNISTR('TRY
-----------
try this: ?

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

C:\>set NLS_LANG=American_America.AL32UTF8

C:\>sqlplus user/pass

SQL*Plus: Release 12.1.0.1.0 Production on Fri Nov 3 12:15:18 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Fri Nov 03 2017 12:15:03 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select UNISTR('try this: \2117') from dual;

UNISTR('TRYTHIS:\2117')
--------------------------------------------
try this: Γäù

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

C:\>chcp 65001
Active code page: 65001

C:\>sqlplus user/pass

SQL*Plus: Release 12.1.0.1.0 Production on Fri Nov 3 12:16:00 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Fri Nov 03 2017 12:15:18 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select UNISTR('try this: \2117') from dual;

UNISTR('TRYTHIS:\2117')
--------------------------------------------
try this: ℗

SQL>

Open in new window

0
sam15Author Commented:
I was using the old 9.2 sql*plus for windows. I tried your commands using the 11.2 new sqlplus they added but did not get same results.
I would not worry about sql*plus. I used sql*developer as you recommended. But is not there a function that can confirm the Unicode equivaluent for letter stored in column? Also, do you know why XML is not displaying correct html entity code?

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\>set NLS_LANG=American_America.AL32UTF8


C:\>sqlplus user/pass@db

SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 3 17:34:14 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL> select UNISTR('try this: \2117') from dual;

UNISTR('TRYTHIS:\2117')
--------------------------------------------
try this: â„—


C:\>chcp 65001
Active code page: 65001

C:\>sqlplus user/pass@db

SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 3 17:35:39 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL> select UNISTR('try this: \2117') from dual;

UNISTR('TRYTHIS:\2117')
--------------------------------------------
try this: â„—

SQL>
0
slightwv (䄆 Netminder) Commented:
>>I tried your commands using the 11.2 new sqlplus they added but did not get same results.

Maybe it has to do with your Windows regional settings in addition to the CMD setup.  I cannot say.  What I posted was using the 12c client running on Windows 10 with Windows regional settings for US EST.

You might need different settings or your specific configuration.  I would check your your System Administrator and/or Oracle DBA to help with your correct settings.

>>Also, do you know why XML is not displaying correct html entity code?

I do not understand what you are asking.  Sample data, what you are doing with it and what you are seeing will help a lot.
0
sam15Author Commented:
Basically I have a web page that generates an XML file It uses the above function I listed for you.
The phono appears correct in SQL Developer in the column I updated. However, the HTML entity in the XML file is not correct.



The xml should have &#8471; for this phono character.

What I see in the XML file is â„—
0
slightwv (䄆 Netminder) Commented:
>>Basically I have a web page that generates an XML file It uses the above function I listed for you.

Then either the web page isn't properly encoding the characters or, as we already know, your function doesn't work.  Your function only accounts for 6 characters and if your XML has ANY of those 6 characters, the web page isn't doing it's job since ALL those are invalid inside XML.

>>What I see in the XML file is â„—

See using what tool?  Just because the tool displaying the output doesn't mean the data in the database isn't correct.  It could be a problem on query not insert.

You can always do a DUMP to see the actual stored values:
SQL> select dump('hello') from dual;

DUMP('HELLO')
---------------------------------
Typ=96 Len=5: 104,101,108,108,111

Open in new window


Dump the characters and compare what is stored with what should be there.
0
sam15Author Commented:
okay let me try the DUMP and
select dbms_xmlgen.convert('Convert XML: &"<>',0) from dual;

to see if the correct Unicode for phono is stored in database and whether the oracle function results in correct HTML entity.

We never had an issue with the custom function in db before but maybe there was no special characters before.
0
slightwv (䄆 Netminder) Commented:
You should not need to use dbms_xmlgen.convert if the web page is correctly generating the XML.  My guess is the site isn't generating correct XML.  It might be using some "custom" code or string concatenation or some other non-standard method.

It could be as simple as the web server not having the correct NLS_LANG setting for your database.  Normally the web server doens't have direct access to the database so it might be a web server configuration issue?
0
sam15Author Commented:
IT is OHS 12c and I recall I set it to UTF-8 on install. Your command runs is a SQL that can be run in SQL Developer or sql*plus on server machine so that should show the correct HTML entity code regardless of web server setting.
0
slightwv (䄆 Netminder) Commented:
>>IT is OHS 12c and I recall I set it to UTF-8 on install.

I'm not familiar with that but it it generates the XML using native calls and it isn't doing it correctly, I would open a SR with Oracle Support.
0
sam15Author Commented:
No, the XML is generated using stored procedure. we only use mod_plsql on OHS to call the procedure and display results on browser.
0
slightwv (䄆 Netminder) Commented:
Whatever converted the '℗"  into "&#738471" doesn't appear to be working correctly.
0
sam15Author Commented:
I ran a DUMP on the column and got this. Is this correct code for phono?

SQL>select dump(note) from test2;

----------------------------------------------------------
Typ=2 len=3:  226,132,151

I tried the XMLGEN.CONVERT on column but I got same characters
select dbms_xmlgen.convert(note) from test2;
0
slightwv (䄆 Netminder) Commented:
>> Is this correct code for phono?

Do the conversion.

In the link you original provided it has:
UTF-8 Encoding:   0xE2 0x84 0x97 (â„—)

Does that match the decimal values from the DUMP command?

>>I tried the XMLGEN.CONVERT on column but I got same characters

you get &#738471;?  If so, then it seems to have been encoded correctly all the time and you shouldn't need to do the replace from the original question.
0
sam15Author Commented:
yes the HEX to DECIMAL conversion indicate correct character stored.
This is the XML output I get
<meta name="Title" content="&amp;#8471;2017 Blue Birds"/>

According to page, the HTML ENTITY is &#8471;

so the program seems to be adding the "amp;" after "&". What do you get when you run XMLGEN.CONVERT on this phono using SQL?
0
slightwv (䄆 Netminder) Commented:
>>yes the HEX to DECIMAL conversion indicate correct character stored.

That was what I got as well so the character is correct in the original question asked.

>>This is the XML output I get

I thought we were dealing with "&#738471;"  When did it become "&#8471;"?

You are double encoding the data.  You need to figure out where that is happening.  I cannot answer that for you.

The first encoding gives you:  &#8471; which is correct XML encoding.

When it is encoded again, the & is re-encoded to &amp; so you get &amp;#8471;
0
sam15Author Commented:
it looks like someone changed the table data as I could find the phono letter.
I corrected one record with the correct phono.

The xml shows this now

<meta name="Title" content="â„—2017 Blue Birds"/>

I see this on the original page I posted next to the UTF8 encoding but I need the HTML entity printed " &#8471;"

Do you think this an decode issue with XML function?
0
slightwv (䄆 Netminder) Commented:
>>The xml shows this now

I really can't answer that.  It isn't the Phono character but I cannot say what is displaying that so I cannot say if it is just a characterset issue or an actual data issue.

>>I see this on the original page I posted next to the UTF8 encoding but I need the HTML entity printed " &#8471;"

Again, in the original question it was "&#738471;"  Where did  " &#8471;" come from?

>>Do you think this an decode issue with XML function?

I have posted several times that you have a bug somewhere.  It is either in the initial encoding or in the decoding when retrieved.  Since I don't know your system and all the code involved, I cannot say.

The dump you posted seems to imply the phono character is stored correctly but I don't know what all processing you did before the dump.  You dumped the notes column and posted 3 decimal values but say the column has "<meta name="Title" content="â„—2017 Blue Birds"/>".  That is much more that 3 decimal values id dumped.

I'm not sure what else I can provide here.  Unless you can post an end-to-end test case with inserts and queries that show the problem, you need to look in the code you've written and I cannot help with that unless you post it.
0
sam15Author Commented:
The "&#738471" was either a typo or page encoding for amp.

The string should be "&amp;#8471".

I created a small test table with note column to play with. There is another production table where I only updated one records to test things before doing global update. I posted the function on top xml_entity_encode.

If you can get the correct XML encoding for this character using DBMS_XMLGEN I can switch to that for encoding this column only. you can try this

create table test (
note    varchar2(30) );

insert into test values ('℗');
commit;


FUNCTION xml_entity_encode( str1 IN VARCHAR2 ) RETURN VARCHAR2
         IS
                 local_str       VARCHAR2(32000) := str1;
         BEGIN


                 local_str := REPLACE (local_str, '&', '&amp;');
                 local_str := REPLACE (local_str, '<', '&lt;');

                 local_str := REPLACE (local_str, '>', '&gt;');
                 local_str := REPLACE (local_str, '''', '&apos;');
                 local_str := REPLACE (local_str, '"', '&quot;');
                 local_str := REPLACE (local_str, '|', '');
         RETURN local_str;
         END xml_entity_encode;
0
slightwv (䄆 Netminder) Commented:
>>The "&#738471" was either a typo or page encoding for amp.  The string should be "&amp;#8471".

Then there isn't and likely hasn't been a problem.  If you look a the link you posted "&#8471;" is correct encoding for that character.

>>I created a small test table with note column to play with.

But that test doesn't use XML.  I need a test case doing what you will be doing in production.

>>  local_str := REPLACE (local_str, '&', '&amp;');

That is likely your problem!  If the data is already properly encoded and you run encoded data through that function, you will get double encoded data.

It the data you have is actually:
<meta name="Title" content="&#8471;2017 Blue Birds"/>

It is correct as-is.  If you take that and run it through the function you created, you get the problem you have and will get:
<meta name="Title" content="&amp;#8471;2017 Blue Birds"/>

which is incorrect.
0
slightwv (䄆 Netminder) Commented:
Try this.

Copy the code from the windows below and save it to a file named test.xml.  Then open that file in Internet Explorer or Edge.  See what it does.

<?xml version="1.0"?>
<root>
<node>&#8471;</node>
<node>&amp;#8471;</node>
</root>

Open in new window

0
NerdsOfTechTechnology ScientistCommented:
Essentially you would have to 'reverse' the &amp; encoding. Any line after this one:
local_str := REPLACE (local_str, '&', '&amp;');

Open in new window

you have to change the &amp; back to & if it meets a certain pattern (to sanitize it from being malicious)

Essentially the search would be:

REGEXP
'.#..&.amp.;.:digit:+?.;.'

In other words the function is converting &#8471; into &amp;#8471 making it double-encoded (not what you want);
thus, you would need to reverse the &amp; back into & if and only if it is part of a pattern match &#digit digit digit digit;

AKA back to REGEXP
'.#..&..;.:digit:+?.;.'

I'm adding regular expressions to the subject list.

Maybe one of the other Query Syntax experts in Oracle with REGEXP knowledge can help with the correct replace syntax.
0
slightwv (䄆 Netminder) Commented:
>>I'm adding regular expressions to the subject list.

I removed it.  The solution here isn't a normal regular expression issue.  If there is a need to reverse the double encoding and need a regular expression to do it, we'll do it with Oracle's syntax and functions.  Oracle's regex functions are expensive to call and should only be used when necessary.

I'm convinced it isn't necessary since the double encoding is self inflicted and should be done away with.
0
sam15Author Commented:
Based on the answers above,  I think there is some confusion.

There is no double encoding as mentioned. I will also present a test case for you to try
but you need OHS 12c/11g and MOD_PLSQL  to run it from a browser.

There was two parts to this question.
1) How do you update the table with correct phono letter.

This was answered and I have correct data now.

2) how do you get the correct HTML entity in the XML text file and this is where
the issue is.

To try this out do this:

-- create test table
CREATE TABLE test
(title    varchar2(50) );

-- load sample data
INSERT INTO test VALUES ('℗ 2017 Blue Birds');
COMMIT;

--confirm data stored correctly
SELECT DUMP(title) from test;

-- Procedure to create the XML

CREATE OR REPLACE procedure test_xml
IS
   v_title     varchar2(50);
BEGIN
  SELECT xml_entity_encode(title) into v_title from test;
 
  HTP.PRINT('<?xml version="1.0"?>');
  HTP.PRINT('<book>');
  htp.p('<title>'||v_title||</title>');
  htp.p('</book>');
 
END;
/

CREATE or REPLACE FUNCTION xml_entity_encode( str1 IN VARCHAR2 ) RETURN VARCHAR2
          IS
                  local_str       VARCHAR2(32000) := str1;
          BEGIN


                  local_str := REPLACE (local_str, '&', '&amp;');
                  local_str := REPLACE (local_str, '<', '&lt;');

                  local_str := REPLACE (local_str, '>', '&gt;');
                  local_str := REPLACE (local_str, '''', '&apos;');
                  local_str := REPLACE (local_str, '"', '&quot;');
                  local_str := REPLACE (local_str, '|', '');
          RETURN local_str;
          END xml_entity_encode;

-- Run the procedure from browser
https://xx.xxx.xxx/dadname/test_xml


The "view source" on browser shows the phono letter as stored in database. The phono letter was not encoded
to the HTML entity "&#8471;".

I hope this explains it well.

It might be that the encode function does not handle special phono or UTF character correctly.
How can it be modified to handle this or can another XML package be used to get correct encoding?
0
slightwv (䄆 Netminder) Commented:
>>How can it be modified to handle this or can another XML package be used to get correct encoding?

Generate XML properly using the build-in Oracle functions and stop using our own functions.

This single select replaces all the functions and string concatenations:
select xmlelement("book",
	xmlforest(title as "title")
)
from test
/

Open in new window



I cannot test this since I'm not running PL/SQL server pages but see what this does:
CREATE OR REPLACE procedure test_xml
IS
BEGIN

	select xmlelement("book",
		xmlforest(title as "title")
	) into v_title
	from test;

	htp.p(v_title);
END;
/

Open in new window

0
sam15Author Commented:
I did it test it and it does not do any conversion. It adds the XML tags but it showed up as ℗ on the XML in browser.

it seems the best way is to hardcode it in the function enrity_encode. I wil try that
 
  local_str := REPLACE (local_str, ' ℗', '"&#8471;"');
0
slightwv (䄆 Netminder) Commented:
>>it seems the best way is to hardcode it in the function enrity_encode.

That is NEVER the best way.

>>It adds the XML tags but it showed up as ℗ on the XML in browser.

Isn't that what you want?

If not, I'm so confused on what you actually want.

I can tell you from experience:  When you have to write  code to do replaces to trick things into working, you are doing it wrong.

If you have plain varchar2 data that has special characters in it, I've shown you how to properly generate XML from it.

If some process mangled the data when it was initially inserted into the varchar2 column, I would look at fixing that process.  If you cannot, then you might need to use Oracle provided functions to un-mangle it before properly generating XML.

You seem like you want to continue down the path of using your own code and that is your choice.
0
sam15Author Commented:
I dont want the phono in xml. I want the html entity code i listed.
I got it working  by adding it to the encoder function. The oracle functions do not seem to encode special characters.
0
slightwv (䄆 Netminder) Commented:
I've not forgotten about this.  I've not been able to figure out a way to get Oracle to encode that character.  I've opened an SR with them about this.  I'll update you when I hear something.
0
sam15Author Commented:
okay, thank you. Let me know when you find out.
0
sam15Author Commented:
did you find out anything from oracle?
0
slightwv (䄆 Netminder) Commented:
I've not forgotten about you.

It's Oracle Support.  It takes a while to get them to understand the actual issue then you wait on development to respond with some nonsense response and you wait again.

What they originally came back with wasn't even close to solving the problem asked but it bought them a few more days to ignore the SR.
0
sam15Author Commented:
it is good to know that I am not the only who thinks oracle support is bad most of the time.
0
slightwv (䄆 Netminder) Commented:
Well, after 22 days and one spin-off SR the results are in:  Oracle doesn't have anything built in to force encoding of 'valid' characters and they suggest using REPLACE.
0
sam15Author Commented:
Does this mean that their internal built-in XML functions do not encode special characters?
 I assume that means you cant use their built-in functions if you have data with special characters.
0
slightwv (䄆 Netminder) Commented:
I was hoping Oracle had an "encode this and I don't care" function buried somewhere before Unicode was so popular and the left it around.  If they do have it, all the new employees have forgotten.

The problem is Oracle only encodes characters that falls into the specifications.  The Sound Recording Copyright is valid in XML and HTML so they don't have anything that encodes them.

If you can find somewhere in the XML of W3C specs where the Sound Recording Copyright needs encoding, open a bug and they sort of have to fix it.

I suppose that sort of begs the question:  Why do you have to manually encode it?  What are you doing with it that doesn't work unless it is in its encoded form?
0
sam15Author Commented:
for same reason you do URL encoding for special characters. URLs can be set over internet using only ASCII characters that is why you do encoding I think.

https://www.w3schools.com/tags/ref_urlencode.asp
0
slightwv (䄆 Netminder) Commented:
URL encoding and XML encoding are two completely different things.

XML should allow valid UTF8 characters with no problems.  Again, the Sound Recording Copyright character appears to be valid in XML so it shouldn't need encoded.

Are you encountering problems or are you trying to prevent a perceived problem that really may not be a problem?
0
sam15Author Commented:
it is XML encoding.
I think it is requested because many text editors or xml decoders do not recognize the special character or support Unicode.

I also thought you will have a problem with you try to pass a special character via URL and that is why you encode it.

So it could be both even though the primary purpose is xml
0
slightwv (䄆 Netminder) Commented:
I cannot speak to what editors and decoders do or don't do.  I can only speak about an Oracle UTF8 database and what is or isn't allowed in the XML spec.

If you can find a specific example where something you are doing fails if you don't encode this character, I'll take a look.

>>I also thought you will have a problem with you try to pass a special character via URL and that is why you encode it.

I would need a real world example not a hypothetical.  It appears from the specs that only ASCII characters are allowed in a URL.  I'll be happy to read anything you can find that says Unicode is allowed.

A LONG time ago you posted:
The phono appears correct in SQL Developer in the column I updated. However, the HTML entity in the XML file is not correct.

I'm starting to stand by my original comment that the web page is doing things incorrectly and it isn't a problem with the data in Oracle.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.