Long to Varchar data conversion

Asked

Viewed 689 times

0

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.

1 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;"

  • However I need you to bring all the records of the same date Bruno, it is possible ?

  • Yes, I’ve edited the answer

  • Thanks Bruno, now if you can help me, I need to insert a LIKE '%SEPSE%' to bring only the records that have this description in the Long field. As I can?

  • You can’t do this directly from the long column or you make an if using varchar2 or turn the long into clob, but in this last case it will depend on the version of the oracle you are using.

  • I am in version 12c, however I think that changing the field will not be feasible without authorization from the system provider... could you give me an example of how to do this using if with varchar2?

Browser other questions tagged

You are not signed in. Login or sign up in order to post.