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,
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
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
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
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
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
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
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.
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
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 :) .
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.
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