Creating a dynamic search query using Oracle

Question

I need to search mine Oracle Databaseusing filters based on a drop down list. Thus, to search for parameters there are only 3 drop-down menus and one textbox. Now I want.

If the user has selected APPLICATIONfrom the first drop-down list and Project Namefrom the second drop-down menu and LIKEfrom the third drop-down list. A dynamic query must be created and based on the fact that it must filter the record from the database. For this, what I tried below

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 NVARCHAR2(4000):='';
BEGIN

  IF(P_OPERATOR ='LIKE') THEN
  BEGIN
   STR:=STR || '  WHERE  AM.APPLICATIONNAME ' || P_OPERATOR || '''%' || P_TEXTVAL ||'%''';
  END;
  ELSE
  BEGIN
      STR:=STR || 'WHERE AM.APPLICATIONNAME ' || P_OPERATOR  || P_TEXTVAL ;
  END;

  END IF;

  DBMS_OUTPUT.PUT_LINE('STR'|| STR);
 IF P_SEARCH_TYPE = 'APPLICATION' THEN
 DBMS_OUTPUT.PUT_LINE('START APPLICATION');
 STREX:='OPEN P_RETURN FOR SELECT AM.APPLICATIONNAME, AM.PROJECTNO, AM.VSS_FOLDER_LOC FROM APPLICATION_MASTER AM 
INNER JOIN APPLICATION_DETAILS AD
ON AM.APP_MST_ID = AD.APP_MST_ID'   || str ||';';

  DBMS_OUTPUT.PUT_LINE('STREX '|| STREX);
END IF;

 END FILTER_SEARCH_DATA;

But it does not work accordingly for what I want.

Let me know if you have any problems with this.

Answer 1

:

STREX:='OPEN P_RETURN FOR SELECT AM.APPLICATIONNAME, AM.PROJECTNO, AM.VSS_FOLDER_LOC FROM APPLICATION_MASTER AM 
INNER JOIN APPLICATION_DETAILS AD
ON AM.APP_MST_ID = AD.APP_MST_ID'   || str ||';';

  DBMS_OUTPUT.PUT_LINE('STREX '|| STREX);

. :

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 := '%' || P_TEXTVAL ||'%';
  ELSE
      val := P_TEXTVAL;
  END IF;

  DBMS_OUTPUT.PUT_LINE('STR'|| STR);
  IF P_SEARCH_TYPE = 'APPLICATION' THEN
     DBMS_OUTPUT.PUT_LINE('START APPLICATION');
     STREX:='SELECT AM.APPLICATIONNAME, AM.PROJECTNO, AM.VSS_FOLDER_LOC 
               FROM APPLICATION_MASTER AM 
                    INNER JOIN APPLICATION_DETAILS AD
                       ON AM.APP_MST_ID = AD.APP_MST_ID 
              WHERE AM.APPLICATIONNAME ' || P_OPERATOR  || ' :PARAM';

     DBMS_OUTPUT.PUT_LINE('STREX '|| STREX);
     open P_RETURN for STREX using val;
END IF;

END FILTER_SEARCH_DATA;

, (:PARAM SQL-), SQL.

: , P_SEARCH_TYPE 'APPLICATION'. - , .

UPD
STREX VARCHAR2, NVARCHAR2.