How to get the String value of a column name in Oracle SQL

Question

The following SQL is trying to select some values, where it satisfies the condition that

select cd as key_type, decode
from general_code 
where key_type = 'A_MAP_TYPE'
and cd in (
    select distinct(A_MAP_TYPE)
    from sales_channel
);

A_MAP_TYPE is a column name (and FK) in table sales_channel, and it is being referenced in general_code (as PK).

I realize that this SQL is non portable in that when the name of A_MAP_TYPE changes, i.e. to B_MAP_TYPE, then the corresponding SQL needs to be changed. Is there a way to do something like PL/SQL's TABLE_NAME.COLUMN_NAME%TYPE in this SQL?

mservidio · Accepted Answer · 2011-08-24 02:58:34Z

3

You could use dynamic SQL. It would probably require some changes to the way your query is setup now, but should be doable.

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm

You basically would need to turn your current query into a string, and execute the string. Where your current A_Map_ field is, that would be a parameter. For the first case you would need to quote it so that it's still a string within the executing string. For the second A_Map_ field, you would put quotes around it,

answered Aug 24, 2011 at 2:58

mservidio

13.1k9 gold badges61 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cade Roux · Accepted Answer · 2011-08-24 03:01:28Z

2

Not really. You can build dynamic SQL and use execute immediate, but I wouldn't recommend that.

It's also possible to code generate it using the column metadata. At this point it's like a dynamically generated CREATE VIEW or CREATE PROC - it's only done once and compiled into a database object.

You can have an exception process which inspects the metadata against data in the table to find things which may need to be updated.

answered Aug 24, 2011 at 3:01

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Collectives™ on Stack Overflow

How to get the String value of a column name in Oracle SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related