--- title: Connect Oracle Database description: Connect Oracle Database - the addresses to allow, the TCPS listener it needs, the Autonomous Database wallet, the read-only user, and identifier folding. updated: 2026-09-22 tier: pro engines: [oracle] --- Oracle Database, self-managed or Autonomous. The section most likely to cost you is [Reach your database](#1-reach-your-database), where the honest answer may be that Dashies cannot reach your instance until you change how it listens. ## Before you start - **Dashies connects from the addresses listed for your workspace in its settings.** Allow them on your listener port, and on Autonomous Database add them to the access control list, the IPv4 address at least. On a self-managed database that runs shared server, allow them on the dispatcher port too (see "Opening one port may not be enough" in section 1). Every region's addresses are on [IP addresses](/reference/ip-addresses). - **Dashies connects OUT to your database** to verify the connection and to run each refresh, on **port 1521, 1522, or 2484**. Any other port is refused by the form. - **Dashies speaks TLS, and only TLS.** It does not speak Oracle native network encryption. On a self-managed instance that means you need a TCPS listener; on Amazon RDS it means the SSL option rather than the native-encryption option. Both are in section 1. - **The certificate is always verified and there is no way to turn that off** on this engine. The connect form carries no opt-out, deliberately, because there is nothing behind it to switch. See [Certificates](#certificates). - **You give Dashies a service name, not a SID.** A SID needs a full connect descriptor, which Dashies does not take. - **You must create a read-only user.** The connect probe refuses a user that can write. ## 1. Reach your database ### Autonomous Database This is the straightforward case. An Autonomous Database already listens on TCPS, already presents a certificate that chains to a public authority, and Oracle's own firewall instruction names one port. **If your database restricts access with an access control list**, add your region's addresses to it, the IPv4 address at least. With the list naming them, Dashies connects; an address the list does not name is refused by the listener before it can sign in. A database with no access control list accepts Dashies without one. **Use the `_medium` service, not `_high`.** Your wallet's `tnsnames.ora` lists several consumer groups. `_high` gives one query the largest share of the instance and runs fewer of them concurrently, which is the wrong trade for a refresh that reads one cube start to finish. `_medium` is what to put in the Service name field, and it looks like `yourdb_medium.adb.oraclecloud.com`. **You need the wallet, and it has to be the INSTANCE wallet.** Oracle publishes two kinds. A **regional** wallet reaches every Autonomous Database in that tenancy and region; an **instance** wallet reaches the one database you downloaded it for. Oracle recommends the instance wallet, and so do we, for the reason the difference implies: a regional wallet handed to a reporting tool is a credential far wider than the job. Download it from your database's console page, choose **Instance Wallet**, and set a wallet password. Upload `ewallet.pem` from inside the zip, or drop the zip itself on the control and Dashies will take `ewallet.pem` out of it. :::warning{title="A wallet expires, on a clock nobody is watching"} Every other credential here fails only when you change it. A wallet fails on a date set when it was downloaded, and the driver's error for an expired wallet is a TLS failure that names no expiry and no date. So Dashies reads the expiry when you upload it and shows it on the form. It has **two readers, and it prefers the wallet's own `README`**, which states the date directly. Where there is no `README` it falls back to walking the certificates and taking the **earliest** `notAfter` in the chain, that being the one that decides when the wallet stops working. On the wallet measured here the two agree to the second, the `README` simply carrying sub-second precision the certificates do not, so the fallback is a second reading of the same date rather than a worse one. The validity period measured was just under five years, which is worse to debug than a short one, not better: nobody who set the connection up is still looking at it. If Dashies cannot read a date it says **expiry unknown** rather than claiming there is none. That takes BOTH readers failing, so it is uncommon: a missing `README` alone still leaves the certificate walk, and the wallet works either way. ::: ### Amazon RDS for Oracle **The SSL option and the native network encryption option are mutually exclusive.** AWS documents that from both sides, and Dashies speaks only TLS, so an instance carrying the native-encryption option has no port we can dial. Moving to the SSL option is an option-group change on your database, not something you can do from here. That is a real limitation rather than a configuration you are getting wrong. If your policy keeps you on native network encryption, Dashies cannot reach that instance. With the SSL option added, nothing else is needed from you on the certificate side: RDS presents a chain under a private per-region Amazon root, and the Dashies extractor image already carries those roots. Set the instance to publicly accessible, and open the SSL option's port inbound to your region's two addresses. :::note{title="One RDS default worth knowing about, though it works today"} The SSL option's cipher-suite setting is not left unset when you omit it. RDS applies its own default, `SSL_RSA_WITH_AES_256_CBC_SHA`, and that is what a default-configured instance negotiates. It works: measured against a live RDS for Oracle instance, the handshake completed and the server was authenticated. It is an older suite with no forward secrecy, so a future Node release dropping it would break every dial at once, with a handshake error naming no cipher. The remedy then is one option-group setting: `SQLNET.CIPHER_SUITE` accepts `TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384` among its allowed values. Nothing to do today. ::: ### Oracle Database you run yourself **You need a TCPS listener.** A stock listener speaks TCP only, and Dashies will not connect to it. Configuring TCPS means giving the listener a wallet and a certificate and adding a `TCPS` endpoint to `listener.ora`; it is a database administration task, done once. **If the instance has no TCPS listener, Dashies cannot reach it.** There is no fallback and no unencrypted mode to fall back to. Allow your region's two addresses on the TCPS port, on the host firewall and on anything in front of it. :::danger{title="Opening one port may not be enough, and the failure arrives after a successful handshake"} Oracle's listener may answer a client by redirecting it to a different process on a **different port**, and on a default shared-server configuration that port is dynamic. Oracle's own Net Services guide describes the redirect, and Oracle's security guidance names firewalls as the reason to pin the dispatcher's port. So "open 1521 outbound" is not a complete instruction for a self-managed Oracle Database, and the symptom does not name the cause: the listener handshake succeeds, and then the connection dies reaching somewhere nobody allowed. If you run shared server, pin the dispatcher port and open that too. **Autonomous Database is very unlikely to be affected**, and the hedge is deliberate. It offers dedicated and pooled servers only, never shared, and Oracle's own firewall instruction for it names one port. What nobody has is a sentence from Oracle saying in terms that an Autonomous Serverless connection is never redirected, so this is strong indirect evidence rather than a documented guarantee. ::: ## 2. Create the read-only user Oracle schemas are user names, so the schemas you import are the users whose tables Dashies reads. Run this as a user who can create one: ```sql create user dashies_ro identified by ""; grant create session to dashies_ro; -- one grant per table you will import: grant select on sales.orders to dashies_ro; grant select on sales.customers to dashies_ro; ``` :::danger{title="Do not grant SELECT ANY TABLE, and do not grant a role"} Both are refused, and neither refusal is about writing, so the message will not point you back here. The connect probe reads three of Oracle's own views and refuses on anything it finds: | View | What it tolerates | |---|---| | `user_sys_privs` | `CREATE SESSION`, and nothing else | | `user_role_privs` | `CONNECT`, and nothing else | | `user_tab_privs_recd` | `SELECT` and `READ`, and nothing else | `SELECT ANY TABLE` is a **system** privilege, so it produces a `user_sys_privs` row and the connection is refused even though it grants no write of any kind. The same is true of any role other than `CONNECT`, including a read-only role you built yourself: the probe sees the role, not what is inside it. So grant `SELECT` on the tables you mean, one statement each. That is narrower than a role anyway, which is the reason the check is shaped this way. ::: **Dashies also refuses a user that can write, and that is enforced rather than recommended.** Any privilege outside the three cells above refuses the connection with: ```bash That database user can do more than read. Dashies requires a read-only user. Grant it SELECT on the schemas you listed and nothing else, then connect again. ``` Connecting as a DBA account will not work, however convenient it is for a first test. ## 3. Add the data source Open [dashies.ai/app/connections](https://dashies.ai/app/connections), click to add a data source, and pick **Oracle Database**. | Field | Required | Example | Notes | |---|---|---|---| | Host | yes | `adb.eu-frankfurt-1.oraclecloud.com` | A hostname. A colon is refused, so the `host:port` and `host:port:SID` forms do not go here. | | Port | yes | `1521` | 1521, 1522, or 2484 only. | | Service name | yes | `yourdb_medium.adb.oraclecloud.com` | The service the listener registers. Not a SID. | | User | yes | `DASHIES_RO` | The read-only database user. | | Password | yes | | Stored encrypted, never shown again. | | Wallet | for Autonomous | `ewallet.pem` | With its wallet password. Both or neither. | | Schemas to import | yes | `SALES, FINANCE` | Comma separated. Oracle schemas are user names. | | Display name | no | `Analytics warehouse` | Up to 120 characters. | **Why a SID is refused rather than accepted and translated.** The Easy Connect form Dashies builds takes a service name; a SID needs a full connect descriptor, which is a different and much larger input, and accepting one would mean taking a connect string you wrote rather than assembling it from validated fields. If you only know a SID, your DBA can tell you the service name the listener registers, or `lsnrctl services` will show it. **Why a colon in the host is refused.** The host field is a hostname, and the port and service name have fields of their own. A colon there almost always means somebody pasted a whole connect string into it, so it is refused where it is typed rather than failing later as a name that does not resolve. ## 4. Connect Creating the data source runs the real work: it signs in to your database from a one-shot machine in the Dashies extractor fleet, checks the user's privileges, and reads back the schemas you listed. There is no separate Test step to click afterwards. **A third outcome is normal and is not a failure.** If Dashies cannot get an answer from your database in time, the form says **Still testing**: the data source is saved, the test may finish on its own, and your data sources page is where you see where it landed. Open it again in a moment rather than creating a second data source. The connect result lists no schemas back to you, deliberately. The probe answers active, error or still-testing and enumerates nothing, so what Dashies will read from is the allowlist you typed rather than something the probe discovered. | Message | Meaning | |---|---| | `That database user can do more than read...` | The credentials work and the user has more than `SELECT`. Section 2. | | `Dashies reached the database but could not read its answer to the read-only check...` | The credentials work, and the answer to the privilege check came back in a form Dashies could not read, so the data source was not accepted. It says nothing about the user's access: connect again, and if it repeats, the fault is on the Dashies side. | | `Dashies could not connect. Check the host, port, service name, user, password, and the wallet if you uploaded one.` | Nothing answered, or the sign-in failed, or the TLS handshake did. | | `The connection test failed. Check the details and try again.` | Anything else. | The first of those is the one to read carefully: it is **not** a connectivity problem. Your host, port, service name and password are all correct, and the refusal is about privileges. ### Certificates Dashies verifies your server's certificate and there is no opt-out on this engine. The driver Dashies uses hardcodes verification with no setting reaching it, so a connection created with verification disabled would carry a setting the extract could not honour. That would read to you as a working escape hatch while being nothing of the kind, which is why the form states the posture instead of offering a control. **If your server uses a private certificate authority, you have two routes, and they are mutually exclusive per connection.** 1. **Upload a PEM wallet.** The driver then uses that wallet as its whole trust store. 2. **Reach the server through a chain the Dashies image already trusts.** That is the stock public trust store for Autonomous Database, and the bundled Amazon roots for RDS. **Uploading a wallet replaces the trust store rather than adding to it.** Measured: the extra certificate authorities the image carries are honoured on the no-wallet path and are inert once a wallet is present. So a connection that needs both a private authority and a public one wants that authority inside the wallet, not beside it. ## What is different here **An Oracle Database dashboard is served.** Each refresh streams your cube over one Oracle Net session, writes the result to Dashies' own storage, and the page asks for what it needs when a reader opens it. That is the same path Postgres, BigQuery, Snowflake, Databricks and SQL Server are on. - **The refresh is asynchronous.** A newly published dashboard reads "Updating" until the first refresh lands. - **Every refresh reads the whole statement.** There is no incremental mode on this engine, so bound the window in your own SQL and anchor it to the data's own latest complete period. ## Dialect notes Cube SQL is Oracle SQL. ### Output aliases: write them however you like, and quote them if you want them lower case Oracle folds every unquoted identifier to upper case, including an output alias, so `sum(amount) as revenue` comes back as a column named `REVENUE`. Dashies matches that against the name your spec declares **ignoring letter case**, so `as revenue` and `as "revenue"` both work and both land under the name you declared. Quoting the alias in lower case is still a reasonable habit, because it makes the statement say what the dashboard will call the column: ```sql select s1 as "s1", cast(sum(amt) as number(18,4)) as "amt" from dash_owner.t_wide group by s1 ``` What Dashies cannot resolve is two output columns that differ only in case where your spec declares one of them, `select x as "amt", y as "AMT"`. That is refused with both spellings named, because picking one would be a coin flip between the right number and the wrong one. **Both aliases have to be QUOTED for that to happen**, which is why the example quotes them: an unquoted `as amt` folds to `AMT` like everything else, so `select x as amt, y as "AMT"` describes two columns both called `AMT` - not a case difference at all, and refused as an ordinary shape mismatch instead. :::note{title="This used to publish cleanly and then fail every refresh"} Until the September 2026 extractor generation, a dataset with unquoted aliases passed validation, passed the dry run, published, went live at a real URL, and then failed its first refresh and every refresh after it, with an error that named no column, no alias and no letter case. If you have a dashboard from before then that has never updated, **its next scheduled refresh should simply work** - the alias half needs nothing from you. Republish it only if it keeps failing, which would mean it is carrying the other half of the same generation: a stored column ORDER that did not come from your projection. ::: ### A number is text unless it carries a declared precision **What decides it is the precision the column has by the time Dashies describes it**, and there are three ordinary ways to have none. `sum(x)`, `avg(x)`, `min(x)`, `max(x)` and `count(*)` all describe as `NUMBER` with **no precision and no scale** - that is Oracle reporting the type of an expression rather than anything about the column underneath. **A column DECLARED plain `number` describes exactly the same way**, so this is not only about what you compute: `select amt as "amt" from sales.orders` over an `amt number` column hits it with no aggregate in the statement at all. And a `number(5,-2)` joins them, because its negative scale stores multiples of a hundred and no decimal with a non-negative scale holds that. In all three cases Dashies carries the value as exact text, so every digit survives the trip instead of going through a float. The stored column is then text, and a dashboard cannot add up text. So say what precision you want: ```sql select region as "region", cast(sum(amt) as number(18,4)) as "amt" from sales.orders group by region ``` **A column declared `number(12,2)` needs no cast** - it arrives with its precision intact and lands as a decimal. Without one, a measure that AGGREGATES the column - `sum`, `avg`, `min`, `max`, a percentile, a standard deviation - is refused at publish, before anything goes live, and the message opens > measure `amt` (`agg: sum`) needs a numeric column, but `amt` is `VARCHAR`. and goes on to tell you to cast it. **A `count` is the exception and is never refused**, because it counts rows and reads no column at all. Adding up a pre-computed `count(*)` column with `sum` is an ordinary aggregate, though, and does want the cast like any other. `number(18,4)` is a reasonable default: eighteen digits holds any total you are likely to have and four decimal places survives a rate or a unit price. Pick your own - what matters is that both numbers are there, because `cast(... as number)` with no precision is the same unsized type and changes nothing. ### A date arrives as a timestamp, and it is the same value on both sides An Oracle `DATE` carries a time of day, so Dashies carries it as a timestamp rather than truncating it, and the authoring sample and the served data agree. Bucketing the temporal in SQL is still the right shape for a date dimension, because a dashboard groups by a bucket rather than by an instant: ```sql select to_char(trunc(d, 'MM'), 'YYYY-MM') as "month", cast(sum(amt) as number(18,4)) as "amt" from sales.orders group by to_char(trunc(d, 'MM'), 'YYYY-MM') ``` ### An empty string IS null, and that is Oracle's own rule A row inserted as `''` and a row inserted as `null` are indistinguishable afterwards. `length` returns null for both, and `where v = ''` matches nothing. If you are porting a dashboard from an engine where the two are distinct, expect one group where you had two, and write `where v is null` rather than comparing against an empty string. ### Column order in the projection **Project your columns in whatever order the statement wants.** A first refresh used to be refused on the ORDER of the projection rather than on anything about the data, reporting that the cube's shape had moved when nothing had moved, and the workaround was to give every projected column a name of the same length and project them alphabetically. That is no longer needed and the advice is withdrawn rather than left standing. A shape refusal on a later refresh still means what it says: the cube really does describe different columns, or the same columns in a different order, from the ones the dashboard was published against. Republish it and the refusal clears. ### The rest - Quote identifiers with `"double quotes"`, doubling an embedded one. An unquoted identifier folds upper, and that applies to table and column names as well as aliases. - Cast anything you aggregate to a sized number - `cast(sum(x) as number(18,4))` - and read [why](#a-number-is-text-unless-it-carries-a-declared-precision) before you skip it. A column declared plain `number` needs it too. - Bucket a date with `trunc(d, 'MM')`, and read [the alias rule](#output-aliases-write-them-however-you-like-and-quote-them-if-you-want-them-lower-case) before you write the alias for it. - A relative window anchored to the data is `d >= add_months((select max(d) from sales.orders), -12)`. - Only the schemas you allowlisted are readable. ## Rotating the password Edit the data source and use the **Password** field. To replace an expiring wallet, upload the new `ewallet.pem` and its password the same way. ## Check it worked 1. The data source reads **active** on [dashies.ai/app/connections](https://dashies.ai/app/connections). 2. Ask your AI tool to introspect it and run one query: > Introspect my Oracle Database data source, then validate this cube SQL against it: > `select 1 as "ok" from dual` Introspection should list the tables of the schemas you imported. **An empty schema list is a failure, not an empty database**: it means the sign-in reached the server but the user cannot see your tables. Re-check the grants in section 2. 3. Then [author a dashboard against it](/guides/author-a-dashboard).