Home » SQL & PL/SQL » SQL & PL/SQL » Get Dynamic column name and corresponding value (Oracle,10.2.0.4.0,WinXP)
| Get Dynamic column name and corresponding value [message #488871] |
Tue, 11 January 2011 20:12  |
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
|
Senior Member |
|
|
Hi Friends,
I want get dynamic column names and its corresponding values
Consider the below sample code
CREATE TABLE LECTURER1
( ID NUMBER(5,0),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(20),
MAJOR VARCHAR2(30),
CURRENT_CREDITS NUMBER(3,0),
PRIMARY KEY (ID));
insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS, ROWID)
values (10001, 'Scott', 'Lawson', 'Computer Science', 11, 'AAAM1FAAEAAAAGeAAA');
insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS, ROWID)
values (10002, 'Mar', 'Wells', 'History', 4, 'AAAM1FAAEAAAAGeAAB');
insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS, ROWID)
values (10003, 'Jone', 'Bliss', 'Computer Science', 8, 'AAAM1FAAEAAAAGeAAC');
insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS, ROWID)
values (10004, 'Man', 'Kyte', 'Economics', 8, 'AAAM1FAAEAAAAGeAAD');
insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS, ROWID)
values (10005, 'Pat', 'Poll', 'History', 4, 'AAAM1FAAEAAAAGeAAE');
insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS, ROWID)
values (10006, 'Tim', 'Viper', 'History', 4, 'AAAM1FAAEAAAAGeAAF');
insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS, ROWID)
values (10007, 'Barbara', 'Blues', 'Economics', 7, 'AAAM1FAAEAAAAGeAAG');
insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS, ROWID)
values (10008, 'David', 'Large', 'Music', 4, 'AAAM1FAAEAAAAGeAAH');
insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS, ROWID)
values (10009, 'Chris', 'Elegant', 'Nutrition', 8, 'AAAM1FAAEAAAAGeAAI');
insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS, ROWID)
values (10010, 'Rose', 'Bond', 'Music', 7, 'AAAM1FAAEAAAAGeAAJ');
insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS, ROWID)
values (10011, 'Rita', 'Johnson', 'Nutrition', 8, 'AAAM1FAAEAAAAGeAAK');
insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS, ROWID)
values (10012, 'Sharon', 'Clear', 'Computer Science', 3, 'AAAM1FAAEAAAAGeAAL');
CREATE TABLE lecturer_cols(col_name VARCHAR2(50),col_val VARCHAR2(50));
INSERT INTO lecturer_cols(col_name,col_val) VALUES ('ID','10001,10002');
COMMIT;
And the procedure is below
create or replace procedure p_dynaget_col_vals(o_res OUT SYS_REFCURSOR) IS
v_sql VARCHAR2(100);
v_sql2 VARCHAR2(100);
BEGIN
v_sql:='SELECT * FROM lecturer ';
FOR i IN (SELECT col_name,col_val FROM lecturer_cols)
LOOP
v_sql2:=CASE WHEN v_sql2 IS NULL THEN v_sql2||' WHERE '||
i.col_name||' IN ('||i.col_val||')' ELSE v_sql2||' AND '||i.col_name||'='||
i.col_val||')' END;
END LOOP;
-- dbms_output.put_line(v_sql||''||v_sql2);
v_sql:= 'OPEN o_res FOR '||v_sql||v_sql2;
dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql;
END p_dynaget_col_vals;
Please help me to get the dynamic column values
Thanks & Regards,
Anil MK
[Updated on: Tue, 11 January 2011 23:18] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Get Dynamic column name and corresponding value [message #488980 is a reply to message #488871] |
Wed, 12 January 2011 10:31  |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You should not be trying to insert rowids. Instead of checking whether v_sql2 is null, a standard method is to make the first condition "where 1=1", then you can use "and" to start the rest of the conditions, and you can simplify it by using just one variable v_sql and concatenating. Once you have your v_sql, then you can open your ref cursor using that. You should not be making the "open ... for" part of your v_sql and there is no need for execute immediate. When you either enclose your query in quotes or put it in a variable, using "open ... for" opens it dynamically. I have provided corrected code below. However, this is an akward design and it would be much better to design your tables such that they can be joined on common columns and avoid the need for dynamic sql.
-- test environment with attempted insertion of rowids removed:
SCOTT@orcl_11gR2> CREATE TABLE LECTURER
2 (ID NUMBER(5,0),
3 FIRST_NAME VARCHAR2(20),
4 LAST_NAME VARCHAR2(20),
5 MAJOR VARCHAR2(30),
6 CURRENT_CREDITS NUMBER(3,0),
7 PRIMARY KEY (ID))
8 /
Table created.
SCOTT@orcl_11gR2> BEGIN
2 insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS)
3 values (10001, 'Scott', 'Lawson', 'Computer Science', 11);
4
5 insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS)
6 values (10002, 'Mar', 'Wells', 'History', 4);
7
8 insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS)
9 values (10003, 'Jone', 'Bliss', 'Computer Science', 8);
10
11 insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS)
12 values (10004, 'Man', 'Kyte', 'Economics', 8);
13
14 insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS)
15 values (10005, 'Pat', 'Poll', 'History', 4);
16
17 insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS)
18 values (10006, 'Tim', 'Viper', 'History', 4);
19
20 insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS)
21 values (10007, 'Barbara', 'Blues', 'Economics', 7);
22
23 insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS)
24 values (10008, 'David', 'Large', 'Music', 4);
25
26 insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS)
27 values (10009, 'Chris', 'Elegant', 'Nutrition', 8);
28
29 insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS)
30 values (10010, 'Rose', 'Bond', 'Music', 7);
31
32 insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS)
33 values (10011, 'Rita', 'Johnson', 'Nutrition', 8);
34
35 insert into lecturer (ID, FIRST_NAME, LAST_NAME, MAJOR, CURRENT_CREDITS)
36 values (10012, 'Sharon', 'Clear', 'Computer Science', 3);
37 END;
38 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> CREATE TABLE lecturer_cols
2 (col_name VARCHAR2(50),
3 col_val VARCHAR2(50))
4 /
Table created.
SCOTT@orcl_11gR2> INSERT INTO lecturer_cols(col_name,col_val)
2 VALUES ('ID','10001,10002')
3 /
1 row created.
-- corrected procedure and execution:
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE p_dynaget_col_vals
2 (o_res OUT SYS_REFCURSOR)
3 AS
4 v_sql VARCHAR2 (32767);
5 BEGIN
6 v_sql := 'SELECT * FROM lecturer WHERE 1 = 1';
7 FOR i IN
8 (SELECT col_name, col_val FROM lecturer_cols)
9 LOOP
10 v_sql := v_sql || ' AND ' || i.col_name || ' IN (' || i.col_val || ')';
11 END LOOP;
12 DBMS_OUTPUT.PUT_LINE (v_sql);
13 OPEN o_res FOR v_sql;
14 END p_dynaget_col_vals;
15 /
Procedure created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> VARIABLE g_res REFCURSOR
SCOTT@orcl_11gR2> EXECUTE p_dynaget_col_vals (:g_res)
SELECT * FROM lecturer WHERE 1 = 1 AND ID IN (10001,10002)
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> COLUMN first_name FORMAT A10
SCOTT@orcl_11gR2> COLUMN last_name FORMAT A9
SCOTT@orcl_11gR2> COLUMN major FORMAT A16
SCOTT@orcl_11gR2> PRINT g_res
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
---------- ---------- --------- ---------------- ---------------
10001 Scott Lawson Computer Science 11
10002 Mar Wells History 4
2 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Aug 27 18:44:41 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
