This discussion is archived
9 Replies Latest reply: Jan 18, 2013 8:13 AM by HamidHelal RSS

Plsql code - error

Rdk Newbie
Currently Being Moderated
Dear sir,

I am using forms 6i and oracle 9i.

I have 2 blocks

Master Block_a

invoice_no - database item
invoice_date ( not a database column )
Search button

Details Block_b

invoice_no - database item
invoice_date - database item

My master and details relation is invoice_no and invoice_date

when button pressed trigger in search button is

declare
v_where_1 varchar2(10);
v_where_2 varchar2(10);
begin
v_Where_1 := ' invoice = '||:block_a.invoive_no;
v_where_2 := ' invoice_date = ' ||:block_a.invoive_date;
set_block_property('block_b', default_where, v_where_1|| 'and' || v_where_2);
go_block('block_b');----detailed block
execute_query;
end;

after pressing search button it clear invoice_date from master block

and displaying error

Frm-40735 when button trigger raise unhandled exception ORA-06502

Kindly give help me solve this problem and also suggest me if there is any other alternate method.

Regards
rdk
  • 1. Re: Plsql code - error
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    Hello,

    Most of the times that errors raises when you try to store more characters than the declared variable can handle, or maybe you have wrong data in a string you want to convert to number.
    Check that out.

    Francois
  • 2. Re: Plsql code - error
    CraigB Guru
    Currently Being Moderated
    Francios is right. Take a look at your code:
    declare
    v_where_1 varchar2(10);
    v_where_2 varchar2(10);
    begin
    v_Where_1 := ' invoice = '||:block_a.invoive_no;
    v_where_2 := ' invoice_date = ' ||:block_a.invoive_date;
    Your problem is that your V_WHERE_? variables are too small for your value assignment right from the start. V_WHERE_1's value is 11 characters without the BLOCK_A.INVOIVE_NO and V_WHERE_2 is 16 characters without the BLOCK_A.INVOIVE_DATE.

    You need to size your variables bigger.

    Craig...
  • 3. Re: Plsql code - error
    HamidHelal Guru
    Currently Being Moderated
    Rdk wrote:
    Dear sir,

    I am using forms 6i and oracle 9i.

    I have 2 blocks

    Master Block_a

    invoice_no - database item
    invoice_date ( not a database column )
    Search button

    Details Block_b

    invoice_no - database item
    invoice_date - database item

    My master and details relation is invoice_no and invoice_date

    when button pressed trigger in search button is

    declare
    v_where_1 varchar2(10);
    v_where_2 varchar2(10);
    begin
    v_Where_1 := ' invoice = '||:block_a.invoive_no;
    v_where_2 := ' invoice_date = ' ||:block_a.invoive_date;
    set_block_property('block_b', default_where, v_where_1|| 'and' || v_where_2);
    go_block('block_b');----detailed block
    execute_query;
    end;

    after pressing search button it clear invoice_date from master block

    and displaying error

    Frm-40735 when button trigger raise unhandled exception ORA-06502

    Kindly give help me solve this problem and also suggest me if there is any other alternate method.
    rdk,
    try this
    declare
    v_where_1 varchar2(200);
    v_where_2 varchar2(200);
    QT VARCHAR2(10) :='''';
    begin
    v_Where_1 := ' invoice = '||QT||:block_a.invoive_no||QT; --- IF INVOICE_NO DATA TYPE IS CHAR/VARCHAR2 
    v_where_2 := ' invoice_date = ' ||QT||:block_a.invoive_date||QT;-- IF INVOICE_DATE DATA TYPE IS DATE
    
    go_block('block_b');----detailed block
    set_block_property('block_b', default_where, v_where_1|| ' and ' || v_where_2);
    execute_query;
    end;
    Hope this works....


    Hamid

    If someone's response is helpful or correct, please mark it accordingly.*
  • 4. Re: Plsql code - error
    Rdk Newbie
    Currently Being Moderated
    Thanks for you reply

    I have checked both

    invoice_no - char (10)
    invoice_date - date (11)
    I have used format mask for invoice_date- DD-MM-YYYY

    Increased both variables to varchar2(16)

    v_where1 varchar2(16);
    v_where1 varchar2(16);

    Still i am getting same error

    regards
    rdk
  • 5. Re: Plsql code - error
    HamidHelal Guru
    Currently Being Moderated
    Did you test my code ?

    Hamid
  • 6. Re: Plsql code - error
    Rdk Newbie
    Currently Being Moderated
    Yes Mr.hamidHelal

    I tested your code master block record is displaying

    but details block is not showing any record.

    error-Frm-41009 function key not allowed

    master block invoice_date is empty after retriving the data

    regards
    rdk
  • 7. Re: Plsql code - error
    HamidHelal Guru
    Currently Being Moderated
    Give the Invoice_no and Invoice_date value and try. then let me know what you got.

    Hamid
  • 8. Re: Plsql code - error
    Rdk Newbie
    Currently Being Moderated
    Thanks mr francois,craigb,Hamidhelal for your help

    it is ok now

    How restore my invoice_date (non-database item ) invoice_date from block_a after execute_query

    regards
    rdk
  • 9. Re: Plsql code - error
    HamidHelal Guru
    Currently Being Moderated
    Rdk wrote:
    Thanks mr francois,craigb,Hamidhelal for your help

    it is ok now

    How restore my invoice_date (non-database item ) invoice_date from block_a after execute_query
    Hi, rdk

    2 way.
    1. After execute query re-assign the value in the item.
    2. delete the relation between block_a and Block_b.


    Hope this works...


    Hamid



    If someone's response is helpful or correct, please mark it accordingly.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points