Home » SQL & PL/SQL » SQL & PL/SQL » Mapping column names into variables
Mapping column names into variables [message #208374] Sat, 09 December 2006 23:49 Go to next message
rajthampi
Messages: 28
Registered: December 2006
Location: Kuwait
Junior Member
Hi guys
Is there any methods available for mapping column names into variables and later run statements like Select inside a procedure?
For example I have a procedure like following:

Declare
Fldname varchar2(30):='test_column';
user_id number;
Begin
    Select Fldname into user_id from mytable
    Where something=something;
End;


I had tried this statement adding '&' ahead of Fldname (clipper supported such mapping, not oracle it seems). So will appreciate incase if somebody could suggest me how I can achieve this.

Thanks and regards

[Updated on: Sat, 09 December 2006 23:50]

Report message to a moderator

Re: Mapping column names into variables [message #208376 is a reply to message #208374] Sun, 10 December 2006 02:54 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, dynamically using EXECUTE IMMEDIATE statement. See this example:
DECLARE
  fidname VARCHAR2(30) := 'dname';
  dpt DEPT.dname%TYPE;
  l_stmt VARCHAR2(200);
BEGIN
  l_stmt := 'SELECT ' || fidname || ' from dept where deptno = 10';
  EXECUTE IMMEDIATE (l_stmt) INTO dpt;
  dbms_output.put_line(dpt);
END;
/

Report message to a moderator

Re: Mapping column names into variables [message #208377 is a reply to message #208376] Sun, 10 December 2006 03:02 Go to previous messageGo to next message
rajthampi
Messages: 28
Registered: December 2006
Location: Kuwait
Junior Member
Thank you very much littlefoot. I will try it and let you know the results Smile
regards

Report message to a moderator

Re: Mapping column names into variables [message #208380 is a reply to message #208376] Sun, 10 December 2006 03:42 Go to previous messageGo to next message
rajthampi
Messages: 28
Registered: December 2006
Location: Kuwait
Junior Member
Surprised
Well My program unit attached with the form module looks exactly like following and I need to pass SField Value when a list of choices are given available:
For example, if user selects 'COMPANY' from the choice list, I want to pass 'PAD_CONT_COMP' which is the real column name to the program unit and do a simple record count...
Please have a look at the coding:
PROCEDURE HIGH_LIGHT IS
CHAR_CHART VARCHAR2(30):='ABCDEFGHIJKLMNOPQRSTUVWXYZ';
REC_COUNT NUMBER;
SField varchar2(30):='PAD_CONT_COMP';
BEGIN
 
 FOR I IN 1..26 LOOP
 	REC_COUNT:=0;
 	SELECT COUNT(*) INTO REC_COUNT FROM PAD_DTLS A
 	WHERE 
 --THIS IS WHERE I NEED THE COLUMN NAME
  [B]SUBSTR(SField,1,1)=[/B]SUBSTR(CHAR_CHART,I,1)
  AND USER_ID=:GLOBAL.USER_ID;
   
  IF REC_COUNT = 0 THEN
  		IF I=1 THEN
  		SET_ITEM_PROPERTY('SORTBY.A',ENABLED,PROPERTY_FALSE);
  		ELSIF I=2 THEN
  		SET_ITEM_PROPERTY('SORTBY.B',ENABLED,PROPERTY_FALSE);
  		ELSIF I=3 THEN
  		SET_ITEM_PROPERTY('SORTBY.C',ENABLED,PROPERTY_FALSE);
  		ELSIF I=4 THEN
  		SET_ITEM_PROPERTY('SORTBY.D',ENABLED,PROPERTY_FALSE);
  		ELSIF I=5 THEN
  		SET_ITEM_PROPERTY('SORTBY.E',ENABLED,PROPERTY_FALSE);
			ELSIF I=6 THEN
  		SET_ITEM_PROPERTY('SORTBY.F',ENABLED,PROPERTY_FALSE);
  		ELSIF I=7 THEN
  		SET_ITEM_PROPERTY('SORTBY.G',ENABLED,PROPERTY_FALSE);
  		ELSIF I=8 THEN
  		SET_ITEM_PROPERTY('SORTBY.H',ENABLED,PROPERTY_FALSE);
  		ELSIF I=9 THEN
  		SET_ITEM_PROPERTY('SORTBY.I',ENABLED,PROPERTY_FALSE);
  		ELSIF I=10 THEN
  		SET_ITEM_PROPERTY('SORTBY.J',ENABLED,PROPERTY_FALSE);
  		ELSIF I=11 THEN
  		SET_ITEM_PROPERTY('SORTBY.K',ENABLED,PROPERTY_FALSE);
  		ELSIF I=12 THEN
  		SET_ITEM_PROPERTY('SORTBY.L',ENABLED,PROPERTY_FALSE);
  		ELSIF I=13 THEN
  		SET_ITEM_PROPERTY('SORTBY.M',ENABLED,PROPERTY_FALSE);
  		ELSIF I=14 THEN
  		SET_ITEM_PROPERTY('SORTBY.N',ENABLED,PROPERTY_FALSE);
  		ELSIF I=15 THEN
  		SET_ITEM_PROPERTY('SORTBY.O',ENABLED,PROPERTY_FALSE);
  		ELSIF I=16 THEN
  		SET_ITEM_PROPERTY('SORTBY.P',ENABLED,PROPERTY_FALSE);
  		ELSIF I=17 THEN
  		SET_ITEM_PROPERTY('SORTBY.Q',ENABLED,PROPERTY_FALSE);
  		ELSIF I=18 THEN
  		SET_ITEM_PROPERTY('SORTBY.R',ENABLED,PROPERTY_FALSE);
  		ELSIF I=19 THEN
  		SET_ITEM_PROPERTY('SORTBY.S',ENABLED,PROPERTY_FALSE);
  		ELSIF I=20 THEN
  		SET_ITEM_PROPERTY('SORTBY.T',ENABLED,PROPERTY_FALSE);
  		ELSIF I=21 THEN
  		SET_ITEM_PROPERTY('SORTBY.U',ENABLED,PROPERTY_FALSE);
  		ELSIF I=22 THEN
  		SET_ITEM_PROPERTY('SORTBY.V',ENABLED,PROPERTY_FALSE);
  		ELSIF I=23 THEN
  		SET_ITEM_PROPERTY('SORTBY.W',ENABLED,PROPERTY_FALSE);
  		ELSIF I=24 THEN
  		SET_ITEM_PROPERTY('SORTBY.X',ENABLED,PROPERTY_FALSE);
  		ELSIF I=25 THEN
  		SET_ITEM_PROPERTY('SORTBY.Y',ENABLED,PROPERTY_FALSE);
  		ELSIF I=26 THEN
  		SET_ITEM_PROPERTY('SORTBY.Z',ENABLED,PROPERTY_FALSE);
  		END IF;
  END IF;
  
  	
  		
  END LOOP;
  
END;

Report message to a moderator

Re: Mapping column names into variables [message #208433 is a reply to message #208380] Sun, 10 December 2006 20:20 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!,
are you using forms? i think execute immediate wont work on it, at least up to 6i im not sure of newer forms, you could write a function pass the sql statement there then put the execute imediate inside it. also try EXEC_SQL.

Report message to a moderator

icon14.gif  Re: Mapping column names into variables [message #208450 is a reply to message #208374] Sun, 10 December 2006 23:22 Go to previous message
rajthampi
Messages: 28
Registered: December 2006
Location: Kuwait
Junior Member
Thanks for the suggestions guys. I had managed what I was trying to do from a tip obtained from thinkoracle blogspot entry Smile
I am gonna add the coding over here...may be somebody else will also get benefited in future. The entire manipulation is made possible by using DECODE

PROCEDURE HIGH_LIGHT(fldname varchar2)
IS
CHAR_CHART VARCHAR2(30):='ABCDEFGHIJKLMNOPQRSTUVWXYZ';
REC_COUNT NUMBER;
SField varchar2(30):=fldname;
BEGIN
 
 FOR I IN 1..26 LOOP
 	REC_COUNT:=0;
 	SELECT COUNT(*) INTO REC_COUNT FROM PAD_DTLS A
 	WHERE 
  SUBSTR((DECODE(SField,'PAD_CONT_COMP',PAD_CONT_COMP,'PAD_CONTACT',PAD_CONTACT,
  'PAD_SUBJ',PAD_SUBJ,'PAD_LINK_DEPT',PAD_LINK_DEPT,'PAD_CONT_EMAIL',UPPER(PAD_CONT_EMAIL),NULL)),1,1)=SUBSTR(CHAR_CHART,I,1)
  AND USER_ID=:GLOBAL.USER_ID);
....

Decode decides the right column based on the string value passed to variable SField.

Regards

Report message to a moderator

Previous Topic: How to Use DECODE as a group function with TEXT?
Next Topic: another design issue - suggestion needed
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Dec 05 13:40:06 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.