Home » SQL & PL/SQL » SQL & PL/SQL » function is inserting multiple time
function is inserting multiple time [message #199161] Fri, 20 October 2006 12:47 Go to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hi Guys,

Any idea why the function is inserting multiple records?

In my select statement i have restricted the retieval WITH '19-FEB-99'
WHICH HAS ONLY 28 RECORDS, but the insert goes on repitatively
for 28 records endless.





CREATE OR REPLACE PACKAGE st
AS
   TYPE student_ntt IS TABLE OF student%ROWTYPE;

   v_errorcntr   BINARY_INTEGER := 0;

   PROCEDURE st_work (p_regi_date IN student.registration_date%TYPE);

   FUNCTION st_insert (v_rec IN student_ntt)
      RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY st
AS
   PROCEDURE st_work (p_regi_date IN student.registration_date%TYPE)
   AS
      TYPE student_id_aat IS TABLE OF student.student_id%TYPE
         INDEX BY BINARY_INTEGER;

      l_student_id          student_id_aat;

      TYPE salutation_aat IS TABLE OF student.salutation%TYPE
         INDEX BY BINARY_INTEGER;

      l_salutation          salutation_aat;

      TYPE first_name_aat IS TABLE OF student.first_name%TYPE
         INDEX BY BINARY_INTEGER;

      l_first_name          first_name_aat;

      TYPE last_name_aat IS TABLE OF student.last_name%TYPE
         INDEX BY BINARY_INTEGER;

      l_last_name           last_name_aat;

      TYPE street_address_aat IS TABLE OF student.street_address%TYPE
         INDEX BY BINARY_INTEGER;

      l_street_address      street_address_aat;

      TYPE zip_aat IS TABLE OF student.zip%TYPE
         INDEX BY BINARY_INTEGER;

      l_zip                 zip_aat;

      TYPE phone_aat IS TABLE OF student.phone%TYPE
         INDEX BY BINARY_INTEGER;

      l_phone               phone_aat;

      TYPE employer_aat IS TABLE OF student.employer%TYPE
         INDEX BY BINARY_INTEGER;

      l_employer            employer_aat;

      TYPE registration_date_aat IS TABLE OF student.registration_date%TYPE
         INDEX BY BINARY_INTEGER;

      l_registration_date   registration_date_aat;

      TYPE created_by_aat IS TABLE OF student.created_by%TYPE
         INDEX BY BINARY_INTEGER;

      l_created_by          created_by_aat;

      TYPE created_date_aat IS TABLE OF student.created_date%TYPE
         INDEX BY BINARY_INTEGER;

      l_created_date        created_date_aat;

      TYPE modified_by_aat IS TABLE OF student.modified_by%TYPE
         INDEX BY BINARY_INTEGER;

      l_modified_by         modified_by_aat;

      TYPE modified_date_aat IS TABLE OF student.modified_date%TYPE
         INDEX BY BINARY_INTEGER;

      l_modified_date       modified_date_aat;
      l_row                 PLS_INTEGER;
      l_student             student_ntt           := student_ntt ();
      v_rc2                 VARCHAR2 (20)         := NULL;
      v_mgna_x              INTEGER               := 1;
      v_returncode          INTEGER;
      v_errmessage          VARCHAR2 (100);
      exit_early            EXCEPTION;
      v_gblxcommit          INTEGER               := 100;
   BEGIN
      SELECT student_id, salutation, first_name, last_name,
             street_address, zip, phone, employer,
             registration_date, created_by, created_date,
             modified_by, modified_date
      BULK COLLECT INTO l_student_id, l_salutation, l_first_name, l_last_name,
             l_street_address, l_zip, l_phone, l_employer,
             l_registration_date, l_created_by, l_created_date,
             l_modified_by, l_modified_date
        FROM student
       WHERE registration_date = p_regi_date;

      l_row := l_student_id.FIRST;

      WHILE (l_row IS NOT NULL)
      LOOP
         FOR indx IN l_student_id.FIRST .. l_student_id.LAST
         LOOP
            l_student.EXTEND;
            l_student (l_student.LAST).student_id := l_student_id (indx);
            l_student (l_student.LAST).salutation := l_salutation (indx);
            l_student (l_student.LAST).first_name := l_first_name (indx);
            l_student (l_student.LAST).last_name := l_last_name (indx);
            l_student (l_student.LAST).street_address := l_street_address (indx);
            l_student (l_student.LAST).zip := l_zip (indx);
            l_student (l_student.LAST).phone := l_phone (indx);
            l_student (l_student.LAST).employer := l_employer (indx);
            l_student (l_student.LAST).registration_date := l_registration_date (indx);
            l_student (l_student.LAST).created_by := l_created_by (indx);
            l_student (l_student.LAST).created_date := l_created_date (indx);
            l_student (l_student.LAST).modified_by := l_modified_by (indx);
            l_student (l_student.LAST).modified_date := l_modified_date (indx);
        END LOOP;

         v_rc2 := st_insert (l_student);

         IF v_rc2 <> 0
         THEN
            st.v_errorcntr := st.v_errorcntr + 1;

            IF st.v_errorcntr >= 10
            THEN
               v_returncode := v_rc2;
               v_errmessage := SQLERRM;
               RAISE exit_early;
            END IF;
         END IF;

         IF v_mgna_x >= v_gblxcommit
         THEN
            v_mgna_x := 1;
            COMMIT;
         ELSE
            v_mgna_x := v_mgna_x + 1;
         END IF;

         l_student.DELETE;
      END LOOP;
   EXCEPTION
      WHEN exit_early
      THEN
         DBMS_OUTPUT.put_line (SQLCODE || '  ' || SQLERRM);
         v_returncode := v_rc2;
         v_errmessage := SQLERRM;
   END st_work;

   FUNCTION st_insert (v_rec IN student_ntt)
      RETURN VARCHAR2
   IS
      v_rc          VARCHAR2 (20) := '0';
     

  BEGIN
      FORALL i IN 1 .. v_rec.COUNT 
         INSERT INTO student_new
              VALUES v_rec (i);
      RETURN v_rc;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (SQLCODE || '    ' || SQLERRM);
         DBMS_OUTPUT.put_line ('error when adding record..');
         v_rc := TO_CHAR (SQLCODE);
         RETURN v_rc;
   END st_insert;
END st;
/

----TESTING BLOCK----

DECLARE
   p_regi_date   DATE;
BEGIN
   p_regi_date := '19-FEB-99';
   st.st_work (p_regi_date => p_regi_date);
END;



[Updated on: Fri, 20 October 2006 12:48]

Report message to a moderator

Re: function is inserting multiple time [message #199180 is a reply to message #199161] Fri, 20 October 2006 17:36 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Why do you need
"WHILE lRow IS NOT NULL LOOP" --???

IMHO it's the reason for infinite loop.
Re: function is inserting multiple time [message #199193 is a reply to message #199180] Fri, 20 October 2006 23:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You forgot to assign l_row the l_student_id.next before the end loop.
Re: function is inserting multiple time [message #199326 is a reply to message #199161] Mon, 23 October 2006 09:43 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
'19-FEB-99' is a date? I'm guessing it's February 19, 1899. that's the first date that came to my mind with a partial character string as such.
Re: function is inserting multiple time [message #199477 is a reply to message #199326] Tue, 24 October 2006 12:48 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Sorry for replying late, Guys!

You are absolutely right I don't need WHILE LOOP.

I remove it and now it is working fine.

and for the date format,I should use format mask.

Thanking you all

Re: function is inserting multiple time [message #199684 is a reply to message #199477] Wed, 25 October 2006 17:03 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hi Guys,
having problem with the following function.

Any idea why this function is returning
NULL though it insert records in the table


  FUNCTION st_insert (v_rec IN student_ntt)
      RETURN VARCHAR2
   IS
      v_rc          VARCHAR2 (20);
     
  BEGIN
      FORALL i IN 1 ..v_rec.COUNT SAVE EXCEPTIONS
         INSERT INTO student_new
              VALUES v_rec (i);
  
 RETURN v_rc;     
   
EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN NULL;
      WHEN TOO_MANY_ROWS
      THEN
       V_RC := SQLCODE;
         DBMS_OUTPUT.PUT_LINE('error :' ||V_RC);
         RAISE;
 
END st_insert;



Here is the test code for the function.


SQL> set timing on
SQL> set serveroutput on
SQL> DECLARE
  2    V_REC ST.STUDENT_NTT;
  3    v_Return VARCHAR2(200);
  4  BEGIN
  5    select *
  6     bulk collect into v_rec
  7      from student;
  8
  9    v_Return := ST.ST_INSERT( V_REC => V_REC);
 10
 11    DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
 12  END;
 13
 14  /
v_Return =

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
SQL>

[Updated on: Wed, 25 October 2006 17:10]

Report message to a moderator

Re: function is inserting multiple time [message #199719 is a reply to message #199684] Thu, 26 October 2006 00:28 Go to previous messageGo to next message
Alien
Messages: 245
Registered: June 1999
Senior Member
Uhm.... you don't assign any value to v_rc. So what did you except to get returned?

Re: function is inserting multiple time [message #199851 is a reply to message #199719] Thu, 26 October 2006 08:01 Go to previous message
Akshar
Messages: 116
Registered: May 2006
Senior Member

Thanks for your insight, Alien!
Got it!

FUNCTION st_insert (v_rec IN student_ntt)
      RETURN VARCHAR2
   IS
      v_rc          VARCHAR2 (20) := 0;   ----got it.
     
  BEGIN
      FORALL i IN 1 ..v_rec.COUNT SAVE EXCEPTIONS
         INSERT INTO student_new
              VALUES v_rec (i);
  
 RETURN v_rc;     
   
EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN NULL;
      WHEN TOO_MANY_ROWS
      THEN
       V_RC := SQLCODE;
         DBMS_OUTPUT.PUT_LINE('error :' ||V_RC);
         RAISE;
 
END st_insert;




Test


SQL>  DECLARE
  2        V_REC ST.STUDENT_NTT;
  3         v_Return VARCHAR2(200);
  4       BEGIN
  5         select *
  6          bulk collect into v_rec
  7           from student;
  8
  9         v_Return := ST.ST_INSERT( V_REC => V_REC);
 10
 11        DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
 12      END;
 13   /
v_Return = 0

PL/SQL procedure successfully completed.

SQL>

Previous Topic: Sum() in DENSE_RANK()
Next Topic: Help on create trigger
Goto Forum:
  


Current Time: Sun Dec 11 04:23:59 CST 2016

Total time taken to generate the page: 0.05587 seconds