Cursor
June 7, 2002 - 10pm Central time zone
Reviewer: munz from Reston, USA
Tom:
1. I tried to use this since I do not have a primary key in EMP
For x in (select emp.*,rownum r from emp)
LOOP
.....
update emp set empid=x.lname||x.firstname where rownum=x.r
END LOOP
END;
The update did not work. It was updating the whole table with the last value. ANy idea why?
2. I used ROWID instead of ROWNUM and it worked fine.
3. IF rowid is the physical address of the record in the data file what is the rownum?
Thank you,
Followup June 8, 2002 - 5pm Central time zone:
well, this has absolutely nothing to do with the original question???
anyway, read
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4061088733083
(your description of what it does is wrong as well. it will NOT update the entire table, in fact,
only the first update will update s single row -- the other updates do nothing -- since "where
rownum = X" where X > 1 is never true.... If you read and understand the above link, you'll know
why.
rowid works cause rowid is a primary key.
rownum is a made up number assigned to a row after it is fetched from a table.
Rownum
June 8, 2002 - 6pm Central time zone
Reviewer: Monz from Reston, USA
TOM:
Oh ok i think I understand it.
When you open the cursor yo are fetching one record a time and every time you fetch the rownum is
1.
I thought the when you open cursor it loads all result set in memory which includes rownum 1..20
and then you will be accessing the rownum of each record.
Am I correct?
Outer join
June 8, 2002 - 9pm Central time zone
Reviewer: Suresh from New York
Tom,
Regarding the 1st question in this thread, could you please let me know how many tables can be
outer joined in a single SQL statement? Thanks.
Followup June 9, 2002 - 10am Central time zone:
There is no limit I am aware of.
Here is a "big one"
1 declare
2 l_query long;
3 l_cnt number;
4 begin
5 l_query := 'select count(*) from dual d1';
6 for i in 2 .. 255
7 loop
8 l_query := l_query || ', dual d'||i;
9 end loop;
10 l_query := l_query || ' where 1=1 ';
11 for i in 2 .. 255
12 loop
13 l_query := l_query || ' and d1.dummy = d' || i || '.dummy(+) ';
14 end loop;
15 execute immediate l_query into l_cnt;
16 dbms_output.put_line( 'and the answer is ' || l_cnt );
17* end;
18 /
and the answer is 1
PL/SQL procedure successfully completed.
June 9, 2002 - 11am Central time zone
Reviewer: Suresh
Tom,
Thanks for your response regarding the maximum number of tables that can be outer joined in a SQL
statement. The reason I asked you is that I got this following error sometime back while trying to
do more than one outer join in a statement.
ORA-01417: a table may be outer joined to at most one other table
So, from your anwer, I understand that there can be many tables outer joined in a single SQL
statement and from the above mentioned error, it is clear that a table cannot be outer joined with
more than one table in a statement.
Please let me know if this is right.
Followup June 9, 2002 - 4pm Central time zone:
What that is saying that if some table T is outer joined to some other table T2, then T may not be
outer joined to T3. In the following, D1 = T, D2 = T2, D3 = T3:
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from dual d1, dual d2, dual d3
2 where d1.dummy (+) = d2.dummy and d1.dummy(+) = d3.dummy;
where d1.dummy (+) = d2.dummy and d1.dummy(+) = d3.dummy
*
ERROR at line 2:
ORA-01417: a table may be outer joined to at most one other table
That is the error you are getting. You can workaround this issue with inline views:
1 select *
2 from ( select d1.dummy d1_dummy, d2.dummy d2_dummy
3 from dual d1, dual d2
4 where d1.dummy(+) = d2.dummy ) d_temp,
5 dual d3
6* where d_temp.d1_dummy(+) = d3.dummy
ops$tkyte@ORA817DEV.US.ORACLE.COM> /
D D D
- - -
X X X
That will physically make "d_temp" exist for a moment and then outer join that temporary set to d3.
The effect is the same (and it is allowed)
Tom, Thanks a lot. As always you are the best.
June 9, 2002 - 7pm Central time zone
Reviewer: Suresh
ANSI multiple outer join avoiding ORA-01417
June 11, 2002 - 2am Central time zone
Reviewer: David Penington from Melbourne Australia
This is where I like the power of the ANSI join notation. You can unambiguously specify the
behaviour while outer-joining to multiple tables. The example which needed an inline view becomes:
select d1.dummy d1_dummy, d2.dummy, d3.dummy
from dual d3 cross join dual d2 left outer join dual d1 on
(d1.dummy = d2.dummy AND d1.dummy = d3.dummy)
/
A more meaningful example:
select xxx.x1, yyy.empid, emp.empname
from xxx join yyy on (xxx.stateid = yyy.stateid)
left outer join emp on (emp.empid = yyy.empid and emp.empname = xxx.x1)
This outer joins two tables to one while avoiding ORA-01417: a table may be outer joined to at most
one other table
Followup June 11, 2002 - 10am Central time zone:
did not mention that as question was for 817.
January 7, 2004 - 2pm Central time zone
Reviewer: A reader
v$instance
January 14, 2004 - 3pm Central time zone
Reviewer: mo
Tom:
Do you know why when i do :
select instance_name into v_instance_name from v$instance;
in pl/sql (package.procedure) I get an error that view/table is not found.
This is 9i database and I run the command in sqlplus and it gives me the instance name.
v$view
January 16, 2004 - 12pm Central time zone
Reviewer: mo
Tom:
DBA does not how to grant a developer account access to all V$views without using a role. Is there
a way to do it?
He says this would not work:
"grant select on v$instance to dev"
He did this workaround:
create view vinstance as select * from v$instance;
grant select on vinstance to dev; as dev:
create synonym vinstance for sys.vinstance;
It would be very tedious to do this for every view.
Followup January 16, 2004 - 12pm Central time zone:
begin
for x in ( select object_name from user_objects where object_type = 'VIEW' and
object_name like 'V\_$%' escape '\' )
loop
execute immediate 'grant select on ' || x.object_name || ' to ops$tkyte';
end loop;
end;
/
Nice
January 17, 2004 - 8am Central time zone
Reviewer: Gerhard from Dusseldorf,Germany
Dear sir,
Why do we use the word "Outer" in Outer join?What is so
special about it?
Followup January 18, 2004 - 12pm Central time zone:
Hopefully this will help spell it out and make it clear. In a nutshell -- "outer" makes it so that
ALL rows from a given table (or two) are returned even if there is no "match" in the other table.
Normally -- in a join, only rows that have matches in both tables are returned. an outer join
preserves all of the rows. consider:
ops$tkyte@ORA9IR2> create table t1 ( x int );
Table created.
ops$tkyte@ORA9IR2> create table t2 ( x int );
Table created.
ops$tkyte@ORA9IR2> insert into t1 values ( 1 );
ops$tkyte@ORA9IR2> insert into t1 values ( 2 );
ops$tkyte@ORA9IR2> insert into t2 values ( 2 );
ops$tkyte@ORA9IR2> insert into t2 values ( 3 );
so that sqlplus will show us "made up values" -- nulls supplied by the outer join operation --
clearly...
ops$tkyte@ORA9IR2> set NULL MadeUp!
ops$tkyte@ORA9IR2> select t1.x, t2.x
2 from t1 join t2 on ( t1.x = t2.x );
X X
---------- ----------
2 2
that is the normal "join" we use all of the time. only rows that satisfy the join condition
appear in the result set. if a row exists in t1 (x=1) but not in t2 -- it will not appear and vice
versa
ops$tkyte@ORA9IR2> select t1.x, t2.x
2 from t1 left outer join t2 on ( t1.x = t2.x );
X X
---------- ----------
2 2
1 MadeUp!
Now, the left outer join says "show ALL rows from the 'table on the left' and IF it has a match
in T2 -- go ahead and show those as well
ops$tkyte@ORA9IR2> select t1.x, t2.x
2 from t1 right outer join t2 on ( t1.x = t2.x );
X X
---------- ----------
2 2
MadeUp! 3
and a right join (right outer join -- the outer is not really needed but implied in a
right/left/full join) is the opposite of the left join. It preserves all of the rows in the righ
most table (t2) and shows all of them, making up a value in T1 when there is no match...
ops$tkyte@ORA9IR2> select t1.x, t2.x
2 from t1 full outer join t2 on ( t1.x = t2.x );
X X
---------- ----------
2 2
1 MadeUp!
MadeUp! 3
and lastly -- a full outer join. BOTH tables will have ALL rows contribute to the result set --
making up rows in the other table when there is no match
Nice
January 19, 2004 - 10am Central time zone
Reviewer: Gerhard from Dusseldorf,Germany
Dear Sir,
Thanks a lot for your explanation.But is there any way
to avoid Outer join or Is there Any other way to replace
outer join?
Bye!
Followup January 19, 2004 - 11am Central time zone:
well -- if you want what you want (all of the rows from one of the tables), the outer join is
typically the most efficient.
But, sure, you can "remove" an outer join. consider:
scott@ORA920PC> select dname, count(empno)
2 from dept left outer join emp on ( emp.deptno = dept.deptno )
3 group by dname
4 order by 1
5 /
DNAME COUNT(EMPNO)
-------------- ------------
ACCOUNTING 3
OPERATIONS 0
RESEARCH 5
SALES 6
scott@ORA920PC>
scott@ORA920PC> select dname, (select count(*) from emp where emp.deptno = dept.deptno)
2 from dept
3 order by 1
4 /
DNAME (SELECTCOUNT(*)FROMEMPWHEREEMP.DEPTNO=DEPT.DEPTNO)
-------------- --------------------------------------------------
ACCOUNTING 3
OPERATIONS 0
RESEARCH 5
SALES 6
scott@ORA920PC>
scott@ORA920PC>
scott@ORA920PC> select dname, count(*)
2 from dept join emp on ( emp.deptno = dept.deptno )
3 group by dname
4 union all
5 select dname, 0
6 from dept
7 where not exists ( select null from emp where emp.deptno = dept.deptno )
8 order by 1
9 /
DNAME COUNT(*)
-------------- ----------
ACCOUNTING 3
OPERATIONS 0
RESEARCH 5
SALES 6
scott@ORA920PC>
Thank u
January 19, 2004 - 3pm Central time zone
Reviewer: Murali from Bangalore, India
Thank u very much Tom...I have very much liked all your answers and dont forget there are lots of
guys out here in India who want to become like you.(including myself)..
Java prepared statement to PL/SQL procedure
January 19, 2004 - 7pm Central time zone
Reviewer: Baqir Hussain from Emeryville, CA USA
Tom,
The following Java code is inefficient in updating/deleting/inserting old client_id with the new
ones. The client_id will be passed on the code.
I need your help to generate an effient PL/SQL code out of this Java code and oblige.
PreparedStatement ps1 = connection.prepareStatement("UPDATE client_history SET client_id = ?
WHERE client_id = ?");
PreparedStatement ps2 = connection.prepareStatement("UPDATE client_stars SET client_id = ?
WHERE client_id = ?");
PreparedStatement ps3 = connection.prepareStatement("UPDATE customer SET
last_logged_in_client_id = ? WHERE last_logged_in_client_id = ?");
PreparedStatement ps4 = connection.prepareStatement("UPDATE directions_address SET client_id =
? WHERE client_id = ?");
PreparedStatement ps5 = connection.prepareStatement("UPDATE ecrm_reply SET client_id = ? WHERE
client_id = ?");
PreparedStatement ps6 = connection.prepareStatement("UPDATE email_notify_link_stats SET
client_id = ? WHERE client_id = ?");
PreparedStatement ps7 = connection.prepareStatement("UPDATE email_track_log SET client_id = ?
WHERE client_id = ?");
PreparedStatement ps8 = connection.prepareStatement("UPDATE home_search SET client_id = ? WHERE
client_id = ?");
PreparedStatement ps9 = connection.prepareStatement("UPDATE journal SET client_id = ? WHERE
client_id = ?");
PreparedStatement ps10 = connection.prepareStatement("UPDATE link_handler SET client_id = ?
WHERE client_id = ?");
PreparedStatement ps11 = connection.prepareStatement("UPDATE link_stat SET client_id = ? WHERE
client_id = ?");
PreparedStatement ps12 = connection.prepareStatement("UPDATE loan_partner_data SET client_id =
? WHERE client_id = ?");
PreparedStatement ps13 = connection.prepareStatement("UPDATE message SET client_id = ? WHERE
client_id = ?");
PreparedStatement ps14 = connection.prepareStatement("UPDATE my_comparables SET user_id = ?
WHERE user_id = ?");
PreparedStatement ps15 = connection.prepareStatement("UPDATE my_homes SET user_id = ? WHERE
user_id = ?");
PreparedStatement ps16 = connection.prepareStatement("UPDATE outbound_links SET client_id = ?
WHERE client_id = ?");
PreparedStatement ps17 = connection.prepareStatement("UPDATE phoenix_idx_retention SET
client_id = ? WHERE client_id = ?");
PreparedStatement ps18 = connection.prepareStatement("UPDATE required_contact SET client_id = ?
WHERE client_id = ?");
PreparedStatement ps19 = connection.prepareStatement("UPDATE scheduled_visit SET client_id = ?
WHERE client_id = ?");
PreparedStatement ps20 = connection.prepareStatement("UPDATE session_log SET client_id = ?
WHERE client_id = ?");
PreparedStatement ps21 = connection.prepareStatement("UPDATE track_end_date SET client_id = ?
WHERE client_id = ?");
PreparedStatement ps22 = connection.prepareStatement("UPDATE track_start_date SET client_id = ?
WHERE client_id = ?");
PreparedStatement ps23 = connection.prepareStatement("UPDATE zip_transaction SET buyer_id = ?
WHERE buyer_id = ?");
PreparedStatement ps24 = connection.prepareStatement("UPDATE zip_transaction SET seller_id = ?
WHERE seller_id = ?");
PreparedStatement ps25 = connection.prepareStatement("INSERT INTO sso_client SELECT * FROM
client WHERE client_id = ?");
PreparedStatement ps26 = connection.prepareStatement("DELETE FROM client WHERE client_id = ?");
PreparedStatement ps1 = connection.prepareStatement("UPDATE client SET customer_id = ? WHERE
customer_id = ?");
PreparedStatement ps2 = connection.prepareStatement("UPDATE email_address SET customer_id = ?
WHERE customer_id = ?");
PreparedStatement ps3 = connection.prepareStatement("UPDATE telephone_number SET customer_id =
? WHERE customer_id = ?");
PreparedStatement ps4 = connection.prepareStatement("UPDATE web_question_answered SET
customer_id = ? WHERE customer_id = ?");
PreparedStatement ps5 = connection.prepareStatement("UPDATE note_history SET customer_id = ?
WHERE customer_id = ?");
PreparedStatement ps6 = connection.prepareStatement("INSERT INTO sso_customer SELECT * FROM
customer WHERE customer_id = ?");
PreparedStatement ps7 = connection.prepareStatement("DELETE FROM customer WHERE customer_id =
?");
Followup January 20, 2004 - 7am Central time zone:
easy:
begin
null;
end;
that code does nothing -- just parses a bunch of statements.
But it would be a trivial stored procedure to just execute those statements as well. there is no
logic there, just a bunch of statements to be executed.
Help
January 19, 2004 - 11pm Central time zone
Reviewer: Mohan from India
Tom,
Is there anyway,through PL/SQL routine, i can know the sessions that got terminated abnormally. The
case with my database is that few permanent objects are created in a session, and when the user
logs off it fires a Schema level trigger which will drop the 'temporary' permanent objects of the
session using dynamic sql. It would have been better done with the use of Temporary Tables but the
db guys before i took over didn't use that. The convention that is followed for the objects is
object_name<session_id>. Can u help me out in dropping the objects for the session terminated
abnormally.
Thanks and Regards
Followup January 20, 2004 - 8am Central time zone:
well, until you fix this for real (because doing ddl in a stored procedure is a truly bad idea for
so many reasons) - you would schedule a job that would query dba_objects looking for objects to
drop. I guess they used userenv('sessionid'), so you would be looking at audsid in v$session for
active sessions -- dropping anything that isn't "active"
use dbms_job to schedule this to run every "whatever unit of time you desire"
v$view
January 20, 2004 - 10am Central time zone
Reviewer: mo
Tom:
1. DBA says grant does not work. Any ideas why.
SQL> grant select on v$instance to nlsadmin;
grant select on v$instance to nlsadmin
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
2. when you do " like 'V\_$%' escape '/'
what is this actually looking for. Is it anything that starts with V_$?
Followup January 20, 2004 - 1pm Central time zone:
1) v$instance is not a view, v_$instance is
2) yes, normally _ matches ANY single character
'\_' escape '\'
looks for an underscore instead. the escape clause lets you use what are normally wildcards in the
search string (_ and %)
v$views
January 20, 2004 - 5pm Central time zone
Reviewer: mo
Tom:
Thank you.
How do you explain this:
SQL> desc v_$instance;
ERROR:
ORA-04043: object v_$instance does not exist
However when I do:
SQL> desc v$instance
it works.
Are they both valid view names?
Followup January 20, 2004 - 10pm Central time zone:
v$instance is a synonym for sys.v_$instance
that is why. desc sys.v_$instance
sys.v_$instance
July 12, 2004 - 12pm Central time zone
Reviewer: Branka from VA, USA
Tom,
Do you have explanation for following problem. I do not know what is going on, and how to solve
problem.
oracle2:/export/home/oracle%sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jul 12 12:54:58 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
sys@TPROD3> grant select on v_$instance to perfstat;
Grant succeeded.
sys@TPROD3> connect perfstat/perfstat
Connected.
sys@TPROD3> desc v_$instance
ERROR:
ORA-04043: object v_$instance does not exist
Followup July 12, 2004 - 8pm Central time zone:
desc sys.v_$instance
desc v$instance
either or.
v$instance is a public synonym that points to sys.v_$instance
sys.v_$instance
July 12, 2004 - 4pm Central time zone
Reviewer: Branka from VA, USA
My mistake.
Somehow synonym V$instance was create for v$instance instead of v_$instance.
I do not know if it is Oracle bug, or someone changed synonym.
Followup July 12, 2004 - 11pm Central time zone:
no bug, no mistake, you don't have a v_$instance in the perfmon schema.
only v$instance and sys.v_$instance are visible in that schema.
v$instance
October 29, 2009 - 11am Central time zone
Reviewer: Raju
Hi Tom,
this is again a great thread.
But, the below link of your answer to one of the threads don't work :
http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html
But, is there another way to get the instance name, database name(any unique identifier of the
database) in PL/SQL without using v$ tables. can we get this from User_ tables?
thanks in advance
Followup October 29, 2009 - 12pm Central time zone:
look on homepage - new link to the page that had all of the "how tos and whys"
the user_ things are views of the dictionary - static objects, things you own, tables and the like.
An instance name is not unique.
The database name is not unique.
IF you are using global_names (as you should be - show parameter global_names), then, create session would be all you need to get the global database name - which would be unique in the set of databases this database connects to, shares data with
ops$tkyte%ORA10GR2> drop user a cascade;
User dropped.
ops$tkyte%ORA10GR2> grant create session to a identified by a;
Grant succeeded.
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> select * from global_name;
GLOBAL_NAME
-------------------------------------------------------------------------------
ORA10GR2.COM
global name
October 29, 2009 - 3pm Central time zone
Reviewer: sam
Tom:
Database and instance names are not unique??
This is the first time i realize it. The PL/SQL program runs inside a database "XXX". How can
V$database have muliple names.
I guess a database can be mounted by multiple instances (RAC database) with the same name. It does
not make sense though.
is not global name basically the instance name + your domain anyhow.
Can you clarify?
Followup October 29, 2009 - 3pm Central time zone:
how many people named Thomas have you met in your life?
A name is just a name.
... I guess a database can be mounted by multiple instances (RAC database) with the
same name. It does not make sense though.
...
why not? they have instance_ids to uniquely identify them. The instance "name" is just the oracle_sid and you know what we use the oracle sid to do? Just to find files.
I have an instance named "test" - do you?
"is not global name basically the instance name + your domain anyhow."
NO, absolutely not. the instance name is whatever you would like it to be, it comes from the environment when you start the database.
the global name is set via the alter database command and would be the same on all nodes in a rac cluster. It would be the unique name given to the database by the DBA in a network of servers
insance
October 29, 2009 - 4pm Central time zone
Reviewer: sam
Tom:
I do not get it.
In my application i always use
SELECT name from V$database
to see if application is in DEV, TEST, or PROD and code accordingly.
our database name is XXXD, XXXT, XXXP
How can i have multiple database names? The pl/sql procedure runs in one and only one database. How
can i have muleiple names for thsi database.
Followup October 30, 2009 - 9am Central time zone:
Sam, you phrase things "unclearly"
database names are not unique. Please tell me how many databases could you personally have that are named "XXXD" (the answer is of course "as many as you like")
You wrote
"Database and instance names are not unique??"
The only answer to that is "no, of course not, I can have one with your database name"
I went on to say "if you use global names, in a network of servers that connect to eachother we can assure you of uniqueness - and select * from global_name will tell you what database you are connected to in that network of servers"
That would be unique in that set of servers.
I don't see how we get from "database and instance names are not unique?" to "how can I have multiple database names"
that leap of logic dumbfounds me.
database
October 30, 2009 - 10am Central time zone
Reviewer: A reader
Tom:
I see what you are saying.
But let us say we have 10 databases on 10 machines or same machine with identical names.
My point is that my pl/sql program will run in one and only one. so when i do
SELECT name from V$databaste
it will get the name of the current database that it ran in which is Unique to that code.
The SELECt is not aware of the other 9 databases that have the same name.
Followup October 30, 2009 - 10am Central time zone:
to which i say
so what?
of course that name is the name of the current database, but that name is just a name - like my name is Thomas as are millions of other people.
so, so what??
If you have a strict naming convention for test/prod/dev - great. Go for it, but please use correct terminology. Say what you mean.
If you want a non-privileged way to get an "id of the database", you can have your DBA look into using the global name as all have access to that view whereas v$database requires a grant.
|