Procedures in Oracle Server and SQL Server

Recently I have been working on a project to provide data for a third party application, called ParcView. The PV is used to view data source from OPC and databases. The requirement of one data source is from Oracle database.

PV provides a system configuration of SQL templates for data source in data, current, tag list and tag information, four major areas (some others are rarely used).  I have done a lots for SQL database server, and some of Oracle database. Based on the recommendation, if the template SQL scripts are too complicated, it is recommended to use stored procedures as a way to provide data.

In SQL SP, data source can be directly returned by using SELECT statement.  However, in Oracle, this type of return is not available.  What I found is that all the data row set has to be returned by a OUT parameter SYS_REFCURSOR type. This makes it is impossible for me to implement in the same way as I did for SQL server before.

I have tried another way, view with parameters. Basically, a package is defined with functions and procedures. They are in a structure of property: get in function and set in procedure.  A local variable in the package as a storage for property values.  Then the view references to the functions are parameter.  Before calling the view, a PL/SQL command exec is called first.  This strategy is back to he none-SQL query problem.  PV does not have a way to call it. As a result, this strategy hits the dead wall again.

It is really hard for me to put scripts on database server side. The lesson I learned is that the PV should problem a kind of interface based APIs to allow plugin components.  Now I recall that the provider of PV does have a way to add customized data source, but we have to ask the company to write codes to do it. They will charge for the service.

Maybe I should ask the computer to provide a component to allow customized plugin to provide data source.

Read More...

Collapse...

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...