dblink_ora supports the following functions and procedures:

dblink_ora_connect()

The dblink_ora_connect() function establishes a connection to an Oracle database with user-specified connection information. This function comes in two forms. The signature of the first form is as follows:

dblink_ora_connect(conn_name, server_name, service_name, user_name, password, port, asDBA)

where,

  • conn_name specifies the name of the link.
  • server_name specifies the name of the host.
  • service_name specifies the name of the service.
  • user_name specifies the name you use to connect to the server.
  • password specifies the password associated with the username.
  • port specifies the port number.

If you want to request SYSDBA permissions on the Oracle server, asDBA is True. This parameter is optional. If it is omitted, the value is FALSE.

The first form of dblink_ora_connect() returns a TEXT value.

The signature of the second form of the dblink_ora_connect() function is as follows:

dblink_ora_connect(foreign_server_name, asDBA)

where,

foreign_server_name specifies the name of a foreign server.

If you want to request SYSDBA permissions on the Oracle server, asDBA is True. This parameter is optional. If it is omitted, the value is FALSE.

The second form of the dblink_ora_connect() function allows you to use the connection properties of a predefined foreign server when you establish a connection to the server.

Before you invoke the second form of the dblink_ora_connect() function, use the CREATE SERVER statement to store the connection properties for the link to a system table. When you call the dblink_ora_connect() function, substitute the server name specified in the CREATE SERVER statement for the name of the link.

The second form of dblink_ora_connect() returns a TEXT value.

dblink_ora_status()

The dblink_ora_status() function returns the database connection status. The signature of dblink_ora_status() is as follows:

dblink_ora_status(conn_name)

where,

conn_name specifies the name of the link.

If the specified connection is active, the function returns a TEXT value of OK.

dblink_ora_disconnect()

The dblink_ora_disconnect() function closes a database connection. The signature of dblink_ora_disconnect() is as follows:

dblink_ora_disconnect(conn_name)

where,

conn_name specifies the name of the link.

The function returns a TEXT value.

dblink_ora_record()

The dblink_ora_record() function retrieves information from a database. The signature of dblink_ora_record() is as follows:

dblink_ora_record(conn_name, query_text)

where,

  • conn_name specifies the name of the link.
  • query_text specifies the text of the SQL SELECT statement that will be invoked on the Oracle server.

The function returns a SETOF record.

dblink_ora_call()

The dblink_ora_call() function executes a non-SELECT statement on an Oracle database and returns a result set. The signature of dblink_ora_call() is as follows:

dblink_ora_call(conn_name, command, iterations)

where,

  • conn_name specifies the name of the link.
  • command specifies the text of the SQL statement that will be invoked on the Oracle server.
  • iterations specifies the number of times the statement is executed.

The function returns a SETOF record.

dblink_ora_exec()

The dblink_ora_exec() procedure executes a DML or DDL statement in a remote database. The signature of dblink_ora_exec() is as follows:

dblink_ora_exec(conn_name, command)

where,

  • conn_name specifies the name of the link.
  • command specifies the text of the SQL INSERT, UPDATE, or DELETE statement that will be invoked on the Oracle server.

The function returns a VOID.

dblink_ora_copy()

The dblink_ora_copy() function copies an Oracle table to a table in a PolarDB for PostgreSQL(Compatible with Oracle). The dblink_ora_copy() function returns a BIGINT value that represents the number of rows copied. The signature of dblink_ora_copy() is as follows:

dblink_ora_copy(conn_name, command, schema_name, table_name, truncate, count)

where,

  • conn_name specifies the name of the link.
  • command specifies the text of the SQL SELECT statement that will be invoked on the Oracle server.
  • schema_name specifies the name of the target schema.
  • table_name specifies the name of the target table.
  • truncate specifies whether the server needs to truncate the table prior to copying. Specify TRUE to indicate that the server needs to truncate the table. This parameter is optional. If it is omitted, the value is FALSE.
  • count instructs the server to report status information every n records, where n is the number specified. During the execution of the function, the PolarDB for PostgreSQL(Compatible with Oracle) raises a notice of severity INFO with each iteration of the count. For example, if FeedbackCount is 10, dblink_ora_copy() raises a notice every 10 records. This parameter is optional. If it is omitted, the value is 0.