title: Dynamic Relational operator in a where clause is Invalid (PL/SQL Oracle)
body: <p>I am using Oracle 10g and I have the following stored procedure:</p>
  
  <pre><code>CREATE OR REPLACE PACKAGE BODY RF_PKG_STFCA_PositivePay
  AS
  PROCEDURE RF_SP_STFCA_PositivePay(IN_DATE IN NUMBER, IN_DATE_OPERATOR IN NVARCHAR2, OUT_DATA OUT CUR_DATA)
  IS
  /* this procedure returns a Ref Cursor with all the requested parameters
  calling the stored procedure from an asp page (and anywhere else)
  does not require posting a predefined number of records */
      PaymentBatchNumber      NVARCHAR2(4);
      CurrencyCode            NVARCHAR2(3);
      TransactionCode         NVARCHAR2(3);
      Transit_BranchNumber        NVARCHAR2(5);
      BankAccountNumber       NVARCHAR2(7);
      ChequeNumber            NVARCHAR2(8);
      ChequeAmount            NVARCHAR2(10);
      ClientReference         NVARCHAR2(19);
      IssueDate           NVARCHAR2(8);
      PayeeName1          NVARCHAR2(60);
      AddressLine1            NVARCHAR2(60);
      AddressLine2            NVARCHAR2(60);
      AddressLine4            NVARCHAR2(60);
      AddressLine5            NVARCHAR2(60);
      DateCreated         NVARCHAR2(25);
      DateVoided          NVARCHAR2(25);
  BEGIN
  OPEN OUT_DATA FOR
      SELECT LPAD(NVL(CD.PAYMENT_BATCH_NO, '0'), 4, '0') AS PaymentBatchNumber, 
      SUBSTR(NVL(CD.CURRENCY_ID, ' '), 1, 1) AS CurrencyCode,
      NVL(CD.STATUS, ' ') AS TransactionCode,
      LPAD(NVL(BA.BRANCH_ID, '0'), 5, '0') AS Transit_BranchNumber,
      LPAD(NVL(BA.ACCOUNT_NO, '0'), 7, '0') AS BankAccountNumber,
      LPAD(NVL(CD.CHECK_NO, '0') , 8, '0') AS ChequeNumber,
      LPAD(TO_CHAR(NVL(CD.AMOUNT, 0)), 10, '0') AS ChequeAmount,
      LPAD(NVL(CD.CONTROL_NO, '0'), 19, '0') AS ClientReference,
      TO_CHAR(NVL(CD.CHECK_DATE, LPAD(' ', 8, ' ')), 'YYYYMMDD') AS IssueDate,
      RPAD(NVL(CD.NAME, ' '), 60, ' ') AS PayeeName1,
      RPAD(NVL(CD.ADDR_1, ' '), 60, ' ') AS AddressLine1,
      RPAD(NVL(CD.ADDR_2, ' '), 60, ' ') AS AddressLine2,
      RPAD(NVL(CD.CITY, '') || CASE WHEN CD.CITY IS NULL OR CD.STATE IS NULL THEN ' ' ELSE ', ' END || NVL(CD.STATE, ''), 60, ' ') AS AddressLine4,
      RPAD(NVL(CD.ZIPCODE, ' '), 60, ' ') AS AddressLine5,
      TO_CHAR(CD.CREATE_DATE, 'YYYYMMDDHH24MISS') AS DateCreated,
      CASE WHEN CD.VOID_DATE IS NULL THEN ' ' ELSE TO_CHAR(CD.VOID_DATE, 'YYYYMMDDHH24MISS') END AS DateVoided
      INTO PaymentBatchNumber, CurrencyCode, TransactionCode, Transit_BranchNumber, BankAccountNumber, ChequeNumber, 
      ChequeAmount, ClientReference, IssueDate, PayeeName1, AddressLine1, AddressLine2, AddressLine4, AddressLine5,
      DateCreated, DateVoided
      FROM BANK_ACCOUNT BA 
      INNER JOIN CASH_DISBURSEMENT CD ON BA.ID = CD.BANK_ACCOUNT_ID 
      WHERE BA.ACCOUNT_NO IS NOT NULL AND CD.CHECK_NO &gt; 0 AND CD.STATUS != 'X' AND CD.AMOUNT != 0 AND ((TO_NUMBER(TO_CHAR(CD.CREATE_DATE, 'YYYYMMDDHH24MISS')) || IN_DATE_OPERATOR || IN_DATE) OR 
      (CASE WHEN CD.VOID_DATE IS NULL THEN 0 ELSE TO_NUMBER(TO_CHAR(CD.VOID_DATE, 'YYYYMMDDHH24MISS')) END || IN_DATE_OPERATOR || IN_DATE)) 
      ORDER BY BA.BRANCH_ID, BA.ACCOUNT_NO;
  END RF_SP_STFCA_PositivePay;
  END RF_PKG_STFCA_PositivePay;
  </code></pre>
  
  <p>And I get the following error when entering this into SQL plus:</p>
  
  <p>invalid relational operator</p>
  
  <p>What I'm trying to do: I have this stored procedure that returns a secordset to my asp.net application using the REF CURSOR. I give it 2 input parameters. 1 is a date (IN_DATE) and 1 is an operator (IN_DATE_OPERATOR). The program works if "|| IN_DATE_OPERATOR ||" is replaces with either = or >= just the way I want it to work. The problem is based on what happens in the .Net application I want the operater it uses in the where clause to be either ">=" or "=" and I wont know which until runtime.</p>
  
  <p>I know I'm doing this wrong but I don't know how to get oracle to reconize that IN_DATE_OPERATOR is a relational operator. I am open to other methods to have a dynamic operator (i tried CASE WHEN IN_DATE_OPERATOR = '=' THEN '=' ELSE '>=' END to no avail too) but I don't want to create a whole seperate stored procedure I will have to maintin in addition to this or a completely dynamic where clause. My ideal solution would make the least amount of changes to this query as possible. Any suggestions?</p>
  
  <p>Edit: ok I've edited my query do be the following:</p>
  
  <pre><code>CREATE OR REPLACE PACKAGE BODY RF_PKG_STFCA_PositivePay
  AS
  PROCEDURE RF_SP_STFCA_PositivePay(IN_DATE IN NUMBER, IN_DATE_OPERATOR IN VARCHAR2, OUT_DATA OUT CUR_DATA)
  IS
  /* this procedure returns a Ref Cursor with all the requested parameters
  calling the stored procedure from an asp page (and anywhere else)
  does not require posting a predefined number of records */
      SQL_Statement       VARCHAR2(8000);
  BEGIN
      SQL_Statement := 'SELECT LPAD(NVL(CD.PAYMENT_BATCH_NO, ''0''), 4, ''0'') AS PaymentBatchNumber, ' ||
      ' SUBSTR(NVL(CD.CURRENCY_ID, '' ''), 1, 1) AS CurrencyCode, ' ||
      ' NVL(CD.STATUS, '' '') AS TransactionCode, ' ||
      ' LPAD(NVL(BA.BRANCH_ID, ''0''), 5, ''0'') AS Transit_BranchNumber, ' ||
      ' LPAD(NVL(BA.ACCOUNT_NO, ''0''), 7, ''0'') AS BankAccountNumber, ' ||
      ' LPAD(NVL(CD.CHECK_NO, ''0'') , 8, ''0'') AS ChequeNumber, ' ||
      ' LPAD(TO_CHAR(NVL(CD.AMOUNT, 0)), 10, ''0'') AS ChequeAmount, ' ||
      ' LPAD(NVL(CD.CONTROL_NO, ''0''), 19, ''0'') AS ClientReference, ' ||
      ' TO_CHAR(NVL(CD.CHECK_DATE, LPAD('' '', 8, '' '')), ''YYYYMMDD'') AS IssueDate, ' ||
      ' RPAD(NVL(CD.NAME, '' ''), 60, '' '') AS PayeeName1, ' ||
      ' RPAD(NVL(CD.ADDR_1, '' ''), 60, '' '') AS AddressLine1, ' ||
      ' RPAD(NVL(CD.ADDR_2, '' ''), 60, '' '') AS AddressLine2, ' ||
      ' RPAD(NVL(CD.CITY, '''') || CASE WHEN CD.CITY IS NULL OR CD.STATE IS NULL THEN '' '' ELSE '', '' END || NVL(CD.STATE, ''''), 60, '' '') AS AddressLine4, ' ||
      ' RPAD(NVL(CD.ZIPCODE, '' ''), 60, '' '') AS AddressLine5, ' ||
      ' TO_CHAR(CD.CREATE_DATE, ''YYYYMMDDHH24MISS'') AS DateCreated, ' ||
      ' CASE WHEN CD.VOID_DATE IS NULL THEN '' '' ELSE TO_CHAR(CD.VOID_DATE, ''YYYYMMDDHH24MISS'') END AS DateVoided ' ||
      ' FROM BANK_ACCOUNT BA ' ||
      ' INNER JOIN CASH_DISBURSEMENT CD ON BA.ID = CD.BANK_ACCOUNT_ID ' ||
      ' WHERE BA.ACCOUNT_NO IS NOT NULL AND CD.CHECK_NO &gt; 0 AND CD.STATUS != ''X'' AND CD.AMOUNT != 0 ' ||
      ' AND ((TO_NUMBER(TO_CHAR(CD.CREATE_DATE, ''YYYYMMDDHH24MISS'')) ' || IN_DATE_OPERATOR || ' :1) ' ||
      ' OR (CASE WHEN CD.VOID_DATE IS NULL THEN 0 ELSE TO_NUMBER(TO_CHAR(CD.VOID_DATE, ''YYYYMMDDHH24MISS'')) END ' || IN_DATE_OPERATOR || ' :2)) ' ||
      ' ORDER BY BA.BRANCH_ID, BA.ACCOUNT_NO ';       
      OPEN OUT_DATA FOR SQL_Statement USING IN_DATE, IN_DATE;     
  END RF_SP_STFCA_PositivePay;
  END RF_PKG_STFCA_PositivePay;/
  </code></pre>
  
  <p>but I get the following error:</p>
  
  <p>LINE/COL ERROR</p>
  
  <hr>
  
  <p>32/3     PL/SQL: Statement ignored
  32/21    PLS-00382: expression is of wrong type</p>