This discussion is archived
12 Replies Latest reply: Jan 23, 2013 6:00 AM by 980086 RSS

Calling a pl/sql function from oracle forms

980086 Newbie
Currently Being Moderated
I have written a pl/sql function in a package.
package name- sup
function name - func
I have written a when-button-pressed trigger .from here I want to call this function.
How can I do that?
Please help..
  • 1. Re: Calling a pl/sql function from oracle forms
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    :result := package_name.function_name(...);
    Francois
  • 2. Re: Calling a pl/sql function from oracle forms
    980086 Newbie
    Currently Being Moderated
    Thanks a lot for your help....
    But one small question ---
    I want to elaborate the situation in a nut shell--
    I have 2 blocks-blk1 & land..depending upon values entered in blk1 , land layout will be populated from a table.In land block I have id,name & a check box for each row of name.I have a OK button in land block.user will check or uncheck for each name through checkbox.when user will hit OK,its when-button-click trigger will fire & that will update the table.the procedure to update the table I have written in sup.func function.I want to call this function from when-button-click trigger.
    NOW,
    what is :result? here...
    how shall I declare that...
    what value should be stored in :result?

    Please help me...
  • 3. Re: Calling a pl/sql function from oracle forms
    HamidHelal Guru
    Currently Being Moderated
    in this situation,
    package_name.function_name(...);
    hope this helps

    Hamid

    If someone's response is helpful or correct, please mark it accordingly.*
  • 4. Re: Calling a pl/sql function from oracle forms
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    :result is the variable that should store the function return value. What did you expect ?!
    Declare 
      v  number;
    Begin
      v := package.function(...);
    End;
    Francois
  • 5. Re: Calling a pl/sql function from oracle forms
    HamidHelal Guru
    Currently Being Moderated
    François Degrelle wrote:
    :result is the variable that should store the function return value. What did you expect ?!
    Declare 
    v  number;
    Begin
    v := package.function(...);
    End;
    Hi, Francois
    I apologize for my mistake. I just copied and paste. I should more careful.
    As it's a function and it will must return a value so a container is needed.

    *@977083*
    In your scenario, you need procedure to fulfill your requirement. and you can call it by
    package_name.procedure_name(...);
    hope this helps

    Hamid

    If someone's response is helpful or correct, please mark it accordingly.
  • 6. Re: Calling a pl/sql function from oracle forms
    980086 Newbie
    Currently Being Moderated
    Thanks a lot..
    User may select 20-30 check boxes for respective name & id...then at the end , user will hit OK...from here when-button-click will call a procedure....that will update/insert/delete a table data.The procedure I have written is:-

    DECLARE
    N_CNT Number(1);
    D_SYSDATE Constant date :=sysdate;
    Begin
    select count(1)
    into N_CNT
    from dbtable L
    where L.land_id = :BLK1.LAND_ID and
    L.Operatoer_id = :BLK1.Operatoer_id and
    L.suppl_id = :BLK1.suppl_id and
    L.suppl_prof_id = :BLK1.suppl_prof_id;
    if ((N_CNT = 0) and (:LANDLISTE.state = 'A')) then
    insert into dbtable
    (
    LAND_ID,
    STATE,
    suppl_prof_id,
    suppl_id,
    OPERATOER_ID,
    SIDST_OPD_DATO,
    SIDST_OPD_INIT
    )
    values
    (
    :BLK1.LAND_ID,
    'Y',
    :BLK1.suppl_prof_id,
    :BLK1.suppl_id,
    :BLK1.Operatoer_id,
    D_SYSDATE,
    :BLK1.SIDST_OPD_INIT
    );

    elseif
    ((N_CNT>0 )and (:LANDLISTE.state = 'A')) then

    update dbtable L
    set L.SIDST_OPD_DATO = D_SYSDATE,
    L.SIDST_OPD_INIT = :BLK1.SIDST_OPD_INIT
    where L.land_id = :BLK1.LAND_ID and
    L.Operatoer_id = :BLK1.Operatoer_id and
    L.suppl_id = :BLK1.suppl_id and
    L.suppl_prof_id = :BLK1.suppl_prof_id;

    elseif ((N_CNT>0 ) and (:LANDLISTE.state = 'D')) then

    delete from dbtable L
    where L.land_id = :BLK1.LAND_ID and
    L.Operatoer_id = :BLK1.Operatoer_id and
    L.suppl_id = :BLK1.suppl_id and
    L.suppl_prof_id = :BLK1.suppl_prof_id;



    end if;
    end;

    Will it be able to load multiple data(20-30 at a time) to the table?
    Should I write anything to commit the data in the table?
    I am very new to oracle forms...please help..
  • 7. Re: Calling a pl/sql function from oracle forms
    HamidHelal Guru
    Currently Being Moderated
    977083 wrote:
    Thanks a lot..
    User may select 20-30 check boxes for respective name & id...then at the end , user will hit OK...from here when-button-click will call a procedure....that will update/insert/delete a table data.
    Your code can work after check box checked, no need to wait for button press if you use u'r code at
    When-CheckBox-Changed trigger
    Other way if you want in your way,that you mention with the button and your code you can put your code in the button directly. you need to little adjust in your code, something like
    go_block('block_name');
    first_record;
    <.......put your code.........>
    And finally, in your scenario you need to pass value in to procedure and make change to make it workable.

    Should I write anything to commit the data in the table?
    Yes. you need to write
    commit; 
    I am very new to oracle forms...
    First go through with some basic over net or book and try to implement it.


    Hope this helps...

    Hamid


    If someone's response is helpful or correct, please mark it accordingly.
  • 8. Re: Calling a pl/sql function from oracle forms
    980086 Newbie
    Currently Being Moderated
    Thanks a lot to all....Now I am able to insert/update/delete data into/from the table as
    I am getting an error like frm-41076 ... no column name or table name or record group is mismatching...
    what could be root cause for this error?....please suggest..
  • 9. Re: Calling a pl/sql function from oracle forms
    HamidHelal Guru
    Currently Being Moderated
    977083 wrote:
    Thanks a lot to all....Now I am able to insert/update/delete data into/from the table as
    I am getting an error like frm-41076 ... no column name or table name or record group is mismatching...
    what could be root cause for this error?....please suggest..
    let us know the exact error message no a chunk

    check your column name and table name is ok and follow the exam when writing sql
    select abc.column1,abc.column2,ddd.column1.....
    from abc,ddd
    where abc.column1=ddd.column1
    ..........
    hope this helps


    Hamid

    If someone's response is helpful or correct, please mark it accordingly.*
  • 10. Re: Calling a pl/sql function from oracle forms
    980086 Newbie
    Currently Being Moderated
    I am sorry.....I have given a wrong record group name ...careless me....SORRY :(
    now my form is almost ready....
    a small fix is needed...for some values in text filed the next block is getting populated...but for some values the next block should not be populated rather should give an error defined by me like "1438:id is not there in the profile selected" ...
    but its throwing "frm-40010:form error message cannot read". please suggest what can I do?
  • 11. Re: Calling a pl/sql function from oracle forms
    HamidHelal Guru
    Currently Being Moderated
    a small fix is needed...for some values in text filed the next block is getting populated...but for some values the next block should not be populated rather should give an error defined by me like "1438:id is not there in the profile selected" ...
    You can write sql and count the exist record like bellow example
    select count(any_column_name) into a_variable
    from table
    where.......;
    
    if a_variable =0 then
    message ('your message');
    message ('your message'); 
    raise form_trigger_failure;
    else
    your code....
    end if;
    Hope this helps


    Hamid
  • 12. Re: Calling a pl/sql function from oracle forms
    980086 Newbie
    Currently Being Moderated
    thanks

Legend

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