PL/SQL, turning off substitution variable


Is it possible to turn off the substitution variable in a PL/SQL block. I want to reference '&' without it requiring a value. If I find '&' in a string I want to replace it but everytime I reference a string with an '&' in it, it asks for a value. I'm using SQL Navigator, here your allowed to switch of the substitution variable but I need to know if a command exists to do this.
Any help would be greatly appreciated !!!

Who is Participating?
Why not pass on the table and remove all the & before starting the procedure?

If you want to remove & use:

update table_name
set column_name = translate(column_name,chr(38),' ');

This replace all '&' with spaces.

Give an example of how you want to use '&'.

Anyway you can use chr(38) instead of '&':

a varchar2(30);
begin a:= '&Question'; dbms_output.put_line(a);

This block asks for the value of Question and replace it.

Now look at this:

a varchar2(30);
begin a:= chr(38)||'Question'; dbms_output.put_line(a);

This prints &Question

Andrei RodionovCommented:
Hi j_brett,

If you want to avoid the ampersand sign ('&') substitution in SQL*Plus you should issue 'set define off'.
For SQL Navigator there are appropriate command buttons in SQL Editor window: "Scan for substitution vars" and "Define/undefine substitution variables".

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Andrei RodionovCommented:
Yeah, you can use chr( 38 ) for '&', of course! And what is more, you can use chr( 32 ) for ' ', chr( 65 ) for 'A', etc...
j_brettAuthor Commented:
Thanks for your answers guys but I don't think I explained my question correctly.

I'm writing a package, it takes info. from a file puts it in a stage and I work with it from there. But there is a chance that an '&' will be placed in the table. In my code I might have:

FROM    per_all_people_f
WHERE  person_id = column9;

If column9 contains an &, my package crashes out, because it's looks for a value.

What i want to do is turn off the substitution check if there's an '&', if so remove it. Execute my select statement. And turn back on the substitution, I need it on at all times but this because we do use & for substitution in these programs.

I don't know if this is possible, any ideas ?? I've tried looking for an '&' using chr(38) but whenever I reference the column with the '&', input is requested.

Thanks again !!!!
Andrei RodionovCommented:
Sorry, j_brett, but I have more questions then earlier :(

Well, what does it mean: "If column9 contains an &, my package crashes out, because it's looks for a value"?
Do you want to store some PL/SQL in your own table? Why your output from per_all_people_f is so sensitive for an '&'?

Explain your technology more precisely if you may.

j_brettAuthor Commented:
Hi Andrew,

Sorry if I'm not explaining myself clearly, the problem itself is a bit hard to explain. I'll explain exactly what I'm doing:

I'm writing a package through SQL Navigator. This package is called from an Oracle Application. The package accepts this file and loads the information into various tables within the application database.

Before we do the loading I first have to perform validation on the data in the file. So I load these into a table. Everything is fine up until now.

Now an example of my validation: I have to check that column1 is a valid Social Security Number, I step through each row and check this. Still everything is fine.

I also have to perform validation on another column, e.g. validating that column9 is a valid person_id, but in testing I've noticed that if I put an '&' in the test file and it gets staged in column9 of the table, when I try validating this column my program will hang, it's because it's waiting for input.

I can't just switch off substitution as I'm running it through an Oracle application. I was hoping that there would be a command to do this in a PL/SQL procedure.

Don't worry if you can't think of anything, I'm not sure if it's possible.

Thanks again for your help,
j_brettAuthor Commented:
Adjusted points from 50 to 100
Andrei RodionovCommented:
Sorry, j_brett,

I've to go now. If you'll cann't get answer I'll try to answer tomorrow may be.

How do you know that it is waiting on input?.

Define or substitution is only a feature in interactive SQL Plus and is not
available in table data.

In any case, oracle never attempts to parse your table data for potential
substitutes. If code is hanging, it could be elsewhere.

In any case, just try feeding some data into the standard input and see
if application finishes normally.

As smaratAshok said substitution is avialabe only in interactive SQL*Plus session. But if you want the statement to turn off the substitution, then you can try this:

SQL> set define off

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.