Re: Two questions

Comments embedded...

fredofred_at_hotmail.com said...
> 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...
>

For this particular output, enter: set verify off

There are other 'set' commands that you might need, which are documented in the SQL*Plus manual. You might also look up the ACCEPT command. You might also use double ampersands, which doesn't repeat the request if you've already entered a value for myVar (though you then have to include UNDEFINE commands in your script).

I normally start all my SQL scripts with:

  set echo off feedback off verify off pause off   set pagesize 0 linesize 200 trimspool on

I don't always need all of them, but...

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

Instead of trying to put the whole INSERT statement in a single string, you might benefit from using the concatenate operator, thus:

  SELECT 'INSERT INTO tableB (NAME) VALUES('

         || chr(39) || NAME || chr(39) || ')...'...

When you use commas instead of concatenates, Oracle outputs them as separate fields and includes a "field separator" between each field. That's why you have spaces on either side of TOTO.

> I would like to avoid shell scripting...
> Thank you for your help and sorry for my english mistakes !
>
> Fred
>

-- 
/Karsten
DBA > retired > DBA