_{This scenario assumes you have VS Code, SQL Developer for VS Code, SQLcl, and an AI agent. This scenario uses the SQLcl MCP Server configured for Cline.}

---

Scenario

Now, imagine you are working a brand new app development project. But, your target database schema is empty. Luckily you can use the SQLcl MCP Server along with your AI Agent (we use Cline in this example) to quickly generate schema objects and sample table data for your project.

To achieve this you‘ll need to “prompt“ your AI Agent. In this example, we include a fully validated prompt that can be copy/pasted directly into your AI Agent “task.”

But as you become more comfortable with communicating with AI Agents, you too can prompt your agent on-the-fly.

The prompt

In this example you’ll find a starter prompt (in Markdown format) for you to use as-is or as a reference guide. Alternatively, you can download the actual markdown file here.

And here is the current version of the prompt (at time of publication):

# Creating mock Schema data, and relevant views

## Connecting and creating database objects

1. Connect as the <USERNAME>. If no user is provided, please prompt me with the available connections, and ask me to choose the one of the connections before proceeding to the next step.
2. Create four tables named car, truck, motorcycle, and manufacturer in the chosen schema with the following characteristics (please review the three "NOTE" notes in this section before creating the tables):

    The car, truck, motorcycle tables should include colums such as: 
      - make
      - model
      - year of manufacture
      - engine displacement
      - wheelbase

    The manufacturer table should include details such as: 
      - Doing business as (dba) name
      - Headquarters location city
      - Headquarters location country 
      - Year manufacturer was established/incorporated
      - Privately held or publiclly held company 

    Create, where applicable for the tables:
      - indexes
      - comments
      - tags
      - references
      - primary and foreign keys  
    
> NOTE: For all tables, recommend any other additional columns you think may be relevant.
    
> NOTE: Ensure that no integrity constraints will be violated. Parent keys, in referred tables, should be verified of their existence before being used in any fictitous data. As an example, make sure the manufacturer_id exists across the various tables.

> NOTE: For simplicity, include Internal Combustion Engine (ICE) vehicles only.

## Inserting data

1. Create 50 unique entries each (in each table) of fictitious, but plausible data for the following tables(please review the two "NOTE" notes in this section before inserting the table data):
    - car
    - truck
    - motorcycle

    > NOTE: When performing the inserts, bulk insert the data with syntax such as this:
    > 
    > ```sql
    > INSERT INTO t(col1, col2, col3) VALUES
    > ('val1_1', 'val1_2', 'val1_3'),
    > ('val2_1', 'val2_2', 'val2_3'),
    > ('val3_1', 'val3_2', 'val3_3');
    > ```

    > NOTE: Take care to not create duplicate data in any of the tables.
    > 
    > NOTE: Issue the COMMIT; statement after each INSERT operation, to ensure the data has been saved to its target table.

2. Create 25 unique entries of fictitious, but plausible data for the following table (please review the two "NOTE" notes in this section before inserting the table data):
    - manufacturer

    > NOTE: When performing the inserts, bulk insert the data with syntax such as this:
    > 
    > ```sql
    > INSERT INTO t(col1, col2, col3) VALUES
    > ('val1_1', 'val1_2', 'val1_3'),
    > ('val2_1', 'val2_2', 'val2_3'),
    > ('val3_1', 'val3_2', 'val3_3');
    > ```

    > NOTE: Take care to not create duplicate data in this table.
    >
    > NOTE: Issue the COMMIT; statement after each INSERT operation, to ensure the data has been saved to its target table.

2. After inserting the data show me the first 5 rows of each table individually. If no data exists, then: 
    - reattempt the inserting of the data in the empty tables, then:
        - show the first 5 rows of the table, and:
            - continue this until we are certain that table data exists in all the tables

## Create four unique views

1. Create the following views for me, according to these specifications: 

| View Name | Descriptions | 
| --- | --- | 
| Vehicle Count by Manufacturer | The view shows the count of vehicles (cars, trucks, motorcycles) for each manufacturer. For example, Toyota, Ford, Honda, and Rivian have entries in all three vehicle categories, while Yamaha, Ram, Kawasaki, Harley-Davidson, GMC, and Ducati have entries in one or two categories.| 
| Average Engine Displacement by Vehicle Type | The average engine displacement for cars is approximately 1.87 liters, for trucks it's about 4.07 liters, and for motorcycles, it's significantly higher at 43.74 liters. |
|Vehicles by Year of Manufacture| The view shows the count of vehicles manufactured each year. The years 2020, 2021, and 2022 have 7, 9, and 10 vehicles respectively.|
|Manufacturer Details with Vehicle Counts| This view provides detailed information about each manufacturer along with the count of vehicles they manufacture. For instance, Toyota, Ford, Honda, and Rivian are listed with their respective vehicle counts |

2. Recommend to me, two additional unique views. These views should:
    - provide me with unique and/or intersting insights
    - be useful enough to include in a dashboard, should I want to do this in the future
    - Feature in-line commenting, so future users can understand what the view is doing
2. After reviewing the views you propose I will either approve or ask you to reiterate. 
3. Once I am satisfied, I will approve, and you will create these views.

## Disconnect

1. Once we are complete, you will disconnect from the Oracle database.

As you can see, the prompt is comprehensive and ultra-prescriptive. It’s designed that way so you can expect predictable and repeatable results; extremely important when you are just starting out with prompt engineering. Recall, as you continue your journey, you may not need even need a prompt. Let’s review how you might use this prompt along with the SQLcl MCP Server and an AI Agent (like Cline).

Using the prompt

You can attach this prompt (as a .md file) to a new AI Agent task (like we do here in Cline). If you use the prompt as-is, make sure you add your target user in the <USERNAME> placeholder (as can be seen in the prompt file). Alternatively, you can explicitly include your username when attaching the prompt file.

If using Cline, toggle the task to Plan mode (until you are satisfied with the steps the Cline agent has presented for you or until the Agent prompts you to switch to Act mode).

Press enter (or accept if your AI Agent asks for confirmation), and your new task will begin (your AI Agent’s steps may vary). Cline will review your prompt and present you with it’s initial plan; a few examples:

Act mode

At some point, your AI Agent will request you switch to Act mode. This is expected, simply review the plan and amend if needed:

Once you make the switch the “plan” will really start to come to life. You’ll follow a similar workflow as to what is depicted in these images. First you‘ll approve table creation.

Then, you’ll populate the tables with sample data.

In this case, the prompt performs a FETCH (first five rows only) on the newly created tables; allowing you to visually confirm the work that was done!

Finally, you ‘ll see the new database objects and four new views created for you.

You’ll also see the two additional views recommended to you by the Agent (as was prescribed in the included prompt).

And that‘s it. In total, this entire exercise should take under 5 mins. You should now have a starter schema with data for your project.

While you are here, don’t forget to take a look at SQLcl Projects (our version control capability that combines the power of Liquibase with the ease and convenience of Git).

Finished product

If you navigate to the Connections Explorer in SQL Developer for VS Code, you can review all the work that was accomplished using the SQLcl MCP Server and the AI Agent. You’ll see new database objects like:

And if you take a closer look, you can review the new database objects and the data too!

With ORDS or the oracledb library, you‘re only a few steps away from feeding your single-page web app.

Disconnect

As a best practice, make sure you explicitly disconnect from the database when you are finished with the SQLcl MCP Sever. This isn ‘t required, but it’s a good habit to get into.

Mission successful

Working with the SQLcl MCP Server and your favorite AI Agent is really that easy. Give it a go with your preferred LLM API Provider and let us know your first impressions!

Some Tips

Some things to consider:

1. Always review what the Agent is proposing, instead of just blindly approving.
2. Use a test user that has the minimal required privileges.
   1. In this example, the user has been granted the CONNECT, RESOURCE, and DB_DEVELOPER_ROLE roles.
3. Don’t trust, always verify. These agents can be extremely convincing. You still need to know your way around the database.
4. These agents will do exactly as you say, so be clear and unambiguous in your instructions.

Want to learn more?

• Blog: Introducing MCP Server for Oracle Database
• Docs: Using the SQLcl MCP Server
• VS Code: SQL Developer for VS Code