Re: Two questions

"Fred S" <fredofred_at_hotmail.com> wrote in message news:3eca9578$0$23972$626a54ce_at_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...

look at the ACCEPT command in sqlplus, documented in the sqlplus users guide.

>
> 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 suspect you are using Char datatypes - which are fixed width - rather than varchar2 which aren't.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************