Author Archives

PLS-00201 in stored procedures

When you grant table access thru a role, you cannot use that role in a stored procedure or view. create role r; create user u1 identified by ***; grant create procedure, create session to u1; create user u2 identified by ***; grant create procedure, create session, r to u2; conn u1/*** create procedure u1.p1 is […]

to R1 or to R2

In the past, most of my customers skipped R1 releases. That is, 8.1.7 -> 9.2 -> 10.2 -> 11.2. SAP does the same. For the very first time SAP plans to go to 12.1.0.2 + some PSU in spring 2015. But only to avoid running out of support and without any fancy feature like Multitenant […]

select pdf from sqlplus

sqlplus 10gR2 and later allows you to select from a BLOB. If you use linux, you could convert the hex output to binary with xxd sqlplus -s scott/tiger <<EOF |xxd -p -r >doc.pdf set pages 0 lin 17000 long 1000000000 longc 16384 select document from emp where ename=user; EOF Obviously, it could also be a […]

powershell goodies for Active Directory

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 […]

in memory option

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 […]

SPARC Supercluster

Oracle buys Sun was an exciting accouncement 20 months ago. What did change in the Solaris/Oracle Database world? First, Oracle delivered Exadata on Sun Hardware (x86_64). Second, they delivered Exadata on Sun Solaris Operating System (x86_64). But now, they announced a combination of software and hardware that will run Oracle database faster than anything ever [...]

connect by and recursive with (part 2)

According to the doc
The subquery_factoring_clause now supports recursive subquery
factoring (recursive WITH), which lets you query hierarchical data.
This feature is more powerful than CONNECT BY in that it
provides depth-first search and breadth-first search, and supports
multiple recursive branches. A new search_clause and cycle_clause
let you specify an ordering for the rows and mark cycles in the
recursion
As written there [...]