Oracle
This page gives information to connect Appsmith to an Oracle database and to and to read and write data in your applications.
Connect Oracle
If you are a self-hosted user, you must whitelist the IP addresses 18.223.74.85 and 3.131.104.27 of the Appsmith deployment on your database instance before connecting to a database. See Managing Internet Protocol Allowlist and Blocklist Rules for more details.
Connection parameters
The following section is a reference guide that provides a complete description of all the parameters to connect to an Oracle database.
- Host Address
- The network location of your Oracle database. This can be a domain name or an IP address. To connect to a local Oracle database, see Connect Local Database for directions.
- Port
- The port number to connect to on the server. Appsmith connects to port `1521` by default if you do not specify one.
- Service Name
- The service name for your database instance.
- Username
- The username that you want to use to authenticate with the Oracle server.
- Password
- Password to use if the server demands password authentication.
- SSL Mode
- Determines whether your queries use an SSL connection to communicate with the database.
- Options:
- TLS: Connection is encrypted but no client verification is done.
- Disabled: Disables SSL completely, and all connections are established without encryption.
Query Oracle
The following section provides examples of creating basic CRUD queries for Oracle.
For Oracle SQL syntax, see the official documentation Oracle SQL Language Reference.
Fetch data
SELECT * FROM users OFFSET {{ UsersTable.pageOffset }} ROWS FETCH NEXT {{ UsersTable.pageSize }} ROWS ONLY;
In the above example, UsersTable is the name of the Table widget used to display the data using server-side pagination to control how much data is queried at once.
Insert data
INSERT INTO users
(name, gender, email)
VALUES
(
{{ NameInput.text }},
{{ GenderDropdown.selectedOptionValue }},
{{ EmailInput.text }}
);
In the above example, NameInput, GenderDropdown, and EmailInput are the names of the widgets used to capture input from the user for name, gender and email fields, respectively.
Update data
UPDATE users
SET email = {{ EmailInput.text }}
WHERE id = {{ UsersTable.selectedRow.id }};
In the above example, EmailInput is the name of the Input widget used to capture the email entered by the user. UsersTable is the Table widget where the user selects the row to update the user's email.
Delete data
DELETE FROM users WHERE id = {{ UsersTable.selectedRow.id }};
In the above example, UsersTable is the name of the Table widget where the user selects the row for deletion.
Prepared statements
Appsmith switches on prepared statements in queries by default to help prevent SQL injection attacks. If the query has widget data bindings using the mustache template {{ }}, Appsmith internally replaces these with question marks (?), translating the queries into prepared statements. See Prepared Statements for more details.