WITH enhancement in Oracle 12c

 No Responses »
Oct 102016
 

My colleague needed to use a function in the database in a query, but found that the function was not defined/reachable in his schema. So the solution is to ask for the function to be granted to you, or to create the function in your schema, or…

…use the new WITH enhancement available from Oracle 12c.

Here is a very simple example, that squares a number and returns it. The function is used in the query. How nice :-p

with
  function s(x in integer) return integer is
  begin
    return x*x;
  end;
  
  q as (select 1 n from dual union all select 2 from dual)
  
  select q.n,s(q.n) from q
/

Acting like you are from Oracle

 No Responses »
Sep 102015
 

The default Oracle 12c database installation (that a lot of developers will use without going into the advanced setup) is a bit strict. For instance you can’t just create users that don’t comply to the user naming policies. But what about “sys” or “system”? They don’t comply either!

Add the following to your script:

alter session set "_ORACLE_SCRIPT"=true;

Things should work now. Basically you’re acting like you’re an Oracle supplied script now. And we know the rules don’t apply to Oracle, right?

Lists as tables in Oracle

 No Responses »
Jul 242015
 

While searching for a way to find records in a table that best match what I’m looking for, I just discovered (Google is your friend) Oracle’s sys.odcinumberlist and sys.odcivarchar2list. Very nice functions to create in-memory tables.

SQL> select * from table(sys.odcivarchar2list('a',null,'b','c'));

COLUMN_VALUE
--------------
a

b
c

Never too old to learn new tricks….

SQL joins

 No Responses »
Mar 122013
 

I’m an oldskool Oracle user, so I learned and used joins in the oldfashioned way. Just name all the tables in the WHERE-clause, and specify the join conditions, and use (+) for outer joins. Times change, and the new syntax has been around for years. Never really used it, but today I did, and I found this image on the internet, I thought I’d share it, since I think it’s quite helpful.

sql-joins

It clearly shows how to use LEFT and RIGHT joins.

Loading 3D points into Oracle with SQL Loader

 2 Responses »
Feb 152013
 

I received some files with only coordinates, which had to be loaded into Oracle. It’s not super difficult, but I thought I’d share it anyway. The files contain some comment lines first, easily recognized the the hash (#) as the first character. The rest of the lines are x, y and z separated by spaces, like this:

#[MetadataBegin]
#[OrderNumber]2013001
#[Measured by]Piet
#[MeasureDate]30-1-2013
#[MetadataEnd]
 58392.50 439762.50 -0.01
 58397.50 439762.50 -0.03
 58402.50 439762.50 -0.03
 58407.50 439762.50 -0.03

The control file should be like this (change the filename etc):

OPTIONS (DIRECT=TRUE,MULTITHREADING=TRUE)
LOAD DATA
INTO TABLE DEPTHDATA
WHEN 1-1<>'#'
FIELDS TERMINATED BY ' '
(ID SEQUENCE(1,1),
 GEOMETRY COLUMN OBJECT
 (
  SDO_GTYPE CONSTANT "3001",
  SDO_POINT COLUMN OBJECT
   (X FLOAT EXTERNAL TERMINATED BY ' ' ,
    Y FLOAT EXTERNAL TERMINATED BY ' ' ,
    Z FLOAT EXTERNAL TERMINATED BY ' ' )
   )
)

Dumping data very fast

 No Responses »
Aug 062011
 

Too bad my comment on the original post was deleted. Perhaps they don’t want to know what I had to say, or they don’t like C#? From the top of my head I wrote something like:
The original post.

I created a very simple C# implementation, a console application with hardcoded username/password and select-statement. It roughly has the same speed as the Pro*C (C routine) version, about 1.7 million ALL_SOURCE records in about 57 seconds. C# is a little more appreciated amongst managers, since C# developers are a lot easier to hire than Pro*C developers. Why on earth you want to fiddle around with Java when something needs to be done fast, puzzles me. Even Java 6 (don’t know about 7 yet) does not come close to the performance of .NET, let alone natively compiling languages.

Oracle SQLDeveloper 3

 No Responses »
Jun 172011
 

Oracle released SQLDeveloper version 3 a while ago. One of the new things is the support for Spatial. If you right-click on a table you will see “spatial” as the last item in the context-menu.

But my my my. We all know GeoRaptor is *the* plugin for SQLDeveloper when it comes to spatial. Now Oracle in all its wisdom tried to create the same thing. Mind the word of choice: “TRIED”.

Dear Larry, go party on your boat, watch IronMan2 or something, but leave software development to people that actually get it. Thank you Simon.

 Older Entries