Solved

PL/SQL, turning off substitution variable

Posted on 2000-04-11
11
2,785 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
 

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

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now