4 Replies Latest reply: Jun 21, 2013 5:43 AM by sam8682 RSS

    using forms 6i, to import excel data to database not a valid month problem

    sam8682

      Hi all,

      i am fetching data from excel dynamically to oracle table, all data is of general type

      here i am passing value V3 varchar2(100) but actual database column field is DATE.

       

      my insert code is given below

      [code]

      PACKAGE BODY PK_EXCEL_TO_DB IS

      PROCEDURE PR_DO_INSERT(i_vcColumn1 IN VARCHAR2,i_vcColumn2 IN VARCHAR2,i_vcColumn3 IN VARCHAR2,i_vcColumn4 IN VARCHAR2,i_vcColumn5 IN VARCHAR2,i_vcColumn6 IN VARCHAR2,i_vcColumn7 IN VARCHAR2,

        i_vcColumn8 IN VARCHAR2,i_vcColumn9 IN VARCHAR2,i_vcColumn10 IN VARCHAR2,i_vcColumn11 IN VARCHAR2,i_vcColumn12 IN VARCHAR2,i_vcColumn13 IN VARCHAR2,i_vcColumn14 IN VARCHAR2,

        i_vcColumn15 IN VARCHAR2,i_vcColumn16 IN VARCHAR2,i_vcColumn17 IN VARCHAR2,i_vcColumn18 IN VARCHAR2,i_vcColumn19 IN VARCHAR2,i_vcColumn20 IN VARCHAR2,i_vcColumn21 IN VARCHAR2,

        i_vcColumn22 IN VARCHAR2,i_vcColumn23 IN VARCHAR2,i_vcColumn24 IN VARCHAR2,i_vcColumn25 IN VARCHAR2,i_vcColumn26 IN VARCHAR2,i_vcColumn27 IN VARCHAR2,i_vcColumn28 IN VARCHAR2,

        i_vcColumn29 IN VARCHAR2,i_vcColumn30 IN VARCHAR2,i_vcColumn31 IN VARCHAR2,i_vcColumn32 IN VARCHAR2,i_vcColumn33 IN VARCHAR2,i_lData IN tDataList) IS

          vcInsert   VARCHAR2(3500);

          i INTEGER:=0;

          flag boolean:= FALSE;

        BEGIN

       

       

          i:=i_lData.COUNT;

          :BLK_MAIN.T4:= vcInsert;

       

          vcInsert:='INSERT INTO CRM_DELT_IMPORT (';

          if length(i_vcColumn1) > 0 then

          flag:=TRUE;

          vcInsert:=vcInsert||i_vcColumn1;

          end if;

       

          if length(i_vcColumn2)  > 0 then

            IF flag THEN

            vcInsert:=vcInsert|| ','||i_vcColumn2;

            else

            vcInsert:=vcInsert||i_vcColumn2;

            end if;

          end if;

       

          if length(i_vcColumn3) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn3;

          end if;

       

          if length(i_vcColumn4) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn4;

          end if;

       

          if length(i_vcColumn5) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn5;

          end if;

       

          if length(i_vcColumn6) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn6;

          end if;

       

          if length(i_vcColumn7) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn7;

          end if;

       

          if length(i_vcColumn8) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn8;

          end if;

       

          if length(i_vcColumn9) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn9;

          end if;

       

          if length(i_vcColumn10) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn10;

          end if;

       

          if length(i_vcColumn11) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn11;

          end if;

       

          if length(i_vcColumn12) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn12;

          end if;

       

          if length(i_vcColumn13) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn13;

          end if;

       

          if length(i_vcColumn14) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn14;

          end if;

       

          if length(i_vcColumn15) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn15;

          end if;

       

          if length(i_vcColumn16) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn16;

          end if;

       

             if length(i_vcColumn17) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn17;

             end if;

           

             if length(i_vcColumn18) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn18;

             end if;

                  

             if length(i_vcColumn19) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn19;

             end if;

           

              if length(i_vcColumn20) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn20;

              end if;

            

             if length(i_vcColumn21) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn21;

              end if;

            

             if length(i_vcColumn22) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn22;

             end if;

           

             if length(i_vcColumn23) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn23;

              end if;

            

             if length(i_vcColumn24) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn24;

              end if;

            

             if length(i_vcColumn25) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn25;

              end if;

            

             if length(i_vcColumn26) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn26;

              end if;

            

              if length(i_vcColumn27) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn27;

               end if;

             

              if length(i_vcColumn28) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn28;

              end if;

            

              if length(i_vcColumn29) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn29;

              end if;

       

       

              if length(i_vcColumn30) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn30;

              end if;

            

              if length(i_vcColumn31) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn31;

              end if;

            

              if length(i_vcColumn32) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn32;

              end if;

            

              if length(i_vcColumn33) > 0 then

          vcInsert:=vcInsert|| ','||i_vcColumn33;

          end if;      

          --

          vcInsert:=vcInsert||') values(';

          --

          if length(i_vcColumn1) > 0 then

          vcInsert:=vcInsert||''''||i_ldata(i).V1||'''';

          end if;

       

          if length(i_vcColumn2) > 0 then

          IF flag THEN

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V2||'''';

          else

          vcInsert:=vcInsert|| ''''||i_ldata(i).V2||'''';

          end if;

       

          end if;

       

          if length(i_vcColumn3) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V3||'''';

          --vcInsert:=vcInsert|| ','||''''||to_date(to_date(i_ldata(i).V3,'dd-mon-yy HH12:MI:SS'),'dd-mon-yy HH12:MI:SS')||'''';

          -- vcInsert:=vcInsert|| ','||''''|| to_date('i_ldata(i).V3','dd-mon-yy HH12:MI:SS')||''''; --to_date('18-JUN-2013 12:00:00', 'DD-MON-YYYY HH24:MI:SS')

          end if;

       

          if length(i_vcColumn4) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V4||'''';

          end if;

       

          if length(i_vcColumn5) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V5||'''';

          end if;

       

          if length(i_vcColumn6) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V6||'''';

          end if;

       

          if length(i_vcColumn7) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V7||'''';

          end if;

       

          if length(i_vcColumn8) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V8||'''';

          end if;

       

          if length(i_vcColumn9) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V9||'''';

          end if;

       

          if length(i_vcColumn10) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V10||'''';

          end if;

       

          if length(i_vcColumn11) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V11||'''';

          end if;

       

       

          if length(i_vcColumn12) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V12||'''';

          end if;

       

          if length(i_vcColumn13) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V13||'''';

          end if;

       

          if length(i_vcColumn14) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V14||'''';

          end if;

       

          if length(i_vcColumn15) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V15||'''';

          end if;

       

          if length(i_vcColumn16) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V16||'''';

          end if;

       

          if length(i_vcColumn17) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V17||'''';

          end if;

       

          if length(i_vcColumn18) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V18||'''';

          end if;

       

       

          if length(i_vcColumn19) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V19||'''';

          end if;

       

       

          if length(i_vcColumn20) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V20||'''';

          end if;

       

          if length(i_vcColumn21) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V21||'''';

          end if;

         

          if length(i_vcColumn22) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V22||'''';

          end if;

       

          if length(i_vcColumn23) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V23||'''';

          end if;

       

       

          if length(i_vcColumn24) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V24||'''';

          end if;

       

          if length(i_vcColumn25) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V25||'''';

          end if;

       

          if length(i_vcColumn26) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V26||'''';

          end if;

       

          if length(i_vcColumn27) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V27||'''';

          end if;

       

          if length(i_vcColumn28) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V28||'''';

          end if;  

       

          if length(i_vcColumn29) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V29||'''';

          end if;

       

       

          if length(i_vcColumn30) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V30||'''';

          end if;

       

          if length(i_vcColumn31) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V31||'''';

          end if;

       

          if length(i_vcColumn32) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V32||'''';

          end if;

       

          if length(i_vcColumn33) > 0 then

          vcInsert:=vcInsert|| ','||''''||i_ldata(i).V33||'''';

          end if;

       

          vcInsert:=vcInsert||')';

             

           FORMS_DDL(vcInsert);

           :BLK_MAIN.T4:= vcInsert;

         

         --END LOOP;

          STANDARD.COMMIT;

        END;

      END;

       

      [/code]

       

      How can i pass '13-06-13 05:54:33' to database table.

      if i put condition

      vcInsert:=vcInsert||','||''''||TO_CHAR('i_ldata(i).V3','DD-MON-RR HH12:MI:SS')||''''

      i get the error

      to_char many declaration..

      if i remove this i got error

      ORA-01843: no a valid month

      Since i am dynamically importing the excel data, every time the position of the date fild is going to change. For static its ok that i know that V3 is going to be a date.
      But for dynamic date may be  at position V22 or at any place.

       

      please suggest something.

      Regards

      Sam

        • 1. Re: using forms 6i, to import excel data to database not a valid month problem
          Marwim

          Hello Sam,

           

          TO_CHAR('i_ldata(i).V3','DD-MON-RR HH12:MI:SS') does not make sense when i_ldata(i).V3 is a string.

           

          When you cannot do a TO_DATE on your data before the insert, then you have to rely on implicit conversion. This means that Oracle will use the NLS settings of the session to interpret the content of a string.

           

          SELECT value
          FROM   nls_session_parameters
          WHERE  parameter = 'NLS_DATE_FORMAT';
          .
          VALUE
          --------------------
          DD.MM.YYYY
          .
          

           

          This is the default of my session. So Oracle will only accept this format for implicit conversion

           

          SELECT TO_DATE('01.01.2013') FROM dual;
          .
          TO_DATE('01.01.2013')
          ---------------------
          01.01.2013
          .
          
          
          

           

          (Practically some other formats like 01-JAN-2013 or 01/01/2013 will work too)

           

          Though a conversion with time part will fail

           

          SELECT TO_DATE('01.01.2013 23:23:45') FROM dual;
          .
          ORA-01830: date format picture ends before converting entire input string
          .
          
          
          

           

          Now I change the session format

          ALTER SESSION SET NLS_DATE_FORMAT='dd.mm.yyyy hh24:mi:ss';
          .
          SELECT TO_DATE('01.01.2013 23:23:45') FROM dual;
          .
          TO_DATE('01.01.201323:23:45')
          -----------------------------
          01.01.2013 23:23:45
          
          
          
          

           

          So your default date format has to match the format in your file.

           

          Regards

          Marcus

          • 2. Re: using forms 6i, to import excel data to database not a valid month problem
            sam8682

            hii Marwin

            Thanks for the reply. Yes i understand what exactly you are saying.

            That's why i have created a test table. All fields in the table are of varchar2 type and no constraints were given to the table and import the data to test table.

            Once the data is imported to test table, then i am inserting data to my original table i.e CRM_DELT_IMPORT. I have written the following code for that.

            But when i press the button i got the error

             

            FRM-40735:WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-00001

             

            my CRM_DELT_IMPORT have composit key on ("ROUTE_ID", "DELDATE", "DELSNO", "PROFILE_ID")

            I also inserted few records with normal insert on SQL prompt , records gets inserted in to the table.

            But when i use the form for insert and click on button of insert i get the error.

            I also check that records gets imported in test table but not get inserted into CRM_DELT_IMPORT table (with the help of form. )

            (can i use IGNORE_ROW_ON_DUPKEY_INDEX here? and how?)

            Please help me in this problem.

            Thanks again.

            Regards

            Sam

            My code on when-button-pressed is

             

            DECLARE
              CURSOR IMP IS
              SELECT ROUTE_ID, to_date(DELDATE,'DD-MM-RR HH24:MI:SS')DELDATE, DELSNO, DEL_BOY,
              PROFILE_ID, TRNTYPE, RECNO, EXP_FR, EXP_PA, EXP_SK, EXP_EMPTY, EXP_TIME, ACT_FR,
              ACT_PA, ACT_SK, ACT_EMPTY, ACT_TIME, LATITUDE, LONGITUDE, IS_FREE, RATE_LITER, AMT_DEBIT,
              DEVICE_ID, PROF_NAME, ADDR1, ADDR2, LANDMARK, PLAN_ID, DEL_MODE, NEW_CHANGE, NO_DEL_CODE,
              TO_DATE(DEV_DATE,'DD-MM-RR HH24:MI:SS')DEVICE_DATE, substr(DEV_TIME,12,19)DEVICE_TIME
              FROM TEST;
              IMP_REC IMP%ROWTYPE;
            BEGIN
              OPEN IMP;
              FETCH IMP INTO IMP_REC;
              WHILE IMP%FOUND
              LOOP
              INSERT INTO CRM_DELT_IMPORT(ROUTE_ID, DELDATE, DELSNO, DEL_BOY, PROFILE_ID, TRNTYPE, RECNO, EXP_FR, EXP_PA, EXP_SK, EXP_EMPTY,
              EXP_TIME, ACT_FR, ACT_PA, ACT_SK, ACT_EMPTY, ACT_TIME, LATITUDE, LONGITUDE, IS_FREE, RATE_LITER, AMT_DEBIT,
              DEVICE_ID, PROF_NAME, ADDR1, ADDR2, LANDMARK, PLAN_ID, DEL_MODE, NEW_CHANGE, NO_DEL_CODE, DEV_DATE, DEV_TIME)
                VALUES(IMP_REC.ROUTE_ID, IMP_REC.DELDATE, IMP_REC.DELSNO, IMP_REC.DEL_BOY, --to_date(DELDATE,'DD-MM-RR HH24:MI:SS')
              IMP_REC.PROFILE_ID, IMP_REC.TRNTYPE, IMP_REC.RECNO, IMP_REC.EXP_FR,IMP_REC.EXP_PA,IMP_REC.EXP_SK,IMP_REC.EXP_EMPTY,IMP_REC.EXP_TIME,IMP_REC.ACT_FR,
              IMP_REC.ACT_PA,IMP_REC.ACT_SK,IMP_REC.ACT_EMPTY,IMP_REC.ACT_TIME,IMP_REC.LATITUDE,IMP_REC.LONGITUDE,IMP_REC.IS_FREE,IMP_REC.RATE_LITER,IMP_REC.AMT_DEBIT,
              IMP_REC.DEVICE_ID,IMP_REC.PROF_NAME,IMP_REC.ADDR1,IMP_REC.ADDR2,IMP_REC.LANDMARK,IMP_REC.PLAN_ID,IMP_REC.DEL_MODE,IMP_REC.NEW_CHANGE,IMP_REC.NO_DEL_CODE,
                IMP_REC.DEVICE_DATE,IMP_REC.DEVICE_TIME); ---TO_DATE(DEV_DATE,'DD-MM-RR HH24:MI:SS'), substr(DEV_TIME,12,19)
              END LOOP;
              COMMIT;
              CLOSE IMP;
            END;
            
            • 3. Re: using forms 6i, to import excel data to database not a valid month problem
              Marwim

              ORA-00001 is quite clear: you have duplicate keys. But only you have the data, so I don't think we can help you.

              can i use IGNORE_ROW_ON_DUPKEY_INDEX here?

              You can if you know that you will lose the duplicate rows.

              and how?

              By not using slow row by row processing.

              Your anonymous block can be written as

              INSERT INTO CRM_DELT_IMPORT(ROUTE_ID, DELDATE, ...)

              SELECT /* IGNORE_ROW_ON_DUPKEY_INDEX crm_delt_import(ROUTE_ID, DELDATE,DELSNO,PROFILE_ID) */

                 ROUTE_ID, to_date(DELDATE,'DD-MM-RR HH24:MI:SS'),... 

              FROM TEST; 

              Though of course I could not test if the hint is written correctly.

              • 4. Re: using forms 6i, to import excel data to database not a valid month problem
                sam8682

                Thanks for reply Marwim,

                "IGNORE_ROW_ON_DUPKEY_INDEX " is ok for me. and it works.

                Thanks again.

                Regards

                Sam