Selecting data and storing it in a variable using EXECUTE IMMEDIATE

Question

I am trying to select some rows and put the data in a variable.the procedure is

CREATE OR REPLACE PROCEDURE GET_MESSAGE
AS
V_RESULT                VARCHAR2(2000);

begin


    EXECUTE IMMEDIATE 'SELECT MESSAGE_ID FROM MSG_TABLE WHERE ( DATE_OF_OPERATION BETWEEN 20180530 AND 20180622) AND (ROWNUM BETWEEN 1 and 2)' INTO V_RESULT;
        dbms_output.put_line('V_RESULT:'||V_RESULT);


end;
/

When executing the procedure I am getting the following error

Error starting at line : 37 in command -
BEGIN GET_MESSAGE; END;
Error report -
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "PG_DBO11.GET_MESSAGE", line 8
ORA-06512: at line 1
01422. 00000 -  "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested

the MSG_TABLE contains following columns and data

MESSAGE_ID    DATE_OF_OPERATION     MESSAGE
1000          20180530              AABC
1001          20180622              XXYZ

The query itself is working fine. I don't know how to store the selected rows into a variable. I may be missing a very general thing here.

Unrelated, but: why are you using dynamic SQL at all? That's completely unnecessary for a simple select on a table that is know at runtime — user330315
– user330315, Commented Jun 26, 2018 at 5:51
because this one is not the actual procedure..the original procedure take the start_date and end_date as input parameters...then the parameters are used in query.... and thanks for the editing — Namrata Shukla
– Namrata Shukla, Commented Jun 26, 2018 at 5:57
ROWNUM BETWEEN 1 and 2 also indicates a misunderstanding on how rownum works. If you intend to do something like ROWNUM BETWEEN 3 and 6 you will find out that that won't work. You need to use something like offset 1 rows fetch first 2 rows only instead — user330315
– user330315, Commented Jun 26, 2018 at 6:04
Different values for start_date and end_date do not require dynamic SQL either. — Wernfried Domscheit
– Wernfried Domscheit, Commented Jun 26, 2018 at 6:58

Kaushik Nayak · Accepted Answer · 2018-06-27 05:53:31Z

3

You don't need dynamic SQL for this. The procedure can accept 2 date parameters and can be used in the query directly. If you want to display multiple rows in DBMS_OUTPUT, one simple way is to use an implicit for loop.

CREATE OR REPLACE PROCEDURE GET_MESSAGE( p_start_date date, p_end_date date)
AS

BEGIN

FOR rec IN 
(
    SELECT MESSAGE_ID FROM MSG_TABLE  
     WHERE  DATE_OF_OPERATION BETWEEN p_start_date AND p_end_date

 ) LOOP
        dbms_output.put_line('V_RESULT:'||rec.MESSAGE_ID);
   END LOOP;
END;
/

call the procedure as

BEGIN 
 GET_MESSAGE(DATE '2018-05-30', DATE '2018-06-22'); 
END;
/

Demo

What exactly were you trying to do while using ROWNUM BETWEEN 1 and 2 ? Note that simply using ROWNUM without an ORDER BY inside a subquery will not ensure that you can see the top 2 message_ids. If your requirement is something else, please specify clearly in the question by editing it and we may help you in modifying the select query.

EDIT: It seems that you are ok to use an XML output, you should prefer dbms_xmlgen.getxml which gives you a CLOB result that can be selected from a select query. Here's an example.

CREATE OR REPLACE FUNCTION pr_outxml RETURN CLOB
    AS
BEGIN
    RETURN dbms_xmlgen.getxml('select * from employees where rownum<= 2');
END;
/

Now, get the output as select pr_outxml from dual;. you may modify this code with additional parameters.

edited Jun 27, 2018 at 5:53

answered Jun 26, 2018 at 6:03

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Namrata Shukla Over a year ago

your solution is correct..but my requirement is to put all selected data in one variable and then send it out from the procedure..

Kaushik Nayak Over a year ago

@NamrataShukla : Please clearly specify what kind of variable that it should be? We can't store multiple rows in a scalar variable. It can be either a collection or a refcursor. But it depends on how exactly you wan't the calling block to read it. So, clarify whether you want an OUT collection variable or a REFCURSOR?

Namrata Shukla Over a year ago

i am new at pl/sql. i was not knowing about OUT collection variable or a REFCURSOR.. thanks for mentioning them..

Namrata Shukla · Accepted Answer · 2018-06-27 05:18:16Z

0

The following solution served my purpose.I have used V_RESULT as XmlType...

CREATE OR REPLACE PROCEDURE GET_MESSAGE
IS
V_RESULT                XMLTYPE;
V_QUERY                 VARCHAR(2000);
begin
V_QUERY:='SELECT
XMLELEMENT("root",XMLAGG(
                XMLELEMENT("data",
                        XMLELEMENT("MESSAGE_ID",MESSAGE_ID),
                        XMLELEMENT("MESSAGE",MESSAGE )

                        ))
                ) FROM MSG_TABLE WHERE (
DATE_OF_OPERATION BETWEEN 20180530 AND 20180622)  AND (ROWNUM BETWEEN 1 and 10)';
    EXECUTE IMMEDIATE  V_QUERY INTO V_RESULT;
    dbms_output.put_line('the result xml is '||v_result.getStringVal());
end;
/

this way I am able to select multiple rows , convert it into xml and store in one variable.

answered Jun 27, 2018 at 5:18

Namrata Shukla

1572 silver badges8 bronze badges

1 Comment

Kaushik Nayak Over a year ago

If you had mentioned in the question that you want to an xml output, we wouldn't have spent time in understanding your problem and providing a different solution. That's the reason we insist you to provide the details so that we don't have to guess too much about your requirement. when you say "put all selected data in one variable", there's nothing that implies that it could be an xml.

Collectives™ on Stack Overflow

Selecting data and storing it in a variable using EXECUTE IMMEDIATE

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related