Forum Stats

  • 3,782,440 Users
  • 2,254,645 Discussions
  • 7,880,078 Comments

Discussions

Categories

PL/SQL Procedure does not allow declarations for t.parameters ~ workaround?

Amba
Amba Member Posts: 36 Green Ribbon
edited Jul 12, 2020 5:42AM in APEX Discussions

Hello group

I am a PL/SQL novice and am trying to get up to speed with it

I seem to have hit a roadblock, however and there is nothing in the documentation that I can find about this... I have looked around this site for something similar..

I want to create a (stored) procedure that will call a WebSource, (so  that I can securely call from a Page. )

However, when I use the  documented example it has a 'declare' section, which APEX's SQL WORKBENCH does not allow.

There is also no "t_parameter" type allowed in the Parameters page in the wizard process for creating a stored procedure. 

Is this intentional or a bug? Or, What am I missing?  Is there a workaround to this?

I refer to :

https://docs.oracle.com/database/apex-18.1/AEAPI/EXECUTE_WEB_SOURCE-Procedure.htm#AEAPI-GUID-30272C98-EA7B-4220-A26B-63DFF9DFCD1F

declare

l_params apex_exec.t_parameters;

begin

  apex_exec.add_parameter( l_params, 'ENAME', :P2_ENAME );

  apex_exec.add_parameter( l_params, 'EMPNO', :P2_EMPNO );

  apex_exec.add_parameter( l_params, 'SAL', :P2_SAL );

  apex_exec.add_parameter( l_params, 'JOB', :P2_JOB );

  apex_exec.execute_web_source(

  p_module_static_id => 'ORDS_EMP',

  p_operation => 'POST',

  p_parameters => l_params );

  :P2_RESPONSE := apex_exec.get_parameter_clob(l_params,'RESPONSE');

end;

many thanks

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • jariola
    jariola Member Posts: 10,542 Silver Crown
    edited Jul 12, 2020 12:43AM Accepted Answer

    You can't use bind variables like :P2_ENAME, :P2_EMPNO... in procedures.

    To create procedure from SQL Commands, try something like below

    create or replace procedure xx_test_proc(  p_ename  in varchar2,  p_empno  in varchar2,  p_sal    in varchar2,  p_job    in varchar2,  p_result out nocopy varchar2)is  l_params apex_exec.t_parameters;begin  apex_exec.add_parameter( l_params, 'ENAME', p_ename );  apex_exec.add_parameter( l_params, 'EMPNO', p_empno );  apex_exec.add_parameter( l_params, 'SAL', p_sal );  apex_exec.add_parameter( l_params, 'JOB', p_job );  apex_exec.execute_web_source(    p_module_static_id => 'ORDS_EMP'    ,p_operation => 'POST'    ,p_parameters => l_params  );  p_result := apex_exec.get_parameter_clob(l_params,'RESPONSE');  end;/
    Amba
    · Share on TwitterShare on Facebook

Answers

  • jariola
    jariola Member Posts: 10,542 Silver Crown
    edited Jul 12, 2020 12:43AM Accepted Answer

    You can't use bind variables like :P2_ENAME, :P2_EMPNO... in procedures.

    To create procedure from SQL Commands, try something like below

    create or replace procedure xx_test_proc(  p_ename  in varchar2,  p_empno  in varchar2,  p_sal    in varchar2,  p_job    in varchar2,  p_result out nocopy varchar2)is  l_params apex_exec.t_parameters;begin  apex_exec.add_parameter( l_params, 'ENAME', p_ename );  apex_exec.add_parameter( l_params, 'EMPNO', p_empno );  apex_exec.add_parameter( l_params, 'SAL', p_sal );  apex_exec.add_parameter( l_params, 'JOB', p_job );  apex_exec.execute_web_source(    p_module_static_id => 'ORDS_EMP'    ,p_operation => 'POST'    ,p_parameters => l_params  );  p_result := apex_exec.get_parameter_clob(l_params,'RESPONSE');  end;/
    Amba
    · Share on TwitterShare on Facebook
  • Amba
    Amba Member Posts: 36 Green Ribbon
    edited Jul 12, 2020 2:00AM

    thanks a lot @jariola that worked! Ok, understood.. and thanks for showing me how the declarations work.

    This time I used the SQL Command page (not the APEX Create Procedure wizard) ~ mentioned it here just it case it may help someone else wanting to achieve something similar..

    · Share on TwitterShare on Facebook
  • jariola
    jariola Member Posts: 10,542 Silver Crown
    edited Jul 12, 2020 2:14AM

    You can also use Create Procedure Wizard. Just define arguments you use inside procedure (p_ename, p_empno, p_sal, p_job, p_result) and then use body like

      l_params apex_exec.t_parameters;  begin        apex_exec.add_parameter( l_params, 'ENAME', p_ename );    apex_exec.add_parameter( l_params, 'EMPNO', p_empno );    apex_exec.add_parameter( l_params, 'SAL', p_sal );    apex_exec.add_parameter( l_params, 'JOB', p_job );        apex_exec.execute_web_source(      p_module_static_id => 'ORDS_EMP'      ,p_operation => 'POST'      ,p_parameters => l_params    );        p_result := apex_exec.get_parameter_clob(l_params,'RESPONSE');      end;
    Amba
    · Share on TwitterShare on Facebook