Skip to main content

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_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."}