PL/SQL, turning off substitution variable

Posted on 2000-04-11
Last Modified: 2012-08-13

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 !!!

Question by:j_brett
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2

Expert Comment

ID: 2703316
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


Expert Comment

ID: 2703338
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".


Expert Comment

ID: 2703355
Yeah, you can use chr( 38 ) for '&', of course! And what is more, you can use chr( 32 ) for ' ', chr( 65 ) for 'A', etc...
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.


Author Comment

ID: 2703508
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 !!!!

Expert Comment

ID: 2703560
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.


Author Comment

ID: 2703673
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,

Author Comment

ID: 2703932
Adjusted points from 50 to 100

Expert Comment

ID: 2703955
Sorry, j_brett,

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


Expert Comment

ID: 2704344
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.


Accepted Solution

sbenyo earned 100 total points
ID: 2705045
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.


Expert Comment

ID: 2705765
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


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question