This discussion is archived
11 Replies Latest reply: Feb 15, 2012 1:37 AM by apex RSS

Use Dynamic Actions with pl/sql calling a javascript alert function

637224 Newbie
Currently Being Moderated
Hi,

I'm using Apex 4.0.1.00.03 with IE7.

The problem I have is:

I'm converting a Oracle Forms application which has a lot of business logic in it. In the forms app it's doing a pl/sql function, which based on a If/else condition calls an alert popup box, which displays an alert message. It calls pl/sql functions, which return vlaues to the IF statement.

Example pl/sql code:

If check_records() > 0 Then
alert box message
ElsIf TypeA_record Then
alert box message
Else
alert box message
End If;

I need to replicate this functionality in Apex 4. I've attempted to create a Dynamic Action on a page item, using a pl/sql function, however, when I call javascript popup code, it does not popup an alert box. I need the pl/sql to run when the page item changes, not when the page is submitted.

The code I've used for testing the pl/sql code, which doesn't work in Dynamic Actions>pl/sql is:

Begin
HTP.p ('<script type="text/javascript">');
HTP.p ('alert(''Today is executing javascript code!'');'
);
HTP.p ('</script>');
End;

I need to create a pl/sql function that can do alert boxes based on an IF condition.

Could someone point me in the right direction? Is using Dynamic Actions the best way forward? I need to trigger on a page item changing value.
  • 1. Re: Use Dynamic Actions with pl/sql calling a javascript alert function
    sddc Newbie
    Currently Being Moderated
    Hello,

    One way to do this is to use ajax in your javascript code. Your pl/sql function called with ajax in javacript has to return a message code or the message text depending on the alert you want to display in javascript.
    Once you get the message code or the message text returned by your pl/sql function, you can display in javacript the alert depending on that message code or directly the message text.

    Regards
  • 2. Re: Use Dynamic Actions with pl/sql calling a javascript alert function
    637224 Newbie
    Currently Being Moderated
    Hi,

    Could you point me in the right direction to an example for this?
  • 3. Re: Use Dynamic Actions with pl/sql calling a javascript alert function
    sddc Newbie
    Currently Being Moderated
    1. On your page, create a new AJAX Callbacks
    Name: my_ajax_process
    Point: On Demand - Run this process when requested by AJAX
    Enter PL/SQL Page Process: (p1_item1 and p1_item2 are items on your page and their values are stored in apex_application.g_x01 and apex_application.g_x02)

    begin
    owa_util.mime_header('text/xml', FALSE );
    htp.p('Cache-Control: no-cache');
    htp.p('Pragma: no-cache');
    owa_util.http_header_close;

    If apex_application.g_x01=... and apex_application.g_x02=... Then
    htp.prn('1');
    ElsIf apex_application.g_x01... Then
    htp.prn('2');
    Else
    htp.prn('3');
    End If;
    end;


    '...' must be replaced by your conditions

    2. In HTML header on your page, write the following code:

    <script language="JavaScript" type="text/javascript">
    <!--

    function run_my_ajax_process(){
    var gReturn = null;
    var get = null;
    get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=my_ajax_process',$v('pFlowStepId'));
    get.addParam('x01',$v('P1_ITEM1'));
    get.addParam('x02',$v('P1_ITEM2'));
    gReturn = get.get();
    get = null;
    if (gReturn){
    if (gReturn == '1'){
    alert('message 1');
    }
    else if (gReturn == '2') {
    alert('message 2');
    }
    else {
    alert('message 3');
    }
    }
    }

    3. AJAX function must be called on change of P1_ITEM1 and P1_ITEM2:
    Create a dynamic action
    Event: Change
    Selection Type: Item(s)
    Item(s): P1_ITEM1,P1_ITEM2
    True Actions:
    Action: Execute JavaScript Code
    Code: run_my_ajax_process();



    It is just an example and code must be modified depending on what you want to do. But with this example, it show you how to display javascript alert depending on your pl/sql code.


    Regards
  • 4. Re: Use Dynamic Actions with pl/sql calling a javascript alert function
    637224 Newbie
    Currently Being Moderated
    Hi,

    Thanks for the example. I know a application process must be created for the pl/sql. In your code example, how do you pass a parameter to this pl/sql process?
  • 5. Re: Use Dynamic Actions with pl/sql calling a javascript alert function
    sddc Newbie
    Currently Being Moderated
    Parameters are passed when calling get.addParam('x01',...) function in the javascript code.
    Then you get this parameter by using apex_application.g_x01 in your pl/sql code.

    I hope that what I write is clear, English is not my native language.
  • 6. Re: Use Dynamic Actions with pl/sql calling a javascript alert function
    637224 Newbie
    Currently Being Moderated
    Hi,

    No you are making sense, however I can't get it to work. This is what I have done(this is just an example):

    1. Created an Ajax Callbacks process called my_ajax_process
    In there I have put:

    begin
    owa_util.mime_header('text/xml', FALSE );
    htp.p('Cache-Control: no-cache');
    htp.p('Pragma: no-cache');
    owa_util.http_header_close;

    If apex_application.g_x01 = 'OTHER' Then
    htp.prn('1');
    Else
    htp.prn('3');
    End If;
    end;

    I'm tested for 'OTHER' in the page item

    2. In page header I have put:

    <script type="text/javascript">
    function run_my_ajax_process(){
    var gReturn = null;
    var get = null;
    get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=my_ajax_process',$v('pFlowStepId'));
    get.addParam('x01',$v('P105_PREP_TYPE'));
    gReturn = get.get();
    get = null;
    if (gReturn){
    if (gReturn == '1'){
    alert('message 1');
    }
    else if (gReturn == '2') {
    alert('message 2');
    }
    else {
    alert('message 3');
    }
    }
    }

    </script>

    3.Created a Dynamic Action:
    Change on the page item using javascript, like you mentioned:

    run_my_ajax_process();


    However, I can't get it to work. Where am I going wrong?
  • 7. Re: Use Dynamic Actions with pl/sql calling a javascript alert function
    sddc Newbie
    Currently Being Moderated
    Do you have an error message somewhere? in javascript for example?
  • 8. Re: Use Dynamic Actions with pl/sql calling a javascript alert function
    637224 Newbie
    Currently Being Moderated
    I've created an on apex.oracle.

    Workspace: hda
    Username: demo
    pword: demo

    Go to: Apex test

    I get message 3 for all repsonses. If I select 'OTHER' from the lov, it should show message 1.

    Can you tell me where I'm going wrong?
  • 9. Re: Use Dynamic Actions with pl/sql calling a javascript alert function
    sddc Newbie
    Currently Being Moderated
    Works fine right now.
    In the javascript code I changed:

    get.addParam('x01',$v('P105_PREP_TYPE'));

    by

    get.addParam('x01',$v('P1_PREP_TYPE'));

    P1_PREP_TYPE is the correct name of the item.
    Now when I select OTHER, message 1 displays.

    Regards
  • 10. Re: Use Dynamic Actions with pl/sql calling a javascript alert function
    637224 Newbie
    Currently Being Moderated
    Thanks for your help. I still need to call some stored procedures from the page process, but I'll have a look at that later. As you've gone out your way to help, I've given you a correct answer.

    Many Thanks
  • 11. Re: Use Dynamic Actions with pl/sql calling a javascript alert function
    apex Newbie
    Currently Being Moderated
    Hi,

    i am trying to acccess your workspace to see the code and i am not able to access.

    Please reset and let me know the detail.

    it will helpful for me.

Legend

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