Thanks for the question, rd.

Asked: August 15, 2001 - 12:24 pm UTC

Last updated: January 27, 2006 - 8:13 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Tom

A lot of times when you are working, quite a few misc questions croos our minds, which we dont attend to or postpone. I have put togeteher a list of such qeustions in my case.

1.From the sqlplus prompt , how should I declare a variable, then use a select statement to store a value in that variable , and then display it.

2.When I use the set serveroutput on and dbms_output.put_line I frequently get , buffer overflow error. what is the reason for this error and how shoud I get over this.

3.From some of the posts I have come to the conclusion that we should not use the when others than exception( when you dont expect any errors), and allow the errors to propogate out of pl/sql, so that the user will know there has been an error. Your comments please.

4.When using sqlplus, I do line<line number> to get a particular line from the buffer, how can I get a group of lines.

5.What is the difference between call and execute sqlplus commands.

6.Why is it that when I run a sql from sqlplus, it gets parsed each time it is run, where as when I run the same sql as a part of a procedure it gets parsed only once for multiple executions.

7.What do the max and min transactions mean in the create table syntax. Can we give them at the table space level.

8.How can I save all my current settings of my sqlplus session in a file.

9.what is the maxvalue we can give to set line size.

10.when I do a insert into t select * from t1, can I capture the rowcount using sql%ROWCOUNT.

11.when I do two consequetive loop like

for rec in c1 loop
for records in c2 loop

..some processing

can I refer to the records in the first loop in the second loop

if I want such type of functionality what is the way of doing it.

12.Which is faster a select into into <variable names> or a cursor fetch or a cursor loop like for rec in c1 loop,and why.

In case if select into is faster , then why do everyone use cursors.

13.I want to know how records are being inserted by a large select statement. One method to do it is using the dbms_application_info package, but I want to know how to do the same using dbms_pipe and dmbs_alert.

14.What is the purpose of using dmbs_session.set_context

15.From your answers , I have come to the conclusion that the order of preference for maximum performance is to do a particular task

i.do it in pure sql if possible
ii.do it in pl/sql
iii.do it in pro*c or c
iii. do it in java
your comments please

and Tom said...

You know, based on your questions -- i have a book suggestion for you:

</code> http://www.amazon.com/exec/obidos/ASIN/1861004826/002-4789950-3064808


I go into most all of this in great depth in there.

this is a truly loooonnnnggg list.

1) I usually use a substitition variable for that:

ops$tkyte@ORA817.US.ORACLE.COM> column X new_val Y
ops$tkyte@ORA817.US.ORACLE.COM> select dummy X from dual;

X
-
X

ops$tkyte@ORA817.US.ORACLE.COM> prompt &Y
X

You can also use bind variables:

ops$tkyte@ORA817.US.ORACLE.COM> variable xx varchar2(20);
ops$tkyte@ORA817.US.ORACLE.COM> begin
  2  select dummy into :xx from dual;
  3  end;
  4  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817.US.ORACLE.COM> print xx

XX
--------------------------------
X


2) if you want the WHOLE story (and its longer then you think) about dbms_output -- get my book.  I explain in somewhat gory detail how dbms_output works exactly.

see

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1011431134399

for the short story.

You can set 

SQL> set serveroutput on size 1000000

to get the maximum output


3) You hit the nail on the head -- thats exactly what I'm trying to say, glad it got across.

A when others will "hide" an error from the caller.  If you do not HANDLE the error, if you are not EXPECTING the error -- then DONT CATCH and subsequently IGNORE the error.  Its a bug waiting to happen.

4)

SQL> list 100 105

will list lines 100 - 105 of the buffer.

5) The CALL statment is SQL (and only understands SQL types).
   EXEC is really shorthand for begin/end;

An example helps best.  CALL can only call those functions/procedures that have SQL types as inputs/outputs so if we:

ops$tkyte@ORA817.US.ORACLE.COM> create or replace procedure p( x in boolean default false )
  2  as
  3  begin
  4          null;
  5  end;
  6  /

Procedure created.

<b>boolean is NOT a sql type but we can call P() using call</b>

ops$tkyte@ORA817.US.ORACLE.COM> 
ops$tkyte@ORA817.US.ORACLE.COM> call p();

Call completed.


<b>but not when we want to supply that boolean input:</b>

ops$tkyte@ORA817.US.ORACLE.COM> call p(true);
call p(true)
       *
ERROR at line 1:
ORA-06576: not a valid function or procedure name

<b>Now exec works cause that runs plsql to run the proceudre:</b>

ops$tkyte@ORA817.US.ORACLE.COM> exec p(true);

PL/SQL procedure successfully completed.

<b>and this just shows that exec is really just shorthand for begin/end;</b>

ops$tkyte@ORA817.US.ORACLE.COM> exec blah
<b>BEGIN blah; END;</b>

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'BLAH' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

6) because SQLPlus uses only one cursor for your statements.  PLSQL caches and keeps many cursors open for you.  PLSQL is optimized for running SQL and PLSQL in the database.  SQLPlus is just a simple little interface.

7) initrans and maxtrans control the initial size of the transaction table on a block and maxtrans limits the overall size.  A transaction table can dynamically grow in size given sufficient free space on the block so the defaults are generally sufficient.  

These settings are specific to segments are are not settable at the tablespace level.

8) see

http://docs.oracle.com/cd/A81042_01/DOC/sqlplus.816/a75664/ch845.htm#1002991


9) see

http://docs.oracle.com/cd/A81042_01/DOC/sqlplus.816/a75664/ch839.htm#1002118


it is OS dependent.  If you do something like:


ops$tkyte@ORA817.US.ORACLE.COM> set linesize 999999999999999999999999999999999
SP2-0267: linesize option -1 out of range (1 through 32767)

it'll tell you.

10) yes.

11) yes -- you can refer to the current value of rec inside of that loop.

12) see

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1544606261686


implicit cursors rule, they are faster, better, cheaper, easier to code -- they ain't broke.

explicit cursors are harder to code, slower -- using them only when you have no choice.

I don't know why people persist in believing mythology such as "explicit cursors are faster".  Once upon a very very long time ago -- it was true in FORMS (like version 2 of forms) that an explicit cursor was marginally faster then a select into HOWEVER that changed many many years ago (like 8 or 9 years ago)

13) I don't know how to do that using dbms_application_info, or dbms_pipe/alert.  If you "insert into t select* from t2" - you can query v$session_longops to see how long it is estimated to take from another session but the session doing the insert into cannot make use of any of those packages during that statement

14) see

http://asktom.oracle.com/~tkyte/article2/index.html

for the use of that package



15) almost -- i actually list this in my book in order:

i) sql
ii) plsql
iii) java stored procedure
iv) c

sql = fastest, easiest, least amount of code to maintain
plsql = best integration with sql, fast, easy to code, best in the database
java = do all of the things plsql cannot do (eg: send an email, write a file)
c = that which java cannot do or java does too slowly.



<b>followup to comment one</b>

Yes - that is the hypothesis people base their fundematal misunderstanding on.

A select into is known by Oracle -- we prefetch in one call 2 rows.  We make no extra call (you can verify this via a SQL_TRACE+TKPROF -- you'll see 1 fetch being made for a select into -- never two).

The other fundemental issue is that:

open c;
fetch c into x;
close c;

is not in any way shape or form equivalent to 

select x into x from t;


You must code:

open c;
fetch c into x;
if (c%notfound) then raise no_data_found; end if;
fetch c into x;
if (c%found) then raise too_many_rows; end if;
close c;

by the time you've done that -- you are way worse off then a select into (which will do the above in C very quickly with minimal round trips)


The mantra should be: if you can do it in a single sql statement -- do it, it'll be faster, cheaper and easier to code.


<b>followup to comment 2, Very good, but we want this clarified</b>

One case is when you use a dynamically opened ref cursor for example.  Eg:

declare
   type rc is ref cursor;
   l_cursor rc;
begin
    open l_cursor for 'some string....';
    loop
       fetch l_cursor into l_rec;
       exit when l_cursor%notfound;
    end loop;
    close l_cursor;
end;

that you cannot do with an implicit cursor. 

<b>followup to comment 3, "dont leave us in confusion"</b>

See

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1544606261686


Also, we just had a big discussion on this in the newsgroups not too long ago:

http://groups.google.com/groups?hl=en&safe=off&th=c41fe18e5bd97a33,17&start=0 <code>

followup to comment 4

Well, tell them its FASTER to code select into -- the code runs FASTER.

If the data in the select into comes from a single table, the code is:

declare
l_rec T%rowtype;
begin
select * into l_rec from t where ....;

using an explicit cursor you have to code:

declare
cursor c is select ....;
l_rec c%rowtype;
begin
open c;
fetch c into l_rec;
if ( c%notfound ) then
raise no_data_found;
end if;
close c;

which one is harder to code???? And what if the select into isn't based on a table but many? Well, I might use a template cursor:

declare
cursor c is select * from t1, t2, t3;
l_rec c%rowtype;
begin
select * into l_rec
from t1, t2, t3
where .......;

still easier then an explicit cursor (note that template cursor doesn't need anything other then the select list and the from clause -- no joins, we'll never use it)

followup to comment 5

Get off the fence, use implicit cursors until you cannot -- then use an explicit one. they are better.

followup to comment 7

The whole store is that

o implicit cursors are easier to code by their very nature. (i'm including cursor for loops in here as well -- not just select intos)

o implicit cursors run faster

o implicit cursors are easiest to understand in the code, less code to read through, less code to manage.

Rating

(37 ratings)

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

Comments

A book in itself

Trevor, August 16, 2001 - 6:28 am UTC

This posting was a book in itself.

I think the idea explicit cursors are faster stems
from a book which explicit cursors were better because
it said something like, implicit cursors always did an
extra fetch. When it reached the end of a data set
and implicit cursor (I heard) will do another read
to check if it really was the end. whereas an explicit
cursor you can control in you program so when the
no more rows is reached you end things.

Very good, but we want this clarified

abbajaan, August 16, 2001 - 9:34 am UTC

Please give us an example for the following 'explicit cursors are harder to code, slower -- using them only when you have no choice.', what are those situations or situation where we can consider tha we have no choice but to use explicit cursors.

dont leave us in confusion

John Quirk, August 16, 2001 - 9:37 am UTC

Illustrate you statement "by the time you've done that -- you are way worse off then a select into (which
will do the above in C very quickly with minimal round trips)" with examples, that is the only way out of this

Daaler, August 16, 2001 - 10:26 am UTC

Developers have got used to explicit cursor so much , that select into is kind of out of fashion.

Tom, can you think of a way to prove conclusively ( I know you have already done that) to the developers, that select into should be used.

But the arguement against select into is that, so many variables need to be declared, in case the select has multiple columns, and that explicit cursors are convenient to code .

You need to do something Tom.

Daaler, August 16, 2001 - 10:36 am UTC

all these days I thought I was doing the right thing using explicit cursors. I feel as if Iam on crossroads, Iam being very indecisive about this , WHICH WAY TO GO?

more discussion on this

Ason, August 16, 2001 - 10:56 am UTC

Tom

I think this is a very good question, every developer gets this kind of trivial doubts while working, he saves it for later, and forgets about it.

I think you shoud beat the select into and explicit cursors discussion to death, as this is very important, many so called oracle experts out there, will be running out for cover.

A reader, August 16, 2001 - 11:16 am UTC

Damit, after working for 7 years in oracle, and doing so many explicit cursors, I get to hear that select into is better

"O.K., TELL ME THE WHOLE STORY"

MIN/MAX subqueries in implicit cursors

A reader, August 16, 2001 - 10:09 pm UTC

I agree on most points, but how about queries designed to give values on a certain point in time.

If you compare the two statements underneath I would prefer the second one using the explicit cursor. Espacially if I could reuse the cursor again and again.

SELECT .. INTO .. FROM t main WHERE main.version_date = (SELECT MIN(sub.version_date) FROM t sub WHERE .. AND sub.version_date >= l_date)

as compared to

CURSOR c_t .. IS SELECT .. FROM t WHERE .. AND sub.version_date >= b_date ORDER BY t.version_date DESC

and then fetching just one row.

response to previous post

Connor, August 17, 2001 - 5:37 am UTC

Implicit is still the way to go here but your code would be:
select blah
from (
select blah
from table
order by date_col desc )
where rownum = 1

because you are giving Oracle EXTRA information that you only want the highest (or lowest) piece of information. In the explicit cursor case, Oracle doesn't know that you'll only be fetching 1 row.

Is there a scrollable cursor in Oracle?

Shane Hu, April 02, 2002 - 11:05 am UTC

DB2 CLI has scrollable cursors. Just wondering how Oracle do backward fetch with pro*c or OCI?

April 02, 2002 - 1:30 pm UTC

see
</code> http://www.oracle.com/pls/db901/db901.to_toc?pathname=appdev.901/a89857/oci04sql.htm#440735 <code>
...

A wee-bit more of clarification wanted!!!!!!!!

AK, April 03, 2002 - 2:13 am UTC

Hi tom,
Admittedly the newsgroup discussion has made it amply clear that select into is to be a preferred choice against an explicit cursor. I desire just one more clarification:

Considering that for each row fetched by a cursor there is explicit processing to be performed, which one of the below is preferable??

1. for crec in (select a,b,c
from <table list>
where <where clauses>
loop
<my processsing>
end loop;

2. Explicit cursors
cursor crec is <select SQl>

open cursor
loop
fetch
exit when crec%notfound
< my processing>
end loop

What i want to know is which has a better performance???

Thanks in advance.

April 03, 2002 - 9:30 am UTC

Just one thing you need to remember:

"implicit cursors rule, they are FASTER, they are easier to code, they are more readable, they cause you to type less characters"

#1 is better. Benchmark it and see!

most of the doubts regarding cursors are clear

srinivasa rao bachina, May 27, 2003 - 5:32 am UTC

Hello Tom
thanks for u r nice explanation
one more doubt regardign cursor declaration
--in the cursor declaration we can use sql statement in the decode satement?
Ex:
select decode(sysdate,sysdate,(Select sysdate+1 from dual),sysdate-1) from dual is working in sqlplus
if i use the same staement in cursor declaration like
declare
cursor c1 is
select decode(sysdate,sysdate,(Select sysdate+1 from dual),sysdate-1) dt from dual
begin
for i in c1
loop
dbms_output.put_line(to_char(i.dt,'DDMMYYYY'));
end loop;
end;
i am getting the error
--
ORA-06550: line 3, column 33:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
ORA-06550: line 3, column 60:
PLS-00103: Encountered the symbol "," when expecting one of the following:

; return returning and or
ORA-06550: line 4, column 2:
PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:

. , @ ; for <an identifier>
<a double-quoted delimited-identifier> group having intersect
minus order partition start subpartition union where connect
--
i am using oracle 8i release 8.1.7.4.0
Can u Please Help me
Thank You in Advance

May 27, 2003 - 7:45 am UTC

in 9i, yes, in 8i no.

in 8i you must hide that construct (and CASE, order by in subquery, group by rollup/cube, analytics and some others) in a DYNAMIC query or in a view.

use ful

srinivasa rao bachina, May 29, 2003 - 7:29 am UTC

Hi tom
Can u explain me this case with some examples,if time permits to you?

thanks in advance

May 29, 2003 - 8:28 am UTC

you mean like

"create view v as select ......"

or

execute immediate open l_cursor for l_query;

it is very much straightforward stuff -- views and native dynamic sql.

One question

VMK, June 27, 2003 - 10:38 pm UTC

Hi tom,

My query

SELECT E.ENAME,E.SAL,E.DEPTNO,
(SELECT DNAME FROM DEPT D WHERE E.DEPTNO = D.DEPTNO)
FROM EMP E;

works fine in sql*plus. But if I want to declare it in cursor, it gives an error

DECLARE
CURSOR CUR_TEMP IS
SELECT E.ENAME,E.SAL,E.DEPTNO,
(SELECT DNAME FROM DEPT D WHERE E.DEPTNO = D.DEPTNO)
FROM EMP E;
BEGIN
NULL;
END;
/

Why it is so?

Thanks in advance.

VMK.

June 28, 2003 - 9:06 am UTC

because you are using 8i and there are some constructs that 8i does not recognize (select of a select, order by in a subquery, cursor() functions, analytics, group by cube/rollup)

solutions:

o use 9i
o use dynamic sql
o use a view.

Command line arguments

a reader, June 29, 2003 - 3:00 pm UTC

How can I pass command line argument to a .sql file
in both of the following situations.

1- Running SQLPLUS from a host prompt.
2- Running .sql file with @ in SQL prompt.

Thanks

June 29, 2003 - 6:26 pm UTC

$ sqlplus user/password @script first_parm second_parm


or

SQL> @script first_parm second_parm

miscelaneous questions

A reader, June 30, 2003 - 2:20 am UTC

As usual excellent and resourceful answers.
Thanks Tom!

Will you please provide a concrete example to handle subselect statement .

onsh, July 06, 2003 - 11:19 pm UTC

Hi Tom,
I want to follow up with you as it would be great to get your assistance to resolve the PLS-00103 error with the following code:
CREATE OR REPLACE
PROCEDURE SPR_REP_VISIT_DETAIL (
v_msched_skey IN NUMBER,
v_start_date IN CHAR,
v_end_date IN CHAR,
v_all_hangarbays IN CHAR,
v_hangarbays IN varchar2,
result_set OUT VVV_REPORTS.vis_det_hangarbay_refcur

)IS

temp_container VVV_REPORTS.vis_det_hangarbay_refcur;

BEGIN

IF ( NVL(v_all_hangarbays, 'FALSE') = 'TRUE') THEN

OPEN temp_container FOR

SELECT (v.base_code||'-'||v.hangar_code||'-'||v.bay_code) hbay ,
(SELECT DISTINCT a.aircraft_serial_num FROM aircraft a WHERE a.item_id = v.item_id) thetail,
(SELECT DISTINCT mp.mntce_project_code|| ' ' ||mp.MNTCE_PROJECT_TITLE FROM MAINTENANCE_PROJECT mp WHERE mp.MNTCE_PROJECT_ID = vmi.MNTCE_PROJECT_ID) projtitle,
(SELECT DISTINCT mp.MNTCE_PROJECT_TYPE_CODE FROM maintenance_project mp WHERE mp.MNTCE_PROJECT_ID = vmi.MNTCE_PROJECT_ID ) checktype,
v.visit_start_date_sched visit_start,
v.visit_end_date_sched visit_end,
(v.visit_end_date_sched - v.visit_start_date_sched ) duration,
(vmi.FORECASTED_START_DATE - v.VISIT_START_DATE_SCHED) slack,
vmi.percent_yield yield,
v.visit_desc comments

FROM visit v,
visit_maint_item vmi
WHERE v.visit_skey = vmi.visit_skey
AND v.msched_skey = v_msched_skey
AND (
( V.VISIT_START_DATE_SCHED >= TO_DATE(v_start_date, 'DD-MON-YYYY')
AND V.VISIT_START_DATE_SCHED <= TO_DATE(v_end_date, 'DD-MON-YYYY')
)

)
ORDER BY 1, 2, 3;
END IF;

END IF;

result_set:= temp_container;

END SPR_REP_VISIT_DETAIL;

I am using Oracle 8.1.7.4 and I realized that PL/SQL engine in 9i is much more smart than in 8i.The code above is running w/o problem in 9i and there are bunch of errors in 8i reffereing to subselect statements. Will you please help me.

Thanks a lot in advance,

onsh

July 07, 2003 - 7:23 am UTC

in 8i, the construct:

select ( select count(*) from dual )
from dual;

is not accepted by PLSQL (in addtition to analytics, case statements, group by rollup/cube, order by in a subquery, and some others I may have forgotten)

The work around is:

open p_cursor for
'SELECT .....' USING <binds>;

(native dynamic sql) OR use a VIEW to hide these constructs from PLSQL.

Thanks a lot for your suggestion

A reader, July 08, 2003 - 7:10 pm UTC

Hi Tom,
I've used a view to hide the subselect with analytic functions.
BTW, is there any reference in Oracle 8i (8.1.7.3) to highlight PL/SQL engine limitations?

Thanks,
onsh

July 09, 2003 - 7:50 am UTC

No, there is not.

OK

R.Chacravarthi, September 10, 2003 - 7:53 am UTC

Dear Sir,
I have a question for you.It is
1)Normally in queries we use column name to restrict rows
for ex.
select * from emp where deptno = 10;
My Question is
Can column values be used for row restriction? Like
select * from emp where 10 ...code goes here.
I expect your reply.
Please provide some examples.
Thanks in advance.

September 10, 2003 - 7:38 pm UTC

sorry, not getting your point. don't understand what you mean

OK

R.Chacravarthi, September 11, 2003 - 1:43 am UTC

Dear Sir,
What I asked you was
'select ename from emp where <condition>;'
condition can be of the column names like ename/deptno/empno.My question is whether column values can
be specified in the condition.?
for ex.
'select ename from emp where 10 (<= -- denotes deptno )
in (...code which tests for
some condition)'
Is this possible?

September 11, 2003 - 8:35 am UTC

if you are asking "can I reference the first column of a table using the number "1" instead of its name"

the answer is NO.

select * from emp where 1 = 'KING';

instead of

select * from emp where enamme = 'KING';

won't work (correctly). where 1='KING' is valid SQL -- but it is using the number 1 in there.

there would be no way to ascertain whether you meant "the number one" or "the first column" in any query -- ever. you would never be able to use a number in a SQL query.

Just great!!!

A reader, September 11, 2003 - 3:35 am UTC

This thread is really going good.

OK

R.Chacravarthi, September 11, 2003 - 9:14 am UTC

Dear Sir,
Received your response and thanks for that.You are saying that numbers cannot be used in sql queries.But certain things behave in  a strange manner.Consider the following 
queries.
QL> select ename from emp where sal = 3000;

ENAME                                                                                               
----------                                                                                          
SCOTT                                                                                               
FORD                                                                                                

SQL> select ename from emp e where 3000 in(select sal from emp where e.ename = ename);

ENAME                                                                                               
----------                                                                                          
SCOTT                                                                                               
FORD                                                                                                

SQL> select ename from emp where deptno = 20;

ENAME                                                                                               
----------                                                                                          
SMITH                                                                                               
JONES                                                                                               
SCOTT                                                                                               
ADAMS                                                                                               
FORD                                                                                                

SQL> select ename from emp e where 20 in(select deptno from emp where e.ename = ename);

ENAME                                                                                               
----------                                                                                          
SMITH                                                                                               
JONES                                                                                               
SCOTT                                                                                               
ADAMS                                                                                               
FORD                                                                                                

SQL> spool off
Why is this happening?Is this correct?.I should mention that
"I am using column values in queries"

September 11, 2003 - 9:51 am UTC

of course you can use numbers in there -- your question is/was very very unclear.

of COURSE you can use a number.

i had no idea what you meant above.

yes, where <NUMBER> <operator> <something> works just dandy.

OK

R.Chacravarthi, September 12, 2003 - 5:36 am UTC

Thanks for your response.Could you please provide some
"extraordinary" examples for such types of queries.Please do
reply.

September 12, 2003 - 10:16 am UTC

huh? "extraordinary"?? don't know what you are looking for.

Basic questions

Suresh, December 03, 2003 - 6:43 am UTC

Hi

I have SOME basic questions

Basically I am SQL SERVER professional. My current project is using ORACLE
So Please clarify these basic questions.

We are using packages. But inside the procedure (which resides
inside the PACKAGES) we are not doing any
commit or rollback work(transaction related).

Q1) so how these datas will get commit. do we have to COMMIT it explicitly.
Actually we are doing up gradation work. But the database is same(ORACLE 8.1.7). I have to
write some more procedures. In the old package I didnt see any
transaction related (commit, rollback) works?

Q2) My Current project is web based project. For e.g. one person login (using website)
and added/edited some datas. Can other session see that datas. Why I am asking
this question is that, these programs are using packages to edit/add. But we dont have any transaction
controls here.
So other session can see these added/edited data

Q3) I have procedure like following in my package.

There are four insert statement are there.
Suppose if the fourth insert statement fails I dont want first there statements.
Can I use AUTONOMUS TRANSACTION? or what to do?

Please explain what I have to do exactly.

December 03, 2003 - 7:22 am UTC

q1) that is awesome -- the sqlserver model of "transactions" is totally wrong -- they "autocommit" (because having locks in their system is like the kiss of death, everything halts).  We do not.

the CLIENT that calls the procedure should commit or rollback -- it is the CLIENTS decision.  In that fashion, you can take procedure1 and procedure2 and call them as a single transaction anytime they want!!!

the client should either:

   begin procedure; end;
   commit or rollback based on the error code (in the event of an error, the rollback of changes is implicit)


or, to call a procedure it can submit this block instead:

   begin
       procedure;
       commit;
   exception when others then
       rollback;
       raise;
   end;


in that fashion, the client can later code:


   begin
       procedure1;
       procedure2;
       procedure3;
       commit;
   exception when others then
       rollback;
       raise;
   end;

as a single atomic transaction.  


q2) other sessions can see the data as soon as the session modifying the data commits.  not before.

q3) if you have my book "expert one on one Oracle" - i cover this topic in huge detail -- huge detail.  this is important stuff (not that I'm trying to see a book here but i think you would benefit from the discussions contained therein).


in Oracle -- ACID is important, we pay attention to it.  A procedure is considered a STATMENT -- if you call a procedure it either

a) entirely succeeds
b) entirely fails and it is as if it never occured.

a simple test:

ops$tkyte@ORA9IR2> create table t ( x int check (x>0) );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure good
  2  as
  3  begin
  4    insert into t values ( 1 );
  5    insert into t values ( 2 );
  6    insert into t values ( 3 );
  7    insert into t values ( 0 );
  8  end;
  9  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure BAD
  2  as
  3  begin
  4    insert into t values ( 1 );
  5    insert into t values ( 2 );
  6    insert into t values ( 3 );
  7    insert into t values ( 0 );
  8  exception
  9    when others then dbms_output.put_line( 'bummer ' || sqlerrm );
 10  end;
 11  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;
 
no rows selected
 
ops$tkyte@ORA9IR2> exec good;
BEGIN good; END;
 
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C002910) violated
ORA-06512: at "OPS$TKYTE.GOOD", line 7
ORA-06512: at line 1
 
 
ops$tkyte@ORA9IR2> select * from t;
 
no rows selected
 
ops$tkyte@ORA9IR2> exec bad;
bummer ORA-02290: check constraint (OPS$TKYTE.SYS_C002910) violated
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from t;
 
         X
----------
         1
         2
         3


<b>as you can see -- if the procedure "fails" (good does that) and returns the error the client -- lets the exception propagate out -- the work it does is "undone" totally.

as you can also see, if you use the "when others" or any other exception to catch the error and "make it go away" as I did here (eg: what I'm getting at is LET THE EXCEPTIONS PROPAGATE TO THE CLIENT, do not catch them and return return CODES!!!!) then the client is responsible for undoing the work.

Use of commit and rollback in java

suresh, December 03, 2003 - 8:13 am UTC

The application(java) programmers are calling a package. How transactions can be comitted/rolled back using the java programs. If the commit/rollback is implemented in package, does that have a down side.

If commit/rollback is not done in the java & packages both, what will be the consequnces ?

December 03, 2003 - 10:37 am UTC

well, i already stated two ways

a) have the application do it:

conn.commit()

b) submit a block that calls procedure and either commits or rollsback.

I FIRMLY believe transactional control MUST be in the client.

SQLPLUS linesize issue/question

JD, December 28, 2005 - 11:50 am UTC

Tom,

With reference to the very first question (and it's answer) in this thread:
---
9.what is the maxvalue we can give to set line size.
---
I have related problem with linesize.

When I run the following script, sqlplus prints heading for the second select statement.

Please see the sql statement and outputs from two different versions of IBM/AIX OS:

=========================================
---
SQL script (say tmp.sql):

set echo off
set doc off
set pause off
set feedback off
set pagesize 0
set wrap off
set verify off
set head off
set linesize 4000
spool tmp.lis
select 'abcdefg' col1 from dual;
select '12345678901234567890123456789012345678901234567890' --1
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890' --5
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890' --10
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890' --15
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890'
||'12345678901234567890123456789012345678901234567890' --20
||'123456789012345678901234' col2
from dual;
/
spool off
exit
---
execute as
sqlplus -s scott/tiger @tmp.sql > tmp.out
---

OUTPUT (tmp.out) on IBM/AIX 4 using SQL*Plus: Release 9.2.0.5.0
abcdefg
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234
OUTPUT (tmp.out) on IBM/AIX 5 using SQL*Plus: Release 9.2.0.4.0
abcdefg
^A^P^SN`À789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234
---
=========================================

- both outputs printed header for second select statement, even though I have both "set head off" and "set pagesize 0"
- Second output printed junk characters (^A^P^SN`À) at the beginning of the header.

Are these known issues? OR am I missing something here??

Any help would be greatly appreciated.

Thanks,

JD

December 28, 2005 - 11:56 am UTC

must be missing something?

I do not see col1 nor col2 in any of the output??

It's not the column header....

JD, December 28, 2005 - 12:29 pm UTC

Sorry, it isn't printing the column headers "col1" and "col2" but it does repeat the value of col2 (from second statement).

I was expecting same output as of first statement (no repetation):

select 'abcdefg' col1 from dual;
>>abcdefg

Hope this is more clear.

Thanks.

December 28, 2005 - 5:45 pm UTC

I don't see any repeats?

I think what JD is asking is...

Kevin, December 28, 2005 - 1:16 pm UTC

He doesn't want to get the line
"OUTPUT (tmp.out) on IBM/AIX 5 using SQL*Plus: Release 9.2.0.4.0"
(i.e., the login banner from sql*plus)
in his output file.
As far as I know, you can't supress the banner -- it happens as you authenticate, so there's no chance to set a parameter to tell it to stop.

But, you're only getting this because you are logging the entire session output to temp.out. The appropriate way to do this (which you seem to be doing in the script file anyway, amusingly -- look at 'spool tmp.lis') is to use the spool command, starting spooling after you've authenticated and set your environment appropriately.

December 28, 2005 - 5:47 pm UTC

I don't think that is what they meant - they wrote that line to tell us which output we were looking at - the and tags were put there by them.

but yes, I too believe they want to use the tmp.lis file, not the redirection

Thanks Kevin... but...

JD, December 28, 2005 - 1:34 pm UTC

Thanks Kevin for the review, BUT the line
"OUTPUT (tmp.out) on IBM/AIX 5 using SQL*Plus: Release 9.2.0.4.0"
is not from the output. I have copied and pasted the output as it is after that/those lines.

Just FYI, you can supress the banner using -s option (also mentioned in the question): sqlplus -s scott/tiger @tmp.sql > tmp.out

Thanks anyway.

Please see the output... seperated in paragraphs

JD, December 28, 2005 - 5:53 pm UTC

Length of the string (of col2) in sql is only 1024 characters, but output, in both cases, is 2048.

--- This is the first line (should not appear)
^A^P^SN`À789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234

--- This is actual column value of col2 (only expected)
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234

Thanks.

December 28, 2005 - 6:13 pm UTC

Oh, that is easy, you ran the query twice:

from dual;
/
spool off

either use the ; to end the query and run it OR the / - but not both.

Even .lis (spool) does the same thing

JD, December 28, 2005 - 5:57 pm UTC

<quote> but yes, I too believe they want to use the tmp.lis file, not the redirection <quote>

Spool output (.lis) also has repeated values. The only difference is there are no junk characters.

Thanks,

JD

December 28, 2005 - 6:13 pm UTC

(you are running the query twice :)

Got it! I think...

Chris Poole, December 28, 2005 - 6:16 pm UTC

to JD from VA

Its because you have a slash AND a semicolon!!

select 'some junk'
from dual;
/

Run that in SQL*Plus, yet get the same staement executed twice. The slash just says 'run whats in the buffer', whats in the buffer? The select from dual you just ran because you put a semicolon on the end!

Drat and double drat, not quick enough!

Chris Poole, December 28, 2005 - 6:41 pm UTC

Tom you have a slight advantage when you post your replies,
it makes us look like we are just repeating you, but I know I got there first! ;)

Happy New Year to you

Got it... Thanks... BUT

JD, December 29, 2005 - 12:07 pm UTC

Thanks.. thanks... thanks...

I got the fact about getting result twice. In fact, I keep advising same thing to others :(

BUT the main issue we are having is JUNK characters in redirect file. Now, if I run the same sql (without ;) and redirect to the file, I see junk character at beginning (in .out file) but the spool file (.lis) does not show any of those. This happens only on the combination of "on IBM/AIX 5 and SQL*Plus: Release 9.2.0.4.0"

--- Part of output
abcdefg
^A^P^SN`À789012345678901.....
---

Is this a known bug or do I need to check some settings in the database?

Also, is/was there any limit on spool file size?

(Thanks to Chris from toasty NORTH [?] Sydney. Happy new year to you and hope you will enjoy the fire works)

.. JD

December 29, 2005 - 1:17 pm UTC

I don't know, as I don't track every bug on all platforms (that would be handled via support)

Could be control characters of some sort.

but the lis file is what you want - The screen output isn't what you want to use.

32bit tools might have file size limits, yes. But if you are 64bit, they should not.

Thanks...

JD, December 29, 2005 - 2:28 pm UTC

Thanks Tom.

Wish you and everyone a Very Happy New Year.

.. JD

SQL Question

bc, January 26, 2006 - 1:29 pm UTC

Tom,

What does the (+) symbol in the following cursor do ?

cursor c1 is
select col_a,
col_b
from table_a
where col_a (+) is null;

Thanks

January 27, 2006 - 8:13 am UTC

nothing. it is rather meaningless in that context.

Thanks

BC, January 27, 2006 - 10:26 am UTC

Tom,

Thank you very much, I apprecitate it.

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

miscelaneous questions

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

A book in itself

Very good, but we want this clarified

dont leave us in confusion

more discussion on this

MIN/MAX subqueries in implicit cursors

response to previous post

Is there a scrollable cursor in Oracle?

A wee-bit more of clarification wanted!!!!!!!!

most of the doubts regarding cursors are clear

use ful

One question

Command line arguments

miscelaneous questions

Will you please provide a concrete example to handle subselect statement .

Thanks a lot for your suggestion

OK

OK

Just great!!!

OK

OK

Basic questions

Use of commit and rollback in java

SQLPLUS linesize issue/question

It's not the column header....

I think what JD is asking is...

Thanks Kevin... but...

Please see the output... seperated in paragraphs

Even .lis (spool) does the same thing

Got it! I think...

Drat and double drat, not quick enough!

Got it... Thanks... BUT

Thanks...

SQL Question

Thanks

More to Explore

PL/SQL demos

PL/SQL docs