Long to Varchar data conversion

Question

I am trying to convert data from a Long field to Varchar, but when I do the procedure, it is returning the following error:

ORA-01422: Exact extraction returns more than the requested number of lines

ORA-06512: in line 4

The code I’m using is as follows::

DECLARE VAR1 LONG; VAR2 VARCHAR2(32000); BEGIN SELECT DS_EVOLUCAO INTO VAR1 FROM PRE_MED WHERE HR_PRE_MED = '01/08/2019'; VAR2 := SUBSTR(VAR1, 1, 32000); DBMS_OUTPUT.PUT_LINE(VAR2); END;

The fields may exceed 32000 of field space, so I used the SUBSTR, but still can not succeed.

Answer 1

O select: "SELECT DS_EVOLUCAO INTO VAR1 FROM PRE_MED WHERE HR_PRE_MED = '01/08/2019';"

Must be returned more than one result, try to use:

"SELECT DS_EVOLUCAO INTO VAR1 FROM PRE_MED WHERE HR_PRE_MED = '01/08/2019' AND ROWNUM < 2;"

Or increase the number of filters.

To print all the lines you can do so:

"DECLARE VAR2 VARCHAR2(32000); BEGIN FOR REG IN (SELECT DS_EVOLUCAO FROM PRE_MED WHERE HR_PRE_MED = '01/08/2019') LOOP VAR2 := SUBSTR(REG.DS_EVOLUCAO, 1, 32000); DBMS_OUTPUT.PUT_LINE(VAR2); END LOOP;
END;"