Dynamic Relational operator in a where clause is Invalid (PL/SQL Oracle) - 【StackMirror】|stored-procedures|dynamic|oracle10g|operators|where-clause

I am using Oracle 10g and I have the following stored procedure:

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 > 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;

And I get the following error when entering this into SQL plus:

invalid relational operator

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.

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?

Edit: ok I've edited my query do be the following:

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 > 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;/

but I get the following error:

LINE/COL ERROR

32/3 PL/SQL: Statement ignored 32/21 PLS-00382: expression is of wrong type

2012-04-04 17:24
by Adamantine

In your edit, you're adding the string IN_DATE. Notice that in my example, I'm replacing that with bind variables (:1 and :2) and then passing the IN_DATE in as a bind variable value when you open the cursor. The parameter IN_DATE is not visible to the dynamic SQL statement that you're building. While you're debugging, it's probably a good idea to add a dbms_output.put_line( sql_statement ) before the OPEN. That will print out the SQL statement that you're creating-- you can then either visually inspect the string for errors or run it manually to get better errors - Justin Cave 2012-04-04 20:04

Sorry, ' || INDATEOPERATOR || ' INDATE) ' should read: ' || INDATEOPERATOR || ' ' || INDATE || ' and so o - Adamantine 2012-04-04 20:05

Also, you don't want to pass IN_DATE_OPERATOR in as a bind variable. That actually needs to be part of the SQL statement you're building. It looks like you're building the SQL statement correctly, you'd just need the USING to specify IN_DATE twice (assuming you replace IN_DATE when you're building the SQL statement with bind variable placeholders) - Justin Cave 2012-04-04 20:05

I edited my edit to reflect your advice but i still get the same erro - Adamantine 2012-04-04 20:20

Don't put the bind variable placeholders in quotes. You don't want a semicolon at the end of the SQL statement that you're building. But I suspect you need to go down the dbms_output path to see the SQL statement you're building to be able to start to debug where the string concatenation isn't doing the right thing - Justin Cave 2012-04-04 20:25

Your example had those things so they must have been typos I gues - Adamantine 2012-04-04 20:28

Yeah, I missed the extra quotes around one of the bind variable placeholders - Justin Cave 2012-04-04 20:31

well without the semicolon on the end of the SQL statement the error changes to: PLS-00103: Encountered the symbol "OPEN" when expecting one of the following: * & = - + ; < / > but with it the error is still: Statement Ignored. expression is of wrong type. I've updated the query in my OP to reflect the most current version - Adamantine 2012-04-04 20:33

Just to be clear, you want a semicolon outside of the string at the end of the assignment to the sql_statement variable. You do not want the semicolon to be part of the string you are creating. In my example, for example, there is a semicolon that terminates the assignement to l_sql_stmt but there is no semicolon in the string itself - Justin Cave 2012-04-04 20:37

I took the ; out of the statement string but I still get the same error : - Adamantine 2012-04-04 20:47

I've run the string building portion in an anonymous block in order to elimitate the possibility that it is a porblem with my string concateination. I copied and paste the result back into SQL plus and it ran as I expected (replacing the bind variable place holders with their actual values). Therefore the problem is deffinately with the open statement. I get the following error when i run the above procedure:

PL/SQL: Statement ignored PLS-00382: expression is of wrong type

this confirms that its the open statement because it breaks on line 32 which is the line of the open statemen - Adamantine 2012-04-05 14:07

I changed the SQLStatement and INDATE_OPERATOR to VARCHAR2 instead of NVARCHAR2 and it worked... lo - Adamantine 2012-04-05 20:06

You would need to dynamically assemble the SQL statement in a string and then use that string to open the cursor. You'll need something along the lines of the get_cur procedure below where you assemble the SQL statement in a local VARCHAR2 variable including the placeholders for the bind variables and then open the cursor using the SQL statement you assembled and the bind variable you passed in.

SQL> create or replace procedure get_cur( p_date in date, p_operator in varchar2, p_cur out sys_refcursor )
  2  as
  3    l_sql_stmt varchar2(1000);
  4  begin
  5    l_sql_stmt := 'select * from emp where hiredate ' || p_operator || ' :1';
  6    open p_cur for l_sql_stmt using p_date;
  7  end;
  8  /

Procedure created.

SQL> var rc refcursor;
SQL> exec get_cur( date '2001-01-01', '>=', :rc );

PL/SQL procedure successfully completed.

SQL> print rc;

no rows selected

SQL> exec get_cur( date '2001-01-01', '<=', :rc );

PL/SQL procedure successfully completed.

SQL> print rc;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        801
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1601        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1251        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2976
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1251       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2851
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2451
        10

      7788 SCOTT      ANALYST         7566 19-APR-87       3001
        20

      7839 KING       PRESIDENT            17-NOV-81       5001
        10


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1501          0
        30

      7876 ADAMS      CLERK           7788 23-MAY-87       1101
        20

      7900 JAMES      CLERK           7698 03-DEC-81        951
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81       3001
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1301
        10


14 rows selected.

My guess is that you want something like this (obviously, since I don't have your tables or types, I can't test that this actually compiles so you'll likely need to correct typos)

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 */
  l_sql_stmt VARCHAR2(4000);
BEGIN
  l_sql_stmt := q'[SELECT LPAD(NVL(CD.PAYMENT_BATCH_NO, '0'), 4, '0') AS PaymentBatchNumber, ]' ||
    q'[SUBSTR(NVL(CD.CURRENCY_ID, ' '), 1, 1) AS CurrencyCode, ]' ||
    q'[NVL(CD.STATUS, ' ') AS TransactionCode, ]' ||
    q'[LPAD(NVL(BA.BRANCH_ID, '0'), 5, '0') AS Transit_BranchNumber, ]' ||
    q'[LPAD(NVL(BA.ACCOUNT_NO, '0'), 7, '0') AS BankAccountNumber, ]' ||
    q'[LPAD(NVL(CD.CHECK_NO, '0') , 8, '0') AS ChequeNumber, ]' ||
    q'[LPAD(TO_CHAR(NVL(CD.AMOUNT, 0)), 10, '0') AS ChequeAmount, ]' ||
    q'[LPAD(NVL(CD.CONTROL_NO, '0'), 19, '0') AS ClientReference, ]' ||
    q'[TO_CHAR(NVL(CD.CHECK_DATE, LPAD(' ', 8, ' ')), 'YYYYMMDD') AS IssueDate, ]' ||
    q'[RPAD(NVL(CD.NAME, ' '), 60, ' ') AS PayeeName1, ]' ||
    q'[RPAD(NVL(CD.ADDR_1, ' '), 60, ' ') AS AddressLine1, ]' ||
    q'[RPAD(NVL(CD.ADDR_2, ' '), 60, ' ') AS AddressLine2, ]' ||
    q'[RPAD(NVL(CD.CITY, '') || CASE WHEN CD.CITY IS NULL OR CD.STATE IS NULL THEN ' ' ELSE ', ' END || NVL(CD.STATE, ''), 60, ' ') AS AddressLine4, ]' ||
    q'[RPAD(NVL(CD.ZIPCODE, ' '), 60, ' ') AS AddressLine5, ]' ||
    q'[TO_CHAR(CD.CREATE_DATE, 'YYYYMMDDHH24MISS') AS DateCreated, ]' ||
    q'[CASE WHEN CD.VOID_DATE IS NULL THEN ' ' ELSE TO_CHAR(CD.VOID_DATE, 'YYYYMMDDHH24MISS') END AS DateVoided ]' ||
    q'[FROM BANK_ACCOUNT BA  ]' ||
    q'[INNER JOIN CASH_DISBURSEMENT CD ON BA.ID = CD.BANK_ACCOUNT_ID  ]' ||
    q'[WHERE BA.ACCOUNT_NO IS NOT NULL AND CD.CHECK_NO > 0  ]' ||
    q'[AND CD.STATUS != 'X'  ]' ||
    q'[AND CD.AMOUNT != 0  ]' ||
    q'[AND ((TO_NUMBER(TO_CHAR(CD.CREATE_DATE, 'YYYYMMDDHH24MISS'))]' || IN_DATE_OPERATOR || ':1') OR  ' ||
    q'[(CASE WHEN CD.VOID_DATE IS NULL THEN 0 ELSE TO_NUMBER(TO_CHAR(CD.VOID_DATE, 'YYYYMMDDHH24MISS')) END]' || IN_DATE_OPERATOR || ':2'))  ' ||
    q'[ORDER BY BA.BRANCH_ID, BA.ACCOUNT_NO ]';
  OPEN out_data
   FOR l_sql_stmt
   USING in_date, in_date;
END RF_SP_STFCA_PositivePay;
END RF_PKG_STFCA_PositivePay;

2012-04-04 17:32
by Justin Cave

How would this work with my current procedure? It looks like you are suggesting I convert the whole query into a Varchar2 and then use the open command on it? I apreciate the code you provided but it would be much more useful in a working example using the code I provided, including the local variables, output curser and so forth (theres a lot more at work here than a simple select statement) - Adamantine 2012-04-04 18:48

@Adamantine - Updated my answer with an attempt to build the string you'd need. There's a decent chance that I didn't build it completely correctly since I can't really test it - Justin Cave 2012-04-04 19:02

What does ":=" do that "=" does not do - Adamantine 2012-04-04 19:08

Oh, thanks for your valiant attempt :) 1 question though, what happened to the local variables in your example? Ie the: PaymentBatchNumber NVARCHAR2(4); CurrencyCode NVARCHAR2(3); etc. that was before the begin statement - Adamantine 2012-04-04 19:10

@Adamantine - You don't need local variables. You're opening a cursor, you're not fetching from the cursor. So you cannot have an INTO clause. That would be the case whether you were using dynamic SQL or not. := is the assignment operator - Justin Cave 2012-04-04 19:12

would the columns still have the custom alias' I provided when I put XXXX AS CurrencyCode for example? This is important because the .Net application loads the results into a datatable and I refer to the columns in the rows by these names - Adamantine 2012-04-04 19:15

@Adamantine - Yes, the aliases will be preserved (though the .Net application will not see the casing you provided unless you use case-sensitive alias names by enclosing every alias in double quotes) - Justin Cave 2012-04-04 19:20