Systems Engineering and RDBMS

Archive for July 11th, 2009

sys.dm_db_persisted_sku_features

Posted by decipherinfosys on July 11, 2009

SQL Server 2008 has a new DMV which you might need if you run into an issue similar to what we ran into last week.  At one of our client sites, they were using CDC and data compression features in SQL Server 2008.  When they tried to restore the database in their development environment, the restore operation failed.  The reason is that for certain features in SQL Server 2008 which are only supported in the Enterprise Edition, if you are using those features in your environment and you try to restore a backup of such a database on an instance that does not support those features, you will not be able to do so.  This is written in BOL as well:

Some features of the SQL Server Database Engine change the way that Database Engine stores information in the database files. These features are restricted to specific editions of SQL Server. A database that contains these features cannot be moved to an edition of SQL Server that does not support them Use the sys.dm_db_persisted_sku_features dynamic management view to list all edition-specific features that are enabled in the current database.

There are four features that fall into this category: CDC (Change Data Capture), Data Compression, TDE (Transparent Data Encryption) and partitioning.  It actually was the same issue in SQL Server 2005 as well when you would have used the partitioning feature.  In such scenarios, you can use the above mentioned DMV to see whether you are using any of those four features.

Posted in SQL Server | Leave a Comment »

Back to the basics: Explicit & Implicit cursors

Posted by decipherinfosys on July 11, 2009

Today in this blog post, we will re-visit most fundamental construct used in PL/SQL programming called “cursors”. Cursors are nothing but the private SQL area in the memory. Cursors provide mechanism to work on individual records by looping through it when more than one records are fetched from the database.

They are divided in two categories:
•    Implicit cursors
•    Explicit cursors

Implicit cursors:

Implicit cursors are also known as SQL Cursors. Oracle opens implicit cursors for every DML (Insert/Update/Delete) statement. It also opens implicit cursor for the ‘select’ statement, which returns exactly one row.  With implicit cursors we don’t have to worry about opening and closing the cursors and fetching the data from the cursors. Oracle handles it automatically so we don’t have to write code to handle it. But there are certain cursor attributes available through which we can track the information about execution of the implicit cursors. Cursor attributes are

•    %FOUND (Returns True/False)
•    %NOTFOUND (Returns True/False)
•    %ISOPEN (Always returns False in case of implicit cursors as cursor is closed internally immediately after execution)
•    %ROWCOUNT (# of records effected by DML or SELECT statement)

Let us start with an example to see how we can track cursor execution information using these attributes. First of all we will create test table to work with.

SQL> CREATE TABLE TEST AS SELECT * FROM USER_OBJECTS;

Now we will write small PL/SQL block that has select statement and one DML statement as well to check the attributes of the implicit cursor.

SET SERVEROUTPUT ON;

DECLARE
v_object_name test.object_name%type;

BEGIN

IF SQL%FOUND IS NULL AND SQL%NOTFOUND IS NULL THEN
dbms_output.put_line(‘Attribute values are NULL’);
END IF;

—–SELECT
SELECT object_name
INTO v_object_name
FROM test
WHERE object_Type = ‘TRIGGER';

dbms_output.put_line(‘SELECT RowCount = ‘ || SQL%ROWCOUNT);

—— DELETE statement
DELETE FROM TEST WHERE OBJECT_TYPE = ‘VIEW';
IF SQL%FOUND THEN
dbms_output.put_line(‘DELETE RowCount = ‘ || SQL%ROWCOUNT);
END IF;

—— UPDATE STATEMENT
UPDATE TEST
SET STATUS = ‘INVALID’
WHERE object_name =’ HELLO';

IF SQL%NOTFOUND THEN
dbms_output.put_line(‘UPDATE RowCount = ‘ || SQL%ROWCOUNT);
END IF;

END;
/

Here is the output of the execution.

Attribute values are NULL
SELECT RowCount = 1
DELETE RowCount = 4
UPDATE RowCount = 0

PL/SQL procedure successfully completed.

Above example, gives the results for all the cursor attributes except %isopen. Notice the first line of output. %found and %notfound attributes has null value before the execution of the statement. If dml statement effects one or more than one rows then %found is true otherwise it is false. Similarly if dml statement doesn’t effect any rows, %notfound is true else false.

Apart from these regular attributes, implicit cursor has one more attribute SQL%BULK_ROWCOUNT when we are using bulk collect to fetch multiple records. We have covered it in our previous blog post http://decipherinfosys.wordpress.com/2007/10/12/bulk-collect-and-rowcount-sqlbulk_rowcount/

Explicit cursors:

When user has to work on specific record for processing, he/she has to define explicit cursor. Cursor is called explicit when user, names the cursor and associate it with query. This is the very first step when dealing with explicit cursors. It is known as defining or declaring a cursor. Next three phases are

•    Opening a cursor (This will initialize the cursor and identifies the result set.)
•    Fetching data from a cursor (This will retrieve the result set for the executed query)
•    Closing a cursor (Once execution is done, disable the cursor)

Explicit cursors also have the same attributes as implicit cursors but when used they are precede with the cursor name. Let us see it with an example now.

SET SERVEROUTPUT ON;
DECLARE
v_object_name test.object_name%type;
v_status test.status%type;
v_opn_stat VARCHAR2(5);

CURSOR c1 IS
SELECT object_name,status
FROM test
WHERE object_Type = ‘VIEW';
BEGIN

OPEN c1;
IF C1%ISOPEN = TRUE THEN
v_opn_stat := ‘TRUE';
ELSE
v_opn_stat := ‘FALSE';
END IF;

dbms_output.put_line(‘Cursor Open Status = ‘ || v_opn_stat);

IF C1%FOUND IS NULL AND C1%NOTFOUND IS NULL THEN
dbms_output.put_line(‘Attribute value is NULL’);
END IF;

LOOP
FETCH c1 INTO v_object_name,v_status;

EXIT WHEN C1%NOTFOUND;

dbms_output.put_line(‘Fetch RowCount = ‘ || C1%ROWCOUNT);

END LOOP;

CLOSE C1;

END;
/

In above PL/SQL block, we have covered the attributes of the explicit cursor. %found and %notfound condition gives an error ‘invalid cursor’ if they are used before opening  a cursor. Since Boolean variable can’t be used in dbms_output,put_line procedure, we are assigning the value to the varchar variable based on the condition.

One thing to remember that cursor attributes always correspond to the last SELECT or last DML statement executed. If two or more DML statements are fired back to back and user is checking the cursor attribute, it will retain results only for the last statement. If we need to check the cursor attribute for the first statement or any interim statement later in the code, we need to assign it to temporary variable and later on use the temporary variable for checking.

One can also pass parameter to the cursor and use the parameter values in the query rather than passing static values.  We will cover it in more detail in our future blog post.

Resources:

  • Articles: – here and here.
  • Oracle documentation – here.

Posted in Oracle | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 78 other followers