OraFAQ Forum: Server Administration » help with dinamic sql

Home » RDBMS Server » Server Administration » help with dinamic sql

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

help with dinamic sql [message #57448]

Mon, 16 June 2003 15:56

Eugene
Messages: 44
Registered: August 2001

Member

I need to come up with sql that dinamically build a list of columns for the table. Can someone help me with that, plese? Here is the sql that I am running:

select column_name||','
from all_tab_columns
where table_name = 'PHPICK00' and
column_name not in ('BATCH_ID','ARCHIVE_BY','ARCHIVE_DATE')
order by column_id

Thanks,
Eugene

Report message to a moderator

Re: help with dinamic sql [message #57450 is a reply to message #57448]

Mon, 16 June 2003 23:56

Maaher
Messages: 7065
Registered: December 2001

Senior Member

With ALL_TAB_COLUMNS, you must be sure that there aren't two schema's containing the same table. You'd better either include 'AND OWNER = 'some_owner'' or use USER_TAB_COLUMNS.

SELECT Decode(Sign(rownum-1),1,',','')||column_name
FROM user_tab_columns
WHERE table_name = 'some_table';

The query will place columns before every column name except the first one.

Since we don't know what is the meaning of this query, we can't help you any further. What do you want to do with the result afterwards?

MHE

Report message to a moderator

Re: help with dinamic sql [message #57461 is a reply to message #57450]

Tue, 17 June 2003 08:37

Eugene
Messages: 44
Registered: August 2001

Member

Thanks Maaher,
How about adding owner = (schema_owner) to the query?
All I need is to build dynamic sql to be like that:
select (list of columns)
from dba_tab_columns
where owner = (schema owner) and
table_name = (table name)

Is it possible via dynamic sql?

Eugene

Report message to a moderator

Re: help with dinamic sql [message #57473 is a reply to message #57461]

Wed, 18 June 2003 04:44

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Yes, adding the owner 'll do the trick. Look at the examples of dynamic SQL on otn (otn.oracle.com/documentation)...

MHE

Report message to a moderator

Previous Topic:	Calling stored procedure from VC++
Next Topic:	ORA-04031 when compling java source

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 11:36:49 CDT 2024