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

Question

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!

Answer 1

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