Calling a function from Oracle forms

Question

I am using Oracle Forms 6i with Oracle db 10g on a Windows machine, in a form, when i click on a button, the trigger WHEN-BUTTON-PRESSED is launched calling a function of a package in the database, Here is the PL/SQL code for both the trigger and the function:

The function:

Function test_function (code Varchar2) RETURN Varchar2 IS
BEGIN
    if code IN ('Y') then
        return('Yes');
    else 
        return('no');
    endif;
END;

The trigger:

DECLARE
    message_test varchar2(20);
BEGIN
    message_test := pkg_test.test_function('Y');
    message('the message is: ' || message_test);pause;
END;

My problem here is that it doesn't show anything, but when i write pkg_test.test_function('Y') in sql*plus or in SqlDevelopper it shows 'Yes' as a result,

Or when i change message_test := pkg_test.test_function('Y'); with select pkg_test.test_function('Y') into message_test from test; it works also.

what might be the cause of that?

PS: this is just a test code, the actual code is more complex than that.

Is your output blank? Or does it show "the message is: " and no message? Because if it's the first one, then your message() function isn't doing anything, but if it's the second one, the problem's with calling pkg_test.test_function(). — kfinity
@kfinity it's blank, there is no "the message is: ", the problem's with calling pkg_test.test_function(). — zenami
Hmm, maybe you're getting an error? You could change the end of the trigger to: EXCEPTION when others then message(dbms_utility.format_error_backtrace); END; to see what it is. — kfinity
@kfinity "message(dbms_utility.format_error_backtrace)" does not work in oracle forms! — zenami
@zenami then, try following in pkg_test.test_function (not in trigger): exception when others then begin rollback; raise_application_error(-20710,'My problem is '||dbms_utility.format_error_stack||dbms_utility.format_error_backtrace||'['||dbms_utility.port_string||']'); end; By the way, why word pause; stands there ? — Barbaros Özhan

Jignesh Jignesh · Accepted Answer · 2017-08-25T15:31:24

If it is just test, Can you try show alert () function ?

DECLARE
    message_test varchar2(20);
    alert_resp number;
BEGIN
    message_test := pkg_test.test_function('Y');
    set_alert_property('ALERT_OK','the message is: ' || message_test);
    alert_resp := show_alert('ALERT_OK');
END;

Make sure that you create alert ALERT_OK in your forms alerts.

Calling a function from Oracle forms

1 Answers