Home » SQL & PL/SQL » SQL & PL/SQL » data_type as condition (Oracle)
data_type as condition [message #329217] Tue, 24 June 2008 10:01 Go to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
Could anyone help me? I would like to select all columns which meet condition their data type is varchar2?
Re: data_type as condition [message #329220 is a reply to message #329217] Tue, 24 June 2008 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


query USER_TAB_COLUMNS
Re: data_type as condition [message #329221 is a reply to message #329220] Tue, 24 June 2008 10:22 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
Hi anacedent. Thank you for replying me and thank you for point me to the posting guidelines. If you do not mind, please tell me if I offended something. I could not figure out.

I might need to refine my question: I have a table MYTAB, I would like to select all columns whose data type are varchar2, for instance. How do I do it?

I would like to know do could I link MYTAB to USER_TAB_COLUMNS?
Thanks.
Re: data_type as condition [message #329222 is a reply to message #329217] Tue, 24 June 2008 10:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member

SQL> desc user_tab_columns
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                               NOT NULL VARCHAR2(30)
 DATA_TYPE                                          VARCHAR2(106)
 DATA_TYPE_MOD                                      VARCHAR2(3)
 DATA_TYPE_OWNER                                    VARCHAR2(30)
 DATA_LENGTH                               NOT NULL NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 NULLABLE                                           VARCHAR2(1)
 COLUMN_ID                                          NUMBER
 DEFAULT_LENGTH                                     NUMBER
 DATA_DEFAULT                                       LONG
 NUM_DISTINCT                                       NUMBER
 LOW_VALUE                                          RAW(32)
 HIGH_VALUE                                         RAW(32)
 DENSITY                                            NUMBER
 NUM_NULLS                                          NUMBER
 NUM_BUCKETS                                        NUMBER
 LAST_ANALYZED                                      DATE
 SAMPLE_SIZE                                        NUMBER
 CHARACTER_SET_NAME                                 VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                               NUMBER
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 AVG_COL_LEN                                        NUMBER
 CHAR_LENGTH                                        NUMBER
 CHAR_USED                                          VARCHAR2(1)
 V80_FMT_IMAGE                                      VARCHAR2(3)
 DATA_UPGRADED                                      VARCHAR2(3)
 HISTOGRAM                                          VARCHAR2(15)
Re: data_type as condition [message #329226 is a reply to message #329222] Tue, 24 June 2008 10:44 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
You could try these:


select column_name
from user_tab_columns
where 1=1
  and data_type = 'VARCHAR2'
  and table_name = <yourtablehere>

Re: data_type as condition [message #329227 is a reply to message #329221] Tue, 24 June 2008 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Reference

Regards
Michel
Re: data_type as condition [message #329230 is a reply to message #329222] Tue, 24 June 2008 10:59 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
As I know, I can first get a column list from user_tab_columns

select column_name
from user_tab_columns
where user_tab_columns.table_name = 'tab'
and user_tab_columns.data_type in ('varchar2', 'char')

Then parse them into a string like 'col1, col2, col3'.

Then incorporate this string into a dynamic query:

'select ' || thisString || ' from tab'

I would like to know is there easier way to do it. Such as:

select *
from tab
where tab.user_tab_columns.data_type in ('varchar2', 'char');

Thanks
Re: data_type as condition [message #329232 is a reply to message #329217] Tue, 24 June 2008 11:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I would like to know is there easier way to do it.
NO

What problem are you really, really trying to solve?
Re: data_type as condition [message #329238 is a reply to message #329232] Tue, 24 June 2008 11:22 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
select *
from tab
where (these columns' data_type is varchar2)

That is what I want. Thanks.
Re: data_type as condition [message #329243 is a reply to message #329238] Tue, 24 June 2008 11:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why?

What would be the use of a query that selects the contents of only the varchar2 columns?
If you tell us, we might offer you an alternative.
Re: data_type as condition [message #329246 is a reply to message #329243] Tue, 24 June 2008 12:36 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
Yes, to get records their data type are only varchar2. That is what I want.

The reason I need this is I try to write a procedure to clean up them. The steps would be:

1. Get a view consists of all fields of varchar2 and char type.
2. Declare a cursor or ref cursor (I have not figured out yet).
3. Open cursor or ref cursor to traverse every field.
4. Parse and update every field.
5. Close cursor or ref cursor.

A part from the issue of data_type as query condition, another big problem is with the cursor or ref cursor.

Since I do not know the columns' name, all columns' name will work as parameter when deal with "update set where". This forces me to build a dynamic update query inside of procedure. The problem is neither cursor nor ref cursor would give me a location reference (where current of) of exact field the cursor/ref cursor works with. In other words, reference of cursor or ref cursor could not be passed into dynamic query.

Please help me.
Re: data_type as condition [message #329248 is a reply to message #329217] Tue, 24 June 2008 12:44 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Rhetorical question - If you don't know the column name(s), how can/will you ensure they get new correct values?

To me this sounds like - Ready, Fire, Aim!
Re: data_type as condition [message #329252 is a reply to message #329248] Tue, 24 June 2008 13:22 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
I think that is why my boss hires me.

Regarding how to make sure the correctness of the value, it would depend on how to implement the business rules.

Actually, I have never see a procedure to traverse all fields in a table.
Re: data_type as condition [message #329253 is a reply to message #329217] Tue, 24 June 2008 13:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

You should not expect any real assistance from here because nobody here knows what you have & we don't know precisely what the desired results are to be.

You're On Your Own (YOYO)!

Re: data_type as condition [message #329256 is a reply to message #329246] Tue, 24 June 2008 13:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
So you get a bunch of columns. You are totally clueless of their names, their functional meaning, etc.
Then you have to update them. Update to what? What should be the new value of the third column in the fourth table?
Re: data_type as condition [message #329262 is a reply to message #329256] Tue, 24 June 2008 15:17 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
Change all payees' name to my name for varchar2 type, and change all values to my bank account number for bank account composited record type.

How does this sound?
Re: data_type as condition [message #329263 is a reply to message #329262] Tue, 24 June 2008 15:18 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
registereduser wrote on Tue, 24 June 2008 13:17

How does this sound?


It sounds like you are wasting everyone's time.
Re: data_type as condition [message #329264 is a reply to message #329263] Tue, 24 June 2008 15:26 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
Dear anacedent, I am sorry you feel that way. I apologize for my joke.

I finished my codeing on first part, not test yet:

create or replace procedure DictionaryTable
( tabName varchar2
, type1 varchar2
, type2 varchar2
, type3 varchar2
) is

newTab varchar2 (50)

begin newTab := Trim(tabName) || '_ColumnNames';

if type3 is not null
then execute immediate
'create or replace table ' || newTab || ' as '
' select * ' ||
' from user_tab_columns ' ||
' where table_name = ' || tab ||
' and type = ' || type1 ||
' or type = ' || type2 ||
' or type = ' || type3
;
commit;
elsif type2 is not null
then execute immediate
'create or replace table ' || newTab || ' as '
' select * ' ||
' from user_tab_columns ' ||
' where table_name = ' || tab ||
' and type = ' || type1 ||
' or type = ' || type2
;
commit;
else execute immediate
'create or replace table ' || newTab || ' as '
' select * ' ||
' from user_tab_columns ' ||
' where table_name = ' || tab ||
' and type = ' || type1
;
commit;
end elsif;

end DictionaryTable;
/

create or replace function ColumnToString
( tabColName varchar2
, delim varchar2
)
return varchar2 is

tabName varchar2 (50) := GetToken(tabColName, '.', 1);
colName varchar2 (50) := GetToken(tabColName, '.', 2);

oDelim varchart (20) := MyTrim(delim);
nDelim varchart (21) := oDelim || ' ';

sResult varchar2 (1000) := "";
// Initialization

refCur is ref cursor return tabName%rowtype;

crs refCur is
select tabColName
from tabName;
crsRead tabName.colName%type;

begin open crs;
loop fetch crs
into crsRead;
exit when csr%notfound;
sResult = sResult || crsRead || nDelim;
end loop;
close crs;

sResult := TrimString(sResult, nDelim)
return sResult;

end ColumnToString;
/

create or replace procedure SelectDataType
( tab varchar2
, nTab varchar2
, type1: varchar2
, type2: varchar2
, type3: varchar2
) is

fields varchar2 (1000);

begin fields := ColumnToString(DictionaryTable(tab, type1, type2, type3), ',')

execute immediate
'create or replace table ' || nTab || ' as ' ||
'select ' || fields ||
' from ' || tab
;
commit;

end SelectDataType
/
Re: data_type as condition [message #329265 is a reply to message #329217] Tue, 24 June 2008 15:32 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

I am puzzled because I saw no UPDATE in posted code.
Have the requirements changed from those previously stated?

Both SQL Reference Manual & PL/SQL Reference Manual can be found at http://tahiti.oracle.com

It is apparent that you have not read either of them.
They can answer all subsequent questions you may have.

[Updated on: Tue, 24 June 2008 17:00] by Moderator

Report message to a moderator

Previous Topic: Any ideas how to concatenate two fields depending on length?
Next Topic: using a package variable for a function argument (urgent)
Goto Forum:
  


Current Time: Fri Dec 09 19:22:48 CST 2016

Total time taken to generate the page: 0.06946 seconds