MyChat Integration API for Oracle PL/SQL
Installation and usage guide

Purpose
=======

These files install the MYCHAT_API PL/SQL package in Oracle. The package sends messages to MyChat through the REST Integration API.

Two operations are implemented for now:

1. Send a private message to a user.
2. Send a message to a text conference.

The user does not need to edit SQL files. All values are entered during installation.


Files required in the example folder
====================================

Minimum file set:

install_mychat_api.cmd
detect_oracle_pdb_connect.ps1
00_install_all.sql
00_skip_examples.sql
01_admin_setup.sql
02_install_package.sql
03_examples.sql
readme_ru.txt
readme_en.txt
readme_ua.txt

All these files must be in the same folder.

File purpose:

install_mychat_api.cmd
  Main startup file. The user runs this file.

detect_oracle_pdb_connect.ps1
  Automatically detects the connection string to the local Oracle PDB using lsnrctl status.

00_install_all.sql
  Main installation scenario. It connects as SYS, runs the admin setup script, then connects as the created schema and installs the package.

00_skip_examples.sql
  Placeholder script used when the user does not want to send test messages.

01_admin_setup.sql
  Creates a separate Oracle schema, grants privileges, and configures ACL access to the MyChat server.

02_install_package.sql
  Creates the MYCHAT_API package.

03_examples.sql
  Sends a test private message and a test conference message.

readme_ru.txt
  Russian guide.

readme_en.txt
  English guide.

readme_ua.txt
  Ukrainian guide.

Run:

install_mychat_api.cmd


Prerequisites
=============

1. Oracle Database is installed and running.
2. SQL*Plus is available from the command line as sqlplus.
3. Oracle listener is running.
4. You have the SYS password for the target PDB.
5. You have the MyChat server address, for example myserver.com.
6. You have the Integration API key from MyChat Server settings.
7. The Oracle machine has HTTPS access to the MyChat server.


Easiest installation method
===========================

1. Open Windows console in the folder with these files.

2. Run:

install_mychat_api.cmd

3. The script enables UTF-8 for SQL*Plus and tries to detect the local Oracle PDB connection string using lsnrctl status.

If the script shows a detected connection string and asks:

Use this connection string? [Y/n]:

press Enter.

If the connection string cannot be detected automatically, the script asks you to enter it manually:

Oracle PDB connection string:

Connection string format:

host:port/service_name

For example, this can be the host and service shown by lsnrctl status. Do not copy text in angle brackets from the guide. Angle brackets mean that you must replace the placeholder with your own value.


Installer prompts
=================

1. SYS password for this PDB:

Enter the SYS password for the target PDB.

2. Oracle schema for MyChat package [MYCHAT_API_USER]:

You can press Enter. A separate schema named MYCHAT_API_USER will be created.

Important: do not enter an existing application schema that contains important tables. The specified schema is recreated.

3. Password for this Oracle schema:

Enter a password for the new schema. This password is only for the created package schema.

4. MyChat host for ACL, without protocol, for example myserver.com:

Enter the MyChat server host without https:// and without the /API/ path.

Example:

myserver.com

5. MyChat HTTPS port [443]:

Usually press Enter.

6. MyChat base URL, for example https://myserver.com:

Enter the base URL of the MyChat server with https://, but without /API/ at the end.

Example:

https://myserver.com

7. MyChat Integration API key:

Enter the Integration API key.

8. Integration name [oracle-plsql]:

You can press Enter.

9. HTTP timeout seconds [15]:

You can press Enter.

10. Send test messages now? [Y/n]:

Press Enter if you want to test message sending immediately.
Enter n and press Enter if you do not want to send test messages.


Test messages
=============

If test sending is enabled, the script asks:

1. Private message receiver UIN/nick/email [2]:

Enter the user ID, nickname, or email of the private message recipient.

2. Private message text:

Enter the private message text.

3. Conference UID [1]:

Enter the text conference UID.

4. Conference message text:

Enter the conference message text.

After successful sending, the messages should appear in MyChat and SQL*Plus will show the API response.


How to send a message manually after installation
=================================================

If SQL*Plus is already open and connected as the schema where MYCHAT_API is installed, you can run the SQL queries below.

Private message:

select dbms_lob.substr(
         mychat_api.send_private_message(
           p_user_to => '2',
           p_msg     => 'Test message from Oracle PL/SQL'
         ),
         4000,
         1
       ) as response
from dual;

In this example, replace:

'2' - with the recipient user ID, nickname, or email;
'Test message from Oracle PL/SQL' - with your own text.


Text conference message:

select dbms_lob.substr(
         mychat_api.send_conference_message(
           p_uid => 1,
           p_msg => 'Test message from Oracle PL/SQL'
         ),
         4000,
         1
       ) as response
from dual;

In this example, replace:

1 - with the conference UID;
'Test message from Oracle PL/SQL' - with your own text.

If the text contains a single quote, write it as two single quotes.

Example:

p_msg => 'It''s OK'


How to use the package from another Oracle schema
=================================================

If the application runs under another Oracle schema, the package owner must grant execute permission:

grant execute on mychat_api to <APP_SCHEMA>;

Replace <APP_SCHEMA> with the application schema name.

After that, the application can call the package using the owner name:

MYCHAT_API_USER.mychat_api.send_private_message(...)

or:

MYCHAT_API_USER.mychat_api.send_conference_message(...)

If another schema name was selected during installation instead of MYCHAT_API_USER, use that name.


Manual installation without install_mychat_api.cmd
==================================================

Use this method if the startup CMD file cannot be used.

1. Run in Windows console:

chcp 65001
set NLS_LANG=.AL32UTF8
sqlplus /nolog

2. Connect as Oracle administrator:

connect sys@<PDB_connection_string> as sysdba

Important: replace <PDB_connection_string> with the real connection string. Do not copy angle brackets.

3. Run the admin setup script:

@01_admin_setup.sql

4. Connect as the created schema:

connect <created_schema>/<password>@<PDB_connection_string>

5. Install the package:

@02_install_package.sql

6. Send test messages if needed:

@03_examples.sql

Enter each command on a separate line. Do not paste connect and @script commands as one long line.


How to find the PDB connection string
=====================================

For local Oracle Free, run:

lsnrctl status

Find these values in the output:

1. HOST and PORT in Listening Endpoints Summary.
2. The PDB service in Services Summary. Usually it is named freepdb1.

The connection string is:

HOST:PORT/service_name

You can test it with:

tnsping HOST:PORT/service_name

If tnsping prints OK, you can use this string in connect.


Common errors
=============

ORA-12541: Cannot connect. No listener

SQL*Plus cannot reach Oracle listener at the specified host and port. Check lsnrctl status and use HOST, PORT, and service name from the listener output.


SP2-0306: Invalid option

Usually means that several commands were pasted as one line. For example, connect and @01_admin_setup.sql must not be written on the same line. Enter each command separately.


SP2-0310: unable to open file

SQL*Plus was not started from the folder with the SQL files. Open the console in the installation folder or run @script with the full path to the file.


ORA-24247: network access denied by access control list

Oracle does not allow the package schema to access the MyChat server over the network. Run 01_admin_setup.sql as SYS AS SYSDBA and specify the correct MyChat host and port.


ORA-01031: insufficient privileges

Insufficient privileges. The admin script 01_admin_setup.sql must be run as SYS AS SYSDBA in the target PDB.


Broken characters instead of text

The package sends JSON in UTF-8. If text was already corrupted before it reached Oracle, the package cannot restore it.

For Windows SQL*Plus, always run before starting sqlplus:

chcp 65001
set NLS_LANG=.AL32UTF8

SQL files must be saved in UTF-8.


What the installer does internally
==================================

1. Creates a separate Oracle schema for the package.
2. Grants create session and create procedure.
3. Grants access to sys.utl_http, sys.utl_i18n, and sys.utl_raw.
4. Configures Oracle ACL for access to the MyChat server.
5. Creates the MYCHAT_API package.
6. Stores the MyChat base URL, Integration API key, integration name, and HTTP timeout inside the package.

The package sends POST requests to:

<MyChat base URL>/API/

The request body is built as JSON using the standard Oracle json_object function.
