| select all columns except one from a table [message #44126] |
Tue, 04 November 2003 05:43  |
ArunKumar
Messages: 6
Registered: November 2001
|
Junior Member |
|
|
|
Select all the columns except one column from a table. Without specifying the columns to be selected in the selection list. Without using the view. I want to select say 998 columns from a table of 1000 columns. How can we do this in oracle using a query?
|
|
|
|
|
|
| Re: select all columns except one from a table [message #44135 is a reply to message #44130] |
Wed, 05 November 2003 03:24  |
ArunKumar
Messages: 6
Registered: November 2001
|
Junior Member |
|
|
Thanks a lot William.
Yes, I do think that we cannot do this with a single static sql.
I checked with my friends and listed out all the possibilities. I have attached it here with this.
We can retrieve
the subset of columns from a table without mentioning the column names to be selected in many
ways.
1. we can create a view with the set of columns required.
sql>create view view_name as select columns_list from table_name;
Henceforth instead of specifying the list of columns in the sql for retrieval you can say
sql>select * from view_name;
--- developers need not typein all the column names again and again.
2. the second one and the third one which i have told is exactly the same with your answer. you
can retrieve the set of column names from the user_tab_columns or all_tab_columns(oracle)
except the column which are not required. You can use the column_id to select the first set of
columns, say first 10 columns.
sql>select column_name from user_tab_columns where column_id < = 10;
--- this is based on the order of the columns
sql>select column_name from user_tab_columns where column_name not in
('unwanted_column_name');
--- this is based on the columns to be excluded
Now we have the set of column names. In the stored procedure we can retrieve this result
set in a cursor and frame the dynamic sql with the set of column names. let this stored
procedure return the result set.
Soln with the stored procedure to exclude the list of columns:
PROCEDURE select_excluding_column_list(excluding_column_names VARCHAR2) IS
TYPE RefCurTyp IS REF CURSOR;
column_name_list RefCurTyp;
result_set RefCurTyp;
column_names user_tab_columns.column_name%TYPE;
dynamic_query VARCHAR2(1000);
BEGIN
OPEN column_name_list FOR ’SELECT column_name FROM user_tab_columns where
column_name not in (’ || excluding_column_names || ')';
LOOP
FETCH column_name_list INTO column_names;
EXIT WHEN column_name_list%NOTFOUND;
dynamic_query := dynamic_query || column_names || ',';
END LOOP;
CLOSE column_name_list;
dynamic_query := 'SELECT ' || substr(dynamic_query,1,length(dynamic_query-1)) || ' FROM
table_name ';
OPEN result_set FOR dynamic_query;
-- print the resultset
CLOSE dynamic_query
END;
(Note: The same routine can be implemented with the column_id in case of, if you want to
select first n no. of columns. In oracle column_id is the order of the column in the table. In
SQL server and sybase its Ordinal_position)
3. We can say that the database design is poor.
4. We can restrict the access to the particular columns of the table using Fine grained access
depends on who has logged in to the database.
|
|
|
|
| Re: select all columns except one from a table [message #44136 is a reply to message #44135] |
Wed, 05 November 2003 07:06 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
Regarding option 2, it would be better to pass in a collection (array) of column names, rather than a comma-separated string. That way you can avoid some of the rebuilding/reparsing/recaching. e.g:
<pre style="color: navy; font-size: small;">CREATE OR REPLACE TYPE VARCHAR2_TT AS TABLE OF VARCHAR2(4000)
/
CREATE OR REPLACE FUNCTION tab_cols_demo
( p_table_name VARCHAR2
, p_exclude_columns VARCHAR2_TT DEFAULT NULL )
RETURN VARCHAR2
AS
v_column_list VARCHAR2(1000);
BEGIN
FOR r IN (
SELECT column_name
FROM user_tab_columns
WHERE table_name = UPPER(p_table_name)
AND column_name NOT IN
( SELECT column_value
FROM TABLE(p_exclude_columns) )
ORDER BY column_id )
LOOP
v_column_list := v_column_list || ', ' || r.column_name;
END LOOP;
v_column_list := LTRIM(v_column_list,', ');
RETURN v_column_list;
END tab_cols_demo;
/
SQL> set autoprint on
SQL> var list VARCHAR2(1000)
SQL> exec :list := tab_cols_demo('TARIFFS')
LIST
-----------------------------------------------------------------------------
TAR_ID, NAME, CHARGE, PERIOD, START_DATE, END_DATE
SQL> exec :list := tab_cols_demo('TARIFFS', VARCHAR2_TT('CHARGE','END_DATE'))
LIST
-----------------------------------------------------------------------------
TAR_ID, NAME, PERIOD, START_DATE
</pre>
You're still stuck with doing it the hard way for the actual dynamic query of course.
Point 3 I would agree with ;-)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
