Oracle query not working in stored procedure

Question

I have a query which I am using for displaying data in front end. The issue is the query is working fine if I run with simple parameters and it fetches the data, but when the same is run through the stored procedure it does not bring any data.

Below is the simple query:

SELECT DISTINCT NVL(STM.SERVICE_TYPE,'-')SERVICETYPE, NVL(SM.SERVICE_NAME,'-')SERVICENAME, NVL(SM.URL,'-')URL, NVL(SM.MXD,'-')MXD, NVL(SD.SERVER_TYPE,'-')SERVERTYPE, NVL(SD.LAYER,'-')LAYER
    FROM SERVICES_MASTER SM
          INNER JOIN SERVICE_DETAILS SD
              ON SM.SERVICE_ID = SD.SERVICE_ID
          INNER JOIN SERVICE_TYPES_MASTER STM
              ON SM.SERVICE_TYPE_ID = STM.SERVICE_TYPE_ID
    WHERE SM.SERVICE_NAME LIKE '%SNOCAlarmBoundary%';

The below is the stored procedure one

STREX:='SELECT DISTINCT NVL(STM.SERVICE_TYPE,''-'')SERVICETYPE, NVL(SM.SERVICE_NAME,''-'')SERVICENAME, NVL(SM.URL,''-'')URL, NVL(SM.MXD,''-'')MXD, NVL(SD.SERVER_TYPE,''-'')SERVERTYPE, NVL(SD.LAYER,''-'')LAYER
    FROM SERVICES_MASTER SM
          INNER JOIN SERVICE_DETAILS SD
              ON SM.SERVICE_ID = SD.SERVICE_ID
          INNER JOIN SERVICE_TYPES_MASTER STM
              ON SM.SERVICE_TYPE_ID = STM.SERVICE_TYPE_ID                 
         WHERE SM.'|| UPPER(P_PARAM_TYPE) ||' '|| P_OPERATOR  || ' :PARAM';

 DBMS_OUTPUT.PUT_LINE('STREX '|| STREX);
 OPEN P_RETURN FOR STREX USING VAL;  

why it's not working in second case and what should I do to work it

update

PROCEDURE FILTER_SEARCH_DATA
  (
P_SEARCH_TYPE IN NVARCHAR2,
P_PARAM_TYPE IN NVARCHAR2,
P_OPERATOR IN NVARCHAR2,
P_TEXTVAL IN NVARCHAR2,
P_RETURN OUT SYS_REFCURSOR 
 ) 
 AS

 STR NVARCHAR2(400):='';
 STROP NVARCHAR2(400):='';
  STREX VARCHAR2(4000):='';
 VAL NVARCHAR2(4000);

 BEGIN
 IF (P_OPERATOR = 'LIKE') THEN
     val := '%' || UPPER(P_TEXTVAL) ||'%';
 ELSE
   val := UPPER(P_TEXTVAL) ;
END IF;

 DBMS_OUTPUT.PUT_LINE('STR'|| STR);
  IF P_SEARCH_TYPE = 'Application' THEN   
   STREX:='SELECT DISTINCT NVL(AM.APPLICATIONNAME,''-'')APPLICATIONNAME, NVL(AD.URLPATH,''-'')URL, NVL(AM.PROJECTNO,''-'')PROJECTNO, NVL(AM.VSS_FOLDER_LOC,''-'')VSSFOLDERLOC,
         NVL(AU.NAME, ''-'')SPOCUSER, NVL(AUR.NAME,''-'')REQUESTEDBY, NVL(AUD.NAME,''-'')DELIVERYMANAGER             
           FROM APPLICATION_MASTER AM 
                INNER JOIN APPLICATION_DETAILS AD
                   ON AM.APP_MST_ID = AD.APP_MST_ID 
                INNER JOIN APPUSER_UMS AU
                   ON AM.APP_MST_ID = AU.APP_USERID    
                   INNER JOIN APPUSER_UMS AUR
                 ON AUR.APP_USERID = AM.REQUESTED_BY_APPUSRID  
                 INNER JOIN  APPUSER_UMS AUD
                      ON AUD.APP_USERID = AM.DELIVERY_MANAGER_APPUSRID  
          WHERE AM.'|| UPPER(P_PARAM_TYPE) ||' '|| P_OPERATOR  || ' :PARAM';

 DBMS_OUTPUT.PUT_LINE('STREX '|| STREX);
 OPEN P_RETURN FOR STREX USING VAL;

 END IF;

  IF P_SEARCH_TYPE = 'Services' THEN   
 STREX:='SELECT DISTINCT NVL(STM.SERVICE_TYPE,''-'')SERVICETYPE, NVL(SM.SERVICE_NAME,''-'')SERVICENAME, NVL(SM.URL,''-'')URL, NVL(SM.MXD,''-'')MXD, NVL(SD.SERVER_TYPE,''-'')SERVERTYPE, NVL(SD.LAYER,''-'')LAYER
    FROM SERVICES_MASTER SM
          INNER JOIN SERVICE_DETAILS SD
              ON SM.SERVICE_ID = SD.SERVICE_ID
          INNER JOIN SERVICE_TYPES_MASTER STM
              ON SM.SERVICE_TYPE_ID = STM.SERVICE_TYPE_ID                             
          WHERE SM.'|| UPPER(P_PARAM_TYPE) ||' '|| P_OPERATOR  || ' :PARAM';

 DBMS_OUTPUT.PUT_LINE('STREX '|| STREX);
 OPEN P_RETURN FOR STREX USING VAL;     

END IF;

 IF P_SEARCH_TYPE = 'Layers' THEN   
 STREX:='SELECT NVL(SERVER_TYPE,''-'')SERVERTYPE, NVL(LAYER,''-'')LAYER, 
NVL(FEATURECLASS,''-'')FEATURECLASS 
          FROM SERVICE_DETAILS
          WHERE '|| UPPER(P_PARAM_TYPE) ||' '|| P_OPERATOR  || ' :PARAM';

 DBMS_OUTPUT.PUT_LINE('STREX '|| STREX);
 OPEN P_RETURN FOR STREX USING VAL;     

END IF;

IF P_SEARCH_TYPE = 'Dbitem' THEN   
STREX:='SELECT NVL(DM.DB_ITEMNAME,''-'')DBITEMNAME, NVL(DM.SCHEMAOWNER,''-'')SCHEMAOWNER, NVL(DT.DBITEMTYPE,''-'')DBITEMTYPE
             FROM DATABASE_ITEMMASTER DM
           INNER JOIN DBITEMTYPE DT
   ON DM.DB_ITM_ID = DT.DB_ITEMTYPE_ID
          WHERE DM.'|| UPPER(P_PARAM_TYPE) ||' '|| P_OPERATOR  || ' :PARAM';

 DBMS_OUTPUT.PUT_LINE('STREX '|| STREX);
 OPEN P_RETURN FOR STREX USING VAL;     

END IF;

END FILTER_SEARCH_DATA;

Answer 1

OK so here comes just a wild guess

(As I mentioned before - we do not have enough information to safely pinpoint the source of your problem).

Try the following:

 IF P_SEARCH_TYPE = 'Services' THEN   
 STREX:='SELECT DISTINCT NVL(STM.SERVICE_TYPE,''-'')SERVICETYPE, NVL(SM.SERVICE_NAME,''-'')SERVICENAME, NVL(SM.URL,''-'')URL, NVL(SM.MXD,''-'')MXD, NVL(SD.SERVER_TYPE,''-'')SERVERTYPE, NVL(SD.LAYER,''-'')LAYER
    FROM SERVICES_MASTER SM
          INNER JOIN SERVICE_DETAILS SD
              ON SM.SERVICE_ID = SD.SERVICE_ID
          INNER JOIN SERVICE_TYPES_MASTER STM
              ON SM.SERVICE_TYPE_ID = STM.SERVICE_TYPE_ID                             
          WHERE UPPER(SM.'|| P_PARAM_TYPE || ') ' || P_OPERATOR  || ' :PARAM';

So what I changed is that now the upper is part of the query string. I'm guessing that, that was your initial intention (as val is always upper case).