It is required to add the SELECT value to the variable:

(SELECT * FROM ( SELECT PERSON_EMAIL FROM PERSONS WHERE rownum BETWEEN 1 AND ID ORDER BY rownum DESC) WHERE rownum=1);

I try to do this:

 EMAIL := (SELECT * FROM ( SELECT PERSON_EMAIL FROM PERSONS WHERE rownum BETWEEN 1 AND ID ORDER BY rownum DESC) WHERE rownum=1);

or so:

 SET EMAIL = SELECT * FROM ( SELECT PERSON_EMAIL FROM PERSONS WHERE rownum BETWEEN 1 AND ID ORDER BY rownum DESC) WHERE rownum=1;

In this case, pre-announcing the email in DECLARE this way:

 email varchar2(4000);

But in any case, does not allow to execute the command. How correctly to bring in a variable?

  • Look at my answer to your previous question, there just the sample from the query falls into the variable (the INTO sentence is used) - Mike

3 answers 3

Use the INTO clause:

 SELECT PERSON_EMAIL INTO email FROM ( SELECT PERSON_EMAIL FROM PERSONS WHERE rownum BETWEEN 1 AND ID ORDER BY rownum DESC ) WHERE rownum=1

PS In general, the request is a bit strange, you are trying to get some kind of "last" record. But Oracle, like other DBMSs, does not guarantee the order of selecting records without specifying the exact sort order. Those. "last" (with a maximum rownum) any record could theoretically be.

    1) The assignment of a variable from a query is done with the following syntax.

     declare email varchar(4000); begin select email into email from имя таблицы where .... end;

    2) In such a request exactly one value must be returned, no more, no less, otherwise orakl will issue an unhandled exception. If you can return a null value, you can either handle this exception or return the maximum value using the max function.

    3) If you need to list all addresses separated by commas, you can use the LISTAGG function in the query:

      select LISTAGG (email,',') WITHIN GROUP (ORDER BY email) into email from имя таблицы where id between 10 and 100;

      Use SELECT INTO , an example:

       declare sysd date; begin select sysdate into sysd from dual; dbms_output.put_line(sysd); end;

      Manual