Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Two questions
Hi,
i'm writing a SQL script and the result of my script requests has
to be another script. I have two problems :
1/ I use variables in my script and when I do this, Oracle shows the
substitutions of my variables, by example, if I got the line
SELECT * FROM tableA WHERE value=&myVar
then Oracle outputs for example:
old : SELECT * FROM tableA WHERE value=&myVar
new : SELECT * FROM tableA WHERE value=5
But I don't want this output since I want reuse this output as a script
So, is there a mean to disable this output or to write it as a comment
(I've tried "SET heading off" but it only disables the command echo,
i've also tried "SET feedback off" but it only disables results like "5 rows
updated",
the "SET echo off" and "set show off" doesn't work neither)
If somebody got an idea...
2/ I want to select some lines to be used as an insert request on another
database
so I do something like this
SELECT 'INSERT INTO tableB (NAME) VALUES(', NAME, ');' FROM tableB WHERE
...;
Unfortunately, if my name value is a string I have to add quotes around the
name, so I add
'''' (4 quotes) before and after the NAME (by the way I don't know if this
is the good way to do this)
Then it gives me :
SELECT 'INSERT INTO tableB (NAME) VALUES(','''', NAME,'''', ');' FROM tableB
WHERE ...;
but I still have a problem, because if my NAME column is 20 chars wide and
my name is toto then I
got this answer :
INSERT INTO tableB (NAME) VALUES(' toto ');There are too many blanks, I've tried to suppress them with LTRIM and RTRIM but it doesn't work
I would like to avoid shell scripting... Thank you for your help and sorry for my english mistakes !
Fred Received on Tue May 20 2003 - 15:41:13 CDT
![]() |
![]() |