Question on Cursor
April 11, 2008 - 11am Central time zone
Reviewer: Vijay Ram from Riverside, CA USA
Hi Tom
Thanks for a precise and clear answer
Cheers
~Vijay
Sysdba password
April 11, 2008 - 1pm Central time zone
Reviewer: Swarup Basu from India
I get connected to database using sql>sqlplus "/as sysdba"
But I want a password while getting connection to database using the above mentioned command.
Another question somehow the oracle user password can be hacked through some software. It is
proved. How to manage this problem.
Followup April 11, 2008 - 2pm Central time zone:
... Another question somehow the oracle user password can be
hacked through some software. It is proved. ..
PROVE IT PLEASE
can you brute force try to guess passwords?
yes, if you
a) don't set up failed logon attempts
b) give someone your hashed passwords (eg: give them DBA so they can steal stuff)
if you don't want a user to be able to connect as sysdba without a password, please simply
a) do not let them log onto the server (it uses os authentication)
b) or if they must log into the server (which is highly DOUBTFUL if they are not to connect as sysdba), remove them from the DBA group in the OS - we use os authentication and if they are in the group that is allowed to log in - then they are allowed to log in, and the way to prevent it would be to remove them from that group.
April 13, 2008 - 10am Central time zone
Reviewer: Swarup Basu
Thank you Tom. I have understood. Admin user password will be with me. I'll provide another OS user
to others and without DBA priviledge. Am I right Sir?
Followup April 16, 2008 - 1pm Central time zone:
why not provide others with their OWN accounts, group accounts are a really bad idea.
and just do not grant the os dba role to those accounts, correct.
Inconsistancy between Control file and Datafile
November 21, 2008 - 7am Central time zone
Reviewer: Swarup Basu from Kolkata, India
This database had no backup, neither cold nor hot. Prob:1. Control01 and Control03 were
inconsistant. It was not getting mount. I copied from Control02 and created 01 and 03 respectively.
Now it getting mounted. But msg. is coming when starting the db :more recovery need for
system01.dbf. Is there any way to recover the database, partial data loss is acceptable.
Followup November 24, 2008 - 2pm Central time zone:
I have no idea - because I have no idea what happened, what you did, or where you are at...
please utilize support for something that is so obviously a support issue at this point. Do not take advice from anyone in an internet discussion forum about this - you need to get all of the details to someone, let them discuss your alternatives and proceed cautiously.
Fetch across DROP
December 1, 2008 - 2am Central time zone
Reviewer: Parthiban Nagarajan from Coimbatore, India
Question on CURSOR
Hi Tom
I am working in Oracle database for past 1-2 years. But, it seems, still I am not clear about the
CURSOR concepts.
I don't know what the following means ....
SQL*Plus: Release 10.1.0.4.2
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL> create table uv( xyz int );
Table created.
SQL> insert into uv
2 select 1 from dual union all
3 select 2 from dual union all
4 select 3 from dual union all
5 select 4 from dual union all
6 select 5 from dual;
5 rows created.
SQL> commit;
Commit complete.
SQL> set serveroutput on
SQL> declare
2 cursor c_uv is
3 select xyz from uv;
4 abc int := -1;
5 begin
6 open c_uv;
7 loop
8 fetch c_uv into abc;
9 exit when c_uv%notfound;
10 dbms_output.put_line(abc);
11 end loop;
12 close c_uv;
13 end;
14 .
SQL> /
1
2
3
4
5
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 declare
2 cursor c_uv is
3 select xyz from uv;
4 abc int := -1;
5 begin
6 open c_uv;
7 loop
8 fetch c_uv into abc;
9 exit when c_uv%notfound;
10 if c_uv%rowcount = 2 then
11 execute immediate
12 'drop table uv';
13 end if;
14 dbms_output.put_line(abc);
15 end loop;
16 close c_uv;
17* end;
SQL> desc uv
Name Null? Type
-------------------------- -------- ----------------------------
XYZ NUMBER(38)
SQL> /
1
2
3
4
5
PL/SQL procedure successfully completed.
SQL> desc uv
ERROR:
ORA-04043: object uv does not exist
I read it on "Fetch across commit" where you have said that no data is loaded into the memory when
the cursor is opened. So, could you explain me where Oracle is fetching the records after the
"DROP" ?
Followup December 1, 2008 - 7am Central time zone:
when you drop, it just deallocates the space (in 10g, it puts it in the recycle bin)
If you have started a query - and then you drop/truncate the segment - the query will likely run to completion UNLESS someone else comes along and reuses that space - in which case you would likely receive an ora-8103
$ oerr ora 8103
08103, 00000, "object no longer exists"
// *Cause: The object has been deleted by another user since the operation
// began, or a prior incomplete recovery restored the database to
// a point in time during the deletion of the object.
// *Action: Delete the object if this is the result of an incomplete
// recovery.
as long as the blocks needed by the query still exist and haven't been overwritten - you'll be OK.
Fetch across DROP
January 8, 2009 - 6am Central time zone
Reviewer: Parthiban Nagarajan from Coimbatore, India
Hi Tom
A few weeks after "Fetch across DROP", I have been reading your "Expert Oracle Database
Architecture: 9i and 10g programming techniques". I stunned there to see some version of the
Question/Answer for "Fetch across DROP". Before asking something new, I have to make sure that its
really "NEW"....
Parameterized Cursor in Pl/Sql
September 18, 2009 - 3am Central time zone
Reviewer: Parthiban Nagarajan from Coimbatore, India
Hi Tom
-- ------------------------------------------------------------------
-- I really enjoyed those three days in SANGAM'09 - Bangalore, India.
-- Thanks for teaching us. We cannot forget those precious moments.
-- ------------------------------------------------------------------
<quote> Pl/Sql automagically binds pl/sql variables in the SQL Statements </quote>
If that is the case, do we really need parameterized cursors?
Followup September 18, 2009 - 10am Central time zone:
... If that is the case, do we really need parameterized cursors?...
as a matter of coding style - you might.
If you put your cursors in the declare section, they might not be able to 'see' your variables.
ops$tkyte%ORA10GR2> create or replace package foo
2 as
3 procedure p;
4 end;
5 /
Package created.
ops$tkyte%ORA10GR2> create or replace package body foo
2 as
3 globals_are_a_bad_idea number := 1;
4
5 cursor c1 is select globals_are_a_bad_idea x from dual;
6 cursor c2(a_parameter in number) is select a_parameter x from dual;
7
8 procedure p
9 is
10 globals_are_a_bad_idea number := 100;
11 l_n number;
12 begin
13 open c1;
14 fetch c1 into l_n;
15 close c1;
16 dbms_output.put_line( 'n = ' || l_n );
17
18 open c2(globals_are_a_bad_idea);
19 fetch c2 into l_n;
20 close c2;
21 dbms_output.put_line( 'n = ' || l_n );
22 end;
23 end;
24 /
Package body created.
ops$tkyte%ORA10GR2> exec foo.p
n = 1
n = 100
PL/SQL procedure successfully completed.
so, if you are using cursors defined as I did, you'd always want to parameterize them for safety - just like you would NEVER use a global instead of a formal parameter to 'pass' data to a subroutine.
cursor
September 24, 2009 - 9pm Central time zone
Reviewer: A reader
Cursor Expression
October 1, 2009 - 8am Central time zone
Reviewer: Tino from Tampa, FL
I am using the HR test schema in Oracle10gR2 to look at the workings of cursor expressions and I generated an execution plan which looks pretty interesting to me. The query returned 27 rows but the A-rows column doesn't show any row activiting for the employees table. Could you explain.
select /*+ gather_plan_statistics */ department_id,
cursor (select employee_id, last_name, first_name
from employees b
where b.department_id = a.department_id) emps
from departments a;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 3vvkz493vfbd6, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ department_id, cursor (select employee_id, last_name,
first_name from employees b where b.department_id = a.department_id) emps
from departments a
Plan hash value: 2785328677
----------------------------------------------------------------------------------------------------
-------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
Buffers |
----------------------------------------------------------------------------------------------------
-------
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 0 | 10 | 0 |00:00:00.01 |
0 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 0 | 10 | 0 |00:00:00.01 |
0 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 27 |00:00:00.01 |
2 |
----------------------------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."DEPARTMENT_ID"=:B1)
Followup October 7, 2009 - 9am Central time zone:
well, given that the cursor query is executed once per each row - not just once like the outer query, I'm not sure at all what they would put there. The e-rows are the estimate for a single execution - there are many tens, hundreds, thousands or more executions of the cursor() expression however.
They just don't have a sensible way to track it.
Clarity needed on cursor declaration
December 17, 2009 - 8am Central time zone
Reviewer: Dayne Olivier from Cape Town, South Africa
Tom,
I have been reading up a lot lately on cursors and SQL parsing and have read about your "softer" soft parse concept. I want to try and achieve the most optimal parse I can (as close to none as possible) but am not clear on some cursor declaration and usage semantics.
I provide you two scenarios below. The first makes use of a ref cursor declared as a sys_refcursor and the second makes use of a static cursor declared using "cursor is".
Scenario 1:
procedure print_vehicle_list(p_userid in number) is
l_cursor sys_refcursor;
l_id number;
l_descrip varchar2(32767);
begin
htp.p('<select name="p_vehicle">');
open l_cursor for
select id, veh_registration
from vehicles
where veh_owner = p_userid;
loop
fetch l_cursor into l_id, l_descrip;
exit when l_cursor%NOTFOUND;
if l_id = p_selected then
htp.p('<OPTION SELECTED value="' || l_id ||'">' || substr(l_descrip, 1, 80) ||
'</OPTION>');
else
htp.p('<OPTION value="' || l_id ||'">' || substr(l_descrip, 1, 80) || '</OPTION>');
end if;
end loop;
close l_cursor;
htp.p('</select>');
exception
when others then
if l_cursor%ISOPEN then
close l_cursor;
end if;
raise;
end print_vehicle_list;
Scenario 2:
procedure print_vehicle_list(p_userid in number) is
cursor l_cursor(cp_userid in number) is
select id, veh_registration
from vehicles
where veh_owner = cp_userid;
l_id number;
l_descrip varchar2(32767);
begin
htp.p('<select name="p_vehicle">');
open l_cursor(p_userid);
loop
fetch l_cursor into l_id, l_descrip;
exit when l_cursor%NOTFOUND;
if l_id = p_selected then
htp.p('<OPTION SELECTED value="' || l_id ||'">' || substr(l_descrip, 1, 80) ||
'</OPTION>');
else
htp.p('<OPTION value="' || l_id ||'">' || substr(l_descrip, 1, 80) || '</OPTION>');
end if;
end loop;
close l_cursor;
htp.p('</select>');
exception
when others then
if l_cursor%ISOPEN then
close l_cursor;
end if;
raise;
end print_vehicle_list;
Questions:
1) What is the different between the two cursor declarations and usage with regard to parsing (hard/soft/softer)?
2) How would you provide a concrete test case to question 1?
Thanks in advance Tom!
Followup December 17, 2009 - 10am Central time zone:
Well, I see you are using mod_plsql - htp.p stuff.
So, basically, after each execution, mod_plsql will reset the package states - which will close all open cursors.
In this case, if you call print_vehicle list ONCE on a page, you will parse exactly the same amount of times. You will not hit session cached cursors - since you do not call this at least three times (we are assuming once this time). Both will soft parse the select statement once.
If you call print_vehicle lists three times or less, you will parse the non-refcursor SQL once (probably, unless you do lots and lots of other sql in between the two/three calls) and the ref-cursor will be parsed THREE times (we cannot cache them like we can cache the statement sql). No softer soft parses either, for either of them (which is OK for the nonrefcursor)
If you call print_vehicle lists more than three times - then the nonrefcursor will parse ONCE and the refcursor will parse N times and N-3 of them will be softer soft parses (3 of them will be soft parses)
As for "how to provide test case" - enable sql_trace, run code, review tkprof - it shows the parsing stuff.
So, do not use a refcursor unless you need to (dynamic sql, picking just the right query of a small set, returning a result set).
And further, you would want to employ array fetching - in 10g and above the easiest way to do that will be:
ops$tkyte%ORA11GR2> create or replace
2 procedure print_vehicle_list(p_userid in number, p_selected in number )
3 is
4 cursor l_cursor(cp_userid in number) is
5 select id, substr(veh_registration ,1,80) descrip
6 from vehicles
7 where veh_owner = cp_userid;
8 begin
9 htp.p('<select name="p_vehicle">');
10 for x in l_cursor(p_userid)
11 loop
12 if x.id = p_selected
13 then
14 htp.p('<OPTION SELECTED value="' || x.id ||'">' || x.descrip || '</OPTION>');
15 else
16 htp.p('<OPTION value="' || x.id ||'">' || x.descrip || '</OPTION>');
17 end if;
18 end loop;
19 htp.p('</select>');
20 end print_vehicle_list;
21 /
Procedure created.
use cursor for loops - less code = less bugs.
Also, your exception block is entirely unnecessary, cursors that go out of scope as yours would are closed automagically for you - you need not close them.
And the exception block would be really unnecessary for my approach - use cursor for loops, they rock.
December 17, 2009 - 10am Central time zone
Reviewer: Dayne Olivier from Cape Town, South Africa
Hi Tom,
I know I am not giving you a chance to respond to my previous question, but I tried a test myself and came up with the following:
I first created the following test objects:
CREATE TABLE VEHICLES
(
ID NUMBER,
REG# VARCHAR2(7 BYTE),
VEH_OWNER VARCHAR2(20 BYTE) DEFAULT user
)
/
SET DEFINE OFF;
Insert into VEHICLES
(ID, REG#, VEH_OWNER)
Values
(1, 'NT4842', 'DAYNEO');
Insert into VEHICLES
(ID, REG#, VEH_OWNER)
Values
(2, 'CA1234', 'DAYNEO');
Insert into VEHICLES
(ID, REG#, VEH_OWNER)
Values
(3, 'CA5432', 'DAYNEO');
Insert into VEHICLES
(ID, REG#, VEH_OWNER)
Values
(4, 'ND2345', 'SSAWYER');
Insert into VEHICLES
(ID, REG#, VEH_OWNER)
Values
(5, 'ND987', 'SSAWYER');
COMMIT;
CREATE OR REPLACE PROCEDURE TEST_PROC1 IS
CURSOR L_CURSOR(CP_OWNER IN VARCHAR2) IS
SELECT ID, REG#
FROM VEHICLES
WHERE VEH_OWNER = CP_OWNER;
L_ID NUMBER;
L_REG VARCHAR2(32767);
BEGIN
FOR I IN 1..10000 LOOP
OPEN L_CURSOR(USER);
LOOP
FETCH L_CURSOR INTO L_ID, L_REG;
EXIT WHEN L_CURSOR%NOTFOUND;
NULL;
END LOOP;
CLOSE L_CURSOR;
END LOOP;
END TEST_PROC1;
/
CREATE OR REPLACE PROCEDURE TEST_PROC2 IS
L_CURSOR SYS_REFCURSOR;
L_ID NUMBER;
L_REG VARCHAR2(32767);
BEGIN
FOR I IN 1..10000 LOOP
OPEN L_CURSOR FOR
SELECT ID, REG#
FROM VEHICLES
WHERE VEH_OWNER = USER AND 1=1;
LOOP
FETCH L_CURSOR INTO L_ID, L_REG;
EXIT WHEN L_CURSOR%NOTFOUND;
NULL;
END LOOP;
CLOSE L_CURSOR;
END LOOP;
END TEST_PROC2;
/
I then used your RUNSTATS package and got the following answers:
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
dayneo@SANDBOX> EXEC RUNSTATS_PKG.RS_START();
PL/SQL procedure successfully completed.
dayneo@SANDBOX> EXEC TEST_PROC1();
PL/SQL procedure successfully completed.
dayneo@SANDBOX> EXEC RUNSTATS_PKG.RS_MIDDLE();
PL/SQL procedure successfully completed.
dayneo@SANDBOX> EXEC TEST_PROC2();
PL/SQL procedure successfully completed.
dayneo@SANDBOX> EXEC RUNSTATS_PKG.RS_STOP();
Run1 ran in 1053 hsecs
Run2 ran in 1050 hsecs
Run1 ran in 100.29% of the time
Name Run1 Run2 Diff
LATCH.job_queue_processes para 1 0 -1
STAT...consistent gets 100,006 100,007 1
LATCH.library cache pin alloca 1 0 -1
LATCH.Consistent RBA 1 2 1
LATCH.lgwr LWN SCN 3 4 1
LATCH.mostly latch-free SCN 3 4 1
LATCH.KMG MMAN ready and start 4 3 -1
LATCH.OS process allocation 4 3 -1
LATCH.ksuosstats global area 0 1 1
LATCH.session idle bit 25 24 -1
LATCH.session allocation 1 0 -1
STAT...redo entries 8 9 1
STAT...calls to kcmgcs 4 5 1
STAT...consistent gets from ca 100,006 100,007 1
LATCH.session timer 4 3 -1
STAT...opened cursors current 3 1 -2
LATCH.channel operations paren 48 50 2
STAT...bytes received via SQL* 1,154 1,152 -2
STAT...CPU used by this sessio 1,048 1,045 -3
LATCH.parallel query alloc buf 0 4 4
STAT...CPU used when call star 1,048 1,044 -4
STAT...DB time 1,053 1,049 -4
STAT...db block changes 45 40 -5
STAT...consistent changes 36 31 -5
LATCH.enqueues 167 162 -5
LATCH.session state list latch 6 0 -6
STAT...enqueue releases 7 1 -6
STAT...enqueue requests 8 1 -7
LATCH.enqueue hash chains 172 164 -8
STAT...session logical reads 100,044 100,034 -10
STAT...db block gets 38 27 -11
STAT...db block gets from cach 38 27 -11
STAT...undo change vector size 2,004 2,016 12
LATCH.active checkpoint queue 4 16 12
LATCH.redo allocation 18 32 14
LATCH.shared pool 29 13 -16
LATCH.redo writing 13 30 17
STAT...redo size 2,684 2,708 24
LATCH.messages 68 92 24
STAT...parse time cpu 0 26 26
STAT...parse time elapsed 0 37 37
STAT...recursive cpu usage 614 652 38
LATCH.SQL memory manager worka 207 274 67
LATCH.simulator lru latch 0 134 134
LATCH.simulator hash latch 0 134 134
LATCH.checkpoint queue latch 56 230 174
LATCH.object queue header oper 20 294 274
LATCH.cache buffers lru chain 8 407 399
LATCH.cache buffers chains 200,207 201,302 1,095
LATCH.library cache 40,100 50,086 9,986
STAT...parse count (total) 7 10,005 9,998
STAT...opened cursors cumulati 7 10,005 9,998
STAT...recursive calls 60,004 70,002 9,998
STAT...execute count 20,005 10,005 -10,000
STAT...calls to get snapshot s 40,002 30,001 -10,001
LATCH.library cache pin 40,064 20,056 -20,008
LATCH.library cache lock 29 40,023 39,994
STAT...session pga memory 65,536 0 -65,536
STAT...session uga memory 65,560 0 -65,560
STAT...session uga memory max 261,964 65,560 -196,404
STAT...session pga memory max 262,144 65,536 -196,608
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
281,437 313,721 32,284 89.71%
PL/SQL procedure successfully completed.
My interpretation of these results would be that the static cursor declaration (using "cursor xxxx is" in the procedure declaration) is a much better choice than the sys_refcursor declaration due to the major difference in "parse count", "open cursors ..." and "library cache lock" stats and latches.
Would this be a correct interpretation?
Is there anything else I should be considering?
Followup December 17, 2009 - 10am Central time zone:
see above....
December 17, 2009 - 12pm Central time zone
Reviewer: Dayne Olivier from Cape Town, South Africa
Hi Tom,
Thanks for that prompt response. Yes, I am using MOD_PLSQL. Actually we are having some major performance problems on a production server and the database waits are reporting 40% on "latch library cache ". I am therefore reviewing the application code and have found mountains and mountains of "execute immediate" and "open for" using concatenated SQL without bind variables.
I converting as many SQL as I can to static SQL and after a whole lot of reading, I discovered that we can use a new parameter that should preserve the SQL cache after each page execute.
The parameter is "PlsqlSessionStateManagement" and supposedly setting it to "StatelessWithFastResetPackageState" will preserve the SQL cache.
See: http://download.oracle.com/docs/cd/B15897_01/web.1012/b14007/confmods.htm#CIHHEBJG
After implementing this, I received user reports stating the application "seemed" faster. So at this point I am kind of assuming it worked without any real numbers to support it.
I have however converted all "open for" dynamic sql without binds, into "open for" with hard coded sql with binds. You have however provided me with the method of getting the numbers (proof)... sqltrace. I will try that first thing in the morning.
More:
As I understand it, you are referring to :
procedure testproc is
cursor l_cur is select col from tbl;
begin
for rec in l_cur loop
..
..
..etc, etc
as static sql
while:
procedure testproc is
l_refcur sys_refcursor;
begin
open l_refcur for select col from tbl;
..
..
..etc, etc
is dynamic sql?
Would that be similar to:
procedure testproc is
l_refcur sys_refcursor;
begin
open l_refcur for 'select col from tbl';
..
..
..etc, etc
which is dynamic sql for sure?
Followup December 17, 2009 - 2pm Central time zone:
... using concatenated SQL without bind variables. ...
that'll be the major contributor to the latch free waits, the hard parsing.
... After implementing this, I received user reports stating the application "seemed" faster. So at this point I am kind of assuming it worked without any real numbers to support it. ...
you said "are reporting 40% on "latch library cache ". Why not just see what you are now? That would help confirm if this did anything material (beware of potential increased memory usage, as the session package memory is not freed using this)
You are not doing dynamic sql at all here - no where. a refcursor is NOT dynamic sql necessarily. It is a pointer to a cursor and cannot be cached by PLSQL in the way a 'regular' cursor can be
ccursor
December 17, 2009 - 3pm Central time zone
Reviewer: A reader
Tom:
is it ok or bad practice to do for loop cursor like this if you have one record in the result set.
would it affect performance?
for x in (select col1,col2,.. from table where empno = p_empno)
loop
---code here
end loop;
would performance be better using this syntax
SELECT col1,col2.. into v_col1,v_col2 from emo where empid=p_empid;
Followup December 17, 2009 - 4pm Central time zone:
If you expect
a) at least one
b) at most one
or just
a) at most one - ever
then select into is the right thing - not for performance reasons - but for code correctness.
select into ensures AT LEAST ONE and AT MOST ONE. Your cursor for loop would need a lot more code to do that.
So, use select into when the result set is supposed to have AT MOST ONE record - if you are expecting at least one and at most one, just code:
begin
....
select into
....
end;
if you are expecting zero or one records, then:
begin
....
begin
select into
exception
when no_data_found then initialize_variables_appropriately;
end;
....
end;
but do NOT use a cursor for loop - you would have to code a lot of code to get the correct code!
cursor
December 17, 2009 - 7pm Central time zone
Reviewer: A reader
appreciate it tom.
But i see it the other way.
with SELECT col1,col2 into v_col1,v_col2,
i have to declare 50 variables in my declare section.
while doing
for x in (sellct col1,... from table)
is a straight hit --- no vairables.
Is it worth to change already production code written this way from for loop to select?
Followup December 18, 2009 - 12pm Central time zone:
umm, you had BETTER write code like:
l_found := false;
for x in (select .... from t)
loop
if (l_found) then raise program_error;
l_found := true;
process...
end loop;
if not(l_found)
then
raise program_error;
end if;
else, your code *has a bug*. Seriously, do you see the difference between "your code has a bug" and using select into?
and your point isn't entirely valid
ops$tkyte%ORA9IR2> declare
2 l_rec scott.emp%rowtype;
3 begin
4 select * into l_rec from scott.emp where rownum = 1;
5 end;
6 /
PL/SQL procedure successfully completed.
... Is it worth to change already production code written this way from for loop to
select?..
IF you have code where by select into should have been used
then you have a bug in your code
UNLESS you have logic to detect "at least one, at most one"
December 18, 2009 - 2am Central time zone
Reviewer: Dayne Olivier from Cape Town, South Africa
To "A Reader",
I think what Tom is saying is that you use "select .... into ..." when you expect one (exactly one)
record returned. Using "select ... into ..." you would receive an error during execution if the
statement returns no rows or more than one row. So you would use "select ... into..." only when,
from a business/function rule, it would be an error to receive no rows, or more than one row.
I don't think you should even be worrying about the "additional effort" in the "select ...
into..."... The only thing you should be considering is whether you expect one and only one record
returned. I think that if you don't consider it this way and you use "for loop" when it should be a
"select ... into...", then it becomes an application bug (hiding from site until one day you get
that "intermittent" error).
Assuming you select all the columns from a table, you can shorten the "select ... into ... " to:
<code>
declare
l_rec tbl%rowtype;
begin
select * into l_rec from tbl where col = :bv;
...
...
or you can define a record type:
declare
type my_rec_type is record
(
col1 datatype,
col2 datatype,
..
coln datatype
);
l_rec my_rec_type;
begin
select col1, col2, ... coln into l_rec from tbl where colx = :bv;
...
...
</code>
cursor
December 29, 2009 - 1pm Central time zone
Reviewer: A reader
tom:
your check for the "many rows found" is not needed when the query is for a primary key like
for x in (select * from EMP where empid=p_empid)
loop
code..
end loop;
Also the
select co1l,col2..into l_rec from EMP works for simple structures (one table).
If you have multiple table joins in the query the "l_rec" will not work. You either have to one
variable per column or a crusor variable which means the SELECT will have to be define in a cursor.
Followup January 4, 2010 - 8am Central time zone:
... your check for the "many rows found" is not needed when the query is for a
primary key like
...
really, are you sure?
ops$tkyte%ORA10GR2> create table t
2 ( x int primary key deferrable initially deferred )
3 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t select 1 from all_users;
46 rows created.
or, what about inadvertent corruption? You know, something you DID NOT EXPECT.
You know when programs (software) dies a horrible death? When something that cannot happen - happenes, and that something happens with alarming frequency
And you know what, I have no problem with you having to actually write a bit of code, I'm not worried about optimizing for your fingers - I'm worried about optimizing for correctness.
... If you have multiple table joins in the query the "l_rec" will not work. ...
can you spell "view"?
and you still need the check for AT LEAST ONE record.
Select into, you cannot beat it. No matter how hard you try to fight it. I'm never on the side of the coder when they start whining about having to write a little bit more code to do something correctly, I just call that lazy.
And the "it cannot happen" stuff - always happens.
April 23, 2010 - 4am Central time zone
Reviewer: Sreekanth from Bangalore, India
Hi Tom,
My Question is regarding Parameterized cursors.Can you please tell me the difference between the
below two blocks :
Block 1 :
---------------------------------------------
Declare
n number;
cursor c is select 'Inside The Cursor' d from dual where 11 > n;
begin
n := 10;
for i in c loop
dbms_output.put_line(i.d);
end loop;
end;
---------------------------------------------
and
Block 2 :
---------------------------------------------
Declare
cursor c(p_n number) is select 'Inside The Cursor' d from dual where 11 > p_n;
begin
for i in c(10) loop
dbms_output.put_line(i.d);
end loop;
end;
---------------------------------------------
Thanks in advance....
Followup April 23, 2010 - 9am Central time zone:
they are the same, however I would say the second one is a far superior coding method - wouldn't you? It is much more clear.
Cursor with hints
February 2, 2011 - 1pm Central time zone
Reviewer: Snehasish Das from USA
Hi Tom,
Can you please help me with the basic functioning of the cursor or point me to any online or
hardbook material which would help me understand it.
1. cursor C_EMP as select /*+ PARALLEL(emp,4) */ * from emp;
better than C_EMP as select * from emp;
if the emp table has say millions or record does the
parallel hint help.
2. When we write a cursor say C_EMP as above.
and then open C_EMP;
when is the results fetched. Are the results stored in
PGA, if the pga memory is small for the cursor result
will we get any errors.
3. for the cursors like for c1 in (select ename from emp )
is the cursor opened and fetched multiple times.
Please help.
Snehasish Das.
Followup February 3, 2011 - 1pm Central time zone:
1) it depends.
If you have a process (a client) that is capable of processing the data really really fast - then using parallel query would allow us to read the data in parallel and would allow you to possible avoid some IO waits.
In general however, it would be dubious that a parallel single table full table scan without a sort or anything would be improved over a serial execution. The client fetching the data would be the bottleneck in this case.
2) The results for that query would be retrieved from disk as needed - the entire result set isn't stored anywhere.
That query would not fail due to out of memory - there would be very little memory associated with it - we fetch from disk as you need the data.
Now, if the query was select * from emp order by ename - then we would likely do a full scan + sort order by. In that case - we would retrieve the entire result set - sort it and likely store much of it in TEMP after exhausting the PGA sortarea we were using.
3) a cursor in the form of:
for x in (select ename from emp )
loop
...
is opened once - fetched from - and then closed when the result set is exhausted or when you exit from the loop
Cursor Record Set
February 3, 2011 - 11pm Central time zone
Reviewer: Snehasish Das from USA
Hi Tom,
Thanks a lot for the answer.
For 1. It was a simplified version of query that i wrote to understand the basic priniciples of a
cursor. We have cursors joining 5-6 tables with many conditions. I guess then the Parallel Hint
really helps.
2. Forgive my ignorance. If i am reading it right then what I understand is that when we Open a
cursor we are not fetching the records, so if we fetch records then is the cursor query fired again
and again for different fetches. How does oracle keep a counter to point to the next record when we
have fetched the pervious row.
Regards,
Snehasish Das.
Followup February 4, 2011 - 9am Central time zone:
1) yes, it could - parallel query is good at taking a large amount of work - like a sort, an aggregate, big joins and breaking it into smaller bits of work that can be done at the same time - assuming ample resources to do so exist.
You should over simplify - the answers to questions change dramatically...
2) the query is "fired" only once. The query has a "plan" generated for it - the plan is in fact a program, a bit of code, a binary object that is interpreted by a runtime engine - just like java byte code or plsql byte code.
The SQL program keeps its place just like Java remembers where it is - instruction pointers and the like. There is an entire state associated with a cursor.
Just think of SQL like any other bit of code you write. Your code gets compiled and then executed. SQL is just like that.
Fetch Operation
February 4, 2011 - 11am Central time zone
Reviewer: Sanghamitra Sen from India
Hi Tom,
I wanted to understand the fetch operation of cursor.
cursor C1 is select * from emp;
begin
open c1; <--- Cursor query is fired and as you say above a object map is created.
loop
fetch c1 into emp_rec; <-- you said above that the result is not stored anywhere so where is it
getting result from, and who keeps the counter i.e when i am doing the next fetch who points me to
the next record.
which when c1%NOTFOUND;
end loop;
Thanks in advance.
Sanghamitra.
Followup February 4, 2011 - 1pm Central time zone:
SQL is compiled into binary code, just like a program.
it is executed in a SQL VM (virtual machine), just like a program.
The same techniques "programs" use to remember "where they are, what state they are in, what they are doing" is used by SQL - there is a state associated with your cursor - a private sql area that holds all of the information necessary to process your requests.
So, it is the same way your PLSQL routine 'remembers' where it is after it comes back from calling SQL - the program has a state.
And I said the result DOESN'T have to be stored anywhere - it depends. If the query was select * from emp order by ename - then the result is probably coming from TEMP
cursor
August 2, 2011 - 7am Central time zone
Reviewer: sam
Tom:
1) Would you consider this to be OK or you would use variables in the cursor.
I did not see a need for variables since the hardcoded values are "static" to build the web page
and do not depend on what user enters.
CURSOR c_vend_contracts (p_vendor varchar2) IS
SELECT vendor, cntr, cy, status FROM vendor_contracts
WHERE vendor = p_vendor
AND cy >= 2010
AND format = 'DVD'
AND status = 'A'
ORDER BY cy desc, cntr
2)
<<just like you would NEVER use a global instead of a formal parameter to 'pass' data to a
subroutine.>>
Why?
I have a many web pages that lookup some SYSTEM CONTROL PARAMETERS defined in a lookup table (i.e
Number of ROWS, ROW COLOR, etc.)
Would you rather have the same SELECT statements in every SP, or have a package that defines all
of these and reference the package values in each Stored procedure.
Followup August 2, 2011 - 7am Central time zone:
1) when using static SQL in plsql - it is not possible to use bind variables incorrectly.
No matter how many times you execute the above query - the values 2010, 'DVD', 'A' will not change - the only variable in that query is p_vendor and it will use a bind variable.
Literals in static sql in plsql are 100% ok, fine, good, desirable.
2) using globals to pass values to procedures is commonly considered a "really bad idea" in all programming languages.
I have a many web pages that lookup some SYSTEM CONTROL PARAMETERS defined in
a lookup table (i.e Number of ROWS, ROW COLOR, etc.)
Those are not parameters - those are constants your program refers to. You don't expect someone to set ROWS, ROW COLOR, etc and then call your procedure right? You defined them once and everyone uses the same values. That is different then a parameter.
For example - you could have a routine:
procedure get_emp_data( p_empno in number ) return emp_table%rowtype
as
begin
....
Or you could have a routine in a package:
create or replace package my_pkg
as
g_empno number;
procedure get_emp_data return emp_table%rowtype;
end;
/
create or replace package body my_pkg
as
procedure get_emp_data return emp_table%rowtype
as
begin
... reference g_empno here ...
end;
....
That second method would be a really bad idea style wise, maintenance wise.
August 2, 2011 - 10am Central time zone
Reviewer: sam
Tom:
Is using a package variable appropriate for looking up CONSTANTS defined in a table like this?
CREATE TABLE ref_sys_params
(
rows_per_page NUMBER,
row_color NUMBER,
col3 NUMBER,
col4 NUMBER,
);
instead of having this in every SP
SELECT ROWS_PER_PAGE, ROW_COLOR, COL3, COL4
into l_rows_per_page, l_row_color, l_col3, l_col4
FROM REF_SYS_PARMAS;
I do this
CREATE OR REPLACE PACKAGE param_pkg
AS
TYPE param_tab IS TABLE OF ref_sys_params%ROWTYPE;
g_params param_tab;
END;
CREATE OR REPLACE PACKAGE BODY param_pkg
AS
BEGIN
SELECT * INTO g_params FROM ref_sys_params;
END;
--OTHER STORED PROCEDURES REFERENCE PACKAGE
BEGIN
DBMS_OUTPUT.put_line(param_pkg.g_params.rows_per_page);
DBMS_OUTPUT.put_line(param_pkg.g_params.rows_color);
END;
Followup August 2, 2011 - 11am Central time zone:
I wonder why you have a table of ref_sys_params%rowtype? Especially since you don't have an order by on your select - nor a bulk collect?
cursor
August 2, 2011 - 4pm Central time zone
Reviewer: sam
Tom:
yes, i originally had BULK COLLECT but it is only ONE ROW. I changed it.
g_params ref_sys_params%rowtype;
begin
select * into g_params from ref_sys_params;
end;
I think The packaged variable approach (instead of having SELECT in each SP) makes it easier for
maintenace. I dont think there will be performance improvement since it is only ONE ROW and oracle
should cache the data after the first SELECT. correct?
Followup August 2, 2011 - 4pm Central time zone:
I would concurr, yes. This is not using globals as a parameter, this is using them like constants - which is considered OK.
|