Oracle sql%rowcount

I have an insert-as-select in a loop. Does rowcount give the number of inserts into the table tab?

Thanks, Ven.

for i in 1 .. n
loop
insert into tab (a, b, c, d)
select 123, x.b, 0, abc_id.seq.nextbal
from class x, room y
where x.id = y.id and x.id = i;
end loop;
no_of_inserts := sql%rowcount

Oracle sql%rowcount and COMMIT in 8i vs. 9i

Is it true that the behavior of sql%rowcount is different in Oracle 8i vs. Oracle 9i ? The following pl/sql procedure seems to demonstate that a COMMIT statement sets sql%rowcount function to 0 in 9i, but leaves is unchanged in 8i. I have not seen this documented. Is this observation in fact true? T ...

Oracle SQL%ROWCOUNT QUESTION ... urgent!

I would like to make a quick question. SQL%ROWCOUNT can be used only after an update or delete statement OR can be used and after a select statement?I have the following code:go_block('efhmeries');execute_query;grammes := SQL%ROWCOUNT;Is this right? I want SQL%ROWCOUNT to return the number of rows f ...

Oracle sql%rowcount within a conditional

I am interested to hear your thoughts on this pseudocode, actual test follows.update table 1if sql%rowcount >0 thenupdate table 2elsif sql%rowcount = 0 theninsert into table 1One might think that if the first update succeeded and the second update failed then the insert would take placebecause sq ...

Oracle sql%rowcount cannot work with select * statement ?

this is my statement :declarecnt number ;beginselect * from testing_timelogging ;cnt := sql%rowcount;IF cnt > 0 THENdbms_output.put_line('got records');END IF ;end; error : ORA-06550: line 4, column 1:PLS-00428: an INTO clause is expected in this SELECT statement ques :1. i must ALWAYS use a sele ...

Oracle SQL%ROWCOUNT - Need to see total # of rows updated in loop.

HERE IS MY LITTLE PROCEDURE, anyone tell me how I can view the total number of rows updated. I have tried a few different things, but it always displays just 1 or nothing. WHen I know I have updated 78000 records. Any Ideas would be greatfull.DECLAREv_desc MAXIMO.WSSDESCRIP.DESCRIPTION%TYPE;v_auto M ...

Oracle SQl%rowcount problem

i have created a function, in pl/sql to check the diffrence betwenn 2 similar tables having same columns . if it has diffrence tha is if select query returns row then it shoul go in error log table but this is not happening could you solve my problem.thanks in advanceCREATE OR REPLACE FUNCTION CAL_B ...

Oracle SQL%ROWCOUNT-How it works?

I wrote the below piece of code.SQL> DECLARE2 v_count VARCHAR2(100);3 v_name VARCHAR2(100);4 BEGIN5 SELECT ename INTO V_name FROM emp6 WHERE empno=1111;7 v_count:= SQL%ROWCOUNT;8 DBMS_OUTPUT.PUT_LINE(v_count);9 END;10 /PL/SQL procedure successfully completed.The PLSQL Procedure was executed succe ...

Oracle SQL%ROWCOUNT and concurrency

Is the technique documented on http://www.jlcomp.demon.co.uk/faq/rowcount.html safe to use? Specifically, what happens if two users execute the sample procedure at exactly the same time. How does SQL%ROWCOUNT work with respect to concurrency? Where is this documented? ...

Oracle SQL%ROWCOUNT is not returning exact rows

I have written a following block. The cursor has 16 rows. I am updating the print flag in table. All flags gets updated but it is showing only 1 row affected.I want to know how many rows are being affected by the update statement.My script goes here.1 declare2 cursor c1 is3 select rowid,a.*4 from ch ...

Oracle SQL%ROWCOUNT vs count(*)

I need to get the count of huge tables having record count more than 2 billion.Iam handling this in pl/sql procedure, which approach is the best1. SQL%ROWCOUNT or2. select /*+ parallel(src) */count(1)into v_t1_srcfrom table.Please note we have multiple cpu and the process run in parallel ...

Oracle SQL%ROWCOUNT=0 OR NO_DATA_FOUND EXCEPTION ?

If my Query is Not Returning any Data i want to Initialize a variable with 1 v_var :=1 i am getting same results for IF SQL%ROWCOUNT=0 THENv_var:=1 ;END IF;EXCEPTION WHEN NO_DATA_FOUND THEN v_var:=1;END;which is preferrable among these ? ...

Oracle SQL%Rowcount issue

I have a trigger which uses the following codeSELECT midINTO v_mid_to_insertFROM midWHERE merch_nbr = v_merch_nbrAND mid = v_midAND mid '00000000001'AND merch_status = '00'AND deleted_mid_yn = 'N' AND ROWNUM <= 1;v_rowcount:=SQL%ROWCOUNT;IF v_rowcount=0 THENBEGINv_mid_to_insert := v_mid;END;END I ...

Oracle sql%rowcount dont work

i write procedure in package and write the sql%rowcount value with dbms_output after 3 insert but sql%rowcount still 0why rowcount dont work?thanks PROCEDURE P_ROL_YETKI_EKLE ( GKISI_UYGULAMA_ID IN GNL_HRK_KULLANICI.NKISI_UYGULAMA_ID%TYPE , ROL_ID IN GNL_SBT_ROL_YETKI.NROL_ID%TYPE, YETKI_ID OUT GNL_ ...

Oracle SQL%ROWCOUNT must return 0 not 1

All,We are running Oracle 10.2.0.1.0. I am having problems with the result of SQL%ROWCOUNT. It should return 0 whenselect doesn't find a match, but it returns a 1. With deletes, SQL%ROWCOUNT works fine and returns 0.SQL> l 1 declare 2 v_count NUMBER; 3 BEGIN 4 v_count := 0; 5 select count(*) into ...

Oracle sql%rowcount returns rows more than table count

Please look at this code:DECLARE CURSOR c1 IS SELECT state_id, state_name, license_nbr FROM states st, licenses licWHERE st.license_id =lic.license_id;TYPE reg_t IS TABLE OF c1%ROWTYPE;v_array reg_t;BEGINOPEN c1;LOOPFETCH c1 BULK COLLECT INTO v_array ;FORALL i IN v_array.FIRST .. v_array.LASTUPDATE ...

Oracle SQL%ROWCOUNT

I have tried to count the number of insert records that are inserted into a table using a plsql block and used SQL%ROWCOUNT but it shows only one record inserted here is the code. DECLARE local_variables....Cursor c1is select * from temp_table;BEGIN IF NOT c1%ISOPEN THENOPEN c1;END IF;LOOPFETCH c1IN ...

Oracle SQL%ROWCOUNT returning null after Forms_DDL on a Dynamic SQL

I'm new to PL/SQL coding and am currently working on some application development in Oracle Forms via the 10g Developer's Suite.I'm having a problem getting a rowcount (# of rows affected by last dml command) to register after using forms_ddl PROCEDURE P_SQL_UPDATE IS   L_string1 VARCHAR2(250); ...

Oracle dyn sql execute immediate A plsql block returning sql%rowcount of rows inserted

I would like to get the number of rows inserted in a dynamic sql statement like following.execute immediate 'begin insert into a(c1,c2,c3) (select ac1,ac2,ac3 from ac where ac1=:thekey UNION select tc1,tc2,tc3 from tc where tc1=:otherkey); end;' using in key1,in key2;I have tried per the oracle8i dy ...

SQL%ROWCOUNT problem in Oracle 9i

We have migrated our application from 8i to 9i. There is a PL/SQL procedure which returns number of rows inserted in the table. In Oracle it was returning correct number of rows but when we have migrated same procedure on Oracle9i it is returning 0 (ZERO) number of records even though procedure inse ...

Oracle How to get SQL%Rowcount in the Execute Immediate Statement for select

I have one query in the variable called CurrentQuery. It is select query basically. But as my stored procedure is dynamic. I have stored all the queries in the one table and I am taking one by one query and I want to take the count of each query.so I am taking the query in the strCurrentQuery variab ...

Oracle Is there an equivalent to SQL%ROWCOUNT usable outside of PL/SQL?

Is there a way to know the number of rows a previous SQL statement affected, outside of using PL/SQL. Example:UPDATE TABLE1 SET COLUMN1 = 'X' WHERE COLUMN1 'X';INSERT INTO TABLE1_LOG_HISTORY( MESSAGE_COLUMN, DATE_TIME )VALUES ('Table activity, rows were updated', SYSDATE) WHERE sql%rowcount {or equi ...

Oracle Can I get individual SQL%ROWCOUNT from a multitable insert?

When using a multitable insert, is it possible to determine the SQL%ROWCOUNT of each of the tables involved in the insert?Oracle 10.2.0.3. ...

Oracle How can I display SQL%ROWCOUNT in the "Process Success Message"

I am trying to display SQL%ROWCOUNT in the "Process Success Message" of a custom update Process.Any ideas? ...

Oracle DELETE using EXECUTE IMMEDIATE and SQL%ROWCOUNT after that.

I am trying to delete few rows from a table (In fact, there are set of tables - these table names are stored in another table, and the idea is to go through all of them and delete all rows). I am trying to put it simple here.I am using execute immediate to delete them. When I ran the following proce ...

Oracle Should i use sql%rowcount or sql%notfound here?

Version:10gR2I have to write a purge script which has a Commit frequency in it(the client insisted on having a COMMIT freq. I personally hate it).Now i am a bit confused about the exit condition.Should it be sql%rowcount likeloop delete from TRACK_MTG where MOD_DATE <= v_purgedate and STAT_ID ...

Oracle Using sql%rowcount after delete

What will be the value of sql%rowcount if no rows are affected by the delete statement? ...

Oracle Is it wrong to use SQL%ROWCOUNT = 0 ?

I have two tables, BILL and VALIDATIONS, both have (LDAP_UID, TERM) as composite key. I wrote a procedure to do the following:1. I use this composite key to retrieve some non-key fields from the BILL table, form a new row and insert it into the VALIATIONS table;2. Before inserting into the VALIDATIO ...

Oracle Replacement for SQL%ROWCOUNT

Please let me know whether there is any other function that does the function of SQL%ROWCOUNT, without the usage of packages or procedures.My intention is to get the count of the number of rows that got updated. ...

Oracle sqlcode and sql%rowcount as test conditions

I am translating a procedure from Ingres to oracle. I use the Oracle sqlcode and sql%rowcount variables in place of Ingres's iierrornumber and iirowcount.I replaced the ingres names with the oracle names, and now am wondering how how oracle uses these values. I am using DBMS_OUTPUT.PUT_LINE to displ ...