12 Replies Latest reply: Jan 23, 2013 8:00 AM by 980086 RSS

    Calling a pl/sql function from oracle forms

    980086
      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çois Degrelle
          :result := package_name.function_name(...);
          Francois
          • 2. Re: Calling a pl/sql function from oracle forms
            980086
            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
              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çois Degrelle
                :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
                  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
                    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
                      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
                        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
                          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
                            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
                              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