Home » SQL & PL/SQL » SQL & PL/SQL » Get a field from a Record Type (merged)
Get a field from a Record Type (merged) [message #241184] |
Mon, 28 May 2007 12:41 |
mbalves
Messages: 20 Registered: May 2007
|
Junior Member |
|
|
Hi,
I want to develop a function who return the value of a field of a table record. The record and the field are parameters:
Create or Replace Function getValue(pRec some_table%rowtype, pField varchar2) return varchar2 is ...
pField is a field of some_table.
Considerer that all data types are varchar2.
I don´t want something like:
lSql := 'Select ' || pField || ' From ' || some_table || ' Where ...';
execute immediate lSql ...
because is to use in a trigger with :new as parameter. I want this because, in a trigger, I select some fields of data dictionary that I want the value and the fields that I want the values depends of some conditions and is not always the same fields.
Best, MBA
|
|
|
execute a command inside variable [message #241185 is a reply to message #241184] |
Mon, 28 May 2007 12:48 |
mbalves
Messages: 20 Registered: May 2007
|
Junior Member |
|
|
Hi,
do you know if it is possible to execute a non SQL command inside of a varchar variable?
Example:
v1 number(2);
v2 number(2);
lCmd varchar2(200);
v2:= 10;
lCmd := 'v1 := v2';
....
/* I want to execute the command inside lCmd */
mainly, I want to know if it is really possible to execute a non SQL command inside of a varchar variable because I try to check some solutions but I don't find anything useful for my case.
Best, MBA
|
|
|
|
Re: Get a field from a Record Type [message #241218 is a reply to message #241184] |
Mon, 28 May 2007 22:26 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
So the name of a column of some_table is stored in the variable pField. When you call the function, you pass in a whole record of a fixed record type, and wish to return a particular attribute of that record depending on the value of pField. Right?
I don't think there's any way to do this with native PL/SQL, you would have to use dynamic PL/SQL.
I've never tried it before, but it would be something like:
sqlString := '
BEGIN
:var := pRec.' || pField || ';
END;
';
EXECUTE IMMEDIATE sqlString USING IN OUT myVar;
Read more about it in the doco.
Ross Leishman
|
|
|
Re: execute a command inside variable [message #241457 is a reply to message #241186] |
Tue, 29 May 2007 12:57 |
mbalves
Messages: 20 Registered: May 2007
|
Junior Member |
|
|
Hi Michel,
I already find my problem. In an EXECUTE IMMEDIATE statement is not allowed non SQL types. I tried to pass a record type. Under I list the program that I can´t compile.
Thanks for your help, Miguel
Create Table t1(f1 number(10) primary key, f2 char(2), f3 char(2), f4 char(2), f5 char(2));
insert into t1 values (1, 'a1', 'b1', 'c1', 'd1');
insert into t1 values (2, 'a2', 'b2', 'c2', 'd2');
insert into t1 values (3, 'a3', 'b3', 'c3', 'd3');
insert into t1 values (4, 'a4', 'b4', 'c4', 'd4');
insert into t1 values (5, 'a5', 'b5', 'c5', 'd5');
create Or Replace Function getValue (rect1 t1%rowtype, pField varchar2) return char is
lCommand varchar2(200);
returnValue char(2);
begin
lCommand := 'begin :returnValue := :rect1.' || pField || '; end;';
execute immediate lCommand using out returnValue, in rect1;
return returnValue;
end;
/
|
|
|
Re: Get a field from a Record Type [message #241458 is a reply to message #241218] |
Tue, 29 May 2007 13:04 |
mbalves
Messages: 20 Registered: May 2007
|
Junior Member |
|
|
Hi Ross,
your code didn´t work because he don't recognize the varialble prec inside the instruction. If you put something like "... :var:= :pRec." and try to pass a rowtype variable to the command, send a error saying that expressions have to be of SQL Types. Under, I list the program that i try to compile.
Thanks, Miguel
Create Table t1(f1 number(10) primary key, f2 char(2), f3 char(2), f4 char(2), f5 char(2));
insert into t1 values (1, 'a1', 'b1', 'c1', 'd1');
insert into t1 values (2, 'a2', 'b2', 'c2', 'd2');
insert into t1 values (3, 'a3', 'b3', 'c3', 'd3');
insert into t1 values (4, 'a4', 'b4', 'c4', 'd4');
insert into t1 values (5, 'a5', 'b5', 'c5', 'd5');
create Or Replace Function getValue (rect1 t1%rowtype, pField varchar2) return char is
lCommand varchar2(200);
returnValue char(2);
begin
lCommand := 'begin :returnValue := :rect1.' || pField || '; end;';
execute immediate lCommand using out returnValue, in rect1;
return returnValue;
end;
/
|
|
|
|
|
Re: Get a field from a Record Type [message #241883 is a reply to message #241474] |
Wed, 30 May 2007 17:25 |
|
Barbara Boehmer
Messages: 9100 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The following may not be exactly what you want, but I believe it meets your stated requirements, demonstrates the functionality, and should give you the general idea. It uses a sql type and passes a record of that type and dynamically retrieves the value of a variable column from that record. However, just responding to this one piece of the puzzle without knowing the rest of your problem, I have to wonder if this is necessary, or if there is an easier way to accomplish your ultimate goal.
SCOTT@10gXE> CREATE TABLE t1
2 (f1 NUMBER (10) PRIMARY KEY,
3 f2 CHAR ( 2),
4 f3 CHAR ( 2),
5 f4 CHAR ( 2),
6 f5 CHAR ( 2))
7 /
Table created.
SCOTT@10gXE> BEGIN
2 INSERT INTO t1 VALUES (1, 'a1', 'b1', 'c1', 'd1');
3 INSERT INTO t1 VALUES (2, 'a2', 'b2', 'c2', 'd2');
4 INSERT INTO t1 VALUES (3, 'a3', 'b3', 'c3', 'd3');
5 INSERT INTO t1 VALUES (4, 'a4', 'b4', 'c4', 'd4');
6 INSERT INTO t1 VALUES (5, 'a5', 'b5', 'c5', 'd5');
7 END;
8 /
PL/SQL procedure successfully completed.
SCOTT@10gXE> CREATE OR REPLACE TYPE t1_typ AS OBJECT
2 (f1 NUMBER (10),
3 f2 CHAR ( 2),
4 f3 CHAR ( 2),
5 f4 CHAR ( 2),
6 f5 CHAR ( 2))
7 /
Type created.
SCOTT@10gXE> CREATE OR REPLACE TYPE t1_tab AS TABLE OF t1_typ;
2 /
Type created.
SCOTT@10gXE> CREATE OR REPLACE FUNCTION getValue
2 (p_rect1 t1_typ,
3 p_Field VARCHAR2)
4 RETURN CHAR
5 AS
6 v_rect1 t1_tab := t1_tab();
7 v_Command VARCHAR2 (200);
8 v_returnValue CHAR ( 2);
9 BEGIN
10 v_rect1.EXTEND;
11 v_rect1(v_rect1.COUNT) := p_rect1;
12 v_Command := 'SELECT t.' || p_field || ' FROM TABLE (:b_rect1) t';
13 EXECUTE IMMEDIATE v_Command INTO v_returnValue USING v_rect1;
14 RETURN v_returnValue;
15 END getValue;
16 /
Function created.
SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> CREATE OR REPLACE FUNCTION test_getValue
2 (p_f1 IN NUMBER,
3 p_field IN VARCHAR2)
4 RETURN CHAR
5 AS
6 v_rect1 t1_tab := t1_tab();
7 v_returnval CHAR (2);
8 BEGIN
9 FOR r IN (SELECT * FROM t1 WHERE f1 = p_f1) LOOP
10 v_rect1.EXTEND;
11 v_rect1(v_rect1.COUNT) := t1_typ (r.f1, r.f2, r.f3, r.f4, r.f5);
12 END LOOP;
13 v_returnval := getValue (v_rect1(1), p_field);
14 RETURN v_returnval;
15 END test_getValue;
16 /
Function created.
SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> COLUMN get_f2 FORMAT A6
SCOTT@10gXE> COLUMN get_f3 FORMAT A6
SCOTT@10gXE> COLUMN get_f4 FORMAT A6
SCOTT@10gXE> COLUMN get_f5 FORMAT A6
SCOTT@10gXE> SELECT f1,
2 test_getvalue (f1, 'f2') AS get_f2,
3 test_getvalue (f1, 'f3') AS get_f3,
4 test_getvalue (f1, 'f4') AS get_f4,
5 test_getvalue (f1, 'f5') AS get_f5
6 FROM t1
7 /
F1 GET_F2 GET_F3 GET_F4 GET_F5
---------- ------ ------ ------ ------
1 a1 b1 c1 d1
2 a2 b2 c2 d2
3 a3 b3 c3 d3
4 a4 b4 c4 d4
5 a5 b5 c5 d5
SCOTT@10gXE>
[Updated on: Wed, 30 May 2007 17:33] Report message to a moderator
|
|
|
Re: Get a field from a Record Type [message #241951 is a reply to message #241883] |
Thu, 31 May 2007 03:01 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Yep, nice solution; and good call on the unlikliness of its "fit for purpose".
The necessity to switch from PL/SQL to SQL inside a trigger is going to have a huge effect on performance.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Thu Dec 12 05:01:51 CST 2024
|