However, when I tried OPENQUERY next day. I was so disappointed. What I found is that the query passed to OPENQUERY() must be a literal string, no variable or expression is supported. That means I cannot build a query string dynamically with different constrains. This greatly limits the usage of OPENQUERY. I just cannot use it in my projects.
Fortunately, I found another pass-through way to leverage Oracle's power to query data, and it is possible to pass result back. I spent about one and half day to figure it out. I posted several questions to StackOverflow web site but I did not get any answer there. Eventually I got the solution through Google searching. I posed my answer to StackOverflow as well.
The method is to use "EXEC (...) AT ..." to the job:
EXEC (@sql) AT linedOracleServer;Actually, there are some good examples of using this method. However, all those examples are using either Microsoft SQL server or Express SQL server. By using pass-through query, the syntax of the query is very different from Microsoft SQL server and Oracle SQL server. That's why it took me a while to figure it out.
I can build an Oracle SQL query to combine all the inputs into a query string. That's an easy part. The most challenge issue is to pass results as output parameters. For example, I need to get total count of rows in an Oracle table with WHERE clause, and pass the result out to my SQL server.
Here is what I did:
DECLARE @sql NVARCHAR(MAX);
DECLARE @myCount INT;
-- Initialize setting
SET @myCount = NULL;
-- Build sql query
SET @sql = N'
BEGIN
SELECT COUNT(*) INTO :myCount
FROM owner.myTable
WHERE id = ' + CAST(@id AS VARCHAR) + N';
END;';
EXEC (@sql, @myCount OUTPUT) AT linedOracleServer;
IF (@myCount IS NOT NULL)
BEGIN
PRINT 'I get the count from my Oracle table: ' +
CAST(@myCount AS VARCHAR);
END
ELSE
BEGIN
PRINT 'It must be an query syntax error';
END
The key points to build an Oracle query with an output parameter is to follow these steps:
- making sure the query being a valid Oracle PL/SQL query such as SELECT ... INTO ... to assign result to a variable or parameter;
- using ":" before a parameter name;
- using an anonymous block (BEGIN...END) to wrap the query; and
- Appending ";" at the "END" block.
One lesson I learned from this practice is that exceptions raised by "EXE () AT" may provide misleading error messages. For example, when I used "@" for parameter or missed ";" after END. I got somethink like "illegal characters in expression". You have to open your view in all different angles.
Today Ajaxian posted 
Today I watched a video on 
Recently I have been working a project to extract data from a huge database server for a daily reporting service. The process generates data in a required format based a complicated business logic.
Posts
