SQLPlus Debug and VIM Find/Replace

Yesterday I was asked to help to resolve some issues for a piece of PL/SQL codes, which was provided by our client support. I was working on only error issues, nothing related to its logic. I spent about 2 hours and finally almost got the script running OK (it was too late in the afternoon). Here are two notes about the work.

First, I needed to debug PL/SQL procedure codes. We were using SQL*Plus command console. For SQLPlus command codes; I saw that command prompt is used to print out messages, but I this command does not work in a block of procedure codes. Quickly I found the command which can be used for PL/SQL procedure debug. It is actually a very simple one, and I used this one long time ago. Here is my note on this again:

SET SERVEROUTPUT ON
dbms_sql.output.PUT_LINE('message');
...
SET SERVEROUTPUT OFF

The second note is about using VIM to add the about debug command dbms_sql.output.PUT_LINE in to codes. The reason I wanted to use VIM was that a block of similar codes are in a repeatedly pattern (the following codes are mock and simplified ones):

strDDL := 'create synonum ' || '&username' || '.AUsers for ' || '&newUser' || '.AUsers';
nCID := dbms_sql.open_cursor;
dbms_sql.parse(nCID, strDDL, dbms_sql.v7);
nCount := dbms_sql.execute(nCID);
dbms_sql.close_cursor(nCID);


strDDL := 'create synonum ' || '&username' || '.BUsers for ' || '&newUser' || '.BUsers';
nCID := dbms_sql.open_cursor;
dbms_sql.parse(nCID, strDDL, dbms_sql.v7);
nCount := dbms_sql.execute(nCID);
dbms_sql.close_cursor(nCID);

...

I did not want to manually type in the debug commands after the first one in each block. That's too tedious. I decided to use my gVIM (for Windows), using its power to get my codes just in one Find/Replace command. This can be done by grouping feature in Find/Replace command, since I wanted to use partial codes in the first line. I figured out the following VIM command:

:%s\(strDDL :=\)\(.*\);\n\(.*\n.*\n.*\n.*\)/\1\2;\rdbms_output.PUT_LINE(\2);\r\3/g

Notes on above command:

• Replace command: %s/find/replace/g where g is for all.
• In find section, use \(...\) to mark a group. In my above command, there are 3 groups and the 2nd ad 3rd groups will be reused in replacement section.
• The group name used in replace section is reference by \#, such as \2 and \3.
• The line break in find section is \n
• The line break in replace section is \r

This is the result:

Read More...

Collapse...

Using EXEC() AT with Oracle DB

My previous blog was about using OPENQUERY() method with an Oracle DB. The advantage of using this method call is to pass through a query to a linked server such as Oracle server and to leverage the Oracle server to do the job. The great benefit of this strategy is to obtain much fast performance compared to using a T-SQL query directly against an Oracle database object locally at SQL server.

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.

Read More...

Collapse...

Using OPENQUERY for Oracle DB

Today I googled an alternative solution to run an Oracle PL/SQL query or stored procedure from Microsoft SQL server 2005. I am very impressed by the result of this alternative way.

What I mean by an alternative way is that I have been using T-SQL queries directly against linked servers defined in Microsoft SQL server. A linked server is a server added to Server Objects->Linked Servers by using Microsoft SQL Sever Management Studio tool.



The linked server that Microsoft SQL server supports covers a variety of database servers, including Oracle server. By using T-SQL queries, you can easily query data from to any database object such as a table or view on this server in the same way as querying data from a local table or view, as in the following example:

SELECT COUNT(*) AS MyCount 
  FROM myOracleServer..owner.tableName 
  WHERE ...;

where myOracleServer could be a linked server to an Oracle server.

One problem I troubles me is that when a table on Oracle side is very big(with millions of rows of data), the query may take a while to execute, sometimes more than 1 minute just for a query call. I had many cases to take more than 10 hours to update data from my Microsoft SQL server to an Oracle server with thousands of T-SQL calls(checking, updating and inserting data).

The alternative way I found today is to use OPENQUERY call like this:

SELECT MyCount 
  FROM OPENQUERY(myOracleServer,
    N'SELECT COUNT(*) AS MyCount FROM owner.tableName WHERE ...');

The execution time is 00:00:00 comparing 00:01:09 with the similar direct T-SQL query as above. It was a stunning when I saw the result back right away.

The information I found is from a discussion on Bytes web forum, where one person responded a recommendation by using OPENQUERY in a Google Groups' discussion. Then I figured out the solution that I need to improve my current T-SQLs.

The reason for such a big difference, I think o as my understanding, is that the SQL server may create a temporary table or allocate a cache for the database object referenced by a linked server in a T-SQL' query, while OPENQUERY is just a OLE DB connection call to the linked server with a SQL query by which the server or Oracle does its job with its full power.

Read More...

Collapse...

Connect to Oracle Dadabase by OracleConnection

I installed Oracle 10g XE in my box, and then Oracle Developer Tools for .Net (ODTwithODAC1020221). After the installation, I started to work on .Net C# project against Oracle DB.

The first thing I have to do is to build a connection. At the start, I got exception when I tried to open an OracleConnection to userID=HR, Data Source=XE:

ORA-12154: TNS:could not resolve the connect identifier specified

Without connection, I cannot continue to do anything about the Oracle DB. After one day struggle, I finally found the reason from Oracle Developer Tools Forum: the home directory for Tools Kit is different from the Oracle db home directory! I have to follow the instruction to copy Oracle's TNSNAMES.ORA to the Tools' home directory. After that, the problem is solved!

Read More...

Collapse...