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 Go to next message
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 Go to previous messageGo to next message
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: execute a command inside variable [message #241186 is a reply to message #241185] Mon, 28 May 2007 13:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use "execute immediate" but you have to prefix your variable name with : and if it is PL/SQL enclose it between "begin" and "end".
SQL> declare
  2    v1 number(2);
  3    v2 number(2);
  4    lCmd varchar2(200);
  5  begin
  6    v2:= 10;
  7    lCmd := 'begin :v1 := :v2; end;';
  8    execute immediate lcmd using out v1, in v2;
  9    dbms_output.put_line('v1='||v1);
 10  end;
 11  /
v1=10

PL/SQL procedure successfully completed.

Regards
Michel
Re: Get a field from a Record Type [message #241218 is a reply to message #241184] Mon, 28 May 2007 22:26 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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: execute a command inside variable [message #241459 is a reply to message #241457] Tue, 29 May 2007 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can't do that and I don't see anyway to do it.

Regards
Michel
Re: Get a field from a Record Type [message #241474 is a reply to message #241458] Tue, 29 May 2007 14:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
As I said in the other topic, you can't do that and I don't see how you can do it.

Regards
Michel
Re: Get a field from a Record Type [message #241883 is a reply to message #241474] Wed, 30 May 2007 17:25 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Get a field from a Record Type [message #241960 is a reply to message #241883] Thu, 31 May 2007 03:56 Go to previous message
mbalves
Messages: 20
Registered: May 2007
Junior Member
Hi Barbara,

Nice solution. I'm trying to adapt to my problem.

Thanks, Miguel
Previous Topic: problem in enabling and disabling constraints
Next Topic: SQL SELECT DOUBT ORA 9i
Goto Forum:
  


Current Time: Thu Dec 12 05:01:51 CST 2024