Skip to Main Content
  • Questions
  • Referencing %rowtype variables without using column names

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Lindsay.

Asked: December 09, 2001 - 6:10 pm UTC

Last updated: February 06, 2013 - 8:04 am UTC

Version: 8.1.7.2.5

Viewed 10K+ times! This question is

You Asked

I have a 3rd party product (which can't be changed) which needs to call a PL/SQL stored procedure (which I am writing) for which an input parameter is a %rowtype variable. I need to create many of these stored procedures.

The problem I am having is - I want to "extract" the values of the columns from this %rowtype variable without "hard coding" the name of each column. I can lookup up the column names in user_tab_columns but am unable to "dereference" the column names to get its values from the %rowtype variable.

What I would like to do is of the following form (pseudo code) -

procedure p_get_col_values (p_table_A%rowtype) is
d_var varchar2(255);
cursor c_tab_cols (c_table_name) is
select column_name, data_type, column_id
from user_tab_columns
where table_name = c_table_name
order by column_id;
begin
..
for c_rec in c_tab_cols loop
if c_rec.data_type = 'DATE' then
d_var := to_char("value_of"(c_rec.column_name), 'dd-Mon-yyyy hh24:mi:ss);
...
elsif c_rec.data_type = 'NUMBER' then
d_var := to_char("value_of"(c_rec.column_name));
..
else
..
end if;

I have had a look at

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:235814350980 <code>

but I don't have a cursor.

What I'm trying to achieve is similar in functionality to the NAME_IN function in Developer Forms or in C this would be a case of double indirection. I am unsure if this can be achieved in PL/SQL. Any help would be greatly appreciated.

Lindsay Hess

PS. Love the book 'Expert One-on-One'

and Tom said...

Well, since you love the book -- i'll tell you one way to do this. It'll be less performant then coding the name cause we have to use dynamic sql, but we can do it.

It can look like this:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package demo_pkg
2 as
3 emp_rec emp%rowtype;
4
5 -- other records here for other tables
6 end;
7 /

Package created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure your_procedure( p_record in emp%rowtype )
2 as
3 l_data long;
4 begin
5 demo_pkg.emp_rec := p_record;
6
7 for x in ( select column_name, data_type
8 from user_tab_columns
9 where table_name = 'EMP' )
10 loop
11 if ( x.data_type = 'DATE' )
12 then
13 execute immediate
14 'begin
15 :x := to_char( demo_pkg.emp_rec.' || x.column_name || ',
16 ''dd-mon-yyyy hh24:mi:ss'' );
17 end;' using OUT l_data;
18 else
19 execute immediate
20 'begin
21 :x := demo_pkg.emp_rec.' || x.column_name || ';
22 end;' using OUT l_data;
23 end if;
24 dbms_output.put_line( x.column_name || ' = ' || l_data );
25 end loop;
26 end;
27 /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
2 for x in ( select * from emp where rownum = 1 )
3 loop
4 your_procedure( x );
5 end loop;
6 end;
7 /
EMPNO = 7369
ENAME = A
JOB = CLERK
MGR = 7902
HIREDATE = 17-dec-1980 00:00:00
SAL = 800
COMM =
DEPTNO = 20

PL/SQL procedure successfully completed.


Note you don't need to do numbers special, they'll be converted OK, just dates...

Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Joining data dictionary column names with values in a row

Dave Fowler, June 11, 2002 - 10:05 pm UTC

This is very similar to what I need to allow a Java application get the column name with the row value for that column from a single row in table/view query.

Thanks.

%rowtype

mo, December 01, 2003 - 11:04 am UTC

Tom:

I have a table named "History" and a variable declared baed on it in a procedure in a package.

l_hist history%rowtype;

When I added another procedure named "History" to the package, I gor an error for the vairiable declaration saying that

(1): PLS-00310: with %ROWTYPE attribute,
'HISTORY' must name a table, cursor or cursor-variable

I think PL/SQL is loooking at history as a procedure name now. How can I force it as a table declaration?

Thank you,

Tom Kyte
December 01, 2003 - 5:51 pm UTC

change the name of the procedure :)

rules of scoping are kicking in here -- plsql scope is overriding the sql scope.

Super!

Orca777, December 02, 2003 - 5:18 am UTC

short and effective !

Data Manuplation in %ROWTYPE

Jack Anderson, January 23, 2004 - 5:23 pm UTC

Hey Tom,
i would like to perform the following task in a procedure.

cursor c1 is select A, B, C, D from tab100;

TYPE s1 IS TABLE OF c1%ROWTYPE NOT NULL
INDEX BY BINARY_INTEGER;

GOOD S1;

BEGIN
FORALL indx IN good.FIRST .. good.LAST
UPDATE tab200
SET col1 = ***
col2 = 'YES', col3 = '1'
WHERE col4 = TO_CHAR(good (indx))
AND col5 = 2;

COMMIT;
END;


I would like to substitute *** with C that is available in the GOOD record.
how can i do that? is it possible?
please let me know
thanks

Tom Kyte
January 23, 2004 - 6:37 pm UTC

you'd have to fetch into scalar arrays -- not an array of records. the forall arrays must be "compact" -- scalar arrays. not array of records.

Fetching into %rowtypw

A reader, September 12, 2004 - 10:08 pm UTC

declare
mytab_rec mytab%rowtype;
begin
select * into mytab_rec from tab where ...
end;

Is the fetch into the %rowtype guaranteed to work regardless of column add or drop in mytab?

Thanks

Tom Kyte
September 13, 2004 - 7:35 am UTC

yes, that is the beauty of PLSQL like that.

Function with %rowtype parameter

VA, February 01, 2005 - 3:33 pm UTC

If I have a function defined like

create or replace function f(p_rec in t%rowtype)

How can I call it?

select f(t.*) from t t

doesnt seem to work?

Thanks

Tom Kyte
February 01, 2005 - 3:51 pm UTC

you cannot, plsql types -- like t%rowtype, are known only to plsql, not sql.

%ROWTYPE Question

Steve Kiteley, October 18, 2005 - 3:45 am UTC

Tom,

I have a problem passing rowtypes based on views that I don't understand.

I'm trying to combine two of our companies existing products that share common data by creating a common table which can be accessed by two views that mimic each individual products original tables. In doing this I hope to minimise recoding. Below is a simplified view of the problem I am encountering (my naming convention would normally be a bit more meaningful!)

CREATE TABLE mytest
(c1 NUMBER(1),
c2 NUMBER(1),
c3 NUMBER(1));

INSERT INTO mytest VALUES (1,2,3);

CREATE VIEW mytest_view AS
SELECT c1
,c2
FROM mytest;

CREATE OR REPLACE PACKAGE mytest_pkg AS
FUNCTION mytest_fn
RETURN mytest_view%ROWTYPE;
END;

CREATE OR REPLACE PACKAGE BODY mytest_pkg AS
FUNCTION mytest_fn
RETURN mytest_view%ROWTYPE IS
lr_mytest mytest_view%ROWTYPE;
BEGIN
SELECT *
INTO lr_mytest
FROM mytest_view
WHERE rownum < 2 ;
RETURN lr_mytest;
END mytest_fn;
END;

This is where my problems start as the two differnt products were originally coded by different people who had a different approach....

DECLARE
l_mytest_rec mytest_view%ROWTYPE;
BEGIN
l_mytest_rec := mytest_pkg.mytest_fn;
DBMS_OUTPUT.put_line (l_mytest_rec.c1);
END;

works fine whereas

DECLARE
l_mytest_rec mytest_view%ROWTYPE;
BEGIN
SELECT mytest_pkg.mytest_fn
INTO l_mytest_rec
FROM DUAL;
DBMS_OUTPUT.put_line (l_mytest_rec.c1);
END;

gives an ORA-00913: too many values error.

Is this a problem using %ROWTYPE against a view?

We are developing on 9.2.0.5

Thanks

Steve




Tom Kyte
October 18, 2005 - 9:08 am UTC

plsql rowtypes are not valid in SQL like that - the problem is that select mytest_pkg.mytest_fn cannot be "used" like that. it is not a sql type.



the select from dual is a horrible way to make an assignment - talk about adding overhead!

%ROWTYPE table as parameter

Carlos Gongora, March 13, 2008 - 4:23 pm UTC

Tom I'm trying to pass table rowtype as a parameter for a record construction. Is there a way to do this? Example:

create or replace procedure rollback_tables(p_table varchar2) is

-- I'm trying to use the parameter here but it's not working
tab_rec p_table%ROWTYPE;

begin
...
end;

Is there a way to do this?

Thanks a lot.

Carlos
Tom Kyte
March 15, 2008 - 9:16 am UTC

please - think about this....


suppose you could use p_table there... so what? Your plsql code between the begin and end would not be able to access it!!


Say I call it with a table name of ALL_USERS, that has columns username, user_id, created.

Say you call it with a table name of ALL_OBJECTS - that has a different set of column names, a different number of column names, a different set of datatypes. Now, what could your code between begin and end do???? I cannot reference tab_rec.username - because ONLY WHEN I CALL IT would it have username, when you call it - it cannot.


So, let us step back for a minute, instead of asking "how do I do something", why don't you tell us "what I am trying to do is...."

And then we can either tell you

a) why that would be a horrible idea, change your mind
b) how to accomplish it.


given what you are trying to do - I'm going to guess "A" is going to be the response :)

Thanks

Carlos, March 17, 2008 - 7:17 pm UTC

My idea of implementing a generic procedure to handle any table was foolish. It can not be done in this way too generic..

Many thanks a lot for your input.

Carlos

Getting Column names from a cursor

Amit Kumar, April 23, 2008 - 2:48 pm UTC

Hi Tom,

Tried a lot but still nothing.

I have a cursor
Cursor c1 is select name,id,value from my_tab;

Now I need to find out all the columns present in cursor c1. Can't look into user_tab_cols as the cursor doesn't contain all the rows. Also, the result should be in appropriate order like name,id,value.

Why this requirement - I use bulk collect to populate a table (245 columns) - so using %rowtype to collect the data.
I have to ensure that the code works fine when column order changes.

Many Thanks
Amit
Tom Kyte
April 28, 2008 - 9:29 am UTC

create a view, and then

cursor c1 is select * from view;


better yet, make your table be the CURSOR TYPE, not a row type. That way, you cannot go wrong on the fetch.

better better yet...

... I use bulk collect to populate a table (245 columns) -
so using %rowtype to collect the data.
...

DO NOT WRITE ANY CODE. just insert into select from... NO CODE WHATSOEVER.

Concurrency problem

Martin Irigaray, October 25, 2010 - 1:34 pm UTC

Hi, i'm thinking of using the technique you have presented to get the values of a rowtype object. My question is, wether setting the rowtype to a public variable in the packege may cause problems with concurrency between sessions or users. What happens if other executions overwrite the public value before the reading the first one?. Thanks.
Tom Kyte
October 25, 2010 - 6:16 pm UTC

data in packages is private to a session.

it works just like any other programming language. You don't expect your global variables in java to be overwritten by other processes - database sessions are like processes. You have your own data segment.

Sri, November 03, 2010 - 4:23 am UTC

Hi Tom,
Here is the problem which we are facing because we used %rowtype. To maintain the versions, in each and every table a new not null field (but with a default value) was decided to be added (not my idea!).

Create (sample)table looks like:

create table TEST_DEFAULT
(
C1 NUMBER,
C2 NUMBER default 100 not null /*newly added field*/
)

I have written an anonymous block to demonstrate what exactly happens inside our package:

Case 1:
declare
testin_rectype test_default%rowtype;
begin
testin_rectype.c1 := 2000;
insert into test_default(c1) values (testin_rectype.c1);
commit;
end;

This works perfectly fine. One record gets created in the table.

Case 2:
declare
testin_rectype test_default%rowtype;
begin
testin_rectype.c1 := 2000;
insert into test_default values testin_rectype;
commit;
end;

This throws ORA-01400: cannot insert null into ("TEST_DEFAULT"."C2")

Even though a default value has been assigned, why this error is popping-up? Is there any reason behind throwing this error?
This is happening in 10g release 2 - 10.2.0.3.0

Tom Kyte
November 03, 2010 - 9:48 am UTC

the error is popping up because you are executing in fact:

insert into test_default ( c1, c2 ) values ( 2000, NULL );

defaults ONLY work when you do not supply something to insert.

Null might be the 'absence of a value', but if you insert it - we have to do that.


if you supply a value - even null - for every column - defaults are not even considered - they cannot be!!! you are supply the value you want us to insert.

Sri, November 03, 2010 - 2:15 pm UTC

Thanks for your reply, Tom. But, the thing which i don't understand is, in both the cases i am not assigning any value to C2 column, so in case 1 only it should have thrown the error. Please feel free to yell at me, if am missing something :) .
Tom Kyte
November 04, 2010 - 3:06 am UTC

yes you ARE assigning a value - you are directly assigning NULL.

You have a record.
This record has components C1 and C2.

you set C1 to some non-null value.
you left C2 in this record default to NULL - as plsql record components are defaulted do.

you inserted this record that has components C1 and C2 into the table.

You directly and purposely inserted NULL into C2 - hence the default will NOT be used - since you set it explicitly.

Referencing columns of an object

Raghu, February 04, 2013 - 7:02 pm UTC

Tom,

We have an object type with 500 columns and a table with just the column descriptions for the object type. Only one row is passed in at a time to the procedure that accesses the object. We need to check if any of the 475 columns (specified) are not null, then some logic. Is there a way to find that or is this approach flawed?

I tried the method mentioned in the first response (using dynamic sql) to no avail.

create or replace type test_object as object
(col_1 varchar2(1),
.
.
col_500 varchar2(10)
);

create or replace type test_type as table of test_object;

my_var varchar2(500);
l_type test_type;

begin
for x in (select column_name from user_tab_columns where table_name = 'my_table')
execute immediate 'begin :x := l_type'||x.column_name||'; end;' using out my_var;
-- counter here
end loop;

if counter > 0 do something else;
end;

Thanks in advance.
Tom Kyte
February 06, 2013 - 8:04 am UTC

use the data dictionary to generate your code. for example:

ops$tkyte%ORA11GR2> declare
  2          l_sql long := 'create type typ as object ( c001 int';
  3  begin
  4          for i in 2 .. 500
  5          loop
  6                  l_sql := l_sql || ', c' || to_char(i,'fm000') || ' int';
  7          end loop;
  8          execute immediate l_sql || ' )';
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t ( x typ );

Table created.



ops$tkyte%ORA11GR2> begin
  2          for x in (select attr_name from user_type_attrs where type_name = 'TYP' order by attr_no)
  3          loop
  4                  dbms_output.put_line( 'case when MY_OBJECT.'||x.attr_name||' is not null then 1 else 0 end+' );
  5          end loop;
  6          dbms_output.put_line( '0;' );
  7  end;
  8  /
case when MY_OBJECT.C001 is not null then 1 else 0 end+
case when MY_OBJECT.C002 is not null then 1 else 0 end+
case when MY_OBJECT.C003 is not null then 1 else 0 end+
case when MY_OBJECT.C004 is not null then 1 else 0 end+
case when MY_OBJECT.C005 is not null then 1 else 0 end+
case when MY_OBJECT.C006 is not null then 1 else 0 end+
...
case when MY_OBJECT.C496 is not null then 1 else 0 end+
case when MY_OBJECT.C497 is not null then 1 else 0 end+
case when MY_OBJECT.C498 is not null then 1 else 0 end+
case when MY_OBJECT.C499 is not null then 1 else 0 end+
case when MY_OBJECT.C500 is not null then 1 else 0 end+
0;

PL/SQL procedure successfully completed.



just take the statement I generated with all of the case statements and compile that into your code.

Thiemo, May 06, 2015 - 5:45 pm UTC

Hi Tom

I am very grateful you share your knowledge with us.

I would like to serialise a record wihtin a package to easily print it on screen just as you depicted in your answer but it fails with me with:
2015-05-06 19:41:02.483176000 [TO_STRING] dummy4
2015-05-06 19:41:02.484261000 [TO_STRING] ORA-06512: in "M_KD_EDW1_MASTER_PRE.MANAGE_KEYSTORE", Zeile 192

2015-05-06 19:41:02.484316000 [TO_STRING] ORA-06550: Zeile 1, Spalte 13:
PLS-00201: identifier 'P_KEYSTORE.ACTIVITY_OBJECT_NAME' must be declared


Code:
function TO_STRING(P_KEYSTORE GT_KEYSTORE) return varchar2 is
C_ROUTINE_NAME constant GT_ROUTINE_NAME :=
'TO_STRING';
V_VALUE varchar2(32767);
V_STRING varchar2(32767);
V_STATEMENT varchar2(32767);
V_COUNT integer := 0;
begin
LG(
P_CALLER => C_ROUTINE_NAME,
P_MESSAGE => P_KEYSTORE.ACTIVITY_OBJECT_NAME
);
for REC in (
select COLUMN_NAME
, DATA_TYPE
from USER_TAB_COLS
where TABLE_NAME = 'V_STAT_KEYSTORE'
order by COLUMN_NAME asc
) loop
V_COUNT := V_COUNT + 1;
if REC.DATA_TYPE = 'DATE' then
V_STATEMENT :=
'begin :1 := to_char(P_KEYSTORE.' ||
REC.COLUMN_NAME || ', ''' || GC_DATE_FORMAT ||
'''); end;';
elsif REC.DATA_TYPE = 'TIMESTAMP' then
V_STATEMENT :=
'begin :1 := to_char(P_KEYSTORE.' ||
REC.COLUMN_NAME || ', ''' || GC_TIMESTAMP_FORMAT ||
'''); end;';
else
V_STATEMENT :=
'begin :1 := P_KEYSTORE.' || REC.COLUMN_NAME ||
'; end;';
end if;
execute immediate V_STATEMENT using out V_VALUE;
if V_COUNT > 1 then
V_STRING := V_STRING || chr(28);
end if;
V_STRING := V_STRING || REC.COLUMN_NAME || '=' || V_VALUE;
end loop;
return V_STRING;
exception
when others then
LG(
P_CALLER => C_ROUTINE_NAME,
P_MESSAGE => dbms_utility.format_error_backtrace
);
LG(
P_CALLER => C_ROUTINE_NAME,
P_MESSAGE => dbms_utility.format_error_stack
);
LG(
P_CALLER => C_ROUTINE_NAME,
P_MESSAGE => 'V_STATEMENT: ' || V_STATEMENT
);
raise;
end TO_STRING;


We are using:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

It seems that the execute immediate does not have access to parameters, variables, constants (I tried latter as well) of the enclosing block. Has this changed or what am I doing wrong?

Kind regards

Thiemo

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library