sql statement help

[beechill4]

Hi,

i have written a piece of sql to query an oracle database from a dts package:

SELECT 'select count(*) from' + TABLE_NAME +';'
FROM USER_TABLES

Now, i want the output of this sql to execute in as straightforward a way as possible.

Could someone advise the best way to do this please.

thanks alot

 

[DrSql]

Can you clarify, you trying to execute the sql statement towards an oracel DB from DTS package?
To execute the statement

[tt]
declare @data char(255)
select @data= 'select count(*) from' + TABLE_NAME +';'
FROM USER_TABLES
execute ('@data')
[/tt]

Is this what you are trying to accomplish.



Dr.Sql
Good Luck.

 

[beechill4]

hi,

this is what i am trying to do. I have very little privileges from an oracle viewpoint have have loads on sql server

i used your code in a dts package but got an unexpected error message?

 

[beechill4]

hI,

i am now using the sql below:

declare @data char(255)
select @data= 'select count(*) from' + TABLE_NAME +';'
FROM dba_TABLES where owner ='OPVAR_LOCAL'
execute (@data)

i still get the 'unexpected error' message in my dts packae but when i run it in query analyser i get 1 result:

tablename
1

Then when i run it again i get a count of one but it shows up a different table name

??????

 

[DrSql]

you need quotes
execute ('@data')

Dr.Sql
Good Luck.

 

[DrSql]

What you are trying to exceute to towards Oracle DB you might have few issues. The way approch is ask your DBA to create view with your SQL statemnt that way the dat will be avialable rather than you trying to execute some statment towards and oracle db. Other option
[tt]
truncate dba_TABLES
[/tt]
then using a DTS import the data. Then you can run any sql statments towards the new table. This avoid any syntax or security conflicts.

Dr.Sql
Good Luck.