Sql Dynamically

[Hubert HG]

Hi guys! I need build a store procedure that receive sql dynamically for paging, but i have problems coz the sql dynamically is inner other sql. This is the code:

CREATE OR REPLACE PROCEDURE SA.Paginado1(
NumMin IN NUMBER,
NumMax IN NUMBER,
SqlStr IN VARCHAR2, -- This is the sql passed
Cur IN OUT Omwb_emulation.globalPkg.RCT1)
AS
BEGIN
OPEN Cur FOR SELECT *
FROM
(
SELECT p.*, ROWNUM rnum
FROM (
-- sql dynamically
) p
WHERE ROWNUM <= NumMax
)
WHERE RNUM > NumMin;
END;
/

Thanks in advance!!!!

[Hubert HG]

Hi guys! I need build a store procedure that receive sql dynamically for paging, but i have problems coz the sql dynamically is inner other sql. This is the code:

CREATE OR REPLACE PROCEDURE SA.Paginado1(
NumMin IN NUMBER,
NumMax IN NUMBER,
SqlStr IN VARCHAR2, -- This is the sql passed
Cur IN OUT Omwb_emulation.globalPkg.RCT1)
AS
BEGIN
OPEN Cur FOR SELECT *
FROM
(
SELECT p.*, ROWNUM rnum
FROM (
-- sql dynamically
) p
WHERE ROWNUM <= NumMax
)
WHERE RNUM > NumMin;
END;
/

Thanks in advance!!!!

Thanks a lot!!!! I found a solution for this problem!!!
I wrote:

CREATE OR REPLACE PROCEDURE SA.Paginado(
NumMin IN NUMBER,
NumMax IN NUMBER,
SqlStr IN VARCHAR2,
Cur IN OUT Omwb_emulation.globalPkg.RCT1)
AS
BEGIN
EXECUTE IMMEDIATE 'drop table sa.v_temp';
EXECUTE IMMEDIATE 'CREATE TABLE sa.v_temp AS '||sqlstr;
OPEN Cur
FOR SELECT *
FROM(
SELECT p.*, ROWNUM rnum
FROM (
v_temp
) p
WHERE ROWNUM <= NumMax
)
WHERE RNUM > NumMin;
END;
/


Explanation: Just create a temp table that receive the sql dynamically and I use this table to obtain the rownums tops.
Thanks a lot!!!!

[debasisdas]

Moved to Oracle forum.