SET DEFINE OFF / ON in Oracle Function

I am manipulating text using a function in oracle.

IF C>=0 THEN
REPLACE(ORIGINAL_TEXT,REPLACEMENT_TEXT);
END IF;


Whenever the Replacement Text has a word containing character '&', it asks the value for the bind variable.

I do not wish that it should occur. The original text should be replaced fully, disregarding importance attached to '&' character. How to do this?

Thanks in advance.
Adwait ChitaleyAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
achitaley,

I understand there was a request for attention on this question.  Please post any additional information you require and I'll attempt to assist you.

If there is something wrong with the test code I posted please explain where it doesn't meet your requirements and I'll try to reproduce any problem on my end.

0
 
POracleCommented:
sql> set define ~

then call your function
0
 
Adwait ChitaleyAuthor Commented:
I have tried that.

I do not want to execute it from the sql prompt but from within the function. How can it be done?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
could you tell me from where the function will be called ?

from sql*plus or some other tool ?
0
 
Adwait ChitaleyAuthor Commented:
The function will be called in a VIEW created in oracle.

Details are given hereundeR:

The VIEW to be generated will contain two columns...

a) Original Text
b) Replaced Text

I have another table named REPLACEMENT_STRINGS which contains words and their replacement words... for e.g.

Word  ||  Replacement Word
&        &
&quot         '
drugs       inappropriate word


I have created a function in Oracle which will be used by the View in generating the "Replaced Text" column. The Replaced Text column will replace all occurance of the text similar to the one in WORDS (Replacement_Strings Table) and replace them with Replacement_Word String.

The function works fine for the third example -- (drug --> inappropriate word). However, when a '&' comes in either of the word, it gives a popup and asks for input.

How do i avoid this by manipulating the fuction? Is there any manner in which I can use SET DEFINE OFF in the function itself to generate the view?

Thanks in advance.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
SET DEFINE OFF is a sql*plus command and it cannot be used in the plsql procedures/functions.

so do you mean to say that the view which you create will be used from sql*plus or from some other tool ? pls. explain.

i can understand that you are setting up a function which you use in your view select statement.
0
 
Adwait ChitaleyAuthor Commented:
I am setting up a function which I intend to use in a VIEW.

The function replaces occurrences of certain characters and replaces them with replacement text. Thereby, the final text is generated which is displayed in the view.

I am not facing any issues when the Word  / Replacement Word are simple characters.

However, the function ceases to operate when either the Word  / Replacement Word contains the character '&'.

& results in the function to ask for a user input.. which I do not require.

I hope I have eluciated the issue to your satisfaction.

Thanks in advance.
0
 
Adwait ChitaleyAuthor Commented:
I do not intend to use it from SQL Prompt. I intend to use the View in an application that we are developing.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
can you just develop the view and try to use it from the application instead of from the sql*plus because you will get the & issue in sql*plus.

I understand that you are now using in sql*plus because you are in the development phase of the view.. but the & issue cannot come for you in your application.

Test it out and let me know. Also what is your application ? is it VB based or java based or what is that ?

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
i mean to say that testing of the view should be done from your application and not from the sql*plus in this case as you have & in your data.

Since this is just testing, you can just use set define off and continue to do your testing in sql*plus but do not think that how are you going to make the set define off work for your in your function/application as this is not required.
0
 
slightwv (䄆 Netminder) Commented:
I agree with everything said but just in case there is still any confusion here's a little working example.

Some times a picture is better.
drop table tab1 purge;
create table tab1(col1 varchar2(50));

--just to get the data into tab1
set define off
insert into tab1 values('AT&T is a phone company');
commit;

--turn it back on even before creating the function
set define on


CREATE OR REPLACE function myFunc(inStr in varchar2) return varchar2
IS
BEGIN
	return replace(inStr,'&','-');

END;
/

show errors


select myFunc(col1) from tab1;

Open in new window

0
 
Adwait ChitaleyAuthor Commented:
Replacing    &    with    -    works.

Replacing    >     with    >    does not.
0
 
Adwait ChitaleyAuthor Commented:
Replacing ">"     with   ">"    does not
0
 
Adwait ChitaleyAuthor Commented:
Oops again... the html equivalent of > ..... i.e.   & g t ;    
0
 
slightwv (䄆 Netminder) Commented:
I don't follow what you are trying to tell us.

The '&' sign is only interpreted by SQ*Plus (and some other Oracle SQL access tools).

The function itself doesn't matter.

Given the code I provided in htp:#a32151397 please explain what doesn't work for you.

0
 
Adwait ChitaleyAuthor Commented:
Foremost, Thanks for your input till date.

1) You have only considered replacing the character '&'. Your function works perfectly when & is not suffixed with any other character(s). Try replacing    "&TTT"   with "---". The function ceases to work.

2) My Application stores HTML Special Characters in Database which I am trying to replace with UTF-8 equivalent characters in runtime using this function. However, such HTML Characters are in the form... & G T ;   (without the spaces in between them) to represent the sign.. " > "     . The function when evoked requests for a user input and asks for a value for "GT;"  (since & character appears before it.). This is not desired. The function should repace '& gt;"   (no space between & and gt;) with the character '>'.

3) I have stored a list of all such phrases (HTML Special Characters) and its Equivalent UTF-8 in a separate table which I shall use in this function.

I hope I have elicited the issue to your understanding.

Thanks once again.
0
 
Adwait ChitaleyAuthor Commented:
One more thing... This function is used in a VIEW to generate final text to be represented in a column in the VIEW for generating reports.
0
 
slightwv (䄆 Netminder) Commented:
>>The function when evoked requests for a user input

Again, it isn't the function.  It's how you are executing the function.  That is what my code is attempting to show.  The only way to get prompted is for SQL*Plus (or similar tool) to see the '&'.

Also, there are built in functions to encode/decode the special HTML characters.

Save the following code to a SQL sript file then execute it:  SQL> @myscript.sql

It doesn't prompt me.  I need to set define off to insert the row but that's it.

If you still can't get it to work, I need to see your code so I can reproduce the issue on my end.
drop table tab1 purge;
create table tab1(col1 varchar2(50));

--just to get the data into tab1
set define off
insert into tab1 values('AT&T is a <phone> company');
commit;

--turn it back on even before creating the function
set define on


CREATE OR REPLACE function myFunc(inStr in varchar2) return varchar2
IS
BEGIN
	return dbms_xmlgen.convert(inStr,1);
END;
/

show errors


select myFunc(col1) from tab1;

Open in new window

0
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
I believe the same was communicated to you clearly in the updates 32124724 and 32124817.

If you are still are not clear as to what was meant by my updates, then i can try to explain further to make you understand.

Thanks,
0
 
Adwait ChitaleyAuthor Commented:
.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.