[SQL Server] Summary of Oracle stored procedure execution through SQL linked server

Summary of Oracle stored procedure execution through SQL linked server
1. Example
We can use the following method to run the Oracle stored procedure in SQL Server through linked server.
(1) Oracle package
Package test_package
Type t_t is table of varchar2 (30)
Index by binary_integer;
Procedure test_procedure1
(P_batch_id in varchar2,
P_number in number,
P_msg out t_t,
P_msg1 out t_t
);
End test_package;
Package body test_package
Procedure test_procedure1
(P_batch_id in varchar2,
P_number in number,
P_msg out t_t,
P_msg1 out t_t
)
As
Begin
P_msg (1): = 'C ';
P_msg (2): = 'B ';
P_msg (3): = 'a ';
P_msg1 (1): = 'abc ';
Return;
Commit;
Exception
When others then
Rollback;
End test_procedure1;
End test_package;

(2) Using linked server in SQL server to execute the Oracle Stored Procedure

Declare @ batchid nvarchar (40)
Declare @ querystr nvarchar (1024)
Declare @ statuscode nvarchar (100)
Declare @ SQL nvarchar (1024)
Set @ batchid = 'aaa'
Set @ querystr = '{call GSN. test_package.test_procedure1 (''' + @ batchid + ''', ''' 4''', {resultset 3, p_msg}, {resultset 1, p_msg1 })}'

(3) execution result
()
Select @ SQL = 'select @ statuscode = p_msg from openquery (hi4db_ms, ''' + @ querystr + ''')'
Exec sp_executesql @ SQL, n' @ statuscode nvarchar (100) output', @ statuscode output
Print @ statuscode
Answer: @ statuscode = 'A'

(B)
Select @ SQL = 'select top 3 @ statuscode = p_msg from openquery (hi4db_ms, ''' + @ querystr + ''')'
Exec sp_executesql @ SQL, n' @ statuscode nvarchar (100) output', @ statuscode output
Print @ statuscode
Answer: @ statuscode = 'A'

(C)
Select @ SQL = 'select Top 2 @ statuscode = p_msg from openquery (hi4db_ms, ''' + @ querystr + ''')'
Exec sp_executesql @ SQL, n' @ statuscode nvarchar (100) output', @ statuscode output
Print @ statuscode

Answer: @ statuscode = 'B'
(D)
Select @ SQL = 'select top 1 @ statuscode = p_msg from openquery (hi4db_ms, ''' + @ querystr + ''')'
Exec sp_executesql @ SQL, n' @ statuscode nvarchar (100) output', @ statuscode output
Print @ statuscode
Answer: @ statuscode = 'C'

(E)
Set @ querystr = '{call GSN. test_package.test_procedure1 (''' + @ batchid + ''', ''' 4''', {resultset 1, p_msg1}, {resultset 3, p_msg})} '---- (note that the order of p_msg1 and p_msg is changed here)
Exec ('select p_msg1 from openquery (hi4db_ms, ''' + @ querystr + ''')')
Select @ SQL = 'select @ statuscode = p_msg1 from openquery (hi4db_ms, ''' + @ querystr + ''')'
Exec sp_executesql @ SQL, n' @ statuscode nvarchar (100) output', @ statuscode output
Print @ statuscode
Answer: @ statuscode = 'abc'

2. Conditions for using the preceding Method
(1) The link server uses Microsoft Driver (Microsoft ole db provider for Oracle)
(2) The return parameter of procedure in Oracle package is of the table type. Currently
Only one column is successful.
(3) When SQL Server's store procedure calls Oracle procedure, the return parameter name must be the same as procedure.

3. Key points of the above method
(1) To implement trans processing between Oracle and SQL Server databases, Oracle procedure should not have statements such as commit and rollback, let SQL Server store procedure control the entire trans commit.
(2) If there are more than one returned parameter, the order of the returned parameter can be changed. Only the first returned parameter is returned during the call, as shown in the preceding execution result (e ). However, the order of input and return parameters cannot be changed.
(3) {resultset N, p_msg1}. N indicates the number of rows in the returned table. N can be greater than or equal to the actual number of rows, but cannot be less than the actual number of rows. An error is returned.
(4) If the returned table has multiple rows of records, execute
Select @ SQL = 'select @ statuscode = p_msg from openquery (hi4db_ms, ''' + @ querystr + ''')'
Exec sp_executesql @ SQL, n' @ statuscode nvarchar (100) output', @ statuscode output
The value in print @ statuscode, @ statuscode is the value of the last record, such as the execution result ().

4. dynamic SQL statements
(1) Common SQL statements can be executed using Exec
Eg: Select * From mcity
Exec ('select * From mcity)
Sp_executesql n 'select * From tablename' -- note that N must be added before the string

(2) When field names, table names, database names, and so on are used as variables, dynamic SQL statements must be used.
Eg: declare @ fielsname varchar (20)
Declare @ sqls nvarchar (1000)
Set @ fielsname = 'city'
Select @ fielsname from mcity -- Error
Exec ('select' + @ fielsname + 'from mcity') -- Note that spaces must be added to the side of the single quotation mark before and after the plus sign.
Set @ sqls = 'select' + @ fielsname + 'from mcity'
Exec sp_executesql @ sqls
Of course, you can also change the string to a variable.
Declare @ s varchar (1000)
Set @ s = 'select' + @ fielsname + 'from mcity'
Exec (@ s) -- successful
Exec sp_executesql @ s -- this sentence will report an error
Declare @ s nvarchar (1000) -- Note that this is changed to nvarchar (1000)
Set @ s = 'select' + @ fname + 'from mcity'
Exec (@ s) -- successful
Exec sp_executesql @ s -- this sentence is correct

(3) Output Parameters
Eg: declare @ num int
Declare @ sqls nvarchar (1000)
Declare @ strtablename nvarchar (55)
Set @ strtablename = 'mcity'
Set @ sqls = 'select count (*) from '+ @ strtablename
Exec (@ sqls)
How can I save the exec execution result to the variable @ num?
Declare @ num int
Declare @ sqls nvarchar (1000)
Declare @ strtablename nvarchar (55)
Set @ strtablename = 'mcity'
Set @ sqls = 'select @ A = count (*) from' + @ strtablename
Exec sp_executesql @ sqls, n' @ A int output', @ num output
Select @ num

(Note: The above SQL test passed in sv-02, qservice .)

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/bugchen888/archive/2006/02/24/608152.aspx