Can not safely replay call when trying to assign and use a variable in procedure

I am trying use the data from a column in one table as column aliases of another table.

DECLARE var1 VARCHAR(20), var2 VARCHAR(20);
BEGIN
WITH
TABLE1 AS (SELECT ROWNUM RN, * FROM TABLE)
,A1 AS (SELECT TO_CHAR(VALUE) INTO var1 FROM TABLE1 WHERE RN = 1)
,A2 AS (SELECT TO_CHAR(VALUE) INTO var2 FROM TABLE1 WHERE RN = 2)
SELECT
COL1 AS var1,
COL2 AS var2
FROM TABLE2;
END;
/

This is obviously a simplified version of my actual procedure, but is there a chance I can get some help in understanding why I am receiving the following error from this:

ORA-25408: can not safely replay call

If there is an easier way to go about this task to begin with, suggestions are more than welcome as well!

1 Answer

Your query does not give that error.

• There is a typo as line 2 should end with ; and not ,
• Then TABLE is a keyword used for table collection expressions and not for an identifier for a table.
• Then you cannot use * with other columns without prefixing it with a table alias.
• If you fix all that then you get the exeception PL/SQL: ORA-01744: inappropriate INTO

From Oracle 12, you can select the first and second values from a table with the name TABLE_NAME into the variables using:

DECLARE var1 VARCHAR(20); var2 VARCHAR(20);
BEGIN SELECT TO_CHAR(VALUE) INTO var1 FROM table_name -- ORDER BY value OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY; SELECT TO_CHAR(VALUE) INTO var2 FROM table_name -- ORDER BY value OFFSET 1 ROWS FETCH NEXT 1 ROW ONLY; DBMS_OUTPUT.PUT_LINE(var1); DBMS_OUTPUT.PUT_LINE(var2);
END;
/

However, you would normally also use an ORDER BY clause rather than taking the rows in whatever order they are read from the data files.

db<>fiddle here