CODEWITHSUNDEEP
oracle-databasepackageoracle-apex
user3138025
user3138025

Reputation: 825

Call a Package from Apex passing two values

I'm using Oracle 11.g and Apex 4.2.

I have an Oracle package (body and spec) that works fine with a stub of test data. My next step is to call this package from an Apex screen. I'd like to pass in two variables:

mail_event_prim_key - a number mail_list_name - a varchar2(1024)

I have two questions.
1) How do I construct the package to receive two IN-only values from Apex? No values need to be returned to Apex. 2) How do I call this from Apex. I assume I'd use a PL/SQL function to do so.

My current package spec is:

create or replace PACKAGE "PKG_MAIL_SEND" as  
  PROCEDURE proc_mail_send;
  PROCEDURE proc_job_mail_send;
  PROCEDURE proc_kill_job_mail;
end PKG__MAIL_SEND;

A portion of my package body is:

create or replace PACKAGE BODY "PKG_MAIL_SEND" as 
  PROCEDURE proc_parish_mail_send 
   is
  mail_event_prim_key number;
  mail_list_name varchar2(1024);
  crlf varchar2(2) := CHR(10) || CHR(13);
  l_html clob;
  l_html_header varchar2(1024);
  l_html_body clob;
  l_html_footer varchar2(1024);
  l_html_total clob;
  l_addresses DBMS_SQL.varchar2_table;
  l_from varchar2(1024);
  l_to varchar2(2048);
  l_subject varchar2(2048);
  l_smtp_hostname varchar2(2048);
  l_smtp_port varchar2(2048);
  l_smtp_username varchar2(2048);
  l_smtp_password varchar2(2048);

  BEGIN
--  Initialize Variables
    mail_event_prim_key := '1';
    mail_list_name := 'My List Name';

    Select lu_value into l_smtp_hostname from hymn_lookup where lu_type = 'smtp_hostname';

-- Lots more code here

End proc_mail_send;

--  Code for PROCEDURE proc_job_mail_send;
--  Code for PROCEDURE proc_kill_job_mail;

end PKG_MAIL_SEND;

Thanks for looking at this.

Upvotes: 0

Views: 3235

Answers (1)

Dmitriy
Dmitriy

Reputation: 5565

To receive 2 values in package you obviously need procedure with 2 parameters. For example for your package:

create or replace PACKAGE "PKG_MAIL_SEND" as  
  -- let this procedure have 2 parameters:
  PROCEDURE proc_mail_send(p_address in varchar2, p_topic in varchar2);
  PROCEDURE proc_job_mail_send;
  PROCEDURE proc_kill_job_mail;
end PKG__MAIL_SEND;

First way
After that click on Create page (or Create region), select type Form, then Form on Procedure. Pass all steps of wizard. After that you will have a region with fields for every parameters of procedure and button to run procedure.

Second way
Create page process. Write in source of process anonymous block, call your procedure from that block (here you need to create manually all things that apex creates automatically in first way - process, items, buttons, etc.).

Third way
Call procedure from dynamic action (it is bit more harder way if you are new to APEX).

Forth way
Call using ajax (procedure will be called inside application process). It is the most hard way, it is used rarely.

Upvotes: 1

Related Questions