![[Aurora PostgreSQL] 拡張機能 orafce が利用できない場合の対処方法](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-7e1a533ff5b5a6a8eb5d4ae82bacdcd6/5845157a3c941213d245ec7cdf7358e8/amazon-aurora)
[Aurora PostgreSQL] 拡張機能 orafce が利用できない場合の対処方法
2025.04.08困っていた内容
Aurora PostgreSQL 16.4 で拡張機能 orafce を有効にしたのですが、Oracle 独自の関数を実行するとエラーが発生します。他にパラメータグループ等の設定が必要なのか教えてください。
回答
拡張機能 orafce の関数を利用するには、クエリ実行時にスキーマ oracle を明示的に指定するか、スキーマ検索パスを設定してください。
やってみた
- 作成した Aurora PostgeSQL のエンジンバージョンを確認します。
postgres=> select version();
version
-------------------------------------------------------------------------------------------------
PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (GCC) 9.5.0, 64-bit
- Aurora PostgeSQL 16.4 で利用可能な拡張機能を確認します。orafce も一覧の中に確認できました。
postgres=> select * from pg_available_extensions;
name | default_version | installed_version | comment
------------------------------+-----------------+-------------------+----------------------------------------------------------------------------------------
-----------------------------
hstore_plperl | 1.0 | | transform between hstore and plperl
bloom | 1.0 | | bloom access method - signature file based index
pg_prewarm | 1.2 | | prewarm relation data
tds_fdw | 2.0.3 | | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)
seg | 1.4 | | data type for representing line segments or floating-point intervals
pg_similarity | 1.0 | | support similarity queries
tcn | 1.0 | | Triggered change notifications
pglogical_origin | 1.0.0 | | Dummy extension for compatibility when upgrading from Postgres 9.4
pg_stat_statements | 1.10 | | track planning and execution statistics of all SQL statements executed
pltcl | 1.0 | | PL/Tcl procedural language
+ orafce | 4.10 | | Functions and operators that emulate a subset of functions and packages from the Oracle
RDBMS
aws_ml | 2.0 | | ml integration
rds_tools | 1.1 | | miscellaneous administrative functions for Aurora PostgreSQL
prefix | 1.2.0 | | Prefix Range module for PostgreSQL
plcoffee | 3.1.10 | | PL/CoffeeScript (v8) trusted procedural language
aws_lambda | 1.0 | | AWS Lambda integration
pg_ad_mapping | 1.0 | | provide Active Directory group to role mapping
babelfishpg_money | 1.1.0 | | babelfishpg_money
pg_hint_plan | 1.6.0 | |
tsm_system_rows | 1.0 | | TABLESAMPLE method which accepts number of rows as a limit
intagg | 1.1 | | integer aggregator and enumerator (obsolete)
h3_postgis | 4.1.3 | | H3 PostGIS integration
plprofiler | 4.2 | | server-side support for profiling PL/pgSQL functions
aurora_stat_utils | 1.0 | | Statistics utility functions
earthdistance | 1.1 | | calculate great-circle distances on the surface of the Earth
fuzzystrmatch | 1.2 | | determine similarities and distance between strings
aws_commons | 1.2 | | Common data types across AWS services
pg_repack | 1.5.0 | | Reorganize tables in PostgreSQL databases with minimal locks
aws_s3 | 1.2 | | AWS S3 extension for importing data from S3
pgstattuple | 1.5 | | show tuple-level statistics
pg_trgm | 1.6 | | text similarity measurement and index searching based on trigrams
dict_int | 1.0 | | text search dictionary template for integers
pg_buffercache | 1.4 | | examine the shared buffer cache
isn | 1.2 | | data types for international product numbering standards
old_snapshot | 1.0 | | utilities in support of old_snapshot_threshold
mysql_fdw | 1.2 | | Foreign data wrapper for querying a MySQL server
postgres_fdw | 1.1 | | foreign-data wrapper for remote PostgreSQL servers
refint | 1.0 | | functions for implementing referential integrity (obsolete)
h3 | 4.1.3 | | H3 bindings for PostgreSQL
pg_visibility | 1.2 | | examine the visibility map (VM) and page-level visibility info
pg_partman | 5.1.0 | | Extension to manage partitioned tables by time or ID
btree_gist | 1.7 | | support for indexing common datatypes in GiST
unaccent | 1.1 | | text search dictionary that removes accents
log_fdw | 1.3 | | foreign-data wrapper for Postgres log file access
pgtap | 1.3.3 | | Unit testing for PostgreSQL
plv8 | 3.1.10 | | PL/JavaScript (v8) trusted procedural language
apg_plan_mgmt | 2.7 | | Amazon Aurora with PostgreSQL compatibility Query Plan Management
postgis_raster | 3.4.2 | | PostGIS raster types and functions
vector | 0.7.3 | | vector data type and ivfflat and hnsw access methods
pgaudit | 16.0 | | provides auditing functionality
lo | 1.1 | | Large Object maintenance
address_standardizer_data_us | 3.4.2 | | Address Standardizer US dataset example
dblink | 1.2 | | connect to other PostgreSQL databases from within a database
tsm_system_time | 1.0 | | TABLESAMPLE method which accepts time in milliseconds as a limit
insert_username | 1.0 | | functions for tracking who changed a table
autoinc | 1.0 | | functions for autoincrementing fields
pgrowlocks | 1.2 | | show row-level locking information
amcheck | 1.3 | | functions for verifying relation integrity
pg_bigm | 1.2 | | text similarity measurement and index searching based on bigrams
bool_plperl | 1.0 | | transform between bool and plperl
pg_cron | 1.6 | | Job scheduler for PostgreSQL
postgis_tiger_geocoder | 3.4.2 | | PostGIS tiger geocoder and reverse geocoder
babelfishpg_common | 4.3.0 | | Transact SQL Datatype Support
cube | 1.5 | | data type for multidimensional cubes
babelfishpg_telemetry | 1.0.0 | | Transact SQL Telemetry
pg_tle | 1.4.0 | | Trusted Language Extensions for PostgreSQL
btree_gin | 1.3 | | support for indexing common datatypes in GIN
rdkit | 4.5.0 | | Cheminformatics functionality for PostgreSQL.
jsonb_plperl | 1.0 | | transform between jsonb and plperl
citext | 1.6 | | data type for case-insensitive character strings
rds_activity_stream | 1.7 | | RDS ACTIVITY Agent
oracle_fdw | 1.2 | | foreign data wrapper for Oracle access
postgis | 3.4.2 | | PostGIS geometry and geography spatial types and functions
sslinfo | 1.2 | | information about SSL certificates
pg_walinspect | 1.1 | | functions to inspect contents of PostgreSQL Write-Ahead Log
pgrouting | 3.6.2 | | pgRouting Extension
plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language
uuid-ossp | 1.1 | | generate universally unique identifiers (UUIDs)
pgcrypto | 1.3 | | cryptographic functions
ltree | 1.2 | | data type for hierarchical tree-like structures
babelfishpg_tds | 1.0.0 | | TDS protocol extension
address_standardizer | 3.4.2 | | Used to parse an address into constituent elements. Generally used to support geocoding
address normalization step.
intarray | 1.5 | | functions, operators, and index support for 1-D arrays of integers
plls | 3.1.10 | | PL/LiveScript (v8) trusted procedural language
hll | 2.18 | | type for storing hyperloglog data
babelfishpg_tsql | 4.3.0 | | Transact SQL compatibility
postgis_topology | 3.4.2 | | PostGIS topology spatial types and functions
moddatetime | 1.0 | | functions for tracking last modification time
plperl | 1.0 | | PL/Perl procedural language
pglogical | 2.4.4 | | PostgreSQL Logical Replication
dict_xsyn | 1.0 | | text search dictionary template for extended synonym processing
pg_proctab | 0.0.10 | | Access operating system process table
tablefunc | 1.0 | | functions that manipulate whole tables, including crosstab
pg_freespacemap | 1.2 | | examine the free space map (FSM)
hypopg | 1.4.1 | | Hypothetical indexes for PostgreSQL
hstore | 1.8 | | data type for storing sets of (key, value) pairs
ip4r | 2.4 | |
(97 rows)
- 現在利用している拡張機能を確認します。plpgsql のみ有効となっています。
postgres=> SELECT * FROM pg_extension;
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+---------+----------+--------------+----------------+------------+-----------+--------------
14501 | plpgsql | 10 | 11 | f | 1.0 | |
(1 row)
- 拡張機能 orafce を有効にする前に、スキーマ一覧を確認しておきます。
postgres=> \dn
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
(1 row)
- 拡張機能 orafce を有効にします。
postgres=> create extension orafce;
CREATE EXTENSION
- PostgreSQL の機能としては提供されていない Oracle の関数 NEXT_DAY を実行します。
postgres=> SELECT NEXT_DAY('15-OCT-2009','TUESDAY');
ERROR: function next_day(unknown, unknown) does not exist
LINE 1: SELECT NEXT_DAY('15-OCT-2009','TUESDAY');
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
拡張機能 orafce 有効後でも、エラーが発生しました。
- 再度、スキーマ一覧を確認してみます。
postgres=> \dn
List of schemas
Name | Owner
--------------+-------------------
dbms_alert | rdsadmin
dbms_assert | rdsadmin
dbms_output | rdsadmin
dbms_pipe | rdsadmin
dbms_random | rdsadmin
dbms_sql | rdsadmin
dbms_utility | rdsadmin
oracle | rdsadmin
plunit | rdsadmin
plvchr | rdsadmin
plvdate | rdsadmin
plvlex | rdsadmin
plvstr | rdsadmin
plvsubst | rdsadmin
public | pg_database_owner
utl_file | rdsadmin
(16 rows)
create extension orafce; を実行する前と比べて多くのスキーマが作成されています。
- スキーマ oracle 内の関数を実行する為に、明示的にスキーマ oracle を指定して関数 NEXT_DAY を実行してみます。
postgres=> SELECT oracle.NEXT_DAY('15-OCT-2009','TUESDAY');
next_day
---------------------
2009-10-20 00:00:00
(1 row)
スキーマを明示的に指定したことで、関数 NEXT_DAY を実行できました。
- SQL を実行する度にスキーマ名で修飾する必要が無いようにスキーマ検索パスを設定します。
postgres=> set search_path to "$user",public,oracle;
SET
postgres=> SHOW search_path;
search_path
-------------------------
"$user", public, oracle
(1 row)
スキーマ検索パスを設定したことで orafce 有効後に失敗したクエリも成功するようになりました。
postgres=> SELECT NEXT_DAY('15-OCT-2009','TUESDAY');
next_day
---------------------
2009-10-20 00:00:00
(1 row)
まとめ
この記事がどなたかのお役に立てば幸いです。
![[アップデート] Amazon Bedrock で Pixtral Large 25.02 が利用可能になりました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-e3065182082062711612153bbdcf1d96/c04359de689df2f56eb066576ab63fb5/amazon-bedrock)
![[アップデート] Amazon ElastiCache for Memcached がスケールアップをサポートしました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-c7d483f10fc34f2be56f1a37c8fc65f7/8d3a05910fb22a9bc4727b93c25b846d/amazon-elasticache)
