15.1 dblink_ora Functions and Procedures

Table of Contents Previous Next


15 dblink_ora : 15.1 dblink_ora Functions and Procedures

The dblink_ora_connect() function establishes a connection to an Oracle database with user-specified connection information. The function comes in two forms; the signature of the first form is:
dblink_ora_connect(conn_name, server_name, service_name, user_name, password, port, asDBA)
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 used to connect to the server.
password specifies the password associated with the user name.
port specifies the port number.
asDBA is True if you wish to request SYSDBA privileges on the Oracle server. This parameter is optional; if omitted, the default 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:
dblink_ora_connect(foreign_server_name, asDBA)
foreign_server_name specifies the name of a foreign server.
asDBA is True if you wish to request SYSDBA privileges on the Oracle server. This parameter is optional; if omitted, the default value is FALSE.
The second form of the dblink_ora_connect() function allows you to use the connection properties of a pre-defined foreign server when establishing a connection to the server.
Before invoking the second form of the dblink_ora_connect() function, use the CREATE SERVER command 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 command for the name of the link.
The second form of dblink_ora_connect() returns a TEXT value.
The dblink_ora_status() function returns the database connection status. The signature is:
conn_name specifies the name of the link.
The dblink_ora_disconnect() function closes a database connection. The signature is:
conn_name specifies the name of the link.
The dblink_ora_record() function retrieves information from a database. The signature is:
dblink_ora_record(conn_name, query_text)
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 dblink_ora_call() function executes a non-SELECT statement on an Oracle database and returns a result set. The signature is:
dblink_ora_call(conn_name, command, iterations)
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 dblink_ora_exec() procedure executes a DML or DDL statement in the remote database. The signature is:
conn_name specifies the name of the link.
command specifies the text of the INSERT, UPDATE, or DELETE SQL statement that will be invoked on the Oracle server.
dblink_ora_copy(conn_name, command, schema_name, table_name, truncate, count)
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 if the server should TRUNCATE the table prior to copying; specify TRUE to indicate that the server should TRUNCATE the table. truncate is optional; if omitted, the value is FALSE.
count instructs the server to report status information every n record, where n is the number specified. During the execution of the function, Advanced Server 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. count is optional; if omitted, the value is 0.

15 dblink_ora : 15.1 dblink_ora Functions and Procedures

Table of Contents Previous Next