Systems Engineering and RDBMS

Archive for September, 2007

DBMS_OUTPUT enhancement in Oracle 10g

Posted by decipherinfosys on September 18, 2007

DBMS_OUTPUT is a widely used package in PL/SQL development.  At times, it is used for effective error logging and displaying the error messages on the screen and debugging as well in the absence of GUI development tools.

Prior to 10g R2, two procedures PUT and PUT_LINE were restricted by length of 255 bytes. Similarly buffer limit was also topped at 1,000,000 bytes. If line limit or buffer limit exceeds the pre-defined limit, Oracle used to throw an error.   The only workaround when dealing with strings more than 255 bytes, was to substring it and then display part of the string individually. Starting with 10g R2, Oracle lifted these restrictions. Line limit is extended to 32767 bytes from 255 bytes. Also, by default, buffer limit is set to unlimited. When ‘set serveroutput on’ statement is issued without any size specification or with size specification of unlimited, there is no upper limit.

Connect to SQL*Plus with proper credentials and run the following query.

DECLARE
v_Str1 VARCHAR2(300);
BEGIN
v_Str1 := lpad(‘ ‘,300,’A’);
DBMS_OUTPUT.PUT_LINE(v_Str1);
END;
/

Query will display 300 bytes without any error. Make sure that ‘SET SERVEROUTPUT ON’ command is issued prior to running the PL/SQL block. You can also check the current SERVEROUTPUT option in effect by issuing ‘SHOW SERVEROUTPUT’ command. Following is the result and command.

SQL> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

Even though it looks very trivial, it helps during development stages as we don’t have to perform string manipulations in order to display it correctly.

Posted in Oracle | 1 Comment »

TRY…CATCH functionality in SQL Server 2005

Posted by decipherinfosys on September 17, 2007

In SQLServer 2005, Microsoft introduced new construct of TRY-CATCH block for effective error handling. This is similar to Oracle’s syntax of handling exceptions in PL/SQL by using BEGIN..EXCEPTION..END block. And this is very similar to the exception handling features of Microsoft Visual C++ and C# languages. Using TRY-CATCH block, we can make code more modular and can handle errors more precisely for a set of statements.

Here is a small code snippet which demonstrates how we can use this new functionality:

CREATE TABLE dbo.Test
( TEST_ID INT NOT NULL,
TEST_DESC VARCHAR(10) NOT NULL
)

We will now try to insert only the description without any ID value in order to simulate an error condition:

DECLARE @TEST_DESC VARCHAR(10)

BEGIN TRY
INSERT INTO TEST(TEST_DESC) VALUES(‘Hello’)
SELECT @Test_Desc = Test_Desc
FROM dbo.TEST
WHERE Test_ID = 1
PRINT ‘Description..’ + @Test_Desc
END TRY
BEGIN CATCH
/*SELECT 1/0*/
SELECT @@ERROR AS ErrorCode
SELECT
ERROR_NUMBER() AS ENumber,
ERROR_SEVERITY() AS ESeverity,
ERROR_STATE() as EState,
ERROR_PROCEDURE() as EProcedure,
ERROR_LINE() as ELine,
ERROR_MESSAGE() as EMessage

END CATCH
GO

If we execute the T-SQL block shown above, it will throw an exception message of cannot insert null value into TEST_ID column. Following is the formatted output.

ErrorCode
———–
515

ENumber ESeverity EState EProcedure ELine EMessage
——- ——— ——- ———- —– ————-
515 16 2 NULL 4 Cannot insert the value NULL
into column ‘TEST_ID’, table ‘DECIPHER.dbo.TEST'; column does not allow
nulls. INSERT fails.

Whenever the very first error is encountered in a TRY block, control immediately goes to CATCH block. No other statement in TRY block will be executed. Once CATCH block execution is complete, control will go to next statement immediately after CATCH block. If there are no errors in the TRY block, then also control will go to next statement immediately after CATCH block.

All the error functions mentioned in the CATCH block returns value only if they are used within the CATCH block otherwise outside of the block, they all return null values. We still can use standard syntax @@ERROR to get the message ID of the error but it should be the first statement in CATCH block. It may give wrong results if it is not the case.

Remove the comment from ‘SELECT 1/0’ statement and re-run the code. This time @@ERROR will return different result but ERROR() functions will display the same result as previously. This also implies that any error in the CATCH block can be handled if there is nested TRY-CATCH block within the CATCH block otherwise error will be thrown back to calling program.

Certain types of error are not handled by CATCH block. They are:
• Compilation errors.
• Object name resolution errors.
• Errors with severity 10 or lower since they are informational messages.
• Errors with severity 20 or higher which closes the database connection.
• If the error terminates the connection, it is not caught by the TRY-CATCH block.

Couple of other salient features for the TRY…CATCH functionality:
• TRY…CATCH blocks can be nested.
• If there is code that is written within the CATCH block, you will need to write a TRY…CATCH block within the specified CATCH block.
• TRY…CATCH can be used to handle deadlocks as well. We had covered that in one of our previous blog post here.
• RAISERROR can also be used within either the TRY or the CATCH block. If the error severity is between 11 and 19 and RAISERROR is being used in the TRY block, the control is transferred to the associated CATCH block. If RAISERROR is in the CATCH block, then an error is returned to the calling batch/application.
• TRY-CATCH cannot be used in a User Defined Function.

Posted in SQL Server | 1 Comment »

Defining one’s own string de-limiter in Oracle 10g

Posted by decipherinfosys on September 16, 2007

Oracle 10g provided one new enhancement for string literals. We can define our own string delimiter character. Normally, when a string containing a single quote needs to be assigned to some variable, we have to put an extra single quote in front of the existing single quote of the string – that acts as the escape character. Example:

SET SERVEROUTPUT ON
DECLARE
v_Str1 VARCHAR2(30);
BEGIN
v_Str1 := ‘Decipher”s blog';
DBMS_OUTPUT.PUT_LINE(v_Str1);
END;
/

Connect through SQL*Plus and run above query. It will give following output.

Decipher’s blog

Notice the quote in the string it self. We prefixed it with another single quote so that string can be displayed as it with single quote. In 10g, we can define our own string delimiter which relieves the need of putting extra quote in the string. Only thing to remember is that, the character you want to use for delimiter should not exist in the string. Let us see that.

DECLARE

v_Str1 VARCHAR2(30);
v_Str2 VARCHAR2(30);
v_Str3 VARCHAR2(30);
v_Str4 VARCHAR2(30);

BEGIN

v_Str1 := ‘Decipher”s blog';
v_Str2 := q’@Decipher’s blog@';
v_Str3 := q’1Decipher’s blog1′;
v_Str4 := q’*Decipher’s blog*';

DBMS_OUTPUT.PUT_LINE(v_Str2);
DBMS_OUTPUT.PUT_LINE(v_Str2);
DBMS_OUTPUT.PUT_LINE(v_Str3);
DBMS_OUTPUT.PUT_LINE(v_Str4);

END;
/

In above example, we are defining our own string delimiter (@, 1 and *) with quote operator ‘q’. This quote operator allows us to definer our own delimiter. None of these characters is part of the original string. When we are using these characters as string delimiter, we are not prefixing extra quote in front of the quote of the string. All of the above gives the same output as shown below.

Decipher’s blog
Decipher’s blog
Decipher’s blog
Decipher’s blog

This becomes very helpful when we have stings which contain lots of double quotes.

Posted in Oracle | Leave a Comment »

Translating Number into Words

Posted by decipherinfosys on September 15, 2007

At times, we run into a situation where we have to spell out a number. For example, we may have to show amount in words i.e. $100 should be displayed as ‘One hundred dollars’ or 1st prize winner should be specifically displayed as ‘first prize winner’ (Ordinal numbers). In such cases, we will have to spell out the numbers.

Oracle provides simple way to do this. We can use SP and SPTH suffix in conjunction with TO_DATE and TO_CHAR function to spell out the numbers. We can take number and convert it using TO_DATE function with date format model ‘J’ (indicates Julian date) and then convert it to character string using TO_CHAR and SP or SPTH suffix. Let us see it with an example. Connect to SQL*Plus with proper credentials and run following query.

SELECT 1,to_char(to_date(1,’J’),’JSP’), to_char(to_date(1,’J’),’JSPTH’)
FROM DUAL
UNION ALL
SELECT 2,to_char(to_date(2,’J’),’JSP’), to_char(to_date(2,’J’),’JSPTH’)
FROM DUAL
UNION ALL
SELECT 123,to_char(to_date(123,’J’),’JSP’), to_char(to_date(123,’J’),’JSPTH’)
FROM DUAL
/

Here is the output of the query.

1 TO_CHAR(TO_DATE(1,’J’),’ TO_CHAR(TO_DATE(1,’J’),’
——— ———————— ————————
1 ONE                      FIRST
2 TWO                      SECOND
123 ONE HUNDRED TWENTY-THREE ONE HUNDRED TWENTY-THIRD

For working with dates, we can simply use DDTH suffix. Following is the example followed by an output.

SELECT TO_CHAR(sysdate, ‘DDth’)||’ ‘||TO_CHAR(sysdate, ‘Month’)||
‘, ‘||TO_CHAR(sysdate, ‘YYYY’) Current_Date
FROM DUAL
/

CURRENT_DATE
——————–
15TH September, 2007

As shown, this is very simple approach to spell out numbers correctly. One can write a function to pass in the number and return the spelled out corrected string.

Posted in Oracle | Leave a Comment »

Altering a regular column to be an identity column in SQL Server

Posted by decipherinfosys on September 14, 2007

At times, we run into a situation where we have to convert an existing non identity column to an identity column. Let us assume that currently there is logic in place to generate running numbers for the primary key column of the table.  We want to convert it to an identity column in order to get database generated value for the primary key and thus avoiding an extra logic to generate running number. In this blog post, we will show you how we can achieve it. Following is the table structure to demonstrate the example. Create the table structure and populate it with data.

CREATE TABLE dbo.Invoice
(
INVOICE_NUMBER   INT NOT NULL,
INVOICE_DATE     DATETIME NOT NULL,
CLIENT_ID        INT   NOT NULL,
INVOICE_AMT      NUMERIC(9,2) DEFAULT 0 NOT NULL,
PAID_FLAG        TINYINT DEFAULT 0 NOT NULL, — 0 Not paid/ 1 paid
CONSTRAINT PK_INVOICE PRIMARY KEY(INVOICE_NUMBER)
)
— FileGroup clause
;

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(1,getdate(),101,1100.00);
INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(2,getDate(),102,1100.00);
INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(4,getdate(),103,1100.00);
INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(6,getdate(),104,1100.00);

This is how data looks like. It is formatted for better viewing.

Inv#  Invoice_Date                  Client_ID   Invoice_Amount    Paid_Flag
—-  ————                  ———-  ————–    ———
1     2007-02-18 15:25:09.107       101         1100.00           0
2     2007-02-18 15:25:09.107       102         1100.00           0
4     2007-02-18 15:25:09.107       103         1100.00           0
6     2007-02-18 15:25:09.107       104         1100.00           0

Unlike Oracle Sequences, which are separate objects, identity property is attached with the table. Table can contain only one identity column. There is no straight way to alter column to identity column or vice versa (altering identity column to regular column). We need to drop and re-create the column with identity property.  This is how we will do it.

Since the invoice_number column is the primary key, we need to drop the primary key constraint first and then drop the column. Also the assumption is that this is a standalone table and doesn’t have any child tables.  If there are child tables, then the FK constraints will need to be dropped, updates will need to be made to those child tables as well to update the references to the parent table using a temp table or a table variable to do the co-relation between the old and the new values and then the FK constraints will need to be re-created.

But before dropping the column, we need to make sure that we don’t loose existing data. So we will first save existing data into other table temporarily.

SELECT * INTO TEMP_INVOICE FROM INVOICE
GO

Successful execution of above command will make sure that existing data is saved safely into newly created TEMP_INVOICE table.
Now first we will truncate the data from existing table and we will drop constraint, column and re-create the column with identity property and then add the constraint back.

TRUNCATE TABLE INVOICE
GO
ALTER TABLE INVOICE DROP CONSTRAINT PK_INVOICE
GO
ALTER TABLE INVOICE DROP COLUMN INVOICE_NUMBER
GO

Now we will add column back with identity property attached to it.

ALTER TABLE INVOICE ADD INVOICE_NUMBER INT IDENTITY(1,1) NOT NULL
GO
ALTER TABLE INVOICE ADD CONSTRAINT PK_INVOICE PRIMARY KEY (INVOICE_NUMBER)
GO

Now we will insert data back into INVOICE table from the TEMP_INVOICE. Since we want to retain the existing values, we will have to turn on the IDENTITY_INSERT property on as INVOICE_NUMBER column is created as an identity column.

SET IDENTITY_INSERT INVOICE ON
GO
INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
SELECT Invoice_Number, Invoice_date, client_ID, Invoice_Amt
FROM Temp_Invoice
GO
SET IDENTITY_INSERT Invoice OFF
GO

Following is the output once we inserted data back into INVOICE table. Output shows that we have retained the existing values of invoice number.

Inv#  Invoice_Date                  Client_ID   Invoice_Amount    Paid_Flag
—-  ————                  ———-  ————–    ———
1     2007-02-18 15:25:09.107       101         1100.00           0
2     2007-02-18 15:25:09.107       102         1100.00           0
4     2007-02-18 15:25:09.107       103         1100.00           0
6     2007-02-18 15:25:09.107       104         1100.00           0

You can add few more records by omitting the invoice_number in the insert statement and see the output. Look for last two records.

INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getdate(),105,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getDate(),106,1100.00);

After adding two more rows, following is the output.

Inv#  Invoice_Date                  Client_ID   Invoice_Amount    Paid_Flag
—-  ————                  ———-  ————–    ———
1     2007-02-18 15:25:09.107       101         1100.00           0
2     2007-02-18 15:25:09.107       102         1100.00           0
4     2007-02-18 15:25:09.107       103         1100.00           0
6     2007-02-18 15:25:09.107       104         1100.00           0
7     2007-02-18 15:43:06.473       105         1100.00           0
8     2007-02-18 15:43:06.473       106         1100.00           0

Last step will be to drop the newly created table which is no longer required as we have successfully put the data back into INVOICE table.

DROP TABLE TEMP_INVOICE
GO

Posted in SQL Server | 2 Comments »

Checking for the existence of a column in a table – DB2

Posted by decipherinfosys on September 13, 2007

In one of our previous post, we had covered how to check for column existence in Oracle and SQL Server.  In this post, we will cover this for DB2 LUW.  One way to add the column non-destructively in DB2 LUW is via stored procedure. In this blog post, we will show you how we can achieve that. Following is the stored procedure code to add column in the table only if it does not exist. If it exists in the column then we will not perform any action in the procedure code. We have used ‘@’ as statement terminator. Let us first create the table and then stored procedure. You can save following code into file and run it from the db2 command line or can connect to db2 command center and run it from there.

CREATE TABLE TEST
(
TEST_ID INT,
TEST_NAME VARCHAR(50)
)@

CREATE PROCEDURE alter_table
( IN tab_name VARCHAR(30), IN col_Name VARCHAR(30),
IN Data_Type VARCHAR(30), IN DATA_LEN VARCHAR(20),
IN Def_Value VARCHAR(20), IN NULLABLE CHAR(1) )
LANGUAGE SQL
SPECIFIC altable
BEGIN

DECLARE V_COUNT INT DEFAULT 0;
DECLARE STATEMENT VARCHAR(1000);

IF DATA_TYPE IN (‘VARCHAR’,’CHAR’,’NUMERIC’) AND DATA_LEN IS NULL THEN
SIGNAL SQLSTATE ‘80000’
SET MESSAGE_TEXT=’Column length is required';
END IF;

SELECT COUNT(*) INTO v_Count
FROM SYSCAT.COLUMNS
WHERE TABNAME = tab_name
AND COLNAME = col_name;

IF (v_count = 0) THEN
SET statement = ‘ALTER TABLE ‘ || tab_Name || ‘ ADD ‘ || col_name || ‘ ‘ || DATA_TYPE || ‘ ‘ ||
CASE WHEN DATA_LEN IS NOT NULL THEN ‘(‘ || DATA_LEN || ‘)’
ELSE ”
END ||
CASE WHEN DEF_VALUE IS NOT NULL THEN ‘ DEFAULT ‘ ||
CASE WHEN DATA_TYPE IN (‘CHAR’,’VARCHAR’) THEN ””
ELSE ”
END ||
DEF_VALUE ||
CASE WHEN DATA_TYPE IN (‘CHAR’,’VARCHAR’) THEN ””
ELSE ”
END
ELSE ”
END ||
CASE WHEN COALESCE(NULLABLE,’Y’) = ‘N’ THEN ‘ NOT NULL ‘
ELSE ”
END;
EXECUTE IMMEDIATE statement;
END IF;
END@

This is very simple stored procedure, which takes 6 arguments and they are pretty self explanatory. We can check whether column exists in the table or not by querying COLUMNS catalog view of SYSCAT schema. SYSCAT schema contains all the metadata information. SYSCAT.COLUMNS has one entry for each column in the database. In our procedure, if the count is 0 (column does not exist) then we go ahead and build the string based on the given input and then execute it using execute immediate statement.

Let us first check what columns are there in the table. We should see only two columns.

SELECT * FROM syscat.columns WHERE tabname = ‘TEST’@

Above query will show only two columns for TEST table. Now we will create one more column by executing stored procedure.

CALL alter_table (‘TEST’,’TEST_DATE’,’TIMESTAMP’,NULL,NULL,NULL)@

In above procedure call we are indicating that we would like to add TEST_DATE column with data type TIMESTMAP in TEST table. Once procedure execution is successful, you can again view syscat.columns to make sure that new column is added. If you re-execute this procedure, it won’t give any error. We can add any column in any table once stored procedure is in place. Let us add one more not null column with default value of 0. Following is the syntax.

CALL alter_table (‘TEST’,’TEST_AMT’,’NUMERIC’,’13,2′,’0′,’N’)@

Here we are taking scale and precision both value in single varchar argument. As we have mentioned earlier, this is very basic procedure. One can refine it further to suit his or her needs. Using same logic one can also check for triggers, tables, procedures etc.

Posted in DB2 LUW | Leave a Comment »

Deleting Duplicate Data

Posted by decipherinfosys on September 12, 2007

Ran into an issue yesterday at a client site where some of the tables in the schema did not have any primary key, any alternate key enforced. As a result, the same order for the same vendor was entered into the system multiple times by different operators. The only thing distinguishing them was the datetime field. This was creating issues during invoicing since the company would end up placing multiple duplicate orders. So, the deal was to first delete the duplicate data and to preserve the first order for the given vendor and the given quantity i.e. the vendor_name + qty was their alternate key in the system. This delete was achieved quite simply by using a CTE (Common Table Expressions) and once the delete was done, the alternate key was enforced in the system to prevent such an occurence in the future.

Here is a sample code snippet to show how in SQL Server 2005, using CTE and the ranking functions, one can get rid of the duplicate data. There are many other ways of doing it as well. This is just one of the ways.

set nocount on

declare @test_tbl TABLE (Vendor_Name varchar(10), QTY INT, Order_Date datetime)

INSERT INTO @test_tbl (Vendor_Name, QTY, Order_Date) Values (‘ABCD’, 1000, ’08/19/2007 10:00:05′)
INSERT INTO @test_tbl (Vendor_Name, QTY, Order_Date) Values (‘ABCD’, 1000, ’08/20/2007 11:00:10′)
INSERT INTO @test_tbl (Vendor_Name, QTY, Order_Date) Values (‘ABCD’, 1000, ’08/20/2007 12:15:10′)
INSERT INTO @test_tbl (Vendor_Name, QTY, Order_Date) Values (‘XYZ’, 2000, ’09/03/2007 10:00:10′)
INSERT INTO @test_tbl (Vendor_Name, QTY, Order_Date) Values (‘XYZ’, 2000, ’09/03/2007 11:00:00′)
INSERT INTO @test_tbl (Vendor_Name, QTY, Order_Date) Values (‘XYZ’, 2000, ’09/03/2007 15:10:30′)

/*before delete*/
select * from @test_tbl

Vendor_Name QTY Order_Date
———– ———– ———————–
ABCD 1000 2007-08-19 10:00:05.000
ABCD 1000 2007-08-20 11:00:10.000
ABCD 1000 2007-08-20 12:15:10.000
XYZ 2000 2007-09-03 10:00:10.000
XYZ 2000 2007-09-03 11:00:00.000
XYZ 2000 2007-09-03 15:10:30.000

;with test (row_num, Vendor_Name, QTY) as
(
select row_number() over (partition by Vendor_Name, QTY order by Order_Date asc) as row_num, Vendor_Name, QTY
from @test_tbl
)
delete test where row_num > 1;

select * from @test_tbl

/*after delete*/

Vendor_Name QTY Order_Date
———– ———– ———————–
ABCD 1000 2007-08-19 10:00:05.000
XYZ 2000 2007-09-03 10:00:10.000

The same logic can be applied in Oracle as well since the same functionality exists in Oracle also.

Posted in Oracle, SQL Server | 1 Comment »

PSSDiag and SQLDiag

Posted by decipherinfosys on September 11, 2007

In late 2004, Microsoft Product Support Services (PSS) team made three very useful tools available to the public. The tools are PSSDiag, Ostress and Read80trace.  We will cover more on the Ostress and the Read80Trace in future blog posts as we demonstrate their usage in benchmark environments.  These tools can save database folks a lot of time in collecting and analyzing the data for troubleshooting a problem or simply for benchmarking purpose.

If you are using SQL Server 7.0 or 2000, you can use the PSSDiag utility and can download it from here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;830232

If you are using SQL Server 2005, this is re-named as SQLDiag and is present with the main SQL Server 2005 install. You can get more information on it from BOL (Books Online). Here is the help output from my instance:

H:\>sqldiag /?

SQLDiag collector version 2005.090.3042.00

c Microsoft Corp. All rights reserved.

Usage: sqldiag [START | STOP | STOP_ABORT] [/O outputpath] [/I cfgfile] [/M machine|@machinelistfile] [/Q] [/C #] [/G] [/R] [/U] [/N #] [/A appname] [/X] [/L] [/B YYYYMMDD_HH:MM:SS] [/E YYYYMMDD_HH:MM:SS]

Posted in SQL Server | 1 Comment »

SQL Server 2008 CTP4

Posted by decipherinfosys on September 10, 2007

Community Technology Preview (CTP) 4 for the next version of SQL Server – SQL Server 2008 was released on 08/31 and can be downloaded from:

http://www.microsoft.com/downloads/details.aspx?FamilyID=6a39affa-db6e-48a9-82e4-4efd6705f4a6&DisplayLang=en

This CTP comes pre-configured as a VHD and you would need Virtual Server 2005 R2 installed on your system in order to use it. You can download the virtual server 2005 R2 from this link.

Posted in SQL Server | Leave a Comment »

Dynamic wrapping using DBMS_DDL

Posted by decipherinfosys on September 10, 2007

In Oracle 10g Release 2, Oracle introduced a way of wrapping PL/SQL Code by using the DBMS_DDL package. Prior to 10g, PL/SQL WRAP utility was used to wrap the code. Only difference between WRAP utility and the subprograms of the DBMS_DDL package which wraps the PL/SQL Code is that the WRAP utility can take multiple routines separated by ‘/’ and wrap the context leaving other text as is while DBMS_DDL package can only take single PL/SQL unit (Procedure, function etc.) for wrapping.

DBMS_DDL package has an overloaded function called WRAP and an overloaded procedure called CREATE_WRAPPED to wrap the code.  Signatures of function are as shown below:

DBMS_DDL.WRAP(
ddl VARCHAR2
)
RETURN VARCHAR2;

DBMS_DDL.WRAP(
ddl DBMS_SQL.VARCHAR2S,
lb PLS_INTEGER,
ub PLS_INTEGER
)
RETURN DBMS_SQL.VARCHAR2S;

DBMS_DDL.WRAP(
ddl DBMS_SQL.VARCHAR2A,
lb PLS_INTEGER,
ub PLS_INTEGER
)
RETURN DBMS_SQL.VARCHAR2A;

Similarly, CREATE_WRAPPED stored procedure also takes the exact same arguments and has the exact same overloaded procedure signature.

First form of the function returns the wrapped or obfuscated text of the passed in string. Remaining two forms of the function return the table of strings that will be concatenated to form the original text but in an obfuscated manner. DBMS_SQL.VARCHAR2 and DBMS_SQL.VARCHAR2A are predefined types, which have limits of 256 bytes per line and 32K bytes per line respectively. So, the function overload with DBMS_SQL.VARCHAR2A can take larger inputs. Let’s use an example to demonstrate this functionality. Here is our original test procedure:

CREATE OR REPLACE PROCEDURE test_proc AS
BEGIN
null;
END;
/

Now we will obfuscate it using CREATE_WRAPPED procedure of DBMS_DDL package. Following is the PL/SQL block which does that:

SET SERVEROUTPUT ON SIZE 1000000
SET PAGESIZE 80
DECLARE
l_ddl  DBMS_SQL.VARCHAR2A;
BEGIN

l_ddl(1) := ‘CREATE OR REPLACE PROCEDURE test_proc AS
BEGIN
NULL;
END test_proc;';

SYS.DBMS_DDL.CREATE_WRAPPED(ddl => l_ddl,
lb  => 1,
ub  => l_ddl.count);

END;
/

When we run the above PL/SQL block, CREATE_WRAPPED procedure will mask the source of the procedure passed as an argument and will compile it. Masked source code will reside in user_Source view.  Following is the SQL to get the source code from the view followed by the result:

SELECT text
FROM User_Source
WHERE NAME = ‘TEST_PROC’
ORDER BY line;

TEXT
———————————————————————–
PROCEDURE test_proc wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
7
62 71
3NOVVKvzTETNLTS5l4TpQf2gO4Qwg5nnm7+fMr2ywWoWLR9JYWl64ruHSLCWdE3OK5QT99
rGePH/yGC/DRufzBUS8Gq3bTLKYhBez7pq2cjq4=

In the same fashion, we can use DBMS_DDL.WRAP function but function will return back the encrypted string.

Posted in Oracle | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 78 other followers