oci
Oracle Cloud Infrastructure - query and provision identity (compartments, users, groups, policies), core services (compute, VCN networking, block storage), object storage, database (including Autonomous), container engine (OKE), load balancers, DNS, KMS and secrets, monitoring, logging, events, functions, resource manager, streaming, budgets, usage and audit using SQL. Completes StackQL hyperscaler coverage alongside the aws, azure and google providers for multi-cloud inventory and FinOps queries.
total services: 22
total resources: 474
See also:
[SHOW] [DESCRIBE] [REGISTRY]
Installation​
To use the oci provider, first download and install stackql:
curl -L https://bit.ly/stackql-zip -O && unzip stackql-zip
Then pull the latest version of the provider:
REGISTRY PULL oci;
To view previous provider versions or to pull a specific provider version, see here.
Authentication​
Requests are signed with an OCI API key (the same credential the OCI CLI and Terraform use; see Required Keys and OCIDs). The following system environment variables are used by default:
OCI_TENANCY- tenancy OCIDOCI_USER- user OCIDOCI_FINGERPRINT- API key fingerprintOCI_KEY_FILE- path to the private key (PEM)OCI_REGION- region (e.g.us-ashburn-1), used to resolve the regional service endpointsOCI_PASSPHRASE- private key passphrase (only if the key is encrypted)
These variables are sourced at runtime (from the local machine or as CI variables/secrets) and are the provider's defaults (stackql v0.12.732 or later), so with them exported no --auth argument is needed:
stackql shell
Use a least-privileged IAM user rather than an administrator. The *_env_var keys are free-form, so an environment that already exports other names (for example Terraform's TF_VAR_* convention) can point them at those instead - or skip environment variables entirely with the config file variant below:
AUTH='{ "oci": { "type": "oci_signing_v1", "tenancy_ocid_env_var": "TF_VAR_tenancy_ocid", "user_ocid_env_var": "TF_VAR_user_ocid", "fingerprint_env_var": "TF_VAR_fingerprint", "private_key_path_env_var": "TF_VAR_private_key_path" }}'
stackql shell --auth="${AUTH}"
Using the OCI config file instead
When none of the OCI_* variables above are set, the standard OCI config file convention applies - the same ~/.oci/config used by the OCI CLI and Terraform. With a DEFAULT profile in ~/.oci/config, no --auth argument is needed at all. To point at a different file or profile:
AUTH='{ "oci": { "type": "oci_signing_v1", "config_file_path": "~/.oci/config", "profile": "DEFAULT" }}'
stackql shell --auth="${AUTH}"
or using PowerShell:
$Auth = "{ 'oci': { 'type': 'oci_signing_v1', 'config_file_path': '~/.oci/config', 'profile': 'DEFAULT' }}"
stackql.exe shell --auth=$Auth
OCI rejects requests with more than 5 minutes of clock skew with a
401 NotAuthenticated; check the local clock if authentication fails with valid credentials.
Endpoints are regional (identity.{region}.oci.oraclecloud.com and similar). The region server variable resolves from OCI_REGION automatically, can be supplied per query in the WHERE clause, and defaults to us-ashburn-1.
The compartment scope pattern​
Nearly every list operation in OCI is scoped by a compartment_id. The tenancy OCID is the root compartment, and oci.identity.compartments enumerates the compartments beneath it - the natural driving table for estate-wide joins:
SELECT
id,
name,
description,
lifecycle_state
FROM oci.identity.compartments
WHERE compartment_id = 'ocid1.tenancy.oc1..your_tenancy_ocid';
Compute estate inventory​
Column names are snake_case; nested detail objects are JSON columns addressed with json_extract:
SELECT
display_name,
shape,
json_extract(shape_config, '$.ocpus') AS ocpus,
json_extract(shape_config, '$.memoryInGBs') AS memory_gb,
lifecycle_state,
time_created
FROM oci.compute.instances
WHERE compartment_id = 'ocid1.compartment.oc1..example'
ORDER BY time_created DESC;
LIMIT pushes down to the OCI limit query parameter, so LIMIT 10 fetches only what it needs.
IAM policy audit​
SELECT
name,
description,
statements
FROM oci.identity.policies
WHERE compartment_id = 'ocid1.tenancy.oc1..your_tenancy_ocid';
Provisioning​
Mutations are SQL verbs - INSERT creates, UPDATE mutates, DELETE removes. Request body attributes use the same snake_case names:
INSERT INTO oci.network.vcns (
compartment_id,
cidr_block,
display_name
)
SELECT
'ocid1.compartment.oc1..example',
'10.0.0.0/16',
'my-vcn';
Actions map to EXEC; exec variables use the wire (camelCase) parameter names:
EXEC oci.compute.instances.instance_action
@instanceId = 'ocid1.instance.oc1..example',
@action = 'STOP',
@actionType = 'stop';
Multi-cloud inventory​
The reason this provider exists - the four-hyperscaler estate in one statement:
SELECT 'oci' AS provider, display_name AS name, shape AS size, lifecycle_state AS state
FROM oci.compute.instances
WHERE compartment_id = 'ocid1.compartment.oc1..example'
UNION ALL
SELECT 'aws', instance_id, instance_type, json_extract(state, '$.name')
FROM aws.ec2.instances
WHERE region = 'us-east-1'
UNION ALL
SELECT 'azure', name, json_extract(properties, '$.hardwareProfile.vmSize'), json_extract(properties, '$.provisioningState')
FROM azure.compute.virtual_machines
WHERE subscriptionId = '00000000-0000-0000-0000-000000000000' AND resourceGroupName = 'my-rg'
UNION ALL
SELECT 'google', name, machineType, status
FROM google.compute.instances
WHERE project = 'my-project' AND zone = 'us-central1-a';