ZAHIR MOHIDEEN: 2023

Saturday, October 14, 2023

New and Niche function in SQL Server 2022. Close cousin in DAX ( Power BI )

SQL Server 2022 introduced the new function GENERATE_SERIES.

With this , we can generate series of values with the appropriate stop interval.

For this function , we need to be on SQL 2022.

Here is the link to the documentation

https://learn.microsoft.com/en-us/sql/t-sql/functions/generate-series-transact-sql?view=sql-server-ver16

1> select @@version

2> go

Microsoft SQL Server 2022 (RTM-GDR) (KB5029379) - 16.0.1105.1 (X64)

Aug 24 2023 02:40:55

Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19044: ) (Hypervisor)

Here , in this example , we will use to generate dates between two dates.

With the following CTE , we are generating the list of dates between 10/14/2023 and 10/22/2023.

We are omitting the step size , as the default is 1.

1> WITH

2> v_param AS

3> ( SELECT

4> '2023-10-14' AS StartDate ,

5> '2023-10-22' AS EndDate ,

6> DATEDIFF( DAY , '2023-10-14' , '2023-10-22') AS Days

7> )

8> SELECT

9> DATEADD ( DAY , gs.value ,

10> ( SELECT

11> startdate

12> FROM

13> v_param ) ) AS Dates

14> FROM

15> GENERATE_SERIES( 0 ,

16> ( SELECT

17> Days

18> FROM

19> v_param )) gs;

20>

21> go

Dates

-----

2023-10-14 00:00:00.000

2023-10-15 00:00:00.000

2023-10-16 00:00:00.000

2023-10-17 00:00:00.000

2023-10-18 00:00:00.000

2023-10-19 00:00:00.000

2023-10-20 00:00:00.000

2023-10-21 00:00:00.000

2023-10-22 00:00:00.000

Usually, I would like to wrap the functionality in a stored procedure ( in SQL Server / MariaDB ) and in a package ( in Oracle).

Here is the equivalent of the above CTE in a stored procedure.

1> CREATE PROCEDURE p_gen_dates

2> @p_start_date DATE , @p_end_date DATE AS

3> BEGIN

4> SET NOCOUNT ON;

5> SELECT

6> DATEADD ( DAY , gs.value , @p_start_date ) AS Dates

7> FROM

8> GENERATE_SERIES( 0 , DATEDIFF( DAY , @p_start_date , @p_end_date) ) gs;

9> END

10> GO

1> EXEC [dbo].[p_gen_dates] '2023-10-14' , '2023-10-16'

2> GO

Dates

-----

2023-10-14

2023-10-15

2023-10-16

This new function is niche and simple to use.

Looks like , this function is borrowed from DAX.

In the Power BI Desktop , navigate to the "Table View" and click on the "New Table" .

That opens up a new window to create a DAX measure . Here we create a calculated table using "GENERATESERIES" function . In the example below , we created a table of 10 records , starting with 1 with a step size of 2 ( 1 is the default for step size)

Comments Welcome!

Monday, May 1, 2023

Scalar SQL Macro and DUAL in 23c

In the last blog post on SQL Macros , ( https://zahirmohideen.blogspot.com/2023/03/sql-macros.html) , I was exploring SQL macros.

In Oracle 23c , scalar macro is ported from 21c .

BTW , I am testing this feature on 23c Free - Developer's Release ( https://www.oracle.com/database/free/)

Here is the test case from the previous post.

BANNER

___________________________________________________________________

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL> CREATE OR replace FUNCTION calculate_pies (

2 p_arg1 NUMBER

3 ) RETURN VARCHAR2 sql_macro ( scalar ) is

4 begin

5 return q'{

6 p_arg1 * 3.14

7 }';

8 end;

9* /

Function CALCULATE_PIES compiled

SQL> SELECT

2 level,

3 calculate_pies(level)

4 FROM

5 dual

6 CONNECT BY

7* level <= 5;

LEVEL CALCULATE_PIES(LEVEL) ________ ________________________ 1 3.14

2 6.28

3 9.42

4 12.56

5 15.7

Another new feature is that we don't need DUAL to get the results.Albeit this feature is tiny , it goes parallel with all other RDBMSes

SQL> SELECT

2 level,

3 calculate_pies(level)

4 CONNECT BY

5* level <= 5;

LEVEL CALCULATE_PIES(LEVEL)

________ ________________________

1 3.14

2 6.28

3 9.42

4 12.56

5 15.7

More to come on 23c New Features.

Stay Tuned.

Thursday, March 2, 2023

SQL Macros

SQL Macros was introduced in Oracle 21c. There are two types of SQL Macros in 21c ( Scalar and Table). These enhancements make it easy to implement a view , where we can pass in the parameter to get the desired results.

Prior to this , we can achieve this by using parameterized views and contexts. It is a bit cumbersome. This feature does not involve context switching.

This feature is backported to 19c. But only "Table type SQL Macro".

As 21c release is the innovation release , Oracle recommends 19c to be the production release.

We create a function , and indicate that it returns TABLE for Macro Type.

Then we can use this , FROM clause of the SELECT statement as shown below.

SQL Macro - Table

=================

SQL> set linesize 1000

SQL> select banner from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

SQL> create table t as select * from all_objects;

Table created.

SQL> CREATE OR replace FUNCTION count_objects_table (

2 obj_owner_name VARCHAR2

3 ) RETURN VARCHAR2

4 SQL_MACRO( TABLE ) is begin RETURN q'{

5 Select object_type , count(*) as Count_Type from t

6 Where owner = count_objects_table.obj_owner_name

7 group by object_type

8 }'

9 ;

10 END;

11 /

Function created.

SQL> set pagesize 1000

SQL> SELECT

2 *

3 FROM

4 count_objects_table ( 'SYS' );

OBJECT_TYPE COUNT_TYPE

----------------------- ----------

INDEX 1623

TABLE 1750

CLUSTER 10

EDITION 1

SEQUENCE 3

DIRECTORY 12

PACKAGE 424

FUNCTION 103

SYNONYM 16

PROCEDURE 25

TYPE 1149

OPERATOR 7

TABLE PARTITION 466

INDEX PARTITION 276

TABLE SUBPARTITION 32

CONSUMER GROUP 18

JOB CLASS 3

DESTINATION 2

SCHEDULE 4

WINDOW 9

SCHEDULER GROUP 4

EVALUATION CONTEXT 1

JAVA CLASS 35259

JAVA RESOURCE 1658

25 rows selected.

SQL Macro - Scalar

=================

Here , we will create a function that returns scalar macro.

And then use it in SELECT clause , as shown below.

SQL> CREATE OR replace FUNCTION calculate_pies (

2 p_arg1 NUMBER

3 ) RETURN VARCHAR2 sql_macro ( scalar ) is

4 begin

5 return q'{

6 p_arg1 * 3.14

7 }';

8 end;

9 /

Function created.

SQL> SELECT

2 level,

3 calculate_pies(level)

4 FROM

5 dual

6 CONNECT BY

7 level <= 5;

LEVEL CALCULATE_PIES(LEVEL)

---------- ---------------------

1 3.14

2 6.28

3 9.42

4 12.56

5 15.7

In 19c

------

As the "table" SQL macro is only the type supported in 19c , the following scipt produces error.

By removing the table type , the function is valid and usable.

SQL> select banner from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> CREATE OR replace FUNCTION count_objects_table (

2 obj_owner_name VARCHAR2

3 ) RETURN VARCHAR2

4 SQL_MACRO( TABLE ) is begin RETURN q'{

5 Select object_type , count(*) as Count_Type from all_objects

6 Where owner = count_objects_table.obj_owner_name

7 group by object_type

8 }'

9 ;

10 END;

11 /

Warning: Function created with compilation errors.

SQL> show errors

Errors for FUNCTION COUNT_OBJECTS_TABLE:

LINE/COL ERROR

-------- -----------------------------------------------------------------

4/10 PLS-00103: Encountered the symbol "(" when expecting one of the

following:

; is default authid as cluster order using external

deterministic parallel_enable pipelined aggregate

result_cache accessible rewrite

SQL> CREATE OR replace FUNCTION count_objects_table (

2 obj_owner_name VARCHAR2

3 )

4 return VARCHAR2

5 sql_macro

6 IS

7 BEGIN

8 RETURN q'{

9 Select object_type , count(*) as Count_Type from all_objects

10 Where owner = count_objects_table.obj_owner_name

11 group by object_type

12 }'

13 ;

14 END;

15 /

Function created.

SQL> set pagesize 100

SQL> Select * From count_objects_table('SYS');

OBJECT_TYPE COUNT_TYPE

----------------------- ----------

SEQUENCE 4

PROCEDURE 20

TABLE SUBPARTITION 32

FUNCTION 101

INDEX PARTITION 130

WINDOW 9

JAVA RESOURCE 1640

JOB CLASS 2

EVALUATION CONTEXT 1

JAVA CLASS 34395

PACKAGE 261

TABLE 1575

EDITION 1

SYNONYM 16

TYPE 1104

SCHEDULE 4

SCHEDULER GROUP 4

CLUSTER 10

OPERATOR 7

TABLE PARTITION 326

INDEX 1454

CONSUMER GROUP 18

DESTINATION 2

24 rows selected.

SQL Macro is a welcome addition to Oracle. It eases some of the complexity.

Similarly , I hope , Oracle adds "Filtered Index" ( as it exists in SQL Server) to its feature list in the coming releases.

Comments Welcome.

Wednesday, February 22, 2023

Strange issue with Informatica Secure Agent Installation

Most of the time , copy and paste is a life saver. Sometimes , this could get tricky.

Recently , we were installing Informatica Secure Agent ( IICS) in one of our servers.

We followed the instructions in Informatica Documentation.

( https://docs.informatica.com/integration-cloud/cloud-data-integration/current-version/getting-started/installing-secure-agents/secure-agent-installation-on-linux/downloading-and-installing-the-secure-agent-on-linux.html)

Copied the command in step5 , as below.

We ran into this issue.

[usr@agentserver]$ ./agent64_install_ng_ext.6425.bin –i console

Preparing to install

Extracting the JRE from the installer archive...

Unpacking the JRE...

Extracting the installation resources from the installer archive...

Configuring the installer for this system's environment...

Launching installer...

Usage: agent64_install_ng_ext.6425 [-f <path_to_installer_properties_file> | -options]

(to execute the installer)

where options include:

-? show this help text

-h show this help text

-help show this help text

--help show this help text

-i [gui | console | silent]

specify the user interface mode for the installer

-D<name>=<value>

specify installer properties

-r <path_to_generate_response_file>

Generates response file.

JVM heap size options are only applicable to Installers

-jvmxms <size>

Specify JVM initial heap size.

-jvmxmx <size>

Specify JVM maximum heap size.

The options field may also include the following in case of uninstaller

if it is enabled for Maintenance Mode

-add <feature_name_1> [<feature_name_2 ...]

Add Specified Features

-remove <feature_name_1> [<feature_name_2 ...]

Remove Specified Features

-repair

Repair Installation

-uninstall

Uninstall

notes:

1. the path to the installer properties file may be either absolute,

or relative to the directory in which the installer resides.

2. if an installer properties file is specified and exists, all other

command line options will be ignored.

3. if a properties file named either 'installer.properties' or

<NameOfInstaller>.properties resides in the same directory as the

installer, it will automatically be used, overriding all other command

line options, unless the '-f' option is used to point to another valid

properties file.

4. if an installer properties file is specified but does not exist, the

default properties file, if present, will be used. Otherwise, any

supplied command line options will be used, or if no additional

options were specified, the installer will be run using the default

settings.

Apparently , this is an issue , when we copy/paste the command.

When we keyed in the same command , the install was successful.

[usr@agentserver]$ ./agent64_install_ng_ext.6425.bin -i console

Preparing to install

Extracting the JRE from the installer archive...

Unpacking the JRE...

Extracting the installation resources from the installer archive...

Configuring the installer for this system's environment...

Launching installer...

===============================================================================

Informatica Cloud Secure Agent (created with InstallAnywhere)

-------------------------------------------------------------------------------

Preparing CONSOLE Mode Installation...

....

Installation Complete

---------------------

Congratulations. Informatica Cloud Secure Agent has been successfully

I checked the command for non printable characters when copied it. Nothing.

Everything seemed to be fine.

Anyways , sharing this post , if it is going to be helpful to others.