James Wiseman

I discovered this little trick today.

If you’re wanting to count the number of characters present in a given SQL Server varchar variable, or common, use the following convention

--assuming a search character contained in the variable @SeekChar

--from a variable
SELECT LEN(@StringVariableName) - LEN(REPLACE(@StringVariableName,@SeekChar,''))

--from a table field
SELECT LEN(SomeTableCol) - LEN(REPLACE(SomeTableCol, @SeekChar, ''))
FROM SomeTable

--The Oracle  PL/SQL Variant (for the hell of it).
--All we have to do is replace the LEN function with LENGTH!
SELECT LENTH(SomeTableCol)-LENGTH(REPLACE(SomeTableCol, @SeekChar, ''))
FROM SomeTable

Quick and easy and no annoying looping constructs.

Been a bit quiet of late, so here’s something for the notepad to kick off new year.

Upon compiling a custom type into an Oracle schema, I was left with an  ”Invalid Object” error message when attempting to use it.

Error: PLS-00905: object MY_SCHEMA.MY_TYPE is invalid

Line: 245Text: PROCEDURE p_my_proc(pt_my_param IN my_type);

The type declaration itslef had seemingly compiled without an issues, so I was a little stumped as to what the problem was.

Some brief enquiries led me in the direction of the user_errors table:

select * from user_errors

And, from that I was able to determine the cause of my problems (which are actually too boring to post here).

I’ve been playing around with SSRS in SQL Server 2005 recently, and have been particularly encouraged by its potential as a central reporting tool. As part of this  I have been demonstrating how we can SSRS to produce reports from numerous different data sources and providers.

In this particular instance I have been testing its ability to produce reports from the output of an Oracle Package/Procedure and a SQL Server Stored Procedure.  Using the built-in .NET Client I was able to do this quite successfully with little fuss.

It was then brought to my attention that Microsoft are deprecating Oracle Client from ADO.NET 4. This means that, while the code may continue working for some considerable time, going forward it is a better strategy to use an alternative. So, I switched to using Oracle 11g Release 2 ODAC 11.2.0.1.2.

It’s worth noting that for those wanting to utilise an Oracle Stored procedure in SSRS, it must return its data in an OUT REF CURSOR. There are also a few other conditions which you must abide by.  For further info see: Tap into Oracle Databases with SQL Server Reporting Services.

Anyway, on to the problem…

So, as already intimated, all was fine and dandy, until I switched provider. I had set up the dataset on my report, entered the Stored Procedure name, and clicked ‘Ok’. Nothing happened; the IDE just hung.

At this point, I should probably reproduce the procedure in question:

PROCEDURE pck_mypackage.pr_myproc(L_CURSOR out SYS_REFCURSOR)
is
   BEGIN
     OPEN L_CURSOR FOR
       SELECT * FROM MyTable;
   END;

And here the connection string (missing the credentials).

Provider=OraOLEDB.Oracle.1;Data Source=DBSOURCE

Nothing shocking whatsoever, right?

For the first day or so, I took to killing Visual Studio, tweaking a few settings and then trying again. No-one in the websphere seemed to have encountered this; I was baffled. I even resorted to Window’s Process Monitor and trawling through the 1 million+ events it had logged.

So, I decided to confide in my old friend, the bullet-point list. I regaled it with the details of what was working, and what was not. Here it is, replete with the pertinent information:

What Works?

• The stored procedure executes correctly in PL/SQL Developer
• The ‘test connect’ in works fine in SSRS
• A query string of SELECT * FROM MyTable; with Command Type of ‘text’ produces the correct fields in the SSRS report.
• .NET Oracle Provider instead of Oracle OLE DB Provider

What Fails:

• If i change the Command Type to ‘Stored Procedure’ and enter ‘pr_myproc’, when I click ‘OK’ Visual Studio 2005 (service pack 2) simply hangs/crashes.

My attention was drawn to the last three words, “simply hangs/crashes.” It was then I noticed that Visual Studio was not actually hanging completely. I could still click the top-right window buttons; it just seemed to be waiting.

I confided in a friendly DBA, who suggested I looked at the Oracle sessions in an attempt to determine:

1. Whether or not a connection was actually being made, and,
2. What it was doing if the connection was successful

Something must have piqued his interest, because he decided to do it there for me himself.

“Ahhh,” he said, “you’re querying sys.obj$.” He then proceeded to explain to me why exactly this was an issue. The schema we were searching had substantially large data dictionary. This dictionary has to be queried in order to bring back the procedure parameters and the REFCURSOR output.  When looking up the information, the two providers (.NET and Oracle) used two different queries that had a dramatic relative difference in their performances.

Here is the one the Oracle Provider used, taking 10+ minutes:

select * from (select null  PROCEDURE_CATALOG
                      , owner PROCEDURE_SCHEMA
                      , object_name PROCEDURE_NAME
                      , decode (object_type, 'PROCEDURE', 2, 'FUNCTION',  3, 1) PROCEDURE_TYPE
                      , null PROCEDURE_DEFINITION
                      , null DESCRIPTION
                      , created DATE_CREATED
                      , last_ddl_time DATE_MODIFIED
              from all_objects where object_type in ('PROCEDURE','FUNCTION')
              union all
              select null PROCEDURE_CATALOG
                          , arg.owner PROCEDURE_SCHEMA
                          , arg.package_name||'.'||arg.object_name PROCEDURE_NAME
                          , decode(min(arg.position), 0, 3, 2) PROCEDURE_TYPE
                          , null PROCEDURE_DEFINITION
                          , decode(arg.overload, '', '', 'OVERLOAD') DESCRIPTION
                          , min(obj.created) DATE_CREATED
                          , max(obj.last_ddl_time) DATE_MODIFIED
              from all_objects obj, all_arguments arg
              where arg.package_name is not null
              and   arg.owner = obj.owner
              and   arg.object_id = obj.object_id
              group by arg.owner, arg.package_name, arg.object_name, arg.overload ) PROCEDURES
WHERE PROCEDURE_NAME = 'pck_my_package.pr_myproc' order by 2, 3

The second SQL Statement in the union is a particular killer. Here, it joins all_objects and all_arguments tables! I actually ran this query in PL/SQL developer, but gave up after it failed to finish in thirty minutes.

This a particular problem of those schemas with large data dictionaries. I tried this in a smaller one, further up our shipping chain, and it returned in an acceptable amount of time.

Dynamic SQL is, by its nature, very flexible and often very useful. However, it is this flexibility that can be both a blessing and a curse.

The offical Oracle recommendations on Static vs Dynamic SQL state that, in practice, “static SQL will meet nearly all your programming needs.”

They go on to give a number of conditions under which they deem it acceptable for you to use dynamic SQL. They suggest it is used when one or more of the following items is unknown at precompile time:

• text of the SQL statement (commands, clauses, and so on)
• the number of host variables
• the datatypes of host variables
• references to database objects such as columns, indexes, sequences, tables, usernames, and views

Although this may be true, I would prefer to add the caveat “Only if you absolutely cannot find a way to do it in Static SQL”

They also state:

However, some dynamic queries require complex coding, the use of special data structures, and more runtime processing. While you might not notice the added processing time, you might find the coding difficult unless you fully understand dynamic SQL concepts and methods.

So rather than have a long and tedious discussion as to the relative merits, lets just list the advantages and disadvantages in a series of bullet points.

Advantages

• It gives flexibility and scalability
• It can reduce the number of lines of code written

Disadvantages

• It can become very complex and difficult to read. Think about quotes embedded in quotes, and other such things.
• It can have a detrimental effect on code stability. Some Dynamic SQL errors will not be known until run time. (An example of this is where you reference a non-existent table)
• Dynamic SQL code is harder to test than the equivalent static SQL. It may also be impossible to test for every possible circumstance that your Dynamic SQL will encounter, thus introducing inherent risk.
• It will be more difficult to conduct an effective impact analysis on Dynamic SQL in your code-base.
• SQL injection and misuse – Dynamic SQL is more prone to misuse, and is invariably less safe than static SQL
• The queries code within Dynamic SQL is not subject to a query plan, and as such optimisations may be missed. As such, it can be slower than the equivalent static SQL
• As the SQL query is not known until runtime, it can be harder to performance-tune SQL Dynamic code (for example, determining the indexes that might be required on a table)

So there we are. Take that and pin in on a wall somewhere

When it boils down to it, is there any difference really?

Not according to Senior Systems Engineer, Steven W. Pavick. He was quoted as saying:

A fighter pilot is merely like a truck driver delivering a package to someone who doesn’t want it but they’re going to receive it anyway

That’s nice to know!

This was brought to my attention today, and made for interesting reading:

Oracle Database 11g vs. Microsoft SQL Server 2008

The report claims in its ‘Executive Summary’ that Oracle 11g holds a substantial advantage over SQL Server, and alleges the following high level points:

• Oracle is faster for DBAs to perform administrative functions
• Oracle has fewer steps for standards RDBMS tasks
• Increased Oracle DBA productivity due to lower complexity and higher efficiency
• Oracle has architectural and functional capabilities for backup and recovery beyond those offered by SQL Server
• Oracle demonstrates significant time savings with regard to performance diagnostics and tuning

Later in the Rationale section, however is the following statement:

Oracle has sponsored the studies in response to a challenge made by Edison to Oracle claims that Oracle Database was significantly easier and thus less costly to manage than the other major database management systems.

Which, of course, makes me suspicious. Regardless of Edison’s so-called ‘challenge’, the report is still sponsored by Oracle (and published on their web site), and I would have been surprised if this came back in SQL Server’s favour.

Predictably, Microsoft has published a response denouncing these claims. It alleges the following:

• The report has unverifiable metrics and inconsistent interpretation of results
• The report is not based on ‘real-world; data
• SQL Licensing Costs are cheaper
• Oracle databases consistently suffer from more security vulnerabilities
• Security patching not reflected in true cost of operating Oracle 11g

I’ll not pass comment, but thought it interesting to share!