0
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

.

krokodilko
  • 35,300
  • 7
  • 55
  • 79
Arihant Rakecha
  • 11
  • 2
  • 3
    Please examine these two lines of code: `mnth:='PREV'||i;` and `DBMS_OUTPUT.PUT_LINE('op'||mnth); `, the mistake is obvious. – krokodilko Oct 06 '15 at 15:35
  • I think you're mixing up identifiers (such as the `PREV1` variable) with literal string values (such as `'PREV'||i`). It would help to know what you're trying to achieve with this procedure. – Jeffrey Kemp Oct 07 '15 at 05:48
  • Hi Jeff,I have three variables prev1, prev2, prev3 and am trying print the values in these variables using for loop. – Arihant Rakecha Oct 07 '15 at 05:55

1 Answers1

0

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.

ALIRA
  • 129
  • 1
  • 9