Infrastructure at your Service

SQL Archives - Page 3 of 3 - Blog dbi services

Franck Pachot

Oracle lateral inline view, cursor expression and 12c implicit statement result

By | Database management, Oracle | No Comments

I’ll present here 3 ways to run a query for each result of another query. Let’s take an exemple: get all executions plan (select from dbms_xplan.display_cursor) for each of my queries (identified from v$sql). The 90’s way was to run the first query, which generates the second queries into a spool file, and execute that file. Here are easier ways, some of them coming from 12c new features lateral join and implicit statement result. Test…

 
Read More
Stephane Biehler

UKOUG 2014 – Middleware Day 3

By | Application integration & Middleware | No Comments

Today, no more sessions on middleware, but a lot of ADF, APEX, developments sessions that looks like really interesting! Unfortunately I can’t attend each one but here are some I selected! The picture that paints a thousand words: Data Visualization (ADF) Duncan Mills (Oracle) In this session Duncan was talking about my favorite topic: data visualization. I am quite used to ADF as I developed an interface for my Capacity Planning project. It was sometimes…

 
Read More
Stéphane Haby

SQL Server tips: how to list orphaned logins

By | Database management | No Comments

I read a lot of about orphaned database users in SQL Server, but I have almost never read about orphaned logins. Many of my customers migrate or remove databases in SQL Server. They forget – not every time but often – to remove the logins and jobs associated with these databases. I have created a script – without any cursors, YES, it is possible – allowing to search all logins who are not “attached” to…

 
Read More
Stéphane Haby

SQL Server tips: Executing a query with the EXECUTE command

By | Database management | No Comments

This short SQL Server blog post is meant to help people who have experienced the error messages 2812 and 203 with the EXECUTE command. The goal is to execute a simple query from a variable in a string format with the EXECUTE (exec) command. Very easy, isn’t it? Ok, let’s GO! If I execute “select * from sys.databases” in SSMS, in SQLCMD or in PowerShell – no problem, I have the list of all databases…

 
Read More
Franck Pachot

Oracle 12.1.0.2.1 Set to Join Conversion

By | Database management, Oracle | No Comments

Recently, I described the Partial Join Evaluation transformation that appeared last year in Oracle 12c. I did it as an introduction for another transformation that appeared long time ago in 10.1.0.3 but was not used by default. And even in the latest Oracle 12c patchset 1 (aka 12.1.0.2.0) it is still not enabled. But it’s there and you can use it if you set optimizer_features_enabled to 12.1.0.2.1 (that’s not a typo!). Yes, that number looks…

 
Read More
Franck Pachot

Oracle 12c extended datatypes better than CLOB?

By | Database management, Oracle | 6 Comments

12c has introduced character strings that can can go above 4000 bytes. In the previous versions, in PL/SQL only we were allowed to have VARCHAR2 up to 32k. In SQL the VARCHAR2 datatype was limited to 4000 and CHAR was limited to 2000. That became a bit small especially when lot of applications needed to store unicode characters. From 12c we can have SQL datatypes having up to 32k bytes for VARCHAR2, CHAR and RAW….

 
Read More
Franck Pachot

ROWNUM vs ROW_NUMBER() and 12c fetch first

By | Database management, Oracle | 4 Comments

Prior to Oracle 12c, there were two ways to do ‘top-n’ queries: use rownum after sorting rows with “order by” use row_number() over (order by) Top-n queries are usually required for result pagination. The application shows the first 10 rows, then the next 10 on the following page, etc. In these queries, user interactions are often stateless, so we cannot just open a cursor and fetch 10 rows on each user request. In both case…

 
Read More