case operator
Mikito Harakiri, March 18, 2003 - 1:36 pm UTC
Tom, why don't you encourage using case operator instead of decode in your examples?
March 18, 2003 - 4:42 pm UTC
i didn't think I didn't?? I mean - case is "first" here with decode as a fallback
I use decode when decode is easier. I use case when it makes sense.
Not every release has case so I try to provide an equivalent decode when I can... Also, you have to hide case in PLSQL in 8i using dynamic sql or a view and I heavily promote PLSQL -- so I want to make sure most people can use it.
Performance with CASE
A reader, March 18, 2003 - 1:52 pm UTC
Tom,
How and when a "CASE" is executed with in a statement? currently when using a CASE i have serious performance issues even though the consistent gets are low?
Thanks,
March 18, 2003 - 4:43 pm UTC
that is like killing the messenger...
CASE is not at fault here -- lets see the actual example with a TKPROF.
Case in update statement
Ram, March 19, 2003 - 12:49 am UTC
i would like to extend this case operator usage in a subquery for the where clause of an update statement but i'm not sure
1. if its possible.
2. if possible, how.
oracle version 8.1.5.
table users.
user_id
class
...<some other columns>
table profiles.
user_id
profile_id
...<a few other columns>
now based on the profile_id i would like to update the class column in the users table. something like this is currently in mind:
update users
set class = 1
where user_id in ( select user_id
from profiles where profile_id IN ('x','y','z')
/
update users
set class = 2
where user_id in ( select user_id
from profiles where profile_id NOT IN ('x','y','z')
/
any ideas ?
Q3. is there any other way to do this in a single sql ?
thanks in advance !
March 19, 2003 - 6:49 am UTC
case did not exist in 815, it was added in 816.
two ways -- if profiles has a primary key on user_id, the first works (else it does not). second works in any case:
ops$tkyte@ORA815> select * from users;
USER_ID CLASS
---------- ----------
1
2
3
ops$tkyte@ORA815> select * from profiles;
USER_ID P
---------- -
1 x
2 a
ops$tkyte@ORA815> update (select class, profile_id
2 from users, profiles
3 where users.user_id = profiles.user_id(+) )
4 set class = decode( profile_id, 'x', 1, 'y', 1, 'z', 1, 2 )
5 /
3 rows updated.
ops$tkyte@ORA815>
ops$tkyte@ORA815> select * from users;
USER_ID CLASS
---------- ----------
1 1
2 2
3 2
ops$tkyte@ORA815> rollback;
Rollback complete.
ops$tkyte@ORA815> select * from users;
USER_ID CLASS
---------- ----------
1
2
3
ops$tkyte@ORA815> update users
2 set class = ( select nvl( max(1), 2 )
3 from profiles
4 where user_id = users.user_id
5 and profile_id in ( 'x', 'y', 'z' ) )
6 /
3 rows updated.
ops$tkyte@ORA815> select * from users;
USER_ID CLASS
---------- ----------
1 1
2 2
3 2
ops$tkyte@ORA815>
Great !
ram, March 19, 2003 - 9:49 am UTC
Using Case in 816
Ram, March 20, 2003 - 6:44 am UTC
How to do the same in 816 using CASE ? Just curious.
March 20, 2003 - 8:52 am UTC
I answered that right at the beginning of my answer?
CASE and Remote DB and ORA-22804
Robert, April 23, 2003 - 2:43 pm UTC
Is it a fact that CASE does not work with db_link
in this fashion below ? thanks :
INSERT
INTO
emp@Remote_db
SELECT
EMPNO,
ENAME,
CASE
WHEN JOB = 'MANAGER'
THEN 'BULLY'
ELSE 'MINION' END JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
FROM emp
/
ORA-22804: remote operations not permitted on object tables or user-defined type columns
April 23, 2003 - 8:13 pm UTC
it is a "no go" -- currently, fixed in the next release (10)
You can contact support and see if there is a fix that is backported to the current releases.
ORA-22804 in execute immediate
Dinesh, September 10, 2005 - 9:37 am UTC
Hi Tom,
In my case i am having a procedure where i am fetching a row from a table and storing it in a %rowtype variable. Then using execute immediate to update a second table as below:-
m_dyn_stmt:=Null;
m_dyn_stmt:= 'UPDATE Table2@'||m_instance||' SET Column1=:m_table1_REC.Column1';
m_dyn_stmt:= m_dyn_stmt||' WHERE seq_key=:p_seq_key';
DBMS_OUTPUT.PUT_LINE(m_dyn_stmt);
Execute Immediate m_dyn_stmt Using m_table1_REC.Column1,p_seq_key;
It is giving me the following error:-
ORA-22804: remote operations not permitted on object tables or user-defined type columns
In the above code m_table1_REC is Table1%Rowtype;
Can you pls provide me a solution how can i use %rowtype variables with execute immediate.
September 10, 2005 - 9:47 am UTC
why are you using dynamic sql. are you totally sure you need to (so so so many reasons not to use it)
the sql should resemble
'update table2@xxxx set column1 = :X where seq_key = :Y'
:x.y as a bind variable isn't right.
ops$tkyte@ORA10G> create table t ( x int );
Table created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> begin
2 execute immediate 'update t set x = :A.B' using 5;
3 end;
4 /
begin
*
ERROR at line 1:
ORA-22806: not an object or REF
ORA-06512: at line 2
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> begin
2 execute immediate 'update t set x = :A' using 5;
3 end;
4 /
PL/SQL procedure successfully completed.
You are getting the error you are because objects and dblinks do not mix very well.
Thanks tom but I am getting ora-22804
Dinesh Kr. Dubey, September 12, 2005 - 2:26 am UTC
Thanks Tom,
But i am getting ora-22804 not ora-22806 as given in your reply.
ORA-22804: remote operations not permitted on object tables or user-defined type
columns
This i am getting while trying to pass a %rowtype variable to a dblink.
Pls help to solve it.
And one more concern it i am using a lots of execute immediate in my programmes as the situation demands like this.Because my procedure can take any one of the 3 dblink that is in my requirement.So how can i handle it.
September 12, 2005 - 7:23 am UTC
and I alluded as to why
....
You are getting the error you are because objects and dblinks do not mix very
well.
...........
you are getting your error because oracle is interpreting that as an object reference and saying "NO, not with this dblink", instead of
ORA-22806: not an object or REF
as I got without it.
Your situation demands you use a bind variable in the fashion I did, you are binding a SCALAR, not an object!
You just have a typo there, use :x to bind that scalar, NOT :x.y which is not valid in your case.
Thanks Tom
Dinesh Kumar Dubey, September 12, 2005 - 9:01 am UTC
Thanks Tom,
For your reply, I have changed my logic.
With regds
Dinesh