0

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!

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Divya T
  • 1
  • Your query is selecting from `table2` so it appears that the three aliases you define in your `with` statement are irrelevant. Is that intentional? The error you're getting indicates that there has been some sort of failover. Does it really happen every time you run the code in question? – Justin Cave May 13 '22 at 17:29

1 Answers1

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

MT0
  • 143,790
  • 11
  • 59
  • 117