Two questions

From: Fred S <fredofred_at_hotmail.com>
Date: Tue, 20 May 2003 22:41:13 +0200
Message-ID: <3eca9578$0$23972$626a54ce@news.free.fr>

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
because the blanks aren't part of the NAME value but are a displayed to format the column.
So my question is how to do to have INSERT INTO tableB (NAME) VALUES('toto'); as an answer
from my request. Can I fit the number of chars of a column to each variable length or can I eliminate
these blanks chars ?

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