0

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

Improve this question
12
  • 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. Commented Apr 4, 2012 at 20:04
  • Sorry, ' || IN_DATE_OPERATOR || ' IN_DATE) ' should read: ' || IN_DATE_OPERATOR || ' ' || IN_DATE || ' and so on Commented Apr 4, 2012 at 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). Commented Apr 4, 2012 at 20:05
  • I edited my edit to reflect your advice but i still get the same error Commented Apr 4, 2012 at 20:20
  • 1
    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. Commented Apr 4, 2012 at 20:37

1 Answer 1

Reset to default
1

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

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 - 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.
What does ":=" do that "=" does not do?
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 - 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.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.