Home » SQL & PL/SQL » SQL & PL/SQL » Column Value and Data Type of column (Oracle 10g, Windows)
icon10.gif  Column Value and Data Type of column [message #341769] Wed, 20 August 2008 04:32 Go to next message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member

Hi,

I have the following requirement as the output of the following query:

select a.col1, a.col2, b.col3, b.col4, c.col5, c.col6
from table1 a, table2 b, table3 c
where ........

as
a.col1    a.col2    b.col3    b.col4    c.col5    c.col6
1         2         3         4         5         6

Now the requirement is to display the datatype of the all columns involved as

select a.col1 || d.datatype, a.col2 || d.datatype, b.col3 || d.datatype, b.col4 || d.datatype, c.col5 || d.datatype, c.col6 || d.datatype
from table1 a, table2 b, table3 c, user_tab_columns d
where ........

as
a.col1    a.col2    b.col3    b.col4    c.col5    c.col6
1 char    2 char    3 char    4 char    5 char   6 char

Can the same be performed using another method.

Any links would be useful too.

Thanks,
Priya.



Re: Column Value and Data Type of column [message #341772 is a reply to message #341769] Wed, 20 August 2008 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No but you need as many user_tab_columns that there are columns in the result.

Regards
Michel

[Updated on: Wed, 20 August 2008 04:38]

Report message to a moderator

icon10.gif  Re: Column Value and Data Type of column [message #341824 is a reply to message #341769] Wed, 20 August 2008 07:59 Go to previous messageGo to next message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member



Hi,

Exactly, that would not be a feasable solution, where there are large number of rows in the query.

I think so some member might have come accross similar issues.

Any links would be helpful too.

Thanks,
Priya.


Re: Column Value and Data Type of column [message #341828 is a reply to message #341824] Wed, 20 August 2008 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I think so some member might have come accross similar issues.

I don't see any business reason to put the datatype along with the value, so I don't think many have seen this (but for a homework).

Regards
Michel
Re: Column Value and Data Type of column [message #341868 is a reply to message #341824] Wed, 20 August 2008 11:35 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
You might also try using an overloaded function.
Re: Column Value and Data Type of column [message #341871 is a reply to message #341868] Wed, 20 August 2008 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Question Explain what you mean.
How overloaded function can add datatype to the displayed value?

Regards
Michel
Re: Column Value and Data Type of column [message #341883 is a reply to message #341871] Wed, 20 August 2008 12:41 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Well when a function is overloaded the data types and number of parameters passed would be used to determine which version of the function is called. So if you had:

FUNCTION foo(param1 in NUMBER) RETURN VARCHAR2 
IS BEGIN 
...(DO SOMETHING)...
END;

FUNCTION foo(param1 in VARCHAR2) RETURN VARCHAR2
IS BEGIN
...(DO SOMETHING)...
END;


And then called them using

DECLARE
res1 VARCHAR2(10);
res2 VARCHAR2(10);
BEGIN
res1 := foo('HELLO');
res2 := foo(1);
END;


res1 will hold the value returned by the function which accepted a VARCHAR2 and res2 will hold the return of the function which accepted a NUMBER. Try applying the same logic to your problem.

Andrew


EDIT: Sorry Michel didn't read who the reply was from so I directed it at the original poster

[Updated on: Wed, 20 August 2008 12:42]

Report message to a moderator

Re: Column Value and Data Type of column [message #341888 is a reply to message #341883] Wed, 20 August 2008 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But what does this have with the question?
Quote:
the requirement is to display the datatype...


Regards
Michel
Re: Column Value and Data Type of column [message #341906 is a reply to message #341888] Wed, 20 August 2008 13:51 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
You create an overloaded procedure called say SHOW_DATATYPE one for DATE one for NUMBER one for VARCHAR2 that simply returns the name of the data-type DATE version returns the string 'DATE', NUMBER version returns the string 'NUMBER' and so on, your results are the column followed by a call using the column to the function which will return and display the data-type.
Re: Column Value and Data Type of column [message #341908 is a reply to message #341769] Wed, 20 August 2008 13:53 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
Please post code & test data so we can all learn from your solution.
Re: Column Value and Data Type of column [message #341911 is a reply to message #341908] Wed, 20 August 2008 14:00 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
anacedent wrote on Wed, 20 August 2008 13:53
Please post code & test data so we can all learn from your solution.



No, it was not my question I posted a response which points the user in the right direction to finding the solution if you expect more than that you better start paying me.
Re: Column Value and Data Type of column [message #341914 is a reply to message #341911] Wed, 20 August 2008 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I posted a response which points the user in the right direction

This is wrong.

First post (I translate to "working" solution):
SQL> create or replace FUNCTION foo(param1 in NUMBER) RETURN VARCHAR2 
  2  IS BEGIN 
  3    return 'NUMBER';
  4  END;
  5  /

Function created.

SQL> sho err
No errors.
SQL> create or replace FUNCTION foo(param1 in VARCHAR2) RETURN VARCHAR2
  2  IS BEGIN
  3    return 'VARCHAR2';
  4  END;
  5  /

Function created.

SQL> sho err
No errors.
SQL> select count(*) from user_objects where object_name='FOO';
  COUNT(*)
----------
         1

1 row selected.

You can't overload standalone funcions.
Embed them into a package:
SQL> create or replace package pkg is
  2    FUNCTION foo(param1 in NUMBER) RETURN VARCHAR2;
  3    FUNCTION foo(param1 in VARCHAR2) RETURN VARCHAR2;
  4  end;
  5  /

Package created.

SQL> sho err
No errors.
SQL> create or replace package body pkg is
  2    FUNCTION foo(param1 in NUMBER) RETURN VARCHAR2
  3    IS BEGIN 
  4      return 'NUMBER';
  5    END;
  6    FUNCTION foo(param1 in VARCHAR2) RETURN VARCHAR2
  7    IS BEGIN
  8      return 'VARCHAR2';
  9    END;
 10  end;
 11  /

Package body created.

SQL> sho err
No errors.
SQL> DECLARE
  2    res1 VARCHAR2(10);
  3    res2 VARCHAR2(10);
  4  BEGIN
  5    res1 := pkg.foo('HELLO');
  6    res2 := pkg.foo(1);
  7    dbms_output.put_line('res1='||res1||' res2='||res2);
  8  END;
  9  /
res1=VARCHAR2 res2=NUMBER

PL/SQL procedure successfully completed.

SQL> select dummy||' '||pkg.foo(dummy) from dual;
DUMMY||''||PKG.FOO(DUMMY)
---------------------------------------------------------------
X VARCHAR2

1 row selected.

OK, but now add CHAR datatype:
SQL> create or replace package body pkg is
  2    FUNCTION foo(param1 in NUMBER) RETURN VARCHAR2
  3    IS BEGIN 
  4      return 'NUMBER';
  5    END;
  6    FUNCTION foo(param1 in VARCHAR2) RETURN VARCHAR2
  7    IS BEGIN
  8      return 'VARCHAR2';
  9    END;
 10    FUNCTION foo(param1 in CHAR) RETURN VARCHAR2
 11    IS BEGIN
 12      return 'CHAR';
 13    END;
 14  end;
 15  /

Package body created.

SQL> sho err
No errors.
SQL> DECLARE
  2    res1 VARCHAR2(10);
  3    res2 VARCHAR2(10);
  4  BEGIN
  5    res1 := pkg.foo('HELLO');
  6    res2 := pkg.foo(1);
  7    dbms_output.put_line('res1='||res1||' res2='||res2);
  8  END;
  9  /
  res1 := pkg.foo('HELLO');
          *
ERROR at line 5:
ORA-06550: line 5, column 11:
PLS-00307: too many declarations of 'FOO' match this call
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored


SQL> select dummy||' '||pkg.foo(dummy) from dual;
select dummy||' '||pkg.foo(dummy) from dual
                   *
ERROR at line 1:
ORA-06553: PLS-307: too many declarations of 'FOO' match this call

Ouch! it no more works.
Well, we'll say CHAR is VARCHAR2 (by the way we don't know the length).
Try with DATE and TIMESTAMP (of any type) these are really different:
SQL> create or replace package pkg is
  2    FUNCTION foo(param1 in NUMBER) RETURN VARCHAR2;
  3    FUNCTION foo(param1 in VARCHAR2) RETURN VARCHAR2;
  4    FUNCTION foo(param1 in DATE) RETURN VARCHAR2;
  5    FUNCTION foo(param1 in TIMESTAMP) RETURN VARCHAR2;
  6  end;
  7  /

Package created.

SQL> sho err
No errors.
SQL> create or replace package body pkg is
  2    FUNCTION foo(param1 in NUMBER) RETURN VARCHAR2
  3    IS BEGIN 
  4      return 'NUMBER';
  5    END;
  6    FUNCTION foo(param1 in VARCHAR2) RETURN VARCHAR2
  7    IS BEGIN
  8      return 'VARCHAR2';
  9    END;
 10    FUNCTION foo(param1 in DATE) RETURN VARCHAR2
 11    IS BEGIN
 12      return 'DATE';
 13    END;
 14    FUNCTION foo(param1 in TIMESTAMP) RETURN VARCHAR2
 15    IS BEGIN
 16      return 'TIMESTAMP';
 17    END;
 18  end;
 19  /

Package body created.

SQL> sho err
No errors.
SQL> DECLARE
  2    res1 VARCHAR2(10);
  3    res2 VARCHAR2(10);
  4  BEGIN
  5    res1 := pkg.foo(sysdate);
  6    res2 := pkg.foo(systimestamp);
  7    dbms_output.put_line('res1='||res1||' res2='||res2);
  8  END;
  9  /
  res2 := pkg.foo(systimestamp);
          *
ERROR at line 6:
ORA-06550: line 6, column 11:
PLS-00307: too many declarations of 'FOO' match this call
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored

Too bad, it does not work either.

Now show us the right direction.

Regards
Michel

Re: Column Value and Data Type of column [message #341918 is a reply to message #341914] Wed, 20 August 2008 14:28 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Is it a perfect solution no? But I would say it is a more useful jumping off point than demanding his business reason for making the request or pointing him in the direction of the posting FAQ which seems to have become the popular way to respond to people learning Oracle these days.
Re: Column Value and Data Type of column [message #341919 is a reply to message #341918] Wed, 20 August 2008 14:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is NOT a perfect solution, it is a NOT working one.

You should at least check what you post.

Regards
Michel

[Updated on: Wed, 20 August 2008 14:35]

Report message to a moderator

Re: Column Value and Data Type of column [message #341920 is a reply to message #341919] Wed, 20 August 2008 14:47 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
It is a method which can be used effectively to find what the OP wanted My original post simply told to OP to use overloaded functions to accomplish what he wanted which is a far cry better than using the tab_column tables which in practice is an unworkable solution equivalent to simply pasting the word VARCHAR2 or DATE as a literal in the next column.
Re: Column Value and Data Type of column [message #341925 is a reply to message #341920] Wed, 20 August 2008 15:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
which in practice is an unworkable solution

But it is the only one that, as far I can see from OP (no-)post, does not need to be "workable". (Actually it is workable as it works even if it would not be in a real case that does not exist.)

Regards
Michel
Re: Column Value and Data Type of column [message #341926 is a reply to message #341769] Wed, 20 August 2008 15:11 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
> My original post simply told to OP to use overloaded functions
yes, you did & I suspect an audience of 1 believes this is a viable solution. I certainly don't think it can actually be implemented.
Re: Column Value and Data Type of column [message #341931 is a reply to message #341926] Wed, 20 August 2008 15:16 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
anacedent wrote on Wed, 20 August 2008 15:11
> My original post simply told to OP to use overloaded functions
yes, you did & I suspect an audience of 1 believes this is a viable solution. I certainly don't think it can actually be implemented.


I have implemented just such a solution in a situation where I needed to tell date from varchar2 from number. In fact I posted about it here at one point, actually about the situation in which no data-type is defined for a column. I mean arguing about the completeness of my example is one thing, but the disbelief I do not really understand.
Re: Column Value and Data Type of column [message #341932 is a reply to message #341769] Wed, 20 August 2008 15:20 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
I am all for learning new (for me) Oracle tricks.

>actually about the situation in which no data-type is defined for a column.

Please post DDL that produces such a column.
Re: Column Value and Data Type of column [message #341933 is a reply to message #341932] Wed, 20 August 2008 15:28 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
anacedent wrote on Wed, 20 August 2008 15:20
I am all for learning new (for me) Oracle tricks.

>actually about the situation in which no data-type is defined for a column.

Please post DDL that produces such a column.



Well you could have tried to find the original post which discussed the topic, but the specific implementation I am talking about was used to transform queries into XML Spreadsheet (Excel) a data-typeless column would be one filled in by the user as simply a NULL which would often be used in a situation where the user was unioning two similar queries together that needed some null spacers to fill in missing columns from one query or another.
Re: Column Value and Data Type of column [message #341934 is a reply to message #341931] Wed, 20 August 2008 15:29 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
To chime in with another possible solution, which might or might not work for the original poster :

Most database drivers can get the information about what format the columns of the Result set have in the client program.

For example with the ResultSetMetaData in the of the JDBC driver.

Example

ResultSetMetaData Methods for Information about a Result Set's Columns

   1. int getColumnCount();
   2. int getColumnDisplaySize(int column);
   3. String getColumnLabel(int column);
   4. String getColumnName(int column);
   5. String getSchemaName(int column);
   6. int getPrecision(int column);
   7. int getScale(int column);
   8. String getTableName(int column);
   9. String getCatalogName(int column);
  10. int getColumnType(int column);
  11. String getColumnTypeName(int column);


Which could perhaps also be implemented with a Java stored procedure in the database itself somehow depending on the actual need.

icon10.gif  Re: Column Value and Data Type of column [message #341972 is a reply to message #341934] Wed, 20 August 2008 23:39 Go to previous messageGo to next message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member


Hi,

Michel, you pointed it out correctly, that the package
SQL> create or replace package pkg is
  2    FUNCTION foo(param1 in NUMBER) RETURN VARCHAR2;
  3    FUNCTION foo(param1 in VARCHAR2) RETURN VARCHAR2;
  4    FUNCTION foo(param1 in DATE) RETURN VARCHAR2;
  5    FUNCTION foo(param1 in TIMESTAMP) RETURN VARCHAR2;
  6    FUNCTION foo(param1 in CHAR) RETURN VARCHAR2;
  7  end;
  8  /

Package created.

SQL> show error
No errors.

SQL> create or replace package body pkg is
  2    FUNCTION foo(param1 in NUMBER) RETURN VARCHAR2
  3    IS BEGIN 
  4      return 'NUMBER';
  5    END;
  6    FUNCTION foo(param1 in VARCHAR2) RETURN VARCHAR2
  7    IS BEGIN
  8      return 'VARCHAR2';
  9    END;
 10    FUNCTION foo(param1 in DATE) RETURN VARCHAR2
 11    IS BEGIN
 12      return 'DATE';
 13    END;
 14    FUNCTION foo(param1 in TIMESTAMP) RETURN VARCHAR2
 15    IS BEGIN
 16      return 'TIMESTAMP';
 17    END;
 18    FUNCTION foo(param1 in CHAR) RETURN VARCHAR2
 19    IS BEGIN
 20      return 'CHAR';
 21    END;
 22  end;
 23  /

Would not work for DATE, TIMESTAMP, CHAR, ... datatypes.

I found one more thing that this procedure cannot idenity is whether VARCHAR2 lenght is what? E.g. VARCHAR2(10) or VARCHAR2(20).

So till now only thing which can be concluded is only user_tab_columns can be performed for such reqirements.

Thanks,
Priya.

Re: Column Value and Data Type of column [message #342013 is a reply to message #341972] Thu, 21 August 2008 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I found one more thing that this procedure cannot idenity is whether VARCHAR2 lenght is what? E.g. VARCHAR2(10) or VARCHAR2(20).

This is what I said:
Quote:
...Well, we'll say CHAR is VARCHAR2 (by the way we don't know the length)...


Regards
Michel

[Updated on: Thu, 21 August 2008 01:18]

Report message to a moderator

Re: Column Value and Data Type of column [message #342014 is a reply to message #341972] Thu, 21 August 2008 01:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is this something you want for a one-time-only query or is it the desired output for a variety of (yet unknown) queries?
If you'd finally tell us what the reason is behind this weird output, we might be able to help you better.
Re: Column Value and Data Type of column [message #342124 is a reply to message #341934] Thu, 21 August 2008 07:37 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
You could also look at the DBMS_SQL package, it would be a PL/SQL route instead of a SQL one which may not meet your requirements but could be used to perform something similar to ThomasG's suggestion.
Re: Column Value and Data Type of column [message #342133 is a reply to message #342124] Thu, 21 August 2008 08:06 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
./fa/1597/0/

Regards,
Rajat
Previous Topic: API Error
Next Topic: Query procedure last executed
Goto Forum:
  


Current Time: Tue Dec 06 00:25:38 CST 2016

Total time taken to generate the page: 0.12781 seconds