PL/SQL API

Explanation

This procedure can be used to send a message to all users, a room, or multiple rooms. An optional username can be specified. The message must be a JSON object or array.

procedure send(
    -- p_url is the URL of the AMS Server
    p_url         in varchar2,
    -- p_api_key is the API Key of the AMS Cloud Server (not required for on-premise)
    p_api_key     in varchar2,
    -- p_room_names is a comma-separated list of room names. When null, all rooms will be used
    p_room_names  in varchar2 default null,
    -- p_username is the username that will be used to send the message
    p_username    in varchar2 default null,
    -- p_message is the JSON message that will be sent to the server
    p_message     in clob,
    -- p_wallet_path is the file system path to a wallet
    p_wallet_path in varchar2 default g_wallet_path,
    -- p_wallet_pwd is the password to access the wallet
    p_wallet_pwd  in varchar2 default g_wallet_pwd,
    -- p_https_host is the host name to be matched against the common name (CN) of the remote server's certificate for an HTTPS request
    p_https_host  in varchar2 default g_https_host,
    -- p_status can either be 'error', 'success', 'partial_success'
    p_status      out varchar2,
    -- p_output is the JSON output from the server
    p_output      out clob
);

Examples

Success Example 1 Room

declare
    l_status varchar2(15); 
    l_output clob;
begin
    ams_api_pkg.send(
        p_url => 'https://api.ams.united-codes.com/',
        p_api_key => 'YOUR_API_KEY',
        p_room_names => 'AMS-Sample-Room',
        p_message => '{"message": "Hello everyone!", "page": "9"}',
        p_username => 'APEX SQL Commands',
        p_status => l_status,
        p_output => l_output
    );
    
    sys.htp.p(l_status);
    sys.htp.p(l_output);
end;

Output:

success
{"message":"Messages sent."}

Success Example Multiple Rooms

declare
    l_status varchar2(15); 
    l_output clob;
begin
    ams_api_pkg.send(
        p_url => 'https://api.ams.united-codes.com/',
        p_api_key => 'YOUR_API_KEY',
        p_room_names => 'AMS-Sample-Room, Testroom',
        p_message => '{"message": "Hello everyone!", "page": "9"}',
        p_username => 'APEX SQL Commands',
        p_status => l_status,
        p_output => l_output
    );
    
    sys.htp.p(l_status);
    sys.htp.p(l_output);
end;

Output:

success
{"message":"Messages sent."}

Success Example All Rooms

declare
    l_status varchar2(15); 
    l_output clob;
begin
    ams_api_pkg.send(
        p_url => 'https://api.ams.united-codes.com/',
        p_api_key => 'YOUR_API_KEY',
        p_room_names => null,
        p_message => '{"message": "Hello everyone!", "page": "9"}',
        p_username => 'APEX SQL Commands',
        p_status => l_status,
        p_output => l_output
    );
    
    sys.htp.p(l_status);
    sys.htp.p(l_output);
end;

Output:

success
{"message":"Messages sent."}

Partial Success Multiple Rooms, one or more Room do not exist

declare
    l_status varchar2(15); 
    l_output clob;
begin
    ams_api_pkg.send(
        p_url => 'https://api.ams.united-codes.com/',
        p_api_key => 'YOUR_API_KEY',
        p_room_names => 'AMS-Sample-Room, Testroom, RoomDoesNotExist',
        p_message => '{"message": "Hello everyone!", "page": "9"}',
        p_username => 'APEX SQL Commands',
        p_status => l_status,
        p_output => l_output
    );
    
    sys.htp.p(l_status);
    sys.htp.p(l_output);
end;

Output:

partial_success
{"message":"Messages sent.","nonExistentRooms":["RoomDoesNotExist"]}

Partial Success Room does not exist

declare
    l_status varchar2(15); 
    l_output clob;
begin
    ams_api_pkg.send(
        p_url => 'https://api.ams.united-codes.com/',
        p_api_key => 'YOUR_API_KEY',
        p_room_names => 'RoomDoesNotExist',
        p_message => '{"message": "Hello everyone!", "page": "9"}',
        p_username => 'APEX SQL Commands',
        p_status => l_status,
        p_output => l_output
    );
    
    sys.htp.p(l_status);
    sys.htp.p(l_output);
end;

Output:

partial_success
{"message":"No rooms to send message to.","nonExistentRooms":["RoomDoesNotExist"]}

Partial Success Multiple Rooms do not exist

declare
    l_status varchar2(15); 
    l_output clob;
begin
    ams_api_pkg.send(
        p_url => 'https://api.ams.united-codes.com/',
        p_api_key => 'YOUR_API_KEY',
        p_room_names => 'RoomDoesNotExist, RoomDoesNotExist2, RoomDoesNotExist3',
        p_message => '{"message": "Hello everyone!", "page": "9"}',
        p_username => 'APEX SQL Commands',
        p_status => l_status,
        p_output => l_output
    );
    
    sys.htp.p(l_status);
    sys.htp.p(l_output);
end;

Output:

partial_success
{"message":"No rooms to send message to.","nonExistentRooms":["RoomDoesNotExist","RoomDoesNotExist2","RoomDoesNotExist3"]}

Errors

declare
    l_status varchar2(15); 
    l_output clob;
begin
    for i in 1..10 loop
    ams_api_pkg.send(
        p_url => 'https://api.ams.united-codes.com/',
        p_api_key => 'YOUR_API_KEY',
        p_room_names => 'AMS-Sample-Room',
        p_message => '{"message": "Hello everyone!", "page": "9"}',
        p_username => 'APEX SQL Commands',
        p_status => l_status,
        p_output => l_output
    );
        
    sys.htp.p(l_status);
    sys.htp.p(l_output);
    end loop;
end;

Output:

error
{"error":"Rate limit exceeded, ignoring event."}