[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

checking SQL%ROWCOUNT value in oracle 10 g (10.2.0)

Posted on 2007-07-20
8
Medium Priority
?
31,429 Views
Last Modified: 2008-01-09
when i was upgrading from 8i to 10g one of the packages had the code..

if SQL%ROWCOUNT = 0 then
    v_txt := v_program||': '||':  Tables Not Created. Check!!!!;

    RAISE_APPLICATION_ERROR(-20000,v_txt,TRUE);

END IF;

This failed while running in 10g database, I tried this
v_ins_row_count := SQL%ROWCOUNT;

IF v_ins_row_count = 0 THEN

    v_txt := v_program||': '||': Tables Not Created. Check!!!!';
    RAISE_APPLICATION_ERROR(-20000,v_txt,TRUE);

END IF;

This worked!! Do you guys know why, I dont want to change the code but looks like the current code always had sql%rowcount value as zero and it went to exception but the modified code did not..

Have you guys experienced this in 10g?
0
Comment
Question by:mahjag
8 Comments
 
LVL 11

Expert Comment

by:sujit_kumar
ID: 19535121
have you tried "if SQL%ROWCOUNT <= 0 then"?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 19540053
looks very strange to me because that should work and i have not heard or went thru any links saying it would not work in 10g...

it would be better if you post your full code and the errors/exceptions you are getting.
0
 

Author Comment

by:mahjag
ID: 19559390
I tried SQL%ROWCOUNT < =0 and still see the same error..

I have insert statement

insert into mytableA
(col1,col2..)
(select col1,col2. from mytableB
)

if SQL%ROWCOUNT = 0 then
    v_txt := v_program||': '||':  Tables Not Created. Check!!!!;

    RAISE_APPLICATION_ERROR(-20000,v_txt,TRUE);

END IF;

that is all the code involved in this error.. let me know..
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 19568129
begin
insert into temp_table select * from testing1 ;
dbms_output.put_line('count:'||sql%rowcount);

if SQL%ROWCOUNT = 0 then
dbms_output.put_line('in exception');
    RAISE_APPLICATION_ERROR(-20000,'error defined by me',TRUE);

END IF;
end;
/

output is ( i have oracle express edition 10g ) :

count:3    ---> this shows me the number of records as 3 and no exception is raised
Statement processed.
0.03 secondsDBMS Output
 
i modified your code to the above because there is no ; after the insert 'insert into mytableA
(col1,col2..)
(select col1,col2. from mytableB
)' and this line i removed because it gave me error saying variable is not defined.

Let me know if you need any other help.

Thanks
0
 

Author Comment

by:mahjag
ID: 19603731
I just tried the same and it always worked if it is written as a test procedure with insert statement and sql%rowcount has a value, but inside a package same insert statement when you do sql%rowcount after the commit statement did not work ( meaning it gave zero as value)
not sure how this can be reproduced..
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19603748
>when you do sql%rowcount after the commit statement did not work
which is "normal". the sql%rowcount needs to be first after the statement
0
 

Author Comment

by:mahjag
ID: 19603808
the same code was working fine in 8i not sure why this is not normal for 10g

8i code

insert statement
commit
if sql%rowcount = 0 then
--error statement
end if;
 
the same code when ported to 10g errored eventhough insert statement worked and the rows inserted to table is greater than zero..
I dont understand how it can work in 8i and not in 10g
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 375 total points
ID: 19606493
the sql%rowcount should be after the insert statement.

you can either put the commit just below the end if of sql%rowcount if condition or as you have done, we can have a variabel and then use that variable.

i am not sure how it worked in 8i code... Ideally it should not and it will not...but i cannot test it because i do not have 8i database with me.

Thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month18 days, 2 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question