Need assistance for dynamic query

[AurobindoSaha]

I am trying to create a dynamic sql query in SP that I like to use in crystal report.

The query has two parts:
1. A static part -----STORED IN STR_1
select .....
from .....
where ... = ...
and ... = ...
2. A dynamic part ---------STORED IN STR_2
if (.....) then
"and ABC = XYZ"
elsif (...) then
"and QWE = RTY"
end if

Now I like to concatenate str1 and str2 into a new variable str3

str3 = str1 + str2;

Then I like to execute str3.
I need help to write this SP in Oracle 8i?
I am sending the actual code below:

CREATE OR REPLACE procedure CRYSTAL_NECLLI_SP
(
SUB_Cursor OUT,
CRYSTAL_NECLLI_PKG.NECLLIType,
p_NECLLI in varchar2(11),
p_limitation in varchar2(4),
p_service_category in varchar2(5),
p_CNUM_status in varchar2(20)
)
as

declare

v_str1 varchar2(4000) := "";
v_str2 varchar2(1000) := "";
v_str3 varchar2(2) := "";

lv_NECLLI varchar2(11);
lv_limitation varchar2(4);
lv_service_category varchar2(5);
lv_CNUM_status varchar2(20);

begin

lv_NECLLI := UPPER(p_NECLLI);
lv_limitation := UPPER(p_limitation);
lv_service_category := UPPER(p_service_category);
lv_CNUM_status := UPPER(p_CNUM_status);


v_str1 ="open SUB_Cursor
SELECT
CNUM_TN_Y.NPA,
CNUM_TN_Y.NXX,
CNUM_TN_Y.LINE,
CNUM_TN_Y.SVC_CAT_CD,
CNUM_TN_Y.EXCN_CD,
CNUM_TN_Y.BGN_DAT,
CNUM_TN_Y.END_DAT,
CNUM_TN_Y.NEID,
CNUM_TN_Y.CUSTID,
CNUM_TN_Y.CUID_CD,
ODI_SRC_SYS_STATUS_Y.SYS_ID,
ODI_SRC_SYS_STATUS_Y.LOAD_ID,
ODI_SRC_SYS_STATUS_Y.STAT_DESCR
FROM
CNUM_TN_Y CNUM_TN_Y,
ODI_SRC_SYS_STATUS_Y ODI_SRC_SYS_STATUS_Y

WHERE
CNUM_TN_Y.CNUM_STAT_CD = ODI_SRC_SYS_STATUS_Y.SYS_STAT_CD(+) AND
CNUM_TN_Y.NEID = lv_NECLLI and
CNUM_TN_Y.END_DAT >= TO_DATE ('31-12-2037 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
CNUM_TN_Y.END_DAT <= TO_DATE ('31-12-2037 23:59:59', 'DD-MM-YYYY HH24:MI:SS') AND
ODI_SRC_SYS_STATUS_Y.SYS_ID = 1 AND
ODI_SRC_SYS_STATUS_Y.LOAD_ID = 1
";

if (lv_service_category = "--ALL--") then
if (lv_limitation = "--ALL--") then
if(lv_CNUM_status = "--ALL--") then
v_str2 = "AND CNUM_TN_Y.NEID = lv_NECLLI;";
else
v_str2 = "AND ODI_SRC_SYS_STATUS_Y.STAT_DESCR = lv_CNUM_status;";
else if(lv_limitation = "--NONE--") then
if(lv_CNUM_status = "--ALL--") then
v_str2 = "And CNUM_TN_Y.EXCN_CD is null"
else
v_str2= "ODI_SRC_SYS_STATUS_Y.STAT_DESCR = lv_CNUM_status and CNUM_TN_Y.EXCN_CD is null;";
else
if(lv_CNUM_status = "--ALL--") then
v_str2 = "AND CNUM_TN_Y.EXCN_CD = lv_limitation;";
else
v_str2 = "AND ODI_SRC_SYS_STATUS_Y.STAT_DESCR = lv_CNUM_status and CNUM_TN_Y.EXCN_CD = lv_limitation"
else
if (lv_limitation = "--ALL--") then
if(lv_CNUM_status)="--ALL--") then
v_str2 = "AND CNUM_TN_Y.NEID = lv_NECLLI and CNUM_TN_Y.SVC_CAT_CD = lv_service_category;";
else
v_str2 = "AND ODI_SRC_SYS_STATUS_Y.STAT_DESCR = lv_CNUM_status and CNUM_TN_Y.SVC_CAT_CD = lv_service_category;";
else if(lv_limitation = "--NONE--") then
if(lv_CNUM_status = "--ALL--") then
v_str2 = "AND CNUM_TN_Y.EXCN_CD is null and CNUM_TN_Y.SVC_CAT_CD = lv_service_category;";
else
v_str2 = "AND ODI_SRC_SYS_STATUS_Y.STAT_DESCR = lv_CNUM_status and CNUM_TN_Y.EXCN_CD is null and CNUM_TN_Y.SVC_CAT_CD = lv_service_category;";
else
if(lv_CNUM_status = "--ALL--") then
v_str2 = " CNUM_TN_Y.EXCN_CD = lv_limitation and CNUM_TN_Y.SVC_CAT_CD = lv_service_category;";
else
v_str2 = "ODI_SRC_SYS_STATUS_Y.STAT_DESCR = lv_CNUM_status and CNUM_TN_Y.EXCN_CD = lv_limitation and CNUM_TN_Y.SVC_CAT_CD = lv_service_category;";




v_str3 = v_str1 + v_str2 + ";"
dbms_output str3;
--dbms_sql.execute (str3) ??Is this correct
end CRYSTAL_NECLLI_SP;
/

 

[HimanB]

Hi,
Try to write as follows:
CREATE OR REPLACE PACKAGE CRYSTAL IS
TYPE Crystalrec is record
(L_NPA,
L_NXX,
L_LINE,
L_SVC_CAT_CD,
L_EXCN_CD,
L_BGN_DAT,
L_END_DAT,
L_NEID,
L_CUSTID,
L_CUID_CD,
L_SYS_ID,
L_LOAD_ID,
L_STAT_DESCR);

TYPE CRYSTALCUR is REF CURSOR RETURN Iafarec;

Procedure Crysatlquery is
(Resultset INOUT CRYSTALCUR,
p_NECLLI in varchar2(11),
p_limitation in varchar2(4),
p_service_category in varchar2(5),
p_CNUM_status in varchar2(20));

END;

CREATE OR REPLACE PACKAGE BODY CRYSTAL IS
Procedure Crysatlquery is
(Resultset INOUT CRYSTALCUR,
p_NECLLI in varchar2(11),
p_limitation in varchar2(4),
p_service_category in varchar2(5),
p_CNUM_status in varchar2(20)) IS
Declare
--- Declare your variables.
Begin
----- Your Code to form Dynamic & Static queries.

v_str3 = v_str1||' '||v_str2;

Open Resultset for v_str3;

EXCEPTION
WHEN OTHERS THEN
--- Do something as you wish like trap error & return or
abort program etc.
END;
END;

HTH

Regards
Himanshu