CODEWITHSUNDEEP
oracle-databaseoracleforms
user3209083
user3209083

Reputation: 23

populating a datablock from a from clause query in oracle forms

I am trying populate the datablock like this:

PROCEDURE POP_BLOCK IS
    Q VARCHAR2(2000);
BEGIN
    Q:='(SELECT ROW_NUMBER () OVER (ORDER BY ROWNUM) SR, LN_DT, PER_MNTH, DEP_CR,
         FIRST_VALUE (LOAN_AMOUNT - DOWN_PAYMENT) OVER (ORDER BY ROWNUM)
       - SUM (PER_MNTH + DEP_CR) OVER (ORDER BY ROWNUM) BALANCE
  FROM (SELECT CAR_LOAN.REGCODE, ROW_NUMBER () OVER (ORDER BY ROWNUM) SR,
               LN_DT, PER_MNTH, CASE
                  WHEN ROWNUM >= 49
                     THEN 0
                  ELSE DEP_CR
               END DEP_CR, LOAN_AMOUNT, DOWN_PAYMENT
          FROM CAR_LOAN, TABLE (LOANINST (CAR_LOAN.LOAN_DATE)))
  )  WHERE REGCODE = '
    ||:LOAN_SYSTEM.REGCODE||')';
    GO_BLOCK('CAR_LOAN_INSTALLMENT');
   Clear_Block ;
SET_BLOCK_PROPERTY('CAR_LOAN_INSTALLMENT', QUERY_DATA_SOURCE_NAME,Q); 
EXECUTE_QUERY;
EXCEPTION WHEN OTHERS THEN
    MESSAGE(SQLCODE || SQLERRM);
    MESSAGE(SQLCODE || SQLERRM);
END;

And I am calling this procedure on new form instance and when I run the form it gives an error:

frm-41380 error - cannot set the blocks query data source

Anyone tell me what is the problem here and what should I have to do now?

Upvotes: 1

Views: 12285

Answers (2)

Paul
Paul

Reputation: 1

Block Name : CAR_LOAN_INSTALLMENT
Set the properties for this block as follows:

1. Database Data Block: Yes
   Query Allowed: Yes
   Query Data Source Type: FROM clause query

2. Keep the below Query in your Query Data Source Name of the Block  CAR_LOAN_INSTALLMENT.

( May be you are equating with null value.)
Use NVL(:LOAN_SYSTEM.REGCODE,0) --Assuming REGCODE as Number. 
If its Varchar2 then use NVAL(:LOAN_SYSTEM.REGCODE,'N/A')
----------------------------------------------------------------------

SELECT ROW_NUMBER () OVER (ORDER BY ROWNUM) SR, LN_DT, PER_MNTH, DEP_CR,
         FIRST_VALUE (LOAN_AMOUNT - DOWN_PAYMENT) OVER (ORDER BY ROWNUM)
       - SUM (PER_MNTH + DEP_CR) OVER (ORDER BY ROWNUM) BALANCE
  FROM (SELECT CAR_LOAN.REGCODE, ROW_NUMBER () OVER (ORDER BY ROWNUM) SR,
               LN_DT, PER_MNTH, CASE
                  WHEN ROWNUM >= 49
                     THEN 0
                  ELSE DEP_CR
               END DEP_CR, LOAN_AMOUNT, DOWN_PAYMENT
          FROM CAR_LOAN, TABLE (LOANINST (CAR_LOAN.LOAN_DATE)))

Note: Check the query whether it returns any result as expected.

3. Default Where caluse of Block Property keep it as follows:

    REGCODE = NVL(:LOAN_SYSTEM.REGCODE,0)

4. Write a trigger to execute Query

   GO_BLOCK('CAR_LOAN_INSTALLMENT');
   Clear_Block;
   EXECUTE_QUERY;

Note: Also Check the Table Function/Pipelined Function used in the Query (TABLE (LOANINST (CAR_LOAN.LOAN_DATE)).

Upvotes: 0

Girish A.
Girish A.

Reputation: 46

I too had a similar issue. Pls make sure that CAR_LOAN_INSTALLMENT data block has following properties (which had helped me resolve the issue):

Database Data Block: Yes
Query Allowed: Yes
Query Data Source Type: FROM clause query
Query Data Source Name: Put a table / view name or a SELECT query

Presume REGCODE is a NUMBER and not VARCHAR2. If it's latter, make sure to enclose it in single-quotes in the dynamic query (i.e. Q above).

Regards,
Girish

Upvotes: 1

Related Questions