11 Replies Latest reply: Jan 2, 2013 2:37 PM by Frank Kulash RSS

    to_date in insert statements in cursor

    Anita.I
      hi guys,

      I was wondering if I could use a to_date in an insert statement in a cursor.
      Would the following piece of code be valid?
      create table main_table
      ( eff_date date,
       version varchar2 (5),
       user1 varchar2(10));
      
      
      create table sample_table
      ( eff_date date,
       version varchar2 (5),
       user1 varchar2(10));
      
      insert into main_table(eff_date ,version,user1) values (sysdate, '1.0',user); 
      insert into main_table(eff_date ,version,user1) values ('01-APR-07', '1.0',user); 
      insert into main_table(eff_date ,version,user1) values ('29-MAY-09', '1.0',user); 
      insert into main_table(eff_date ,version,user1) values ('14-FEB-11', '1.0',user); 
      
      CREATE OR REPLACE PROCEDURE PROCEDURE1
      is 
      type rec1
      IS
        record
        (
          eff_date main_table.eff_date%type,
          version main_table.eff_date%type,
          USER1 MAIN_TABLE.EFF_DATE%TYPE);
      type type_tab_sample
      IS
        TABLE OF REC1;
        tab_sample type_tab_sample;
        CURSOR c1
        IS
          SELECT EFF_DATE,VERSION,USER FROM MAIN_TABLE WHERE VERSION = '1.0';
          num_count number := 0;
      BEGIN
        OPEN C1;
        FETCH c1 BULK COLLECT INTO tab_sample ;
        CLOSE c1;
        
        IF  sql%rowcount > 0 THEN
          FOR num_pos IN tab_sample.first .. tab_sample.last
          LOOP
            UPDATE SAMPLE_TABLE
            SET eff_date    = to_date(tab_sample(num_pos).eff_date,'dd-mon-yyyy'), -- is this valid?
              version       = tab_sample(num_pos).version,
              user1         = USER;
            IF sql%rowcount = 0
            THEN
              INSERT  INTO sample_table
                (
                  eff_date,
                  version,
                  user1
                )
                VALUES
                (
                  to_date(tab_sample(num_pos).eff_date,'dd-mon-yyyy'), -- is this valid?
                  TAB_SAMPLE(NUM_POS).VERSION,
                  tab_sample(num_pos).user1
                ) ;
            END IF;
          END LOOP;
        END IF;
      END PROCEDURE1;
      Thanks,
      Anita

      Edited by: Anita.I on Jan 2, 2013 11:19 AM

      Edited by: Anita.I on Jan 2, 2013 11:26 AM
        • 1. Re: to_date in insert statements in cursor
          Frank Kulash
          Hi, Anita,
          Anita.I wrote:
          hi guys,

          I was wondering if I could use a to_date in an insert statement in a cursor.
          Sure.
          Would the following piece of code be valid?
          Try it and see.
          If it doesn't do what you want, then explain what you want. Post a complete script that people can run to re-create the problem and test their ideas. Include CREATE TABLE and INSERT statements for any tables used as they exist before the code is run, and the results you want, that is, the contents of the changed table(s) after everything is finished.
          Format your code.
          As always, take baby steps. Write as little as possible, test, fix (if necessary), test again, and, when that tiny amount is working the way you want, add 1 or 2 more lines, and repeat.
          See the forum FAQ {message:id=9360002}
          declare 
          type rec1 is record(
          eff_date main_table%type,
          version main_table%type,
          user main_table%type);
          %TYPE is used after a column name, for example "emp.ename%TYPE".
          type tab_bsc is table of rec1;
          
          cursor c1 is 
          select eff_date,version,user from main_table where version = '1.0';
          
          begin
          open c1;
          fetch c1 bulk collect into tab_bsc;
          close c1;
          
          num_count := tab_bsc.count;
          
          if tab_bsc.count > 0 
          then 
          for num+pos in tab_bsc.first .. tab_bsc.last 
          Did you mean num_pos, with _ instead of +?
          loop
          
          update sample_table
          set  eff_date = to_date(tab_bsc(num_pos).eff_date,'dd-mm-yyyy'), -- is this valid?
          version  = tab_bsc(num_pos).version,
          user = tab_bsc(num_pos).user);
          That depends. What are the data types involved? What kind of data do these columns contain? This is one example of why you need to post the CREATE TABLE and INSERT statements

          That UPDATE statement will change every row in the table. Is that what you want? This is one example of why you bneed to post the table contents and your desired results.
          if sql%rowcount = 0 
          Use MERGE instead of UPDATE and INSERT when you're not sure if the row already exists or not.
          then 
          insert into sample_table 
          ( eff_date,
          version,
          user)
          USER is a built-in function; it's not a very good name for a column in your table.
          values
          ( to_date(tab_bsc(num_pos).eff_date,'dd-mon-yyyy'), -- is this valid?
          Again, it depends on the data type, and perhaps the data.
          tab_bsc(num_pos).version,
          tab_bsc(num_pos).user) );

          end if;

          end;
          Thanks,
          Anita                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
          • 2. Re: to_date in insert statements in cursor
            sb92075
            Anita.I wrote:
            hi guys,

            I was wondering if I could use a to_date in an insert statement in a cursor.
            Would the following piece of code be valid?
            what error, if any, results when you run the posted code?

            since we don't have your tables or data, we can not run, test, or improve what you posted.


            How do I ask a question on the forums?
            SQL and PL/SQL FAQ
            • 3. Re: to_date in insert statements in cursor
              6363
              Anita.I wrote:
              set  eff_date = to_date(tab_bsc(num_pos).eff_date,'dd-mm-yyyy'), -- is this valid?
              ( to_date(tab_bsc(num_pos).eff_date,'dd-mon-yyyy'), -- is this valid?
              It depends

              http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions203.htm#SQLRF06132

              >
              TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of DATE data type.
              >

              If tab_bsc(num_pos).eff_dt is either CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type and in the format 'dd-mm-yyyy' then it is.

              Otherwise it isn't
              • 4. Re: to_date in insert statements in cursor
                Anita.I
                Hi guys,

                I have made correction to the code..
                The error that I receive is ORA-01841: (full) year must be between -4713 and +9999, and not be 0..

                I don't understand why this error.
                The procedure functionality is to update the rows if there are any, or else if none, insert them. Both the tables are similar..
                • 5. Re: to_date in insert statements in cursor
                  Frank Kulash
                  Hi,
                  Anita.I wrote:
                  ... The procedure functionality is to update the rows if there are any, or else if none, insert them.
                  Use MERGE. You can probably do whatever you need more efficiently, with less code, in pure SQL, without any PL/SQL.
                  Both the tables are similar..
                  Then why do you need TO_DATE? That would only be needed if, say, main_table.eff_date was a VARCHAR2 (which is not a very good way to store date information), and sample_table.eff_date was a DATE.

                  Edited by: Frank Kulash on Jan 2, 2013 2:59 PM

                  The MERGE statement might look something like this:
                  MERGE INTO     sample_table          dst
                  USING (
                            SELECT  primary_key
                         ,       eff_date. version, user_name
                         FROM    main_table
                         where       version     = '1.0'
                        )                         src
                  ON            (src.primary_key  = dst.primary_key)
                  WHEN MATCHED THEN UPDATE
                  SET              dst.eff_date     = src.eff_date
                  ,            dst.version     = src.version
                  ,            dst.user_name     = src.user_name
                  WHEN NOT MATCHED THEN 
                  INSERT            (dst.primary_key, dst.eff_date, dst.version, dst.user_name)
                  VALUES            (src.primary_key, src.eff_date, src.version, src.user_name)
                  ;
                  Without the information I requested earlier, I have to guess at several things. Guessing isn't always the best way to solve problems.
                  • 6. Re: to_date in insert statements in cursor
                    Anita.I
                    I do understand that merge is a better option .. but the requirement is such that I have to follow the mentioned steps...

                    I know that its rather redundant to do a to_date to a date field but I am not sure what to do of the error..

                    Any suggestions as to why the error?

                    ORA-01841: (full) year must be between -4713 and +9999, and not be 0
                    ***** Once the problem is resolved, the job can be re-run! *****
                    declare
                    *
                    ERROR at line 1:
                    ORA-01841: (full) year must be between -4713 and +9999, and not be 0
                    ORA-06512: at line 223

                    Edited by: Anita.I on Jan 2, 2013 12:06 PM
                    • 7. Re: to_date in insert statements in cursor
                      Frank Kulash
                      Hi,
                      Anita.I wrote:
                      I do understand that merge is a better option .. but the requirement is such that I have to follow the mentioned steps...
                      That's one reason why you need you explain your requirements.

                      If you understand that MERGE is better, why do you insist on doing things in a slower, more complicated, and more error-prone way?
                      I know that its rather redundant to do a to_date to a date field but I am not sure what to do of the error..
                      If you don't use TO_DATE, then you won't get the error. Why do you need (or want) TO_DATE? Again, this is one reason why you need to post your data (CREATE TABLE and INSERT statements).
                      Any suggestions as to why the error?

                      ORA-01841: (full) year must be between -4713 and +9999, and not be 0
                      ***** Once the problem is resolved, the job can be re-run! *****
                      declare
                      *
                      ERROR at line 1:
                      ORA-01841: (full) year must be between -4713 and +9999, and not be 0
                      ORA-06512: at line 223
                      Errors like that occur when the 1st VARCHAR2 argument of TO_DATE is not in the format specified by the 2nd argument.
                      You can avoid the error by not using TO_DATE at all.
                      One more time: post the information I keep asking for, which is also mentioned in the forum FAQ {message:id=9360002}
                      • 8. Re: to_date in insert statements in cursor
                        6363
                        Anita.I wrote:

                        I have made correction to the code..
                        Thanks, now we can see that tab_bsc(num_pos).eff_dt is DATE data type and not CHAR, VARCHAR2, NCHAR, or NVARCHAR2

                        So, as shown in the manual, it is not valid to use it as the input to TO_DATE.
                        The error that I receive is ORA-01841: (full) year must be between -4713 and +9999, and not be 0..
                        I don't understand why this error.
                        Because TO_DATE function should not be called with a DATE input value.
                        • 9. Re: to_date in insert statements in cursor
                          Anita.I
                          Frank,

                          I have already posted the insert and create statements....
                          • 10. Re: to_date in insert statements in cursor
                            Anita.I
                            Hi,

                            The error I got was before I used the to_date format..
                            The original code does not contain a to_Date part..

                            Just wanted to knw if using the to_date part would solve my problem but your explanation proves that to_date will not do so...

                            Thanks,
                            • 11. Re: to_date in insert statements in cursor
                              Frank Kulash
                              Hi,
                              Anita.I wrote:
                              create table main_table
                              ( eff_date date,
                              version varchar2 (5),
                              user1 varchar2(10));
                              ...
                              insert into main_table(eff_date ,version,user1) values (sysdate, '1.0',user); 
                              insert into main_table(eff_date ,version,user1) values ('01-APR-07', '1.0',user); 
                              insert into main_table(eff_date ,version,user1) values ('29-MAY-09', '1.0',user); 
                              insert into main_table(eff_date ,version,user1) values ('14-FEB-11', '1.0',user); 
                              Don't try to INSERT VARCHAR2 values into a DATE column.
                              The first INSERT statement above looks okay, because the value you're putting into the eff_date column is a DATE. (The SYSDATE function returns a DATE.)
                              All the other INSERT statements are definitely wrong, because (for example) '01-APR-07' is a VARCHAR2, not a DATE. Use DATE literals or, to convert a VARCHAR2 into a DATE, use TO_DATE.