Multiple row generation from DUALselect rownum i from dual connect by level <= 16
Above query works only in 10g
select rownum from dual a, dual b connect by level <>
This query works on both 9i and 10g
Desc queryIn oracle, you would have used desc to describe the table. Here is how to generate DESC output through sql query.
SELECT column_name || ' ' || decode(nullable, 'N', 'not null', ' ') || ' ' || data_type ||
decode(data_type, 'NUMBER', decode('' || nvl(data_precision, -1), '-1', '', '(' || data_precision || ',' || data_scale || ')'),
'(' || data_length || ')') fields
WHERE TABLE_NAME = 'PO_VENDORS';
How to convert row values into columns. or how to perform multi dimensional query?For Eg
Here you need to display the report in following format. That is comparative sales analysis of each department between 2005 and 2006.
The challenge is to bring the row values into columns. And here is the solution.
SELECT dept, sum(decode(year,'2005',amount,0)) "2005",
group by dept;
How can one dump/ examine the exact content of a database column?
SELECT DUMP (col1) FROM tab1 WHERE cond1 = val1;
Typ=96 Len=4: 65,66,67,32
For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.
Find out foreign constraints referring your table
select table_name, constraint_name from user_constraints
where r_constraint_name IN (select constraint_name from user_constraints where table_name = 'XML_DOCUMENT' and constraint_type = 'P')
and constraint_type = 'R' ;