Solved

PL/SQL, turning off substitution variable

Posted on 2000-04-11
11
2,869 Views
Last Modified: 2012-08-13
Hi,

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

Thanks
0
Comment
Question by:j_brett
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 5

Expert Comment

by:sbenyo
ID: 2703316
Give an example of how you want to use '&'.

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


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

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

Now look at this:

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

This prints &Question

0
 
LVL 2

Expert Comment

by:AndrewRodionov
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".

Andrew
0
 
LVL 2

Expert Comment

by:AndrewRodionov
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...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:j_brett
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:

SELECT *
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 !!!!
0
 
LVL 2

Expert Comment

by:AndrewRodionov
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.

Andrew.
0
 

Author Comment

by:j_brett
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,
                Justin
0
 

Author Comment

by:j_brett
ID: 2703932
Adjusted points from 50 to 100
0
 
LVL 2

Expert Comment

by:AndrewRodionov
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.

Andrew.
0
 
LVL 2

Expert Comment

by:SamratAshok
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.



0
 
LVL 5

Accepted Solution

by:
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.

0
 
LVL 4

Expert Comment

by:sudhi022299
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

Regards,
Sudhi.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

810 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