[PL/SQL] Convert number to binary string

Here is a simple function to convert a number to a binary string in Oracle PLSQL. This can also easily be converted to an number array instead.

 function num_to_bin(   
  n number   
  )   
  return varchar2   
  is   
  binval varchar2(64);   
  n2  number := n;   
  begin  
  if (n == 0) then  
   return '0';  
  end if;   
  while (n2 > 0) loop   
   binval := mod(n2, 2) || binval;   
   n2  := trunc(n2 / 2);   
  end loop;   
     
  return binval;   
  end num_to_bin;   

[Oracle] List uploaded Java resources

After extending functionality on an Oracle DB with Java resources, it is possible to list the available ones and their status with a query on the user_objects table:

SELECT

  object_name

 ,object_type

 ,status

 ,timestamp

FROM

  user_objects

WHERE

  (    object_name NOT LIKE 'SYS_%' 

   AND object_name NOT LIKE 'CREATE$%' 

   AND object_name NOT LIKE 'JAVA$%' 

   AND object_name NOT LIKE 'LOADLOB%'

  ) 

AND object_type LIKE 'JAVA%'

ORDER BY

  object_type

 ,object_name

;

[PL/SQL] existing state of package has been invalidated error

After compiling Oracle packages, it is possible to get a "existing state of packages has been discarded" or "existing state of package has been invalidated" error.

The cause is usually a global variable or constant declared in the package or package body that is re-initialized after the compilation action. The error is thrown to avoid having programs read the wrong state (variable/constant) of the package.

The easiest way to solve this issue is to simply disconnect the session and reconnect.

[PLSQL] Split string by delimiter and store it in VARRAY

Here is some sample Oracle PL/SQL code to split a string using a specific delimiter and storing the result pieces in a VARRAY.

 DECLARE  
   
 TYPE t_varchar2_varray IS TABLE OF VARCHAR2(4000);  
 list_val t_varchar2_varray := t_varchar2_varray();  
 l_explode_at PLS_INTEGER;  
   
 l_string VARCHAR2(4000) := your_string;  
 l_separator VARCHAR2(1) := your_separator;  
   
 BEGIN  
   
 l_explode_at := INSTR (l_string, l_separator); -- if the separator is not in the string, it returns 0   
   
 IF l_explode_at != 0 THEN   
        -- split all the values in the list and store them in our temp variable   
      LOOP   
      EXIT WHEN l_string IS NULL; -- keep going as long as there are values in the list   
        
       l_explode_at := INSTR (l_string, l_separator);   
       list_val.EXTEND;   
         
       -- if we have other values after this one   
       IF l_explode_at !=0 THEN   
         
            -- store it and keep going   
            list_val(list_val.COUNT) := TRIM (SUBSTR (l_string, 1, l_explode_at - 1)); --get the current value   
            l_string := SUBSTR (l_string, l_explode_at + 1); -- and move on the the next one   
              
       -- if there are no more values after me   
       ELSE   
            -- store the last one and quit   
            list_val(list_val.COUNT) := TRIM (SUBSTR (l_string, 1, LENGTH(l_string)));   
            l_string := NULL;   
       END IF;   
         
      END LOOP;   
        
 END IF;  
   
 END;  

Additionally, if you want to remove "empty" values (eg: tabs or spaces) from the string, you can add this code initially:

l_string := regexp_replace(l_string, '[[:space:]]*','');

[SQL] Oracle count elements in nested table

When working with collections in Oracle, you might need to count the number of elements they contain.

In the case of Nested Tables, you can either COUNT(*) after you unnest them:

SELECT COUNT(nt.*)
FROM myTable t, TABLE(t.nested_table) nt

or use the far simpler CARDINALITY function (returns NULL in case of empty list):

SELECT NVL(CARDINALITY(t.nested_table), 0)
FROM myTable t

In case of VARRAYs instead, you must still COUNT the elements but if you're working in PL/SQL though, you can go with:

myVarray.COUNT

[SQL] Oracle subquery in join statement

In Oracle, it's possible to use sub queries in a join statement by giving an alias to the subquery and joining on that alias:

SELECT a.column1, a.column2, c.column3

FROM a JOIN (

SELECT b.column1, b.column2, b.column3

FROM b

) c

ON (a.column1 = c.column1 AND a.column2 = c.column2)

Obviously you would never write a SIMPLE query EXACTLY as the example above, it's just to show the mechanics when you actually need to create a slightly more complex one

[Oracle DB] Create and schedule a job

To create and schedule a basic job on an Oracle DB, you'll need to create:

• A script performing the desired action
• A schedule
• A job

The script can be passed as code directly to the job, a function/procedure, a package function/procedure.

Step 1: Create package with our function

CREATE OR REPLACE PACKAGE myPackage AS   

PROCEDURE myProcedure;  

END;

CREATE OR REPLACE PACKAGE BODY myPackage AS

PROCEDURE myProcedure AS

--variable declaration here

BEGIN

  --something

  NULL;--not actually needed

END myProcedure;

END;

Step 2: Create schedule and job. Refer to the linked documentation for more information regarding the various parameters

 BEGIN  
   
 --create schedule  
 DBMS_SCHEDULER.CREATE_SCHEDULE (           
      repeat_interval => 'FREQ=YOUR_FREQUENCY',     
      start_date => TO_TIMESTAMP('START_DATE', 'FORMAT'),  
      schedule_name => '"mySchedule"');  
       
 --create job on mySchedule  
 DBMS_SCHEDULER.CREATE_JOB (  
       job_name => 'myJob',  
       job_type => 'PLSQL_BLOCK',  
       schedule_name => '"mySchedule"',  
       job_action => 'BEGIN myPackage.myProcedure; END;',  
       number_of_arguments => 0,  
       job_class => 'DEFAULT_JOB_CLASS',  
       enabled => true,  
       auto_drop => true,  
       comments => 'SOME_DESCRIPTION');  
 END;  
   

[Oracle] Run commands in different schema

In Oracle, it is possible to change the current schema/user with the ALTER SESSION statement:

ALTER SESSION SET CURRENT_SCHEMA = new_schema;

All subsequent commands will use that schema as default when nothing is specified. Of course, you'll need permission to execute the ALTER SESSION and all other statements on the new schema.

[PL/SQL] Extract data from XML BLOB

What if you need to query a stored XML, but it was unfortunately stored as a BLOB instead of a CLOB?

You can easily convert it to a CLOB first, then query it with:

 DECLARE  
   
 l_XML XMLTYPE;   
 l_blob BLOB;  
 l_clob CLOB;  
 l_dest_offsset PLS_INTEGER := 1;  
 l_src_offsset PLS_INTEGER := 1;  
 l_lang_context PLS_INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;  
 l_warning PLS_INTEGER;  
   
 l_myData MYTYPE;  
        
 BEGIN  
   
 --get the blob  
 SELECT myBlob  
 INTO l_blob  
 FROM myTable  
 WHERE something;  
   
 --create empty CLOB  
 DBMS_LOB.CREATETEMPORARY(l_clob, true);  
    
 DBMS_LOB.CONVERTTOCLOB(  
      dest_lob => l_clob,  
      src_blob => l_clob,  
      amount => DBMS_LOB.LOBMAXSIZE,  
      dest_offset => l_dest_offsset,  
      src_offset => l_src_offsset,  
      blob_csid => DBMS_LOB.DEFAULT_CSID,  
      lang_context => l_lang_context,  
      warning => l_warning  
 );  
   
 --crea XML from CLOB  
 l_XML := XMLTYPE.CREATEXML(l_clob);  
   
 --extract data from the XML  
 BEGIN  
 SELECT EXTRACTVALUE(  
   l_xml,  
   '//root/node/text()',  --be sure to match your XML tree naming! - /text() is optional, use it if the node content is CDATA
   'xmlns="OPTIONAL_XMLNS_HERE"' --optional, if you don't need it, do not even pass it to the function   
 ) node_value  
 INTO l_myData  
 FROM DUAL;  
 EXCEPTION WHEN NO_DATA_FOUND THEN --optional exception block  
      --something  
 WHEN OTHERS THEN  
      --something else  
 END;  
   
 END;  
   

[PL/SQL] Convert BLOB to CLOB

So you decided for some reasons that a BLOB would be a good type choice to store something for which the CLOB was invented, but now you'd like to take full advantage of all the CLOB functions or you need to query that XML you stored.

After blaming the gods for that poor choice, you may want to ease your pain by converting it to a CLOB:

 DECLARE  
   
 l_blob BLOB;  
 l_clob CLOB;  
 l_dest_offsset PLS_INTEGER := 1;  
 l_src_offsset PLS_INTEGER := 1;  
 l_lang_context PLS_INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;  
 l_warning PLS_INTEGER;  
   
 BEGIN  
   
 SELECT myBlob  
 INTO l_blob  
 FROM myTable  
 WHERE myConditions;  
   
 DBMS_LOB.CONVERTTOCLOB(  
      dest_lob => l_clob,  
      src_blob => l_blob,  
      amount => DBMS_LOB.LOBMAXSIZE,  
      dest_offset => l_dest_offsset,  
      src_offset => l_src_offsset,  
      blob_csid => NLS_CHARSET_ID('YOUR_CHARSET'), --optional, you may also use DBMS_LOB.DEFAULT_CSID if you don't have specific requirements  
      lang_context => l_lang_context,  
      warning => l_warning  
 );  
   
 END;  

You'll then have your converted BLOB in the l_clob variable

[PL/SQL] Duplicate rows changing column values

Imagine you have a table user_accesses like this:

NAME--SURNAME--DEPARTMENT
John--Smith----Marketing;Sales
Mary--Ann------Accounting;Something;Something_else

And you want to split the department column so that each row only has one value instead of a semi-colon separated list.

The following script replicates the rows where the specified column has multiple values, replicating all the data in the other columns. The specified column will be populated with each value in the list (replicated values are not discarded).

[PL/SQL] INSERT row as ROWTYPE

You can perform an INSERT passing a ROWTYPE value in Oracle versions greater than 9 with:

INSERT INTO mytable VALUES myrowtypeval;

[PL/SQL] Oracle continue loop with goto

Oracle database versions previous than 11g do not support the CONTINUE statement to skip to the end of a loop and keep cycling. As an alternative, you can use the GOTO statement WITH CAUTION.

Firstly, declare a label which marks the point were you want the execution to continue from using:

 <<label_name>>  

and remember that the label must be followed by an executable statement; in practice, it means you cannot place it directly before the END LOOP instruction. Instead, follow it with a NULL statement as in:

   <<label_name>>  
   NULL;  
 END LOOP;  

Then simply skip to it as needed with:

GOTO label_name;

[PL/SQL] Read, parse and store data from an XML file

Suppose you have a fairly complex XML file with this structure:

 <myRoot>  
      <myElement>  
           <aNode>  
                <aData>123</aData>  
                <bData><![CDATA[some lengthy text with special <characters>in</it>]]></bData>  
           </aNode>   
           <otherNode>  
                <otherData>31/12/1900</otherData>  
                .  
                .  
                .  
                <otherData>01/01/2000</otherData>  
           </otherNode>  
           <lastNode>  
                <simpleTag>  
                     <simpleValue1>some text</simpleValue1>  
                     <simpleValue2>other text</simpleValue2>  
                     <simpleFilter>0</simpleFilter>  
                </simpleTag>  
                <simpleTag>  
                     <simpleValue1>blah blah</simpleValue1>  
                     <simpleValue2>other blah</simpleValue2>  
                     <simpleFilter>1</simpleFilter>  
                </simpleTag>  
                <simpleTag>  
                     <simpleValue1>foo</simpleValue1>  
                     <simpleValue2>bar</simpleValue2>  
                     <simpleFilter>1</simpleFilter>  
                </simpleTag>  
                .  
                .  
                .  
           </lastNode>  
      </myElement>  
      .  
      .  
      .  
 </myRoot>  

Also, we won't have a proper schema file but we know that it'll have one or more myElement nodes, for each of which it'll have:

• a single aNode node with an integer aData element and a CDATA bData text element
• a single otherNode node with multiple otherData elements representing dates
• a single lastNode node with multiple simpleTag child nodes, each composed of three elements: a string in both simpleValue1 and simpleValue2 and an integer in simpleFilter. Additionally, we also know that for every lastNode node, just one and only one simpleTag node will have its simpleFilter element set to 0.

Now you want to store it in a table, then read it, parse its contents and save them in another table.

[PL/SQL] Oracle send mail through SMTP server with authentication

Sending mail through a PL/SQL script in Oracle is pretty easy thanks to the UTL_SMTP package.

Much like TELNET, sending a mail via our script will require us to use the HELO, MAIL FROM, RCPT TO and DATA commands. Thus, when sending a mail to multiple recipients - directly or in CC - we must issue the RCPT TO command once for each one and add the CC mail header to our message in order for them to appear as such.

[PL/SQL] Create and call Java procedure to read directory content

Sometimes even PL/SQL isn't enough; luckily Oracle gives us the ability to store Java functions and treat them as procedures.

Consider the following example: we know some files will be stored in a certain directory without knowing their names beforehand and we'd like to read and process those files from our PL/SQL code. By creating a simple Java function depending on the File class, we can solve our problem quickly and easily.

[SQL] Oracle IF SELECT statement

While working with PL/SQL, you may encounter the need to use something like:

IF EXISTS(SELECT ...) THEN


or

IF (SELECT ...) IS NULL THEN

but Oracle doesn't allow this, instead, you'll have to alter your function/procedure:

DECLARE
    myFlag INTEGER;
BEGIN
    SELECT COUNT(*) INTO myFlag
    FROM table t
    WHERE [conditions]
  AND ROWNUM = 1;
  IF myFlag = 1 THEN
    --something was returned
  ELSE
    --else no data was found
  END IF;
END;

Using both COUNT and ROWNUM=1 ensures us that, no matter whether something was returned by the SELECT or not, myFlag will in any case be either 1 or 0, which reflects the outcome you'd have had if you could have used the two statements at the beginning of this post.

[SQL] Oracle ORA-21000 "error number argument to raise_application_error of X is out of range"

Oracle allows us to raise user defined exceptions from PL/SQL code with RAISE_APPLICATION_ERROR.

The most common usage is:

RAISE_APPLICATION_ERROR(code, message)

Where code is an integer and message a string. Now, when the exception occurs, the user will see ORA-code: message.

Sometimes you may get the ORA-21000: error number argument to raise_application_error of X is out of range error. This happens because, for user defined errors, the error code (first argument) MUST be between -20000 and -20999 included.

[SQL] Oracle call web service and read response

In Oracle, you can invoke a web service directly from a PL/SQL function/procedure and parse the response using the UTL_HTTP module.

It should be included with every standard installation, and it's owned by SYS; you may need to run:

GRANT EXECUTE ON UTL_HTTP TO [user]

 as SYS (usually user: SYS password: manager) to allow other users to execute that package's procedures.

[PL/SQL] Test a string for a numeric value

In PL/SQL, you can easily check whether a string contains numeric values with:

LENGTH(TRIM(TRANSLATE(string, '0123456789', ' ')))

This will return null if the string contains only numeric characters otherwise it will return the number of non-numeric characters in it.