Access an Oracle Database Using SQLcl

Using SQLcl, you can run SQL statements from a build to connect and access an Oracle Database. You can use SQLcl to access any publicly available Oracle Database that you can connect to using a JDBC connect string. You can run DML, DDL, and SQL Plus statements. You can also use SQLcl in a test scenario and run SQL scripts to initialize seed data or validate database changes.

To learn more about SQLcl, see http://www.oracle.com/technetwork/developer-tools/sqlcl/overview/index.html. Also see Using the help command in SQLcl in Using Oracle Database Exadata Express Cloud Service and the SQL Developer Command-Line Quick Reference documentation to know more about using SQLcl supported commands.

To connect to Oracle Database Exadata Express Cloud Service, download the ZIP file that contains its credentials and upload it to the job’s Git repository. You can download the ZIP file from the Oracle Database Cloud Service service console. See Downloading Client Credentials in Using Oracle Database Exadata Express Cloud Service.

Set Up a VM Build Executor and a Build Executor Template with SQLcl

Before you can create a build step that uses SQLcl commands, your organization administrator must create a build executor template that includes the SQLcl software and add a VM build executor that uses that build executor template. The build executor template can be created from scratch or software can be added to an existing build executor template.

Note:

To find your organization administrator, click Contacts under your user profile. Your administrator, or a list of administrators, will display.

See Create and Manage Build Executor Templates in Administering Visual Builder Studio.

After the organization administrator adds a VM build executor to the build executor template, you can create and configure a job to use that build executor template and add SQLcl commands.

Configure a Job to Run SQLcl Commands

Before you configure the job, you need to be aware of the following information:

• VB Studio doesn’t support SQL commands to edit buffer (such as set sqlformat csv) or edit console.
• VB Studio doesn’t support build parameters in the SQL file.
• If you are using Oracle REST Data Services (ORDS), some SQLcl commands, such as the BRIDGE command, requires a JDBC URL:

  BRIDGE table1 as "jdbc:oracle:thin:DEMO/demo@http://examplehost.com/ords/demo"(select * from DUAL);

• To mark a build as failed if the SQL commands fail, add the WHENEVER SQLERROR EXIT 1 line to your script.

Here's how you create and configure a job that runs SQLcl commands:

1. Open the job’s configuration page.
   If you're creating a job, in Template in the New Job dialog box, select the SQLcl build executor template. Jump to step 5.
2. Click Settings .
3. In the Software tab, select the SQLcl build executor template.
4. From the Java drop-down list, select the version.
5. Click Configure .
6. In the Git tab, add the Git repository where you uploaded the script file.
7. Click the Steps tab.
8. From Add Step, select SQLcl.
9. In Username and Password, enter the user name and password of the Oracle Database account.

   You can also use build parameters in Username and Password.

10. To connect to Oracle Database Exadata Express Cloud Service, in Credentials File, enter the workspace path of the uploaded credentials zip file.
11. In Connect String, enter the JDBC or HTTP connection string of the Oracle Database account using any of the host_name:port:SID or host_name:port/service_name formats.

    Here's a JDBC example:

    test_server.oracle.com:1521:adt1100

    In this example, adt1100 is the SID, and ora11g is the service name in test_server.oracle.com:1521/ora11g.

    Here's an HTTP example:

    http://test_server.oracle.com:8085/ords/demo

    You can also use build parameters in Connect String.

12. If the SQL statements are available in a file uploaded to the project Git repository, in Source, select SQL File. In SQL File Path, enter the Git repository path of the SQL file. You can copy the file’s path from the Git page.
    To enter SQL statements, in Source, select Inline SQL. In SQL Statements, enter the SQL statements. You can also use build parameters in SQL Statements.
13. In Role, if necessary, select the database role of the user.
14. In Restriction Level, if necessary, specify the restriction level on the type of SQL statements that are allowed to run.
15. In JVM Options, enter the parameters you want to pass to the Java VM and the build will pass the parameters as _JAVA_OPTIONS.

    You’re setting this parameter (or these parameters) specifically for the session running SQLcl – if you set it globally, that would impact every Java program running on the machine, which you probably don't want to do.

16. Click Save.