web analytics

Use nested table as table in select

Filed in Oracle Leave a comment

create or replace type char_nt is table of varchar2(64);
/



with letters as (select column_value
                 from table(char_nt('W','X','Y','Z')))
select letters.column_value
,           dual.dummy
from letters
left outer join dual on (dummy = letters.column_value)
union
select letters.column_value
,           object_name
from user_objects
join letters on (substr(object_name,1,1) = letters.column_value); 
;

COLUMN_NAME DUMMY
W
X X
X XMLTEST
Y
Z

Loading files into a BLOB column using Oracle External LOB BFILE Data type

Filed in Database | Oracle Leave a comment

I have a list of filenames and the corresponding files. I need to insert them into a table (filename, blobfile).

Make sure the user has execute privileges on UTL_FILE and DBMS_LOB
Put the files into an Oracle directory accessable by the oracle user

CREATE TABLE filenametab (filename VARCHAR2(100));
INSERT INTO filenametab values ('a.pdf');
INSERT INTO filenametab values ('b.pdf');

CREATE TABLE blobtab (filename VARCHAR2(100), blobfile BLOB);

CREATE OR REPLACE PROCEDURE LOAD_BLOBS (dir VARCHAR2)
IS
-- Declare section
  CURSOR ft IS 
  SELECT *
  FROM filenametab;
  
  PROCEDURE ins_blob (ft filenametab%ROWTYPE)
  AS
      lBlob  BLOB;
      lFile  BFILE := BFILENAME(dir, ft.filename);
  BEGIN 
      INSERT INTO blobtab (filename
                          ,blobfile)
      VALUES (ft.filename
             ,empty_blob())
      RETURNING blobfile INTO lBlob;
      DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);
      DBMS_LOB.OPEN(lBlob, DBMS_LOB.LOB_READWRITE);
      DBMS_LOB.LOADFROMFILE(DEST_LOB => lBlob
                           ,SRC_LOB  => lFile
                           ,AMOUNT   => DBMS_LOB.GETLENGTH(lFile));
      DBMS_LOB.CLOSE(lFile);
      DBMS_LOB.CLOSE(lFile);
      DBMS_LOB.CLOSE(lBlob);
      COMMIT;
  END;
  
-- Main section  
BEGIN
  FOR c1 IN ft
  LOOP
    ins_blob(c1);
  END LOOP;
END;
/

EXEC load_blobs('YOURDIRECTORY');

Detect Omissions : update to NULL using SOA JCA database Adapter

Filed in Oracle | SOA Leave a comment

Default detect omissions is enabled. Empty elements in an update request are ignored and the corresponding fields in the database are left as is. Bottom line is: you can’t update a field to NULL. Disabling this option will set the fields to NULL. Warning : this means all elements you leave empty or do not specify in the call will be set to NULL

Detect Omissions Allows the MERGE and INSERT to ignore empty or missing XML elements in the input payload. For a MERGE this will prevent valid but unspecified values from being overwritten with NULL. For INSERT operations, they will be omitted from the INSERT statement, allowing default values to take effect.

Oracle quote operator

Filed in Database Leave a comment

I hate double quoting, ”’it”s a mess”’. Luckely these days we have the quote operator:

q'{delimiter}string{delimiter}'

INSERT INTO height(id, height) 
VALUES(height-seq.nexval, q'#5'9#');

Drop a table

Filed in Database Leave a comment

I like it when my scripts only return ORA- errors if there is something wrong. Often I see scripts that want to make sure a table does not exist before creating it. The logfiles are flooded by
ORA-00942: table or view does not exist.
It’s not difficult to filter them but it’s much nicer to do saome sort of a ‘drop if exist’.

One solution is to create a procedure that queries the dictionary

CREATE OR REPLACE PROCEDURE drop_if_exists (mytable VARCHAR2)
DECLARE
  l_exists varchar2(1);
BEGIN
  select 'Y' 
  into l_exists 
  from user_tables
  where table_name = mytable;

  if l_exists = 'Y' 
  then
    execute immediate 'drop table '||mytable;
  end if;
END;
/

But this queries the dictionary for every drop statement. More efficient is to handle the exeception as it occurs.

CREATE OR REPLACE PROCEDURE drop_if_exists (mytable VARCHAR2)
DECLARE
   tab_not_exist EXCEPTION;
   PRAGMA EXCEPTION_INIT(tab_not_exist, -942);
BEGIN
   execute immediate 'DROP TABLE '||mytable;
EXCEPTION
   WHEN tab_not_exist THEN
      null; -- handle the error
END;
/

How to make a Webservice with an IN LIST as input using SOA Composite Editor / JCA Database Adapter

Filed in Oracle | SOA | XML Leave a comment

I need to create a function that takes a list of integers (StudentId’s) as input and returns a list of Students. I found this link. It works but it breaks on a parameter that contains the delimiting character so it’s not ideal.

Database Objects

For this solution we need three Oracle user defined types and a function. It relies on objects in the database.

-- A VARRAY of Integers used for the IN parameter of the function:

create or replace
TYPE int_array AS TABLE OF integer;

-- An Object describing a Person:

create or replace
type pers_row_type as object 
(persno integer
,firstname varchar2(10)
,lastname varchar2(30)
);

-- A VARRAY of pers_Row_Type used as the out parameter:

create or replace
TYPE pers_tab_type IS TABLE OF pers_Row_Type;

-- Create the function:

create or replace
function list_persons (perslist int_array) return pers_tab_type
is
  v_tab pers_tab_type := pers_tab_type();
begin
  for cur_row in (select * 
                  from person
                  ,    table(cast(perslist as int_array)) dum
                  where person.persno = dum.column_value)
                  
  loop
    v_tab.extend;
    v_tab(v_tab.last) := pers_row_type(cur_row.persno,cur_row.first_name,cur_row.last_name);
  end loop;
  return v_tab;
end;

Create Project

Open JDeveloper and start a new Project
Create a SOA project : SOAInListDemo
create database adapter

Use empty composite template
Finish

Create Database Adapter

Pull the component “Database Adapter” into the “External References” swimming lane
create database adapter

Give it a name : getStudentsByIds
Select connection to the database where you created the function
Speficy JNDI Name : eis/DB/dev11col
Select : “Call a Stored Procedure or Function”

create database adapter

Click browser and select the LIST_PERSONS function

create database adapter

Next|Finish
A WSDL and a XSD file are created with the schema definitions for the Oracle Types of the IN and OUT parameters

Create the Mediator

Pull the component “Mediator” into the middle “Components” swimming lane

create database adapter

Give it a name : “PersonsMediator”
Select from Template the option “Interface Definition from WSDL
To the right of the field WSDL URL click the icon “Find existing WSDL’s”
Select the file generated from the database adapter : getStudentsByIds.wsdl

create database adapter

Connecting the components

Connect the Mediator to the Database Adapter

create database adapter

Transformation

In the mediator we connect the source and target messages
Open the mediator

create database adapter

Click the icon right of the “transform Using” fields
Start with the request
Create new mapper file
Connect elements and insert XSLT construct “for each” as shown below

create database adapter

Open response mapper file
Connect elements and insert XSLT construct “for each” as shown below

create database adapter

Deploy and test

Request

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:list="http://xmlns.oracle.com/pcbpel/adapter/db/COL/LIST_PERSONS/">
   <soapenv:Header/>
   <soapenv:Body>
      <list:InputParameters>
         <!--Optional:-->
         <list:PERSLIST>
            <!--Zero or more repetitions:-->
            <list:PERSLIST_ITEM>1</list:PERSLIST_ITEM>
            <list:PERSLIST_ITEM>2</list:PERSLIST_ITEM>
            <list:PERSLIST_ITEM>3</list:PERSLIST_ITEM>
            <list:PERSLIST_ITEM>4</list:PERSLIST_ITEM>
         </list:PERSLIST>
      </list:InputParameters>
   </soapenv:Body>
</soapenv:Envelope>

Response
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing">
   <env:Header>
      <wsa:MessageID>urn:35D50010459911E1BF388DF20C4C6D71</wsa:MessageID>
      <wsa:ReplyTo>
         <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>
         <wsa:ReferenceParameters>
            <instra:tracking.ecid xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">0000JK9abd4Fw000jzwkno1F62iL000puA</instra:tracking.ecid>
         </wsa:ReferenceParameters>
      </wsa:ReplyTo>
   </env:Header>
   <env:Body>
      <db:OutputParameters xmlns:db="http://xmlns.oracle.com/pcbpel/adapter/db/COL/LIST_PERSONS/">
         <db:LIST_PERSONS>
            <db:LIST_PERSONS_ITEM>
               <db:PERSNO>1</db:PERSNO>
               <db:FIRSTNAME>Rob</db:FIRSTNAME>
               <db:LASTNAME>van Laarhoven</db:LASTNAME>
            </db:LIST_PERSONS_ITEM>
            <db:LIST_PERSONS_ITEM>
               <db:PERSNO>2</db:PERSNO>
               <db:FIRSTNAME>Bas</db:FIRSTNAME>
               <db:LASTNAME>Jansen</db:LASTNAME>
            </db:LIST_PERSONS_ITEM>
            <db:LIST_PERSONS_ITEM>
               <db:PERSNO>3</db:PERSNO>
               <db:FIRSTNAME>Mark</db:FIRSTNAME>
               <db:LASTNAME>Schenk</db:LASTNAME>
            </db:LIST_PERSONS_ITEM>
            <db:LIST_PERSONS_ITEM>
               <db:PERSNO>4</db:PERSNO>
               <db:FIRSTNAME>Koen</db:FIRSTNAME>
               <db:LASTNAME>van Dijk</db:LASTNAME>
            </db:LIST_PERSONS_ITEM>
         </db:LIST_PERSONS>
      </db:OutputParameters>
   </env:Body>
</env:Envelope>

Conclusion

The downside of this solution is that we have to manage (different versions of) Oracle database objects . But we can use the the power of PL/SQL procedures and functions. Every function and procedure in the database can be accessed using Web Services.

zenity

Filed in Oracle Leave a comment

Sometimes you learn something new and ask yourself : how is it possible I’ve never seen this before. Zenity is a tool that allows you to display Gtk+ dialog boxes from the command line and through shell scripts. Here is a rather crude example:

#!/bin/sh

ORATAB=/etc/oratab; export ORATAB
ORAENV=/usr/local/bin/oraenv;export ORAENV



# Set this in accordance with the platform
db=`if [ ! $ORATAB ] ; then
      echo "$ORATAB not found"
      exit 1;
    fi

    (cat $ORATAB |grep -v "^$" | while read LINE
    do
    case $LINE in
      \#*)                ;;
      *)                  echo FALSE
                          echo $LINE | awk -F: '{print $1}'
                          ;;
    esac
    done) |
    zenity  --list  --text "Choose a database" --radiolist  --column "Pick" --column "Database" --width=300 --height=300`

un=`zenity --entry \
        --title="Username for $db " \
        --text="Enter your _username:" \
        --entry-text "system"`

pw=`zenity --entry \
        --title="Add an Entry" \
        --text="Enter your _password for $db:" \
        --entry-text "password" \
        --hide-text`

zenity --question \
          --text="Are you sure you wish to logon on $db as $un?"
if [ $? ]
then
   ORACLE_SID=$db;export ORACLE_SID
   ORAENV_ASK=NO;export ORAENV_ASK
   $ORAENV $db
   sqlplus $un/$pw@$db
fi

Run this script:



VirtualBox 4.0 released

Filed in Oracle Leave a comment


Oracle VM VirtualBox is an x86 virtualization software package. It is installed on an existing host operating system; within this application, additional guest operating systems, each known as a Guest OS, can be loaded and run, each with its own virtual environment.

Use it for:

  • Maintaining multiple (conflicting) development environments. Create a virtual server for each. Activate/deactivate them as needed
  • Share a preconfigured development environment with co workers
  • Save an environment with old software versions for testing. You need to test something on XP but run Windows7 on your client.
  • Test new software: Windows 8, first install it on a virtual machine
  • Get it here

    Select hidden optimizer parameters

    Filed in Oracle Leave a comment

    Hidden parameters are not included in the v$parameter view. Use this statement to view all optimizer parameters in a 11g (and 10g) database:

    SELECT
      x.ksppinm name,
      y.ksppstvl VALUE,
      decode(ksppity,
        1,   'BOOLEAN',
        2,   'STRING',
        3,   'INTEGER',
        4,   'PARAMETER FILE',
        5,   'RESERVED',
        6,   'BIG INTEGER',
        'UNKNOWN') typ,
      decode(ksppstdf,
        'TRUE',   'DEFAULT VALUE',
        'FALSE',   'INIT.ORA') isdefault,
      decode(bitand(ksppiflg / 256,   1),
        1,   'IS_SESS_MOD(TRUE)',
        'FALSE') isses_modifiable,
      decode(bitand(ksppiflg / 65536,   3),
        1,   'MODSYS(NONDEFERED)',
        2,   'MODSYS(DEFERED)',
        3,   'MODSYS(*NONDEFERED*)',
        'FALSE') issys_modifiable,
      decode(bitand(ksppstvf,   7),
        1,   'MODIFIED_BY(SESSION)',
        4,   'MODIFIED_BY(SYSTEM)',
        'FALSE') is_modified,
      decode(bitand(ksppstvf,   2),
        2,   'ORA_STARTUP_MOD(TRUE)',
        'FALSE') is_adjusted,
      ksppdesc description,
      ksppstcmnt update_comment
    FROM x$ksppi x,
      x$ksppcv y
    WHERE x.inst_id = userenv('Instance')
     AND y.inst_id = userenv('Instance')
     AND x.indx = y.indx
     AND x.ksppinm LIKE '%optim%';
    
    

    Tsst

    Filed in Fun Leave a comment

    Words of wisdom from the man :

      “To be happy, (dogs) basically need a good job, and good food, and a pat on the head. Americans tend to over-do on the affection and under-do on the exercise.”
      “Stay calm and assertive.”
      “Humans are the only animals who will follow unstable pack leaders.”

    Click on his image for an impression of his work. You’ll first have to sit through some advertising.

    TOP
    Wordpress Code Snippet by Allan Collins