replace HTML Entities in sqlplus command

Question

So, I have an auto-generated sql file I'm trying to run using sqlplus command but the problem is... there might be HTML entities (' in this case) that need to be replaced so it doesn't ask the user for any value, here's an example of what I have:

CREATE TABLE MY_TABLE (
    TABLE_KEY CHAR(24) DEFAULT '&apos; &apos;' NOT NULL,
    TABLE_FIELD CHAR(40) DEFAULT '&apos; &apos;' NOT NULL
)

I'm trying to run it using the following command:

sqlplus USER/PASSWORD @<path_to_sql_file>

Is there any way to get the actual apostrophe without sqlplus asking me for the "apos" value?

Thanks a lot for your help on this!

Wernfried Domscheit · Accepted Answer · 2017-03-15 07:20:47Z

2

You can either use CHR(38) instead of & (as already proposed by Ersin) or you run

SET DEFINE OFF

at top of your SQL*Plus script. You can even define other character than & to be used for prefix substitution variables, see documentation SET DEFINE

UPDATE

If you write in your script DEFAULT '' '' then Oracle puts ' ' as default - what else do you expect? An Oracle Database is not a HTML browser.

If you want to have two apostrophe then write DEFAULT ''' '''. In order to escape ' inside a string, simply double it.

If you have to update existing values you can use update statement as this:

UPDATE MY_TABLE SET TABLE_KEY = DBMS_XMLGEN.CONVERT(TABLE_KEY, 1);

edited Mar 15, 2017 at 7:20

answered Mar 14, 2017 at 7:57

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Julio Fernandez Over a year ago

The problem is that, the default value would be something like this: chr(38)||'apos; '||chr(38)||'apos;' and I the intention is to have just the apostrophe character like this: ' '

Julio Fernandez Over a year ago

Temporary workaround would be to use command line and replace ' for the actual apostrophe but the thing is... I rely on the sql file not having any other html entities, otherwise I would need to adjust it every time...

miracle173 Over a year ago

@JulioFernandez I don't understand. Why can't you use Werner's solution? It turns off the undesirable feature of &.

Julio Fernandez Over a year ago

Hi @miracle173, so, I don't want the string ' to be there, I want the actual apostrophe: ' so, is there a way to tell sqlplus to replace ' with the actual intented value?

miracle173 Over a year ago

@JulioFernandez But in your question you wrote that it has to be replaced so that the user is nor prompted for a value. If you use Werner's or Ersin's solution you will not be prompted for a value. Did you pose the wrong question or do I misread your question?

|

CompEng · Accepted Answer · 2017-03-14 07:48:24Z

0

you can replace '&' char with '||chr(38)||'

try this:

CREATE TABLE MY_TABLE (
    TABLE_KEY CHAR(24) DEFAULT chr(38)||'apos; '||chr(38)||'apos;' NOT NULL,
    TABLE_FIELD CHAR(40) DEFAULT chr(38)||'apos; '||chr(38)||'apos;' NOT NULL
)

answered Mar 14, 2017 at 7:48

CompEng

7,41618 gold badges78 silver badges135 bronze badges

Collectives™ on Stack Overflow

replace HTML Entities in sqlplus command

2 Answers 2

6 Comments

try this:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

try this:

Comments

Your Answer

Sign up or log in

Post as a guest

Related