Solved

Returning number of rows inserted in the function's insert query

Posted on 2008-10-03
5
3,282 Views
Last Modified: 2013-12-07
I need to write a function such that it will have an insert statement
and this function should return the number of rows inserted into the table. How to do that?

My table name is my_table and suppose there is one column which is column1 and the insert statement is
"insert into my_table as select * from temp;"

Please suggest.
0
Comment
Question by:srikanthradix
5 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 250 total points
ID: 22636721
....
insert into my_table as select * from temp;
return SQL%RowCount;
0
 

Assisted Solution

by:Duffsoft
Duffsoft earned 150 total points
ID: 22637300
Could you use a merge statement?  This is not a function but you could modify the the code to make it return the value illustrated in the DBMS_OUTPUT.PUT_LINE.  It appears that the merge statement handles the scoping of the SQL%ROWCOUNT value differently than a straight insert within a function.  

SQL> BEGIN
  2     MERGE INTO emp_target et
  3        USING ( SELECT * FROM emp_source ) es
  4        ON    ( et.empno = es.empno )
  5     WHEN MATCHED THEN
  6     UPDATE
  7        SET et.ename  = es.ename
  8        ,   et.sal    = es.sal
  9        ,   et.mgr    = es.mgr
 10        ,   et.deptno = es.deptno
 11     WHEN NOT MATCHED THEN
 12     INSERT
 13        ( et.empno, et.ename, et.sal, et.mgr, et.deptno )
 14        VALUES
 15        ( es.empno, es.ename, es.sal, es.mgr, es.deptno );
 16  
 17     DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows merged.');
 18  END;
 19  /
0
 
LVL 18

Assisted Solution

by:sventhan
sventhan earned 100 total points
ID: 22637353
CREATE FUNCTION rows_inserted (
   table_name IN VARCHAR2) RETURN INTEGER AS
BEGIN
   EXECUTE IMMEDIATE
      'INSERT INTO ' || table_name || 'AS SELECT * FROM EMP';
   RETURN SQL%ROWCOUNT;  -- return number of rows deleted
END;
0
 

Expert Comment

by:Duffsoft
ID: 22637357
My apologies.  I forgot to mention that the above example comes from the following site, and just below the code above you can find a section on ETL merge function which may be closer to what you are looking for:

http://www.quest-pipelines.com/newsletter-v4/0903_D.htm

"ETL PACKAGE

As can be seen, the SQL%ROWCOUNT attribute provides us with the total MERGE count, but we have no idea of how many UPDATEs or INSERTs were performed, which doesn't help us when we have a production batch run to audit or debug. To enable us to keep track of this, I created a small package named ETL with three functions ( plus two overloads ) and one procedure. The source code for this is as follows:-

    CREATE OR REPLACE PACKAGE etl AS

       c_inserting CONSTANT PLS_INTEGER := 0;
       c_updating  CONSTANT PLS_INTEGER := 1;

       FUNCTION merge_counter (
                action_in IN PLS_INTEGER DEFAULT c_inserting
                ) RETURN PLS_INTEGER;

       FUNCTION get_merge_update_count RETURN PLS_INTEGER;

       FUNCTION get_merge_update_count (
                merge_count_in IN PLS_INTEGER
                ) RETURN PLS_INTEGER;

       FUNCTION get_merge_insert_count RETURN PLS_INTEGER;

       FUNCTION get_merge_insert_count (
                merge_count_in in PLS_INTEGER
                ) RETURN PLS_INTEGER;

       PROCEDURE reset_counters;

    END etl;
    /

    CREATE OR REPLACE PACKAGE BODY etl AS

       g_update_counter PLS_INTEGER NOT NULL := 0;
       g_insert_counter PLS_INTEGER NOT NULL := 0;

    /*-------------- START OF FUNCTION merge_counter --------------------*/
    FUNCTION merge_counter (
             action_in IN PLS_INTEGER DEFAULT c_inserting
             ) RETURN PLS_INTEGER IS
    BEGIN
       CASE action_in
          WHEN c_updating
             THEN g_update_counter := g_update_counter + 1;
          WHEN c_inserting
             THEN g_insert_counter := g_insert_counter + 1;
          ELSE
             RAISE PROGRAM_ERROR;
       END CASE;
       RETURN 0;
    END merge_counter;

    /*----------- START OF FUNCTION get_merge_update_count V1 ---------------*/
    FUNCTION get_merge_update_count
       RETURN PLS_INTEGER is
    BEGIN
       RETURN g_update_counter;
    END get_merge_update_count;

    /*----------- START OF FUNCTION get_merge_update_count V2 ---------------*/
    FUNCTION get_merge_update_count (
             merge_count_in IN PLS_INTEGER
             ) RETURN PLS_INTEGER IS
    BEGIN
       RETURN NVL( merge_count_in - g_insert_counter, 0 );
    END get_merge_update_count;

    /*----------- START OF FUNCTION get_merge_insert_count V1 ---------------*/
    FUNCTION get_merge_insert_count
       RETURN PLS_INTEGER IS
    BEGIN
       RETURN g_insert_counter;
    END get_merge_insert_count;

    /*----------- START OF FUNCTION get_merge_insert_count V2 ---------------*/
    FUNCTION get_merge_insert_count (
             merge_count_in IN PLS_INTEGER
             ) RETURN PLS_INTEGER IS
    BEGIN
       RETURN NVL( merge_count_in - g_update_counter, 0 );
    END get_merge_insert_count;

    /*-------------- START OF FUNCTION reset_counters --------------------*/
    PROCEDURE reset_counters IS
    BEGIN
       g_update_counter := 0;
       g_insert_counter := 0;
    END reset_counters;

    END etl;
    /
0
 
LVL 11

Expert Comment

by:yuching
ID: 23171474
Instead of doing loop statements, you can try use MERGE Statement which will be lot faster

Merge Into monthly_product_price dst
Using (
   Select plan_ctry,division ,material ,mkt_typ_id, exp_sty_ind, bucket_month,
           whlsl_price,sap_prod_stat_cd,min_mkt_typ_ind
  From monthly_product_indicator
) src
On (dst.plan_ctry = src.plan_ctry And dst.division = src.division And dst.material = src.material
   And dst.mkt_typ_id = src.mkt_typ_id And dst.exp_sty_ind = src.exp_sty_ind
   And dst.bucket_month = src.bucket_month
)
When Matched Then
  Update Set  dst.whlsl_price = src.whlsl_price,
         dst.sap_prod_stat_cd = src.sap_prod_stat_cd,
         dst.min_mkt_typ_ind = src.min_mkt_typ_ind,
        zz_chng_tmst = (Case When dst.whlsl_price <> src.whlsl_price Or
       dst.sap_prod_stat_cd <> src.sap_prod_stat_cd Or
      dst.min_mkt_typ_ind <> src.min_mkt_typ_ind Then Sysdate
             Else dst.zz_chng_tmst End),
          zz_chng_cnt = (Case When dst.whlsl_price <> src.whlsl_price Or
        dst.sap_prod_stat_cd <> src.sap_prod_stat_cd Or
       dst.min_mkt_typ_ind <> src.min_mkt_typ_ind Then dst.zz_chng_cnt + 1
             Else dst.zz_chng_cnt End)
When Not Matched Then
   Insert (dst.plan_ctry, dst.division, dst.material, dst.mkt_typ_id, dst.exp_sty_ind,
         dst.bucket_month, dst.whlsl_price, dst.sap_prod_stat_cd, dst.min_mkt_typ_ind,
        dst.zz_setup_tmst,  dst.zz_chng_tmst, dst.zz_chng_cnt, dst.xxx_chng_usr_id)
   Values (src.plan_ctry, src.division, src.material, src.mkt_typ_id,
       src.exp_sty_ind, src.bucket_month,  src.whlsl_price, src.sap_prod_stat_cd,
        src.min_mkt_typ_ind, nsc_v.g_processing_date, Sysdate,0,User
   );
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
Oracle Listener Not Starting 11 53
oracle RMAN - trying to duplicate a database 5 39
migration MS SQL database to Oracle 30 69
setting local variables in a cursor block 3 20
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

830 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