This discussion is archived
7 Replies Latest reply: Nov 12, 2012 8:20 AM by user12155310 RSS

Dynamic sql from Forms 6 with return value

fehers Newbie
Currently Being Moderated
Hi,

My goal is to run pl/sql code (functions) which return values and use the returned value in forms6.
To be more precise:
I put some little business logic into a table's column e.g some_function(in_parameter_1 number);
My app starts a cursor, reads the records and wants to execute some_function and catch it's return value.
Because the function's name is not the same every time I need to do it in dynamic way somehow.
I used recently form's built in exec_sql to run dml statements without returning values or fetching anything.
Please let me know how can I achieve this in simplest way.

thanks., Sandor
  • 1. Re: Dynamic sql from Forms 6 with return value
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    Hello,

    The easyest way is to create a stored function in the DB that uses the EXECUTE IMMEDIATE feature, and set the function return value.
    So that, all you have to do within Forms is to call that stored function.

    Francois
  • 2. Re: Dynamic sql from Forms 6 with return value
    user346369 Expert
    Currently Being Moderated
    Yes, Francois is correct. Exec_SQL is extremely cumbersome and slow, requiring lots of extra calls to the database to get any data back. A single call to a stored function (or a function in a database package), passing it the name of the function you want to run would be far easier.
  • 3. Re: Dynamic sql from Forms 6 with return value
    Christian Erlinger Guru
    Currently Being Moderated
    I put some little business logic into a table's column e.g some_function(in_parameter_1 number);
    My app starts a cursor, reads the records and wants to execute some_function and catch it's return value.
    Because the function's name is not the same every time I need to do it in dynamic way somehow.
    Just to be annoying, but exactly how often do those functions change or are some other functions added to this table? If the answer isn't "every day" then I wouldn't go down the dynamic SQL route. I'll tell you why:

    1.) As your functions most certainly will have different parameters you'll have to implement a dynamic bind algorithm with dbms_sql as otherwise you'll be SQL Injection prone AND most likely will fill up the shared pool of the database with somewhat similar statements making the whole database suffer of a trashed shared pool. This will result in a bunch of hard-to-read code.
    2.) If someone chooses to change the signature of some_function (add a parameter) and forgets to change it in your table as well or analyze your whole code and remove it as it seems to be dead code you won't detect that until it is too late.
    3.) Have fun debugging your procedures
    4.) All in all to me this sounds like an unperformant, hard to maintain new invention of stored procedures

    If you are lazy like me keep your table with your procedure calls, but instead of executing your procedures dynamically everytime write them out in a textfile once, paste the code in a stored procedure and call that stored procedure instead of the dynamic thingie.
    If you are using dynamic SQL just because you are to lazy to write down all the somewhat similar code then write a code-generating procedure which does that for you. Trust me,maintenance of static code is a lot easier as it is dynamic code :)

    cheers
  • 4. Re: Dynamic sql from Forms 6 with return value
    fehers Newbie
    Currently Being Moderated
    christian erlinger wrote:
    I put some little business logic into a table's column e.g some_function(in_parameter_1 number);
    My app starts a cursor, reads the records and wants to execute some_function and catch it's return value.
    Because the function's name is not the same every time I need to do it in dynamic way somehow.
    Just to be annoying, but exactly how often do those functions change or are some other functions added to this table? If the answer isn't "every day" then I wouldn't go down the dynamic SQL route. I'll tell you why:
    This is not an everyday task.
    >
    1.) As your functions most certainly will have different parameters you'll have to implement a dynamic bind algorithm with dbms_sql as otherwise you'll be SQL Injection prone AND most likely will fill up the shared pool of the database with somewhat similar statements making the whole database suffer of a trashed shared pool. This will result in a bunch of hard-to-read code.
    2.) If someone chooses to change the signature of some_function (add a parameter) and forgets to change it in your table as well or analyze your whole code and remove it as it seems to be dead code you won't detect that until it is too late.
    This won't be happened.
    3.) Have fun debugging your procedures
    It's not impossible but debugging stored procedures called forms would be a nightmare.
    4.) All in all to me this sounds like an unperformant, hard to maintain new invention of stored procedures

    If you are lazy like me keep your table with your procedure calls, but instead of executing your procedures dynamically everytime write them out in a textfile once, paste the code in a stored procedure and call that stored procedure instead of the dynamic thingie.
    If you are using dynamic SQL just because you are to lazy to write down all the somewhat similar code then write a code-generating procedure which does that for you. Trust me,maintenance of static code is a lot easier as it is dynamic code :)
    I figured out the solution which perfectly suits to my needs.


    1. I created a stored func like this:

    FUNCTION process(in_text varchar2) RETURN NUMBER IS
    result NUMBER;
    BEGIN
    EXECUTE IMMEDIATE in_text USING OUT result;
    RETURN result;
    END;

    2. I call this from Forms with this little sql block.

    declare
    w_sum_ora number;
    w_osszeg number:=0;
    begin
    begin
    select sum(napora) into w_sum_ora from bbdetail1 where bbjog_ervev=:ERVEV and feldho=:FELDHO and bbst_torzsszam=:TORZSSZAM
    and bbjog_jogcim in (50,51);
    exception when no_data_found then w_sum_ora:=0;
    end;
    if nvl(w_sum_ora,0)=:GL201*:PV240 then
    :return:=:FT_OSSZEG;
    else
    w_osszeg:= ROUND((:FT_OSSZEG/(:GL201*:PV240))*NVL(W_SUM_ORA,0),0);
    end if;

    :return:=w_osszeg;
    end;

    This gives back a number as result which is perfect for me.
    The bottom line the code will be very small more sql block rather than complex procedures. So I'm happy now :).

    And a big thank you to everyone!

    Regards., Sandor



    >
    cheers
  • 5. Re: Dynamic sql from Forms 6 with return value
    user346369 Expert
    Currently Being Moderated
    I am glad you are happy.

    But I do not see any calls to your stored function from your block of sql code.

    So I am puzzled.
  • 6. Re: Dynamic sql from Forms 6 with return value
    fehers Newbie
    Currently Being Moderated
    Steve Cosner wrote:
    I am glad you are happy.

    But I do not see any calls to your stored function from your block of sql code.

    So I am puzzled.
    1. [ param table with sql block]
    2. forms procedure processing param table, gets formula column, makes some variable binding (
    eg. replace(:PV240,something) then
         calling stored function with w_sql_text has only :return variable in it.
         
         declare
         cursor c1 is select * from myparamtable;
         w_ret_val number;
         w_sql_text varchar2(4000);
         
         begin
         for r1 in c1 loop
    w_sql_text:=replace(r1.formula,':PV240','8');
                   w_sql_text:=replace(r1.formula,':...','XXX');
              w_ret_val:=process(w_sql_text);     
         insert into some_table values (w_ret_val);          
              end loop;
         end;

    I hope it helps clarify the solution.

    thnx., Sandor
  • 7. Re: Dynamic sql from Forms 6 with return value
    user12155310 Newbie
    Currently Being Moderated
    Hi,

    you can try a little [dynamic sql demo |http://friedhold-matz.blogspot.de/2012/03/forms11g-procedure-block-mit.html]
    defined in a table also running in Forms 6i.

    Hope it helps
    Friedhold

Legend

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