UTL_MAIL quirks

I prefer using using UTL_MAIL over UTL_SMTP as I can use the smtp_out_server from the v$parameter without having code the value of smtp host in my package . 

Few weeks back , I ran it into an issue where my package was failing as the attachment supposedly exceeded 32K. 

Error report -
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 747
ORA-06512: at "DUSER.EMAIL_PKG", line 638
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.



I have been using UTL_MAIL  for years and knew that it had 32K size limitation . What did not I know/realize  was that it was the size of encoded file .  

Here is an example . I have a file test.txt that that is 23609  bytes .  But when it is encoded its size was 32462 bytes . As this file was less than 32K , the process ran fine. 

 Directory of d:\reports


11/11/2016  02:59 PM            23,609 test.TXT
               1 File(s)         23,609 bytes


SQL> SET serveroutput ON
SQL> DECLARE
  2  BEGIN
  3    test_email('test.TXT', 'test.TXT' , 'test@email.com' , 'testre@email.com' , 'test' , 'Test Body', 'REPORTS');
  4  END ;
  5  /
Original Size: 23609 bytes
Encoded Size: 32462 bytes

PL/SQL procedure successfully completed.



Let us look at another file ( test1.txt ) that is 25K  and let us run our script for this. As the encoded file was over 32K , my script erred out.  

11/01/2016  10:25 AM            24,645 test1.TXT
               1 File(s)         24,645 bytes

SQL> SET serveroutput ON

SQL> DECLARE

  2  BEGIN

  3    test_email('test1.TXT', 'test1.TXT' , 'test@email.com' , 'testre@email.com' , 'test' , 'Test Body', 'REPORTS');

  4  END ;

  5  /

Original Size: 24645 bytes

DECLARE

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: raw variable length too long

ORA-06512: at "SYS.UTL_ENCODE", line 243

ORA-06512: at "DUSER.TEST_EMAIL", line 22

ORA-06512: at line 3

I was able to simulate the error that UTL_EMAIL was producing . 

Here is the script to simulate . BTW , it was documented in metalink  ( Bug 9038705)  

CREATE OR REPLACE PROCEDURE test_email(

    FileName       IN VARCHAR2,

    AttachmentName IN VARCHAR2 ,

    SentFrom       IN VARCHAR2 ,

    SentTo         IN VARCHAR2 ,

    Subj           IN VARCHAR2 ,

    Mesg           IN VARCHAR2,

    FileDir        IN VARCHAR2 )

AS

  FileHandle BFILE;

  Buffer RAW(32767);

  EncodedBuffer RAW(32767);

  OrgSize INTEGER;

  Offset  INTEGER := 1;

BEGIN

  FileHandle := BFILENAME(FileDir, FileName);

  OrgSize    := DBMS_LOB.GETLENGTH(FileHandle);

  DBMS_LOB.FILEOPEN(FileHandle, DBMS_LOB.FILE_READONLY);

  DBMS_LOB.READ(FileHandle, OrgSize, Offset, Buffer);

  DBMS_LOB.FILECLOSE(FileHandle);

  DBMS_OUTPUT.PUT_LINE(' Original Size: ' || OrgSize || ' bytes' );

  EncodedBuffer := UTL_ENCODE.BASE64_ENCODE(Buffer);

  DBMS_OUTPUT.PUT_LINE(' Encoded Size: ' || UTL_RAW.LENGTH(EncodedBuffer) || ' bytes' );

END test_email;

For this use case , I went back to using UTL_SMTP. 

Learning day by day :-) . 

Columnstore Index Enhancement in SQL 2016

SQL Server 2012 introduced the "Columnstore" indexes , which was intended for data warehousing applications. Before than , the indexes were of "rowstore" ( ie , regular indexes that we have seen from inception ).

In SQL Server 2012 , this feature was not widely adapted , as the table was not updatable with the column store index on . To load the data , we need to drop the index , load the data and recreate the index . Not all the business can afford to do this. 


In SQL Server 2014 , we were to able to update the table , if it had clustered columnstore index. This will be only index in this table. Little bit better .  We were able to add any other indexes such as non clustered primary key to enforce the unique constraints on the underlying data set or some other index that will speed up other processes.  

In SQL Server 2016 , things get better. Table having clustered column store index , can have ONE non clustered index .  Hopefully , the limit of ONE will be released in the next releases. 


Here is an example in SQL Server 2014 

1>  Select @@version
2> go

-
Microsoft SQL Server 2014 - 12.0.2269.0 (X64)
        Jun 10 2015 03:35:45
        Copyright (c) Microsoft Corporation
        Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 10586: )


(1 rows affected)
1> Create Table Sales
2> ( OrderID int not null ,
3>   DateID  int  not null ,
4>   BuyerID int  not null ,
5>   ProductID int  not null ,
6>   Quantity int  not null ,
7>   Price numeric(7,2)  not null
8>   ) ;
9> go

1>
2> CREATE CLUSTERED COLUMNSTORE INDEX ccidx_Sales ON Sales  ;
3> go

1>
2>
3> ALTER TABLE Sales ADD CONSTRAINT t_Sales_pk  PRIMARY KEY nonclustered (OrderID,ProductID) ;
4> go

Msg 35303, Level 16, State 1, Server XXXXXX, Line 3
CREATE INDEX statement failed because a nonclustered index cannot be created on a table that has a clustered columnstore index. Consider replacing the clustered columnstore index with a nonclustered columnstore index.
Msg 1750, Level 16, State 1, Server XXXXXX, Line 3

Could not create constraint or index. See previous errors.


Let us run the same scripts in SQL Server 2016 . 


4> Create Table Sales
5> ( OrderID int not null ,
6>   DateID  int  not null ,
7>   BuyerID int  not null ,
8>   ProductID int  not null ,
9>   Quantity int  not null ,
10>   Price numeric(7,2)  not null
11>   ) ;
12> go

1> CREATE CLUSTERED COLUMNSTORE INDEX ccidx_Sales ON Sales  ;
2> go

1>
2>  ALTER TABLE Sales ADD CONSTRAINT t_Sales_pk  PRIMARY KEY nonclustered (OrderID,ProductID) ;

3> go


Now , we are able to have the table with a clustered columnstore index and one non clustered index. 

Fixing "DBProviderFactories" section issue in SSDT / SSIS / SSMS

Recently , I installed SQL Server Data tools ( SSDT) after installing SQL Server 2014 . 

As mentioned in one of my post (http://mfzahirdba.blogspot.com/2016/02/sql-server-2014-surprises.html )  , SSDT is not bundled in the installer . You will need to download and install it.

After the install , I created a SSIS project , and created a data flow task. 
When , I am try to create a new connection manager , I was greeted (!) with this error. 






Here is how , I resolved the issue .

a)  Identify the correct machine.config in the .NET framework 
     In my case , it was  
   C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config

b)  Remove one of the tags (DbProviderFactories)  in section  in "system.data" 
section. 

  
        

After the edit ,I was able to create the connection managers with no issue.


It looks like , when you install the ODBC / .NET drivers , it adds the additional tag into the .NET framework configuration file. 

Hope this is helpful .

IDENTITY column in Oracle

Prior to Oracle 12c , there was no direct equivalent to Sybase / SQL Server / PostgreSQL  identity column . This enhancement is available in 12c. 

Under the hood , Oracle creates sequence and associate it to the base table.  The  sequence name is tagged as "ISEQ$$_XXXXX"  , where XXXX is the object id of the base table.   The record is available in xxx_sequences ( all / user / pdb) views , till the recycle bin is purged . 

To create a table with the identity column , the user needs to "CREATE TABLE" and "CREATE SEQUENCE" privilege . 

Datapump has been enhanced to accommodate this enhancement as well. Here is an example. 




SQL> show user
USER is "ZAHIR"
SQL>

SQL> Create table t_identity
  2  (  empid int GENERATED BY DEFAULT ON NULL AS IDENTITY ,
  3     empname varchar2(20)
  4     ) ;
Create table t_identity
*
ERROR at line 1:
ORA-01031: insufficient privileges

C:\Users\mohideen>sqlplus system@pdb_test

SQL*Plus: Release 12.1.0.1.0 Production on Wed Apr 20 13:04:13 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Wed Apr 20 2016 12:42:47 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SQL> grant create sequence to zahir ;

Grant succeeded.

SQL> conn zahir/password@pdb_test
Connected.
SQL>
SQL> Create table t_identity
  2  (  empid int GENERATED BY DEFAULT ON NULL AS IDENTITY ,
  3     empname varchar2(20)
  4     ) ;

Table created.

SQL> col object_name format a20
SQL> Select  object_name , object_id , object_type  from user_objects where object_name like 'T_IDENT%';

OBJECT_NAME           OBJECT_ID OBJECT_TYPE
-------------------- ---------- -----------------------
T_IDENTITY                92733 TABLE

SQL> col sequence_name format a20
SQL> Select  sequence_name , min_value  from user_sequences ;

SEQUENCE_NAME         MIN_VALUE
-------------------- ----------
ISEQ$$_92733                  1

SQL> drop table t_identity;

Table dropped.

SQL> Select  sequence_name , min_value  from user_sequences ;

SEQUENCE_NAME         MIN_VALUE
-------------------- ----------
ISEQ$$_92733                  1

SQL> purge recyclebin ;

Recyclebin purged.

SQL> Select  sequence_name , min_value  from user_sequences ;

no rows selected

Accessing Postgresql / MySQL / MS SQL Server from Oracle.

Gone are the days , where a business has only one DBMS to maintain its operations.

Most of the times , you are dealing with multiple DBMS in your organization. 

The vendors provide some means of connecting to distributed databases . For instance ,

Oracle provides "database link"  ( via transparent gateway), MS SQL Server provides "Linked Server" mechanism to connect to heterogeneous databases and other data sources.

Here , we will look at connecting to PostgreSQL from Oracle by using transparent ODBC gateway .  This process is applicable any ODBC complaint data sources . 

Oracle does provides transparent gateways for MS SQL Server , DB2 . But those needs to be licensed.

As of today , ODBC transparent gateway is not separately licensed . Please check with your rep for the licensing details.

Here are the steps.

Step 1 :

Configure ODBC connection and a create a system DSN for PostgreSQL

Let us call it as "pgsales”

Note : This needs to be configured as "System DSN"  , not as User DSN.

Step2 :

Create a file under %ORACLE_HOME%\hs\admin in the form initpgsalesodbc.ora . The naming convention is “init” followed by  desired name . In my case , I have it as  initpgsalesodbc.ora. 

Set the HS_FDS_CONNECT_INFO to the DSN name that was configured in ODBC setup . 

I would recommend to keep the trace level to 'ON' for the initial setup . Once the setup is complete ; connectivity is established ; we can turn off the trace .  

Oracle provides sample file . We can use clone this file to suit our needs.  

Here are the contents of initpgsalesodbc.ora.

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = pgsales

HS_FDS_TRACE_LEVEL =  ON

#

# Environment variables required for the non-Oracle system

#

#set =

Step 3 :

Add a entry in listener.ora to register this data source.   The SID name must match with the init file created in the above step.

      (SID_DESC =

              (SID_NAME = pgsalesodbc)

              (ORACLE_HOME = c:\app\oracle\product\12.1.0\dbhome_1)

              (PROGRAM = dg4odbc)     

    )

Step 4: 

Add entry in tnsnames.ora . The SID  in connect_data should match SID defined in the listerner.ora and the file in hs\admin. 

pgsqllink  =

  (DESCRIPTION=

 (enable=broken)

    (ADDRESS=(PROTOCOL=tcp)(HOST=ZAHIRSER)(PORT=1521))

    (CONNECT_DATA=(SID= pgsalesodbc))

    (HS=OK)

  )

Step 5:

Restart the listener.You should see to make sure that the listener is servicing the services we created.

Service "pgsalesodbc" has 1 instance(s).

  Instance "pgsalesodbc", status UNKNOWN, has 1 handler(s) for this service...

Step 6: 

Create a database link .  

In the following example , LINK2PGSQL is our db link , 'zahir' is the user id in the postgresql database and "passwd1234" is the password . 

 

SQL> CREATE DATABASE LINK LINK2PGSQL   CONNECT TO "zahir" IDENTIFIED BY "passwd1234" USING 'pgsqllink'  ;

 Database link created.

 

To test the connectivity , issue a select statement as below . Please note , for Postgresql databases  , you will need to enclose the table/view names in quotes.

 

SQL> select * from "t"@LINK2PGSQL;

c

--

A

B

C

With this feature , we can manipulate the data stored in heterogeneous databases . 

Comments Welcome .