Oracle forms cursor issue

Question

I am using Oracle Forms and facing issue in writing a cursor. My cursor is

   CURSOR ss
   IS
        SELECT pjno, bdate, PROJECT
          FROM billcrown
               LEFT JOIN bank_details ON billcrown.pjno = bank_details.pjno
         WHERE billcrown.bdate > '01-Jul-2017'
      GROUP BY billcrown.pjno
        HAVING SUM (billcrown.PAMT) <> NVL (SUM (bank_details.AMOUNT), 0);

   s   ss%ROWTYPE;

It's showing error on pining on left join. Error is (Encountered the symbol 'JOIN' when expecting one of the following. , ; ) It is working fine at SQL prompt. Please suggest.

Littlefoot Littlefoot · Accepted Answer · 2018-06-07T09:59:36

Which Forms version is it? If it complains on LEFT JOIN, that's probably 6i or something. Previous (very old) Forms versions' PL/SQL engine didn't quite follow database's PL/SQL engine so not everything, that worked in the database, worked in Forms as well.

Therefore, I'd suggest you to try with the old Oracle outer join operator, (+).

Furthermore, if BDATE column's datatype is DATE, you should use DATE values against it, not strings. '01-Jul-2017' is a string. DATE '2017-07-01' is date (literal).

Finally, saying that your code works fine in SQL*Plus - no, it is not. GROUP BY contains only c.pjno column, so bdate, PROJECT should be included in there as well (or you should rewrite that query).

Something like this:

   CURSOR ss
   IS
        SELECT c.pjno
          -- , bdate, PROJECT                 --> removed because of the GROUP BY clause
          FROM billcrown c, bank_details d
         WHERE c.pjno = d.pjno (+)            --> this
         and c.bdate > date '2017-07-01'      --> use dates, not strings!
      GROUP BY c.pjno
        HAVING SUM (c.PAMT) <> NVL (SUM (d.AMOUNT), 0);

Oracle forms cursor issue

1 Answers