Re: SQL problem

From: Mark D Powell <markp7832_at_my-deja.com>
Date: Wed, 06 Sep 2000 14:21:53 GMT
Message-ID: <8p5jt9$hmd$1@nnrp1.deja.com>

In article <8p5fpb$c6tvb$1_at_ID-3532.news.cis.dfn.de>,   "Johannes" <jori_at_young-world.com> wrote:
> Hi everybody,
>
> does anyone know the reason why following statment produces just a
> string-output, but is
> not executed?
>
> entered statement: select 'select * from <SchemaOwner>.' ||
 table_name ||
> ';' from dba_tables where table_name
 like
> '%TPFHT';
>
> output-string: select * from <SchemaOwner>.xyzTPFHT;
>
> Thanx for every tip!
>
> Bye
> Johannes
>

You got the results you did because that is what you asked for! You are using SQL to generate a list of text strings that just happen to be SQL statements. If you want to execute them you should either spool them to a .sql file and start the file from sqlplus or use dynamic SQL inside a pl/sql module in a loop.

example Sql*Plus solution -

set pagesize 0   <== sqlplus command eliminate page breaks/headings
set linesize 120 <== ensure each command is a single text line
set feedback off <== eliminate number rows process statement
set echo     off <== eliminate echo sql statement in spool/screen
spool x.sql      <== create sql command file

select 'SQL_Commands' from dict_table;
spool off

set echo     on   <== show commands as executed
set feedback on   <== show success/failure
start x           <== execute the generated commands

This should help you.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Sep 06 2000 - 09:21:53 CDT