5 Replies Latest reply: Feb 2, 2013 9:36 AM by EdStevens RSS

    Logic for create date column

    user1014019
      Hi Sir ,

      I have a schema DEF and I have a column_name CREATE_DATE.

      I wanted to write a procedure which will give me list of tables whose CREATE_DATE data is prior to year 2009.

      Can you explain me what would be the logic for it.

      Please help me sir as I have been stuck?

      Thanks & Regards
      Manoj
        • 1. Re: Logic for create date column
          Etbin
          Maybe NOT TESTED!
          select owner,object_name
            from all_objects
           where object_type = 'TABLE'
             and created < date '2009-01-01'
          Regards

          Etbin
          • 2. Re: Logic for create date column
            EdStevens
            user1014019 wrote:
            Hi Sir ,

            I have a schema DEF and I have a column_name CREATE_DATE.

            I wanted to write a procedure which will give me list of tables whose CREATE_DATE data is prior to year 2009.

            Can you explain me what would be the logic for it.

            Please help me sir as I have been stuck?

            Thanks & Regards
            Manoj
            What is the data type of CREATE_DATE?

            You say you are stuck, but what have you tried? It is generally frowned upon to ask for help without showing what you've done to help yourself.

            To understand about DATE columns, you might want to see: [url http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/] But I want to store my date as ... 
            • 3. Re: Logic for create date column
              user1014019
              Hi Stevens,

              The datatype of create_date is date.

              Here create_date is the column_name and table_name I wanted to find whose create_date is prior < 2009

              I have tried this

              procedure abc(p_table_name varchar2(100)) is

              v_cnt number;
              begin
              for rec in(select table_name from dba_tab_columns where column_name='CREATE_DATE' AND owner not in('SYS'))
              LOOP
              v_tab_name := rec.table_name;
              select count(1) over () ,table_name into v_cnt,v_tab_name FROM v_tab_name where to_char(create_date,'YYYY') < TO_CHAR('2009','YYYY);
              IF V_CNT > 0 then
              dbms_output.put_line(v_tab_name);
              end if;
              end loop;
              end;

              This is the approach I have decided
              Any other approach would be recommended?

              Appreciate your help ?

              Thanks & Regards
              Manoj
              • 4. Re: Logic for create date column
                sb92075
                user1014019 wrote:
                Hi Stevens,

                The datatype of create_date is date.

                Here create_date is the column_name and table_name I wanted to find whose create_date is prior < 2009

                I have tried this

                procedure abc(p_table_name varchar2(100)) is

                v_cnt number;
                begin
                for rec in(select table_name from dba_tab_columns where column_name='CREATE_DATE' AND owner not in('SYS'))
                LOOP
                v_tab_name := rec.table_name;
                select count(1) over () ,table_name into v_cnt,v_tab_name FROM v_tab_name where to_char(create_date,'YYYY') < TO_CHAR('2009','YYYY);
                IF V_CNT > 0 then
                dbms_output.put_line(v_tab_name);
                end if;
                end loop;
                end;

                This is the approach I have decided
                Any other approach would be recommended?
                interesting code with multiple syntax errors.
                You should only post code that is 100% valid & correct syntax.

                SQL must be known & valid at compile time.


                How do I ask a question on the forums?
                SQL and PL/SQL FAQ
                • 5. Re: Logic for create date column
                  EdStevens
                  user1014019 wrote:
                  Hi Stevens,

                  The datatype of create_date is date.

                  Here create_date is the column_name and table_name I wanted to find whose create_date is prior < 2009

                  I have tried this

                  procedure abc(p_table_name varchar2(100)) is

                  v_cnt number;
                  begin
                  for rec in(select table_name from dba_tab_columns where column_name='CREATE_DATE' AND owner not in('SYS'))
                  LOOP
                  v_tab_name := rec.table_name;
                  select count(1) over () ,table_name into v_cnt,v_tab_name FROM v_tab_name where to_char(create_date,'YYYY') < TO_CHAR('2009','YYYY);
                  You said CREATE_DATE is of type DATE. So why are you passing it through TO_CHAR? And your second use of TO_CHAR ('TO_CHAR('2009','YYYY)') is completely bogus. You are passing a character string to to_char.
                  IF V_CNT > 0 then
                  dbms_output.put_line(v_tab_name);
                  end if;
                  end loop;
                  end;

                  This is the approach I have decided
                  Any other approach would be recommended?
                  First, get something without the syntax errors. THEN we can work with what you have.
                  Appreciate your help ?

                  Thanks & Regards
                  Manoj
                  You've been a member of this forum for almost 4 years, and have (as of this post) 119 posts. I would have hoped that by now you would be familiar with [url https://forums.oracle.com/forums/thread.jspa?threadID=2174552#9360002]How to ask a question ... especially the parts about 1) provide table structure, 2) provide sample data, and 3) use of the \
                   tags.  OK, I see that this is based on DBA_TAB_COLS, so I'll give you a pass on the table structure and sample data ..  but you still need to learn to use code tags.