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
.
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.