Hi,
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 .
I am working in a procedure where I want to manipulate the data produced by a WITH AS clause. But it is giving "PL/SQL: ORA-00942: table or view does not exist" error. A small example to illustrate my problem is given below. Here in the example am just trying to get a replaced string by using the out put of a WITH AS clause.
SET SERVEROUTPUT ON;
DECLARE
CNT INTEGER;
LETTER CHAR(1);
REPLACED_STRING VARCHAR2(10);
BEGIN
REPLACED_STRING := 'ABC';
WITH T AS
(SELECT 'ABC' VAL FROM DUAL),
TMP (LEN, SUBVAL) AS
(SELECT LENGTH(VAL) LEN,
SUBSTR(VAL, 1, INSTR(VAL, 'B', 1, 1)) SUBVAL
FROM T
UNION ALL
SELECT LENGTH(VAL)-1 LEN,
SUBSTR(VAL, 2, INSTR(VAL, 'C', 1, 1)) SUBVAL
FROM T
)
SELECT COUNT(*) INTO CNT FROM TMP,T ;
FOR I IN 1..CNT LOOP
SELECT SUBSTR(SUBVAL,1,1) INTO LETTER FROM TMP,T;
SELECT REPLACE(REPLACED_STRING,LETTER,'X')INTO REPLACED_STRING FROM DUAL;
DBMS_OUTPUT.PUT_LINE(REPLACED_STRING);
END LOOP;
END;
I thought of declaring a cursor which will hold the data produced by WITH clause but it did not work. Can you please let me know what are the possible options to do this.
Thanks