Database Administrators may be accustomed to some SQL Plus Commands which may work when used in the Oracle SQL Plus Client (CLI: sqlplus); however SQL Plus Commands are not supported in Vault which may include commands such: EXECUTE ... or RUN ...; See the Oracle documentation for a complete list of SQL*Plus Commands.
It's worth noting that Vault Oracle Plugin uses the GoLang library: go-oci8 with dependencies to Oracle Client SDK that's linked in the binary build of the plugin.
If you're observing responses prefixed with ORA- followed by some number (eg: ORA-00911) in the Vault system or Audit logs then it's directly from the Oracle Database instance that's already configured in Vault and in these cases the correlating (debug) logs from the Oracle Database itself would be worth reviewing. For a complete list of ORA codes and potential issues refer to your Oracle Documentation - eg:
In most cases it's worth pursuing the Oracle Database directly with a DBA for misconfiguration or any other related privileges that are needed.
In some cases the default revocation statement is working as expected but using a more complex SQL revocation_statements you're faced with an ORA response when these revocations are performed; this is typically indicative of a bad quote, missed or bad characters or use of SQL Plus Commands that are not supported.
In other cases your SQL statement may be rather long and or complex which is better fitted onto its own file such as:
cat oracle_create.sql
# ...
# // multi-line SQL syntax properly terminated.
vault write .../roles/my-role-name db_name=... \
default_ttl="1h" max_ttl="24h" \
creation_statements=@oracle_create.sql \
revocation_statements=@oracle_revoke.sql
The parameters: creation_statements, rotation_statements and revocation_statements are stored in Vault as Base64 encoded JSON arrays and it may also be used to test any character encoding issues that's brought about with the use of dollar signs ($) in the wrong quote types resulting in bad escaping or unwanted evaluation in the used CLI shells as opposed to intended SQL references.
The dollar sign uses in SQL syntax are reserved for identifiers (see Oracle documentation).
select count(*) into restoreexist from V\$RESTORE_POINT where NAME='GOOD';
In the above case if the dollar symbol is not escaped then it can result in a reference to a environment variable instead. Especially in the case of double quotes ("...") and where no back-slash (\) is used prior to the dollar the resulting statement may be made invalid.
For a detailed explanation and example of Bas64 encoding of Databases SQL statements in Vault please see the article: