Home » SQL & PL/SQL » SQL & PL/SQL » function required (oracle 10g)
function required [message #426051] Wed, 14 October 2009 00:31 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,
what is the function for finding out the column where data type is number and varchar values are stored?
Re: function required [message #426053 is a reply to message #426051] Wed, 14 October 2009 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
user_tab_columns

Regards
Michel
Re: function required [message #426056 is a reply to message #426053] Wed, 14 October 2009 00:43 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
select * from user_tab_columns where table_name='X'

Where i need to check in this.
Re: function required [message #426063 is a reply to message #426056] Wed, 14 October 2009 00:48 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
SQL>desc user_tab_cols;
 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)
 HIDDEN_COLUMN                                                                                VARCHAR2(3)
 VIRTUAL_COLUMN                                                                               VARCHAR2(3)
 SEGMENT_COLUMN_ID                                                                            NUMBER
 INTERNAL_COLUMN_ID                                                                  NOT NULL NUMBER
 HISTOGRAM                                                                                    VARCHAR2(15)
 QUALIFIED_COL_NAME                                                                           VARCHAR2(4000)

SQL>select distinct data_type from user_tab_cols;

DATA_TYPE
----------------------------------------------------------------------------------------------------
TIMESTAMP(6)
NUMBER
CHAR
UROWID
BINARY_DOUBLE
TIMESTAMP(9)
DATE
VARCHAR2

8 rows selected.

Cannot you understand this?

regards,
Delna

[Updated on: Wed, 14 October 2009 00:50]

Report message to a moderator

Re: function required [message #426099 is a reply to message #426056] Wed, 14 October 2009 01:57 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
rajasekhar857 wrote on Wed, 14 October 2009 06:43
select * from user_tab_columns where table_name='X'

Where i need to check in this.

In Oracle?
As usual, your questions are somewhat unclear. Please spend some time and effort in composing your questions. I realise that English isn't your first language, but that just means that you should be putting in even more effort. GL.
Re: function required [message #426118 is a reply to message #426099] Wed, 14 October 2009 02:41 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, if I parse the question

Quote:

what is the function for finding out the column where data type is number and varchar values are stored?


He is looking for a column with the data type NUMBER in which VARCHAR values are stored.

Such a column is impossible to exist, so there is no use trying to find it.
Re: function required [message #426123 is a reply to message #426118] Wed, 14 October 2009 02:47 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
ThomasG wrote on Wed, 14 October 2009 08:41
Well, if I parse the question

Quote:

what is the function for finding out the column where data type is number and varchar values are stored?


He is looking for a column with the data type NUMBER in which VARCHAR values are stored.

Such a column is impossible to exist, so there is no use trying to find it.

Ahhh, this is true. I mis-read it, assuming that the OPactually meant that he was looking to find the datatypes of columns. My point still stands with
Quote:
Where i need to check in this.
More effort is required when posting questions.
Re: function required [message #426142 is a reply to message #426051] Wed, 14 October 2009 03:31 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hmmmm....
I think, ThomasG sir is right.
And in that case my previous answer can not direct to OP's requirement.

regards,
Delna
Re: function required [message #426178 is a reply to message #426142] Wed, 14 October 2009 05:24 Go to previous message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
delna.sexy wrote on Wed, 14 October 2009 09:31
Hmmmm....
I think, ThomasG sir is right.
And in that case my previous answer can not direct to OP's requirement.

regards,
Delna

Just for clarity, who is this post a reply to Delna?
Previous Topic: utl_smtp question?
Next Topic: SQL PROBLEM FOR MERGING THE RECORD
Goto Forum:
  


Current Time: Fri Dec 09 06:14:36 CST 2016

Total time taken to generate the page: 0.11038 seconds