?
Solved

PL/SQL, turning off substitution variable

Posted on 2000-04-11
11
Medium Priority
?
3,495 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
[X]
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
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

770 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