Search code examples
databaseplsqlprocedures

Getting incorrect output in below PL/SQL Proc


CREATE OR REPLACE PROCEDURE Proc_TEST(T_DATE DATE DEFAULT TRUNC(SYSDATE))
AS
  PREV1 VARCHAR(20);
  mnth VARCHAR(20);
BEGIN
SELECT TO_CHAR(TO_DATE(TRUNC(T_DATE,'MM')-1),'MON_YYYY')  INTO PREV1  FROM DUAL;
FOR i IN 1 .. 3 LOOP
  mnth:='PREV'||i;
  DBMS_OUTPUT.PUT_LINE('op'||mnth);
  DBMS_OUTPUT.PUT_LINE('op'||PREV1);
END LOOP;
END;
/

I am getting output as

opPREV1 
opSEP_2015

whereas i must get

opSEP_2015
opSEP_2015

.


Solution

  • You can not make a variable identifier by concatenating some strings, the string literals act as string not the identifier with same spell!

    You can use VARRAY for a fixed-size array:

    declare
       type array_t is varray(3) of varchar2(10);
       array array_t := array_t('Matt', 'Joanne', 'Robert');
    begin
       for i in 1..array.count loop
           dbms_output.put_line(array(i));
       end loop;
    end;
    

    Or TABLE for an unbounded array:

    ...
       type array_t is table of varchar2(10);
    ...
    

    The word "table" here has nothing to do with database tables, confusingly. Both methods create in-memory arrays.