6 Replies Latest reply on Oct 19, 2007 2:06 PM by Tomeo

    Block based on "From clause query" and substitution variable

    Tomeo
      Hi Folks,

      I have a "From clause query" block type, which is based on query that uses a substitution variable (:BLOCK.COLUMN) from previous block. When I run a query, then I obtain an error: ORA-01008: not all variables bound.

      My query looks like:
       select seq_no, prod_code, descr, curr_code,
             max(decode(sched_type_code,'ROLLOVER',amt,0)) OUTSTD_DRAW,
             max(decode(sched_type_code,'INTCHG',amt,decode(sched_type_code,'FEEREC',amt,0))) OUTSTD_INTEREST_FEE
      from
      (
          select r.fac_no, r.prod_code, f.descr, r.prod_seq_no seq_no, r.curr_code, r.sched_type_code,sum(nvl(rep_amt,0)) amt
            from repay_scheds r,
                 fac_prods f
           where r.fac_no = f.fac_no
             and r.prod_code = f.prd_code
             and r.prod_seq_no = f.seq_no
             and r.fac_no = :B2.FAC_NO
             and r.trans_ref_from is not null
             and r.status         <> 'P'
          group by r.fac_no, r.prod_code, f.descr, r.prod_seq_no, r.curr_code, r.sched_type_code 
      )
      group by seq_no, prod_code, descr, curr_code
      having max(decode(sched_type_code,'ROLLOVER',amt,0)) >0 or max(decode(sched_type_code,'INTCHG',amt,decode(sched_type_code,'FEEREC',amt,0))) > 0
      Once I replace that substitution variable in query condition with some exact test number then it works fine.
      select seq_no, prod_code, descr, curr_code,
             max(decode(sched_type_code,'ROLLOVER',amt,0)) OUTSTD_DRAW,
             max(decode(sched_type_code,'INTCHG',amt,decode(sched_type_code,'FEEREC',amt,0))) OUTSTD_INTEREST_FEE
      from
      (
          select r.fac_no, r.prod_code, f.descr, r.prod_seq_no seq_no, r.curr_code, r.sched_type_code,sum(nvl(rep_amt,0)) amt
            from repay_scheds r,
                 fac_prods f
           where r.fac_no = f.fac_no
             and r.prod_code = f.prd_code
             and r.prod_seq_no = f.seq_no
             and r.fac_no = 2012500
             and r.trans_ref_from is not null
             and r.status         <> 'P'
          group by r.fac_no, r.prod_code, f.descr, r.prod_seq_no, r.curr_code, r.sched_type_code 
      )
      group by seq_no, prod_code, descr, curr_code
      having max(decode(sched_type_code,'ROLLOVER',amt,0)) >0 or max(decode(sched_type_code,'INTCHG',amt,decode(sched_type_code,'FEEREC',amt,0))) > 0
      How can I use substitution variable within query for "From clause query" block type? Or any other way how to get the same result?

      Thanks for your reply.

      Tomas
        • 1. Re: Block based on "From clause query" and substitution variable
          Tomeo
          I have a solution:

          Before entering block I'm calling function, that populates my block:
          PROCEDURE POP_<<MY_BLOCK>>_BLOCK IS
            query_txt varchar2(2000);
          BEGIN
             query_txt := '(select seq_no, prod_code, descr, curr_code,
                                  max(decode(sched_type_code,''ROLLOVER'',amt,0)) OUTSTD_DRAW,
                                  max(decode(sched_type_code,''INTCHG'',amt,decode(sched_type_code,''FEEREC'',amt,0))) OUTSTD_INTEREST_FEE
                                                        from
                                                       (
                                                           select r.fac_no, r.prod_code, f.descr, r.prod_seq_no seq_no, r.curr_code, r.sched_type_code,sum(nvl(rep_amt,0)) amt
                                                             from repay_scheds r,
                                                                  fac_prods f
                                                            where r.fac_no = f.fac_no
                                                              and r.prod_code = f.prd_code
                                                              and r.prod_seq_no = f.seq_no
                                                              and r.fac_no = '||:B2.FAC_NO||'
                                                              and r.trans_ref_from is not null
                                                              and r.status         <> ''P''
                                                           group by r.fac_no, r.prod_code, f.descr, r.prod_seq_no, r.curr_code, r.sched_type_code 
                                                       )
                                                       group by seq_no, prod_code, descr, curr_code
                                                       having max(decode(sched_type_code,''ROLLOVER'',amt,0)) >0 or max(decode(sched_type_code,''INTCHG'',amt,decode(sched_type_code,''FEEREC'',amt,0))) > 0)';
             Go_Block('<<MY_BLOCK>>' );
             Clear_Block ;
             Set_Block_Property( '<<MY_BLOCK>>', QUERY_DATA_SOURCE_NAME, query_txt) ;
             -- populate my  block --
             Execute_Query ;
          END;
          Thanks,
          Tomas
          • 2. Re: Block based on "From clause query" and substitution variable
            221488
            A from clause query is basically a updateble view and it is sent to the database verbatim from Forms. Since the DB does not know anything about Forms substitution variables its not possible to use them there.
            Your solution is what I would have suggested.
            • 3. Re: Block based on "From clause query" and substitution variable
              Zlatko Sirotic
              Problem with this solution is that it don't use bind variable - database must (re)parse query for each different value of :B2.FAC_NO.

              One workaround is to use database function in from clause.
              For example:
              CREATE OR REPLACE PACKAGE from_clause_pkg IS 
                 PROCEDURE set_deptno (p_deptno dept.deptno%TYPE);
                 FUNCTION  get_deptno RETURN dept.deptno%TYPE;
              END;
              /
              CREATE OR REPLACE PACKAGE BODY from_clause_pkg IS
                 m_deptno dept.deptno%TYPE;
                 
                 PROCEDURE set_deptno (p_deptno dept.deptno%TYPE) IS
                 BEGIN
                    m_deptno := p_deptno;
                 END;

                 FUNCTION get_deptno RETURN dept.deptno%TYPE IS
                 BEGIN
                    RETURN m_deptno;
                 END;
              END;
              /
              From clause:
               
              (SELECT e.empno, e.ename, d.deptno, d.dname
                 FROM emp e, dept d
                WHERE e.deptno = d.deptno
                  AND d.deptno = from_clause_pkg.get_deptno
              )
              Regards,
              Zlatko Sirotic
              • 4. Re: Block based on "From clause query" and substitution variable
                Zlatko Sirotic
                Another way - using Application Context:
                CREATE OR REPLACE CONTEXT from_clause_context USING set_from_clause_context
                /
                CREATE OR REPLACE PROCEDURE set_from_clause_context (p_deptno dept.deptno%TYPE) IS
                BEGIN
                   DBMS_SESSION.SET_CONTEXT ('from_clause_context', 'deptno', p_deptno);
                END;
                /
                From clause:
                (SELECT e.empno, e.ename, d.deptno, d.dname
                   FROM emp e, dept d
                  WHERE e.deptno = d.deptno
                    AND d.deptno = SYS_CONTEXT ('from_clause_context', 'deptno')
                )
                Regards,
                Zlatko Sirotic
                • 5. Re: Block based on "From clause query" and substitution variable
                  Tony Garabedian
                  You cannot directly reference variables in a "From Clause Query" in the data block property, you'll get that error.

                  The best workaround for it is to set it in a trigger
                  like PRE-QUERY or WHEN-NEW-FORM/BLOCK-INSTANCE
                  using
                  SET_BLOCK_PROPERTY('BLOCK_NAME', QUERY_DATA_SOURCE_NAME, 'query text');

                  where query text is a VARCHAR2 variable initialized by your SQL Statement text which includes the variables.

                  This is described on metalink in Note:104771.1

                  It's a very simple workaround. Try it.


                  Tony

                  Message was edited by:
                  Tony Garabedian
                  • 6. Re: Block based on "From clause query" and substitution variable
                    Tomeo
                    Hi folks,

                    thanks a lot for your usefull comments.

                    Tomas