SQL & PL/SQL

There are probably many ways of doing this. The first one that springs to mind is to create a view based on your query, then describe it, .i.e

CREATE OR REPLACE VIEW my_complex_view AS SELECT * FROM hr.employees;
desc my_complex_view

Name           Null     Type        

-------------- -------- ------------

EMPLOYEE_ID    NOT NULL NUMBER(6)   

FIRST_NAME              VARCHAR2(20)

LAST_NAME      NOT NULL VARCHAR2(25)

EMAIL          NOT NULL VARCHAR2(25)

PHONE_NUMBER            VARCHAR2(20)

HIRE_DATE      NOT NULL DATE        

JOB_ID         NOT NULL VARCHAR2(10)

SALARY                  NUMBER(8,2) 

COMMISSION_PCT          NUMBER(2,2) 

MANAGER_ID              NUMBER(6)   

DEPARTMENT_ID           NUMBER(4)   

If it's just for checking I would use dump(column_name) instead of column_name - DUMP

Regards

Etbin

select dump(empno,10,1,1),dump(ename,10,1,1),dump(job,10,1,1),dump(mgr,10,1,1),dump(hiredate,10,1,1),dump(sal,10,1,1),dump(comm,10,1,1),dump(deptno,10,1,1)

  from emp

where mgr is not null

   and comm is not null

   and rownum = 1

Having lots of columns the projection (or even the complete query) can be generated rather easily

select listagg('dump('||lower(column_name)||',10,1,1)',', ') within group (order by column_id) projection

  from user_tab_cols

where table_name = 'EMP'

There are many ways to check the data type of columns (a couple good ones have already been suggested).

However, perhaps you don't even need to do this. You said you want to check the column data types to answer another question: Why a MINUS operation produces an unexpected output. You suspect there is a mismatch in data types. The answer to this higher-level question is NO - the unexpected result cannot be produced by different data types. If the columns didn't have the same data types in a MINUS operation you would get an error, with an error message, telling you exactly that - unless your query is not actually a query, but included in a PL/SQL block, with the nonsensical but still very common "when others" clause in the error-checking block.

If you need more help figuring out why the MINUS operation produces the wrong result, I am afraid you will need to share (part of) your code and some small amount of representative data.

Cheers,   mathguy

There's a small probability of missing minus is a set operation.

Is the following OK for you ?

with

a as

(select 1 col from dual union all

select 2 from dual union all

select 2 from dual union all

select 2 from dual union all

select 3 from dual

),

b as

(select 1 col from dual union all

select 2 from dual union all

select 3 from dual union all

select 3 from dual union all

select 4 from dual

)

select col from a

select col from b

select col from a

minus

select col from b

no data found

select col from a b

minus

select col from b a

Regards

Etbin

There's a small probability of missing minus is a set operation.

Huh? Please explain that.

select col from a
minus
select col from b
no data found
select col from a
minus
select col from b
  

DId you mean to reverse the order of the queries for the second test and select from 'b' first?

If the columns didn't have the same data types in a MINUS operation you would get an error

I think I know what you meant but the way you say it above just isn't the right rule. The datatypes do NOT have to be the same - they just need to be 'compatible'.

https://docs.oracle.com/database/121/SQLRF/queries004.htm#SQLRF52341

The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same data type group (such as numeric or character).

And for comparison purposes that isn't the worst of it. There are further implicit conversions that can occur that can cause a datatype and/or value to appear differently to Oracle during a UNION [ALL], INTERSECT, MINUS than they appear when either query result is examined individually.

https://docs.oracle.com/database/121/SQLRF/queries004.htm#SQLRF52341

If component queries select character data, then the data type of the return
values are determined as follows:
    If both queries select values of data type CHAR of equal length,
    then the returned values have data type CHAR of that length. If the
    queries select values of CHAR with different lengths, then the returned 
    value is VARCHAR2 with the length of the larger CHAR value.
    If either or both of the queries select values of data type VARCHAR2,
    then the returned values have data type VARCHAR2.
If component queries select numeric data, then the data type of the return
values is determined by numeric precedence:
    If any query selects values of type BINARY_DOUBLE, then the returned
    values have data type BINARY_DOUBLE.
    If no query selects values of type BINARY_DOUBLE but any query selects
    values of type BINARY_FLOAT, then the returned values have data type BINARY_FLOAT.
    If all queries select values of type NUMBER, then the returned values
    have data type NUMBER.
In queries using set operators, Oracle does not perform implicit conversion 
across data type groups. Therefore, if the corresponding expressions of component
queries resolve to both character data and numeric data, Oracle returns an error.

The above shows that (for char) the datatype of the result may be different than either of the datatypes of the source.

It also shows that for BINARY_DOUBLE and BINARY_FLOAT there may be implicit conversions involved. If those conversions are from NUMBER to one of the binaries then precision can be lost since NUMBER values are stored in a decimal floating point format while the binaries are stored in a binary format.

This test code illustrates what CAN happen.

drop table a
drop table b
create table a (chr5 char(5), chr10 char(10), nbr32 number(3,2), bdouble binary_double)
create table b (chr5 char(5), chr10 char(10), nbr32 number(3,2), bdouble binary_double)
insert into a values ('abcde', 'abcde', 1/3, 1/3)
insert into b values ('abcde', 'abcde', 1/3, 1/3)
select bdouble from a
minus select nbr32 from b
0.3333333333333333
select chr5 from a
minus select chr10 from b
abcde
create table c as 
select chr5 vchar from a
minus select chr10 from b
desc c
Name  Null Type    
 
----- ---- ------------ 
VCHAR  
VARCHAR2(10) 

In the first example the MINUS reports one row. The 'nbr32' value may look like it is the same as the 'bdouble' value to a human depending on what tool is used to display it, what value they actually entered and the actual scale/precision involved.

I just used a simple number(3,2) to illustrate the concept. With the simple example it will be easy to see the values are really different but with a spec like number(15, 12) some tools will just display some number like '.3333333333' and the value may display the same as the bdouble.

The second text example makes it easier to see the implicit conversion effect.  The result datatype is actually VARCHAR2(10) even though both source datatypes were CHAR.

And the result reports one row of difference even though to a human the values look the same.

I am trying to perform a MINUS on the result set of this query with a result set from another table.. I am not sure, however that it is giving me the correct result.
To test it I limited both the query and the table to the same 2 rows and performed the MINUS operation, expecting 0 rows displayed. I still got 2 rows showing, so wondered if this is because data types diff between the query and the table?

Yes - it actually is possible for that to occur due to certain data type differences. But, as others have stated, the MOST COMMON reason is when the datatypes are the same but the data itself is different.

And a 'common' reason for that is because people THINK the data is the same because the tool they use to view the data makes it look the same even though the data in the DB is really different.

Confusing? You betcha!  Depending on the tool used and the way the display is configured the tool may not be displaying the FULL value of the data. For example a numeric column may actually be longer than what is displayed by the tool and so could be different in a position the tool doesn't display.

See my reply to another responder for the reasons and for the doc link and quotes that explain it.

Is there a way to check the data types of each column of the large query after running it in SQL developer?

There is no way to know without seeing the query.

Although others have suggested creating a view from the query that is NOT always possible.

For example this is a perfectly valid query

select bdouble, bdouble from a
0.3333333333333333    0.3333333333333333

But you can't create a table or view from it

create view e as select bdouble, bdouble from a
Error starting at line : 5 in command -
create view e as select bdouble, bdouble from a
Error report -
SQL Error: ORA-00957: duplicate column name
00957. 00000 -  "duplicate column name"
*Cause:    
*Action:

The query returns multiple values with the same column name so you will get an exception if you try to create a view or table

This is also a valid query

select bdouble, 1 + 3 from a
0.3333333333333333    4

But you get an exception if you try to create a view or table

create view f as select bdouble, 1 + 3 from a
Error starting at line : 5 in command -
create view f as select bdouble, 1 + 3 from a
Error report -
SQL Error: ORA-00998: must name this expression with a column alias
00998. 00000 -  "must name this expression with a column alias"
*Cause:    
*Action:

The query has an expression but no alias to provide a 'name' for a column of a view or table.

Although others have suggested a view I prefer to create a table from the query when possible.

The reason is to actually preserve the data you are using. The main reason you are doing this is for analysis. It is much easier to analyze (and reanalyze) data that is persisted in a table that to try to do it from the query results of a view.

This thread is a PERFECT EXAMPLE of how a seemingly simple question does NOT necessarily have a simple answer. The devil is in the details. Unfortunately those 'details' are in the actual query and the DDL of the tables involved that you didn't post.

The OP suspected that the incorrect result of a minus operation might be due to non-matching data types of corresponding columns, but mathguy mentioned that minus operator would throw an error when corresponding columns data types don't match, I thought about the possibility of OP missing the fact that minus operator works on sets showing two examples (a single row in table b containing 2 annihilates three rows from table a containing the same value and similarly two rows from table b containing 3  annihilate a single row in table a containing the same value), but unfortunately I failed to reverse a and b in my second example.

Going to correct my previous post.

Regards

Etbin

OraFAQ Forum: Client Tools » Check data types of columns from large query

Barbara Boehmer wrote:
OraFAQ Forum: Client Tools » Check data types of columns from large query 

Barbara,

Please avoid responding with just links to other websites/blogs.  See the FAQ:   section titled "I have an excellent blog entry that answers a very complex user question. Can I just post a link as the answer?" which applies to blogs and other links.

In addition to what BluShadow said the reply provided by that link is likely wrong.

In the above example, the differences are in the usage of CHAR versus VARCHAR2 data types for the dname and ename columns. The CHAR data type pads the data with spaces up to 14 characters, whereas the VARCHAR2 data type does not, causing the data to be different.

That statement at best is misleading and, at worst, is just wrong.

It is comparing the 'view' datatypes with the datatypes from one of the queries. But it does NOT show the datatypes from the other table/query. So it does NOT show 'the usage of CHAR versus VARCHAR2 data types for the dname and ename columns' of the actual source tables. It shows them for the view and ONE of the source tables.

And, as I showed in my reply, Oracle can (and does) use a different datatype for the result for some use cases involving CHAR.

Just review the sample I provided that shows both source tables using CHAR with different lengths and Oracle using VARCHAR2 for the result column.

BluShadow wrote:
Barbara Boehmer wrote:
OraFAQ Forum: Client Tools » Check data types of columns from large query 
Barbara,
Please avoid responding with just links to other websites/blogs. See the FAQ: Oracle Community – General FAQ section titled "I have an excellent blog entry that answers a very complex user question. Can I just post a link as the answer?" which applies to blogs and other links.

The link that I posted was to my response to this same question by this same poster on the other forum.  It just seemed easier than re-posting the entire response here and trying to format it properly using the editor here.  Since that is apparently not allowed, I will attempt to re-post and format the content of that post here.

-- If you have a query with some joins that returns a limited number of rows, like this:

SCOTT@orcl_12.1.0.2.0> select d.deptno, d.dname, e.empno, e.ename
  2  from  dept d, emp e
  3  where  d.deptno = e.deptno
  4  and    d.deptno = 10
  5  /
    DEPTNO DNAME              EMPNO ENAME
---------- -------------- ---------- ----------
        10 ACCOUNTING          7782 CLARK
        10 ACCOUNTING          7839 KING
        10 ACCOUNTING          7934 MILLER
3 rows selected.

-- and you have another query on a single table that appears to return the same data:

SCOTT@orcl_12.1.0.2.0> select deptno, dname, empno, ename
  2  from  test
  3  /
    DEPTNO DNAME              EMPNO ENAME
---------- -------------- ---------- ----------
        10 ACCOUNTING          7782 CLARK
        10 ACCOUNTING          7839 KING
        10 ACCOUNTING          7934 MILLER
3 rows selected.

-- and you use minus to subtract the data from one query from the other, but still get the same rows:

SCOTT@orcl_12.1.0.2.0> select d.deptno, d.dname, e.empno, e.ename
  2  from  dept d, emp e
  3  where  d.deptno = e.deptno
  4  and    d.deptno = 10
  5  minus
  6  select deptno, dname, empno, ename
  7  from  test
  8  /
    DEPTNO DNAME              EMPNO ENAME
---------- -------------- ---------- ----------
        10 ACCOUNTING          7782 CLARK
        10 ACCOUNTING          7839 KING
        10 ACCOUNTING          7934 MILLER
3 rows selected.

-- in order to compare the data types easily, you can create a view of your query:

SCOTT@orcl_12.1.0.2.0> create or replace view your_query as
  2  select d.deptno, d.dname, e.empno, e.ename
  3  from  dept d, emp e
  4  where  d.deptno = e.deptno
  5  and    d.deptno = 10
  6  /
View created.

-- then you can describe the view and describe the other table and compare the differences:

SCOTT@orcl_12.1.0.2.0> describe your_query
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
DEPTNO                                    NOT NULL NUMBER(2)
DNAME                                              VARCHAR2(14)
EMPNO                                    NOT NULL NUMBER(4)
ENAME                                              VARCHAR2(10)
SCOTT@orcl_12.1.0.2.0> describe test
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
DEPTNO                                            NUMBER
DNAME                                              CHAR(14)
EMPNO                                              NUMBER
ENAME                                              CHAR(10)
SCOTT@orcl_12.1.0.2.0>

In the above example, the differences are in the usage of CHAR versus VARCHAR2 data types for the dname and ename columns. The CHAR data type pads the data with spaces up to 14 characters, whereas the VARCHAR2 data type does not, causing the data to be different.

Please reread my reply to you showing why that 'solution' is, at best, misleading and at worst just wrong.

You can't compare the datatypes of the view with one of the source table columns. You have to compare the source table column datatypes themselves.

The reasons are the ones I stated in my other replies and the example I provided illustrates the problem.