Skip navigation.

Laurent Schneider

Syndicate content
Oracle Certified Master
Updated: 12 hours 31 min ago

DBMS_METADATA.GET_DDL in sqlplus

Wed, 2014-08-20 10:10

Some settings matter when using dbms_metadata.

define large clobs


set long 1000000 

large long columns

set longchunksize 32000 

long lines

set linesize 32000 

no trailing spaces

set trimspool on  

no header

set heading off  

no page size

set pages 0  

no page feed (^L)

set newpage none  

no start of page

set embedded on  

no tabulator (^T)

set tab off  

no feedback (n rows returned)

set feedback off  

no echo

set echo off

Per default you get no terminator


exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true)

When running the spooled code, allow blank lines


set sqlblanklines on

Get rid of &


set define off

Get rid of a leading #


set sqlprefix off

Get rid of . on a single line


set blockterminator OFF

To get rid of a trailing -, you may use set lin bigger than set longc and trimsp off, but I could not make it bug yet


CREATE TABLE T(x number default -
1);

would give 1 instead of -1 when run in sqlplus. But METADATA translates it to

  CREATE TABLE "SCOTT"."T"
   (    "X" NUMBER DEFAULT -
1
   )

So just forget about trailing dash for now.

Now try


SQL> set SQLBL ON SQLPRE OFF DEF OFF BLO OFF
SQL> create view v as select '
  2
  3  .
  4  #?
  5  &_date
  6  ' x from dual;

View created.
SQL> set long 1000000 longc 32000 lin 32000 trims on hea off pages 0 newp none emb on tab off feed off echo off
SQL> exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true)
SQL> select dbms_metadata.get_ddl('VIEW','V') from dual;

  CREATE OR REPLACE FORCE VIEW "SCOTT"."V" ("X") AS
  select '

.
#?
&_date
' x from dual;

Now you are safer to use dynamic sql in sqlplus. But hardly ever 100% safe.

Plenty of useful transformation parameters there to get rid of storage, tablespace and others.

dotNet transaction guard

Mon, 2014-08-11 10:16

also with ODP in 12c, you can check the commit outcome as in jdbc

let’s create a table with a deferred primary key


create table t (x number primary key deferrable initially deferred);

Here an interactive Powershell Demo


PS> [Reflection.Assembly]::LoadFile("C:\oracle\product\12.1.0\dbhome_1\ODP.NET\bin\4\Oracle.DataAccess.dll")

GAC    Version        Location
---    -------        --------
True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_64\Oracle.DataAccess\v4.0_4.121.1.0__89b483f429c47342\Oracle.DataAccess.dll

I first load the assembly. Some of my frequent readers may prefer Load(“Oracle.DataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342″) rather than hardcoding the oracle home directory.

PS> $connection=New-Object Oracle.DataAccess.Client.OracleConnection("Data Source=DB01; User Id=scott; password=tiger")

create the connection

PS> $connection.open()

connect

PS> $cmd = new-object Oracle.DataAccess.Client.OracleCommand("insert into t values (1)",$connection)

prepare the statement

PS> $txn = $connection.BeginTransaction()

begin transaction

PS> $ltxid = ($connection.LogicalTransactionId -as [byte[]])

Here I have my logical transaction id. Whatever happends to my database server, crash, switchover, restore, core dump, network disconnection, I have a logical id, and I will check it later.


PS> $cmd.executenonquery()
1

One row inserted


PS> $connection2=New-Object Oracle.DataAccess.Client.OracleConnection("Data Source=DB01; User Id=scott; password=tiger")
PS> $connection2.open()

I create a second connection to monitor the first one. Monitoring your own session would be too much unsafe and is not possible.


PS> $txn.Commit()

Commit, no error.


PS> $connection2.GetLogicalTransactionStatus($ltxid)
     Committed     UserCallCompleted
     ---------     -----------------
          True                  True

It is committed. I see it Committed from $connection2. This is what I expected.

Because I have a primary key, let’s retry and see what happend.


PS> $txn = $connection.BeginTransaction()
PS> $ltxid = ($connection.LogicalTransactionId -as [byte[]])
PS> $cmd.executenonquery()
1
PS> $txn.Commit()
Exception calling "Commit" with "0" argument(s): "ORA-02091: Transaktion wurde zur├╝ckgesetzt
ORA-00001: Unique Constraint (SCOTT.SYS_C004798) verletzt"
At line:1 char:1
+ $txn.Commit()
+ ~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OracleException
PS> $connection2.GetLogicalTransactionStatus($ltxid)
     Committed     UserCallCompleted
     ---------     -----------------
         False                 False

The commit fails, and from the connection2 we see it is not committed. It is a huge step toward integrity, as Oracle tells you the outcome of the transaction.

We see Committed=False.

Transaction guard

Fri, 2014-08-08 08:05

Getting the logical transaction id in 12c will greatly simplify your error handling and enhance your business continuity in your application.

In 11g and below, your java code use to look like


try {
  insert into...
} catch () {
  error_handling()
}

but one probably assumed the insert failed when it was committed (e.g. database server process core dump).

Now in 12c, you can get a logical transaction id and then later, from another session, check if that transaction was committed. Which solves quite a bunch of integrity issues (e.g. duplicate rows)

Let’s try


import java.sql.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.*;

public class TG {
  public static void main(String argv[]) throws
      SQLException {
    String url = "jdbc:oracle:thin:@(DESCRIPTION"
      +"=(ADDRESS=(PROTOCOL=TCP)(Host=srv01)("
      +"Port=1521))(CONNECT_DATA=(SERVICE_NAME="
      +"svc01)))";
    OracleDataSource ods=new OracleDataSource();
    ods.setURL(url);
    ods.setUser("SCOTT");
    ods.setPassword("tiger");
    OracleConnection conn = (OracleConnection) 
      ods.getConnection();
    LogicalTransactionId ltxid = conn.
      getLogicalTransactionId();
    try {
      System.out.println("Start");
      conn.prepareStatement(
        "insert into t values (1)").execute();
      if (Math.random() > .5) {
        throw new Exception();
      }
      System.out.println("OK");
    } catch (Exception e) {
      System.out.println("ERROR");
      OracleConnection conn2 = 
        (OracleConnection) ods.getConnection();
      CallableStatement c = conn2.prepareCall(
        "declare b1 boolean; b2 boolean; begin" 
        +"DBMS_APP_CONT.GET_LTXID_OUTCOME(?,b1,"
        +"b2); ? := case when B1 then "
        +"'COMMITTED' else 'UNCOMMITTED' end; "
        +"end;");
      c.setBytes(1, ltxid.getBytes());
      c.registerOutParameter(2, 
        OracleTypes.VARCHAR);
      c.execute();
      System.out.println("Status = "+
        c.getString(2));
    }
  }
}

getLogicalTransactionId gives me a transaction id (this is internally saved in SYS.LTXID_TRANS so it survives reboots, failover and disconnections) and GET_LTXID_OUTCOME gets the outcome.

There is few preparation steps


GRANT EXECUTE ON DBMS_APP_CONT TO SCOTT;
declare PARAMETER_ARRAY dbms_service.
  svc_parameter_array; 
begin 
  PARAMETER_ARRAY('COMMIT_OUTCOME'):='true';
  dbms_service.create_service(
    'SVC01','TNS01',PARAMETER_ARRAY); 
  dbms_service.start_service('SVC01'); 
end;
/
CREATE TABLE SCOTT.T(x number);

Due to my Random() call, I get exceptions sometimes, but it is always commits


C:\> java TG
Start
OK

C:\> java TG
Start
ERROR
Status = COMMITTED

C:\> java TG
Start
ERROR
Status = COMMITTED

No need to redo the insert.

Now I dropped the table t and run the same code


SQL> drop table scott.t;

Table dropped.

C:\>java TG
Start
ERROR
Status = UNCOMMITTED

Now it fails and I know it!

powershell goodies for Active Directory

Fri, 2014-07-11 07:04

What are my groups?


PS> Get-ADPrincipalGroupMembership lsc |
      select -ExpandProperty "name"
Domain Users
oracle
sybase

Who is member of that group ?

PS> Get-ADGroupMember oracle| 
      select -ExpandProperty "name"
Laurent Schneider
Alfred E. Newmann
Scott Tiger

What is my phone number ?

PS> (get-aduser lsc -property MobilePhone).MobilePhone
+41 792134020

This works like a charm on your Windows 7 PC.
1) Download and install Remote Server Administration Tools
2) Activate the windows feature under control panel program called “Active Directory Module for Powershell”
3) PS> Import-Module ActiveDirectory
Read the procedure there : how to add active directory module in powershell in windows 7

in memory option

Wed, 2014-07-09 05:12

Oracle 12cR1 patchset 1 is due this month and there is a new parameter that you can set to boost your performance. It is a bit of a SET "_FAST"=true parameter.

The in memory parameter is part of the sga. It is not mandatory to size it correctly, even if you do not have enough memory to hold your complete database, you can still play around with this parameter.

In a way, alter table t inmemory reminds me to the Oracle 8i alter table t cache and the Oracle 9i alter table t storage (buffer_pool keep).

But it is not free, I expect something close to the partitioning option, and it surely requires Enterprise Edition.

And also Oracle makes big noise about it, experts talk about a 1000x improvement, watch Database Industry Experts Discuss Oracle Database In-Memory.

The in memory cache is redundant with the database cache. It stores columns instead of blocks (or even results with the RESULT CACHE in 11g)

Don’t miss the Oracle Blog of @db_inmemory

Providing in-memory database is also positioning against HANA, a SAP in memory database. From OTN : Oracle Database In-Memory
Versus SAP HANA

A few years ago, Oracle acquired TimesTen. TimesTen is an in-memory database that works differently, where you can have fast response time (microseconds?) and could lose transactions (better faster than zero-data-loss). While TimesTen improves transaction speed, inMemory mostly improves queries (not writes).

check jdbc version

Wed, 2014-06-25 05:12

There are 2 versions to check when using jdbc.

The first one is in the name of the file : classes12.zip works with JDK 1.2 and later, ojdbc7.jar works with java7 and later.

Even if classes12.zip works fine with JAVA 8, it is not supported.

Be sure you check the support matrix on the Oracle JDBC FAQ

According to the support note 401934.1, only Oracle JDBC driver 11.2.0.3 (and greater) versions support JDK 1.7.

To check your version of the JDBC Driver, there are two methods.

One is with the jar (or zip) utility.


$ jar -xvf ojdbc7.jar META-INF/MANIFEST.MF
 inflated: META-INF/MANIFEST.MF
$ grep Implementation META-INF/MANIFEST.MF
Implementation-Vendor: Oracle Corporation
Implementation-Title: JDBC
Implementation-Version: 12.1.0.1.0
$ unzip classes12.zip META-INF/MANIFEST.MF
Archive:  classes12.zip
  inflating: META-INF/MANIFEST.MF
$ grep Implementation META-INF/MANIFEST.MF
Implementation-Title:   classes12.jar
Implementation-Version: Oracle JDBC Driver 
  version - "10.2.0.1.0"
Implementation-Vendor:  Oracle Corporation
Implementation-Time:  Jun 22 18:51:56 2005

The last digit is often related to the java version, so if you have ojdbc6 and use java 6, you’re pretty safe. If you have java 8, you won’t find any ojdbc8 available at the time of writing, a safer bet is to use the latest version and to wait for a support note. The latest notes about ojdbc7.jar currently does not display java 8 certification. Probably we will have to wait for a more recent version of ojdbc7.jar.

Another mean to find the version of the driver is to use DatabaseMetaData.getDriverVersion()


public class Metadata {
  public static void main(String argv[]) 
    throws java.sql.SQLException {
    java.sql.DriverManager.registerDriver(
      new oracle.jdbc.driver.OracleDriver());
    System.out.println(
      java.sql.DriverManager.
        getConnection(
"jdbc:oracle:thin:@SRV01.EXAMPLE.COM:1521:DB01", 
          "scott", "tiger").
            getMetaData().getDriverVersion());
  }
}


$ javac -classpath ojdbc6.jar Metadata.java
$ java -classpath ojdbc6.jar:. Metadata
11.2.0.3.0

fun with cron

Thu, 2014-06-19 03:10

Today I find out that my scheduler was too busy to execute all jobs in my crontab !?


* * * * * (while :;do ssh example.com :; done)
59 23 19 06 * touch /tmp/bang

my while loop is going to produce so much hangs on the cron deamon that it may not be able to read the crontab once a minute. If it reads it at 23:58 and at 00:00, the 23:59 won’t be run.

This is actually the first time I see this behaviour. And -believe me- it’s annoying!

distinct listagg

Fri, 2014-05-16 23:07

One limitation in listagg, you cannot use DISTINCT. Okay, there are plenty of distinct-capable listagg workarounds, but to get the real listagg working, it is a bit of an headache

With one listagg


SELECT 
  DEPTNO,
  LISTAGG (JOB, ',') 
    WITHIN GROUP (ORDER BY JOB) JOBS
FROM (
  SELECT DISTINCT DEPTNO, JOB  FROM EMP)
GROUP BY DEPTNO;

    DEPTNO JOBS                          
---------- ------------------------------
        10 CLERK,MANAGER,PRESIDENT       
        20 ANALYST,CLERK,MANAGER         
        30 CLERK,MANAGER,SALESMAN        

ok, it was not that hard, but it gets more difficult with two listagg’s


SELECT 
  LISTAGG (job, ',') 
    WITHIN GROUP (ORDER BY job) jobs,
  LISTAGG (deptno, ',') 
    WITHIN GROUP (ORDER BY deptno) deptnos
FROM (
  SELECT 
    DECODE(
      ROW_NUMBER () OVER (
        PARTITION BY deptno 
        ORDER BY 1),
      1, deptno) deptno,
    DECODE (
      ROW_NUMBER () OVER (
        PARTITION BY job 
        ORDER BY 1),
      1, job) job
  FROM emp
);
DEPTNOS  JOBS                                      
-------- ----------------------------------------
10,20,30 ANALYST,CLERK,MANAGER,PRESIDENT,SALESMAN

Too bad the DISTINCT keyword was not implemented

disable commit in procedure

Thu, 2014-05-08 00:14

There is an obscure syntax that prevents a procedure from issuing a commit


alter session DISABLE COMMIT IN PROCEDURE;

According to the doc, it prevents procedure from committing your data

Test case


SQL> alter session DISABLE COMMIT IN PROCEDURE
Session altered.
SQL> create table t(x number)
Table created.
SQL> create or replace procedure p is 
begin
  commit;
end;
Procedure created.
SQL> insert into t values (1)
1 row created.
SQL> exec p
BEGIN p; END;
Error at line 17
ORA-00034: cannot COMMIT in current PL/SQL session
ORA-06512: at "SCOTT.P", line 3
ORA-06512: at line 1

But some sys procedures may bypass this restriction


SQL> exec dbms_stats.gather_table_stats(user, 'T')
 PL/SQL procedure successfully completed.
SQL> rollback
Rollback complete.
SQL> select * from t

         X
----------
         1

The row was silently committed.