I mostly work with SQL. I don't know how to convert that query to Oracle. Can anyone tell me how to convert this?

    DECLARE @START_YEAR INT,@MONTH INT, @END_YR INT
    SELECT 
    @START_YEAR = 2010,
    @END_YR = 2010,
    @MONTH = 1

    WHILE ( @START_YEAR <= @END_YR)
    BEGIN
    WHILE ( @MONTH <= 12)
    BEGIN

    PRINT 'INSERT INTO dbo.Mem_TXN
               ([VALUE]
               ,[BEGIN_DATE]
               ,[END_DATE]
               ,[CREATED_BY]
               ,[CREATED_DATE]
               ,[MODIFIED_BY]
               ,[MODIFIED_DATE])
         VALUES
               (''RGLR'' 
               ,'''    cast( Convert(date, DATEADD(MONTH, @MONTH - 1, DATEADD(YEAR, @START_YEAR - 1900, 0)))  AS varchar)   '''  
               ,'''   cast( Convert(date,DATEADD(MONTH, @MONTH,     DATEADD(YEAR, @START_YEAR - 1900, -1))) AS VARCHAR)   '''
               ,''admin''
               ,'  FORMAT(GETDATE(), 'yyyy-mm-dd hh:mm')  '
               ,''admin''
               ,'  FORMAT(GETDATE(), 'yyyy-mm-dd hh:mm')  '
               ,0);
               '
    SET @MONTH = @MONTH   1
    END
    SET @MONTH = 1
    SET @START_YEAR = @START_YEAR   1
    END

This SQLis required for Oracle to generate the input statement. Thank you in advance.

CodePudding user response：

I tried with Oracle Sql Developer, and the code like this:

DECLARE
    v_START_YEAR NUMBER(10);
    v_MONTH NUMBER(10);
    v_END_YR NUMBER(10);
    v_QUERY NVARCHAR2(2000);
    v_DATENOW NVARCHAR2(20);
    v_DATEFST NVARCHAR2(15);
    v_DATELST NVARCHAR2(15);
    v_DATECHAR NVARCHAR2(15);
BEGIN
    v_START_YEAR := 2010;
    v_END_YR := 2010;
    v_MONTH := 1;
   
    SELECT to_char(sysdate,'yyyy-MM-dd hh24:mi') INTO v_DATENOW FROM dual;

    WHILE  v_START_YEAR <= v_END_YR
    LOOP
        WHILE ( v_MONTH <= 12)
        LOOP
            --date char
            v_DATECHAR := ''||to_char(v_START_YEAR)||'-'||to_char(v_MONTH)||'-01';
           
            --first date
            select to_char(TRUNC(TO_DATE(v_DATECHAR, 'YYYY-MM-DD'), 'MM'), 'yyyy-MM-dd') into v_DATEFST from dual;
            -- last date
            select to_char(LAST_DAY(ADD_MONTHS(TO_DATE(v_DATECHAR, 'YYYY-MM-DD'),0)), 'yyyy-MM-dd') into v_DATELST from dual;
           
            v_QUERY := 'INSERT INTO Mem_TXN
                        (VALUE,
                        BEGIN_DATE,
                        END_DATE,
                        CREATED_BY,
                        CREATED_DATE,
                        MODIFIED_BY,
                        MODIFIED_DATE)
                        VALUES (
                            ''RGLR'',
                            '|| v_DATEFST ||',
                            '|| v_DATELST ||',
                            ''admin'',
                            '|| v_DATENOW ||',
                            ''admin'',
                            '|| v_DATENOW ||',
                            0
                        )';
               
            dbms_output.put_line(v_QUERY);
           
        v_MONTH := v_MONTH   1;
        END LOOP;
        v_MONTH := 1;
        v_START_YEAR := v_START_YEAR   1;
    END LOOP;
END;
/

Then result like this: view result

CodePudding user response：

Here's an insert statement that will do what you need:

INSERT INTO dbo.men_txn ("VALUE", begin_date, end_date, created_by, created_date, modified_by, modified_date)
WITH dts AS (SELECT add_months(to_date('01/01/2010', 'dd/mm/yyyy'), LEVEL - 1) dt
             FROM   dual
             CONNECT BY LEVEL <= 12)
SELECT 'RGLR' val,
       dt month_start,
       last_day(dt) month_end,
       'admin',
       SYSDATE,
       'admin',
       SYSDATE
FROM   dts;

Arguably, this is easier to read and follow than a recursive CTE, but you can do the same as follows:

INSERT INTO dbo.men_txn ("VALUE", begin_date, end_date, created_by, created_date, modified_by, modified_date)
WITH dts (dt) AS (SELECT to_date('01/01/2010', 'dd/mm/yyyy')
                  FROM   dual
                  UNION ALL
                  SELECT add_months(dt, 1)
                  FROM   dts
                  WHERE  add_months(dt, 1) <= add_months(to_date('01/01/2010', 'dd/mm/yyyy'), 12))
SELECT 'RGLR' val,
       dt month_start,
       last_day(dt) month_end,
       'admin',
       SYSDATE,
       'admin',
       SYSDATE
FROM   dts;

N.B. I assume you're capable of working out how to specify a starting date and however many months you want to generate the list of dates for.

Also note, in Oracle, the DATE format contains time elements, so bear that in mind if you're using the dbo.men_txn table to compare to other dates - e.g. if you query for dates between 01/01/2022 and 31/01/2022, you will miss rows with dates after midnight on 31st Jan 2022.