How to replace the string which has passed in procedure while using EXECUTE IMMEDIATE in Oracle?

Posted on

Question :

Can some one help me how to use the string inside a string? I need to use something without bound variables because I am passing all the input parameters from a procedure which I am using in my real requirement, so the values are fixed.

create or replace procedure Sample( rowsize number )
is
    v_result varchar2(100);
    v_str varchar2(200);
begin
    v_str := 'Select rtrim(legl_ent_name) || '' | '' ||rtrim(legl_ent_prd_grp) as heirarchy from legl_ent_regn where rownum < rowsize';
    EXECUTE IMMEDIATE v_str ;
    DBMS_OUTPUT.PUT_LINE(rowsize);

end;
/

I’m getting the error below when I run the procedure.

BEGIN 
dbms_output.enable(); 
Sample(2); 
END;

ERROR at line 1:
ORA-00904: "ROWSIZE": invalid identifier
ORA-06512: at "RATABASE.SAMPLE", line 7
ORA-06512: at line 3

Answer :

create or replace procedure Sample(rowsize number)
is
    v_result varchar2(100);
    v_str varchar2(200);
begin
    v_str := 'Select rtrim(legl_ent_name) || '' | '' ||rtrim(legl_ent_prd_grp) as heirarchy from legl_ent_regn where rownum < :1';
    EXECUTE IMMEDIATE v_str USING rowsize;
    DBMS_OUTPUT.PUT_LINE(rowsize);
end;

Leave a Reply

Your email address will not be published. Required fields are marked *