issues in creating pl/sql procedure with %ROWTYPE

Question

I am facing issues in creating a procedure that performs an operation. Basically I have two tables one is INCOME Table another one is RECURRINGTRANSACTION. I am planning to create a job in oracle 11g which execute a procedure daily. the procedure will have a start date , end date and number of occurrences so my procedure should check if start date is today's date and number of occurrences!=0 then it should insert a row into my 1st table which is INCOME table. Below are my table descriptions and data .

create or replace procedure recurTransDaily(currTransID in number)
is

  type r_currentTransRow is record(
   RECTRANSACTIONID RECURRINGTRANSACTION.RECTRANSACTIONID%TYPE,
   AMOUNT RECURRINGTRANSACTION.AMOUNT%TYPE,
   DESCRIPTION RECURRINGTRANSACTION.DESCRIPTION%TYPE,
   ISEXPENSE RECURRINGTRANSACTION.ISEXPENSE%TYPE,
   ISINCOME RECURRINGTRANSACTION.ISINCOME%TYPE,
   NOOFOCCURENSES RECURRINGTRANSACTION.NOOFOCCURENSES%TYPE,
   TRANSACTIONDATE RECURRINGTRANSACTION.TRANSACTIONDATE%TYPE,
   TRANSSACTIONENDDATE RECURRINGTRANSACTION.TRANSSACTIONENDDATE%TYPE,
   CATEGORYID RECURRINGTRANSACTION.CATEGORYID%TYPE,
   PAYEEID RECURRINGTRANSACTION.PAYEEID%TYPE,
   RECURRINGTRANSACTIONTYPE     RECURRINGTRANSACTION.RECURRINGTRANSACTIONTYPE%TYPE,
   PERSONID RECURRINGTRANSACTION.PERSONID%TYPE);


BEGIN
   select *
   into r_currentTransRow
   from RECURRINGTRANSACTION where RECTRANSACTIONID=currTransID;

 if to_char(r_currentTransRow.TRANSACTIONDATE,'MM-dd- yyyy')=to_char(sysdate,'MM-dd-yyyy') then
 -- insert a record into income table
    insert into   income(AMOUNT,DESCRIPTION,TRANSACTIONDATE,CATEGORYID,PAYEEID,PERSONID) values
  (r_currentTransRow.AMOUNT,r_currentTransRow.DESCRIPTION,r_currentTransRow.TRANSACTIONDATE,
   r_currentTransRow.CATEGORYID,r_currentTransRow.PAYEEID,r_currentTransRow.PERSONID);

    -- update the present RECURRINGTRANSACTION row with TRANSACTIONDATE=sysdate+1 and NOOFOCCURENSES=NOOFOCCURENSES-1
   update RECURRINGTRANSACTION set  RECURRINGTRANSACTION.TRANSACTIONDATE=to_char(sysdate+1,'MM-dd-yyyy'),
        RECURRINGTRANSACTION.NOOFOCCURENSES=r_currentTransRow.NOOFOCCURENSES-1 
             WHERE   RECURRINGTRANSACTION.RECTRANSACTIONID=r_currentTransRow.RECTRANSACTIONID;
   commit;
 end if;
 END;

If I execute the above script it is giving me the below errors. I am new to PL/SQL. Can some one explain the meaning of the errors and the way to fix them

Answer 1

/*Hello you can try below snippet. Since I don't have workspace with me now so may contain syntax error. Let me know if this helps. */

CREATE OR REPLACE PROCEDURE recurTransDaily(
    currTransID IN NUMBER)
IS
type r_currentTransRowrcrd
IS
  record
  (
    RECTRANSACTIONID RECURRINGTRANSACTION.RECTRANSACTIONID%TYPE,
    AMOUNT RECURRINGTRANSACTION.AMOUNT%TYPE,
    DESCRIPTION RECURRINGTRANSACTION.DESCRIPTION%TYPE,
    ISEXPENSE RECURRINGTRANSACTION.ISEXPENSE%TYPE,
    ISINCOME RECURRINGTRANSACTION.ISINCOME%TYPE,
    NOOFOCCURENSES RECURRINGTRANSACTION.NOOFOCCURENSES%TYPE,
    TRANSACTIONDATE RECURRINGTRANSACTION.TRANSACTIONDATE%TYPE,
    TRANSSACTIONENDDATE RECURRINGTRANSACTION.TRANSSACTIONENDDATE%TYPE,
    CATEGORYID RECURRINGTRANSACTION.CATEGORYID%TYPE,
    PAYEEID RECURRINGTRANSACTION.PAYEEID%TYPE,
    RECURRINGTRANSACTIONTYPE RECURRINGTRANSACTION.RECURRINGTRANSACTIONTYPE%TYPE,
    PERSONID RECURRINGTRANSACTION.PERSONID%TYPE);
TYPE lv_tab
IS
  TABLE OF r_currentTransRowrcrd;
  r_currentTransRow lv_tab;
BEGIN

  EXECUTE IMMEDIATE 'ALTER SESSION set nls_timestamp_format = ''DD-MON-YY HH24:MI:SS.FF6''';
  EXECUTE IMMEDIATE 'ALTER SESSION set nls_date_format = ''DD-MON-YY''';

  SELECT * BULK COLLECT
  INTO r_currentTransRow
  FROM RECURRINGTRANSACTION
  WHERE RECTRANSACTIONID =currTransID;

  FOR I IN r_currentTransRow.FIRST..r_currentTransRow.LAST

  LOOP
    IF TO_CHAR(TO_TIMESTAMP(r_currentTransRow(i).TRANSACTIONDATE,'DD-MON-YY HH24:MI:SS.FF6'),'DD-MON-YY') = to_char(sysdate,'DD-MON-YY') THEN
      -- insert a record into income table

      INSERT
      INTO income
        (
          AMOUNT,
          DESCRIPTION,
          TRANSACTIONDATE,
          CATEGORYID,
          PAYEEID,
          PERSONID
        )
        VALUES
        (
          r_currentTransRow(i).AMOUNT,
          r_currentTransRow(i).DESCRIPTION,
          r_currentTransRow(i).TRANSACTIONDATE,
          r_currentTransRow(i).CATEGORYID,
          r_currentTransRow(i).PAYEEID,
          r_currentTransRow(i).PERSONID
        );
      -- update the present RECURRINGTRANSACTION row with TRANSACTIONDATE=sysdate+1 and NOOFOCCURENSES=NOOFOCCURENSES-1
      UPDATE RECURRINGTRANSACTION
      SET RECURRINGTRANSACTION.TRANSACTIONDATE   =SYSTIMESTAMP+1,
        RECURRINGTRANSACTION.NOOFOCCURENSES      =r_currentTransRow(i).NOOFOCCURENSES-1
      WHERE RECURRINGTRANSACTION.RECTRANSACTIONID=r_currentTransRow(i).RECTRANSACTIONID;

      COMMIT;
    END IF;
  END LOOP;
END;