Function issue

Im using SQL Developer and I have a function that cleans non ascii values from strings but when there is an & in the name being cleaned, it prompts me. I know that if I run the function with SET DEFINE OFF it wont prompt me. Problem is I cant include SET DEFINE OFF in my function? How can I include either in my function or in my procedure that is calling my function or whether or not this can be done at all?
Who is Participating?

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

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.

johnsoneSenior Oracle DBACommented:
Depends on the tool being used when you call the function.

SET DEFINE OFF isn't a option for Oracle.  That prompt isn't coming from the database.  It is coming from the tool (specifically SQL Developer in your case, but SQL*Plus does the same thing).

If you ran that same function from a Java application (for example), you wouldn't be prompted.
jknj72Author Commented:
Is there any way I can get around this using SQL Developer?
johnsoneSenior Oracle DBACommented:
I don't use SQL Developer that much, but I would assume that it can run a script, like SQL*Plus can.  Include the SET DEFINE OFF in the script before the function call.  Then run the script and don't run the function directly.
put "set define off" or "set scan off"  as the first line in your script.

It's the execution of the script, not the function, that causes the substitution check to occur.

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
jknj72Author Commented:
First line was the key....I couldnt run it until you said this...Thanks
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
Oracle Database

From novice to tech pro — start learning today.