oracle substrb function to chop a string down to its first 4000 bytes

The substrb 1 , 4000 function will chop a string down to its first 4000 bytes, e.g.:

SELECT SUBSTR('1€3',2,1) , SUBSTRB('1€3',1,3) , LENGTH('1€3') , LENGTHB('1€3') FROM dual ;

Needed if using the (non default) option of ‘char’ in column lengths:

CREATE TABLE mytable ( col1 VARCHAR2(4000 CHAR) ) ;

You can change the default setting at session level:

ALTER SESSION SET nls_length_semantics = 'CHAR' ;

But that needs run every time you connect in, it doesn’t remember session settings after you disconnect. Permanently changing that parameter at database level is possible but not advisable.

You can see what is set as default at each of the 3 levels with:

SELECT * FROM nls_session_parameters  WHERE parameter IN ( 'NLS_LENGTH_SEMANTICS' , 'NLS_CHARACTERSET' , 'NLS_NCHAR_CHARACTERSET' ) ORDER BY parameter ;
SELECT * FROM nls_instance_parameters WHERE parameter IN ( 'NLS_LENGTH_SEMANTICS' , 'NLS_CHARACTERSET' , 'NLS_NCHAR_CHARACTERSET' ) ORDER BY parameter ;
SELECT * FROM nls_database_parameters WHERE parameter IN ( 'NLS_LENGTH_SEMANTICS' , 'NLS_CHARACTERSET' , 'NLS_NCHAR_CHARACTERSET' ) ORDER BY parameter ;

The NVARCHAR2 datatype could be useful if you want to use a different character set for a particular column.

Posted in Uncategorized | Leave a comment

Format sql files with sed: tab to space, pad commas with spaces, uppercase match

Shell script to make more readable all sql files in a directory – pads commas and brackets with whitespace, converts tabs to spaces, makes SQL keywords uppercase.

# FUNCTIONS
pad () {
    sed -i "s/\([^ ]\)$1/\1 $1/g" *sql
    sed -i "s/$1\([^ ]\)/$1 \1/g" *sql
}
upper () {
    sed -i "s/\(\<$1\>\)/\U\1/g" *sql
}
# MAIN
dos2unix *sql
# tabs to 4 spaces
sed -i 's/\t/    /g' *sql
# pad brackets commas semicolons
pad '('
pad ')'
pad ','
pad ';'
pad '!='
pad '='
pad '||'
# uppercase
upper nvl
upper decode
upper to_char
upper least
upper greatest
upper when
upper then
upper case
upper end
upper values
upper varchar2
upper in
upper date
upper upper
upper regexp_replace
upper sysdate
upper exists
upper replace
upper else
upper substr
upper count
upper distinct
upper partition
upper over
Posted in Uncategorized | 1 Comment

Laravel Symfony CodeIgniter

Laravel is the new CodeIgniter, a coding framework on top of PHP for simplified/standardised syntax. Complex PHP applications can become un-supportable without a coding framework like those in place.

To install and demo Laravel on WAMP:

(1) follow the steps in http://www.darwinbiler.com/how-to-install-laravel-on-wamp-for-beginners/ (note, uses a different WAMP php.ini file from normal).

(2) File c:\wamp\www\laraveltest\public\.htaccess then needs

RewriteBase /laraveltest/public/

added under the RewriteEngine line, as at http://stackoverflow.com/a/18412482

(3) Using WAMP click on wamp icon > apache > apache modules > scroll and check ‘rewrite_module Restart a LoadModule’ http://stackoverflow.com/a/14490649

(4) Then edit c:\wamp\www\laraveltest\app\routes.php and browse to http://localhost/laraveltest/public/user to see effects.

(5) For database connectivity, edit c:\wamp\www\laraveltest\app\config\database.php to specify database name.

(6) To demo database connectivity, again edit c:\wamp\www\laraveltest\app\routes.php to add code like

Route::pattern('id', '[0-9]+');
Route::get('/user/{id}', function($id)
{
    // Only called if {id} is numeric.
    $user = DB::table('users')->where('id', $id)->first();
    var_dump($user);

    echo '

prev | next

'; });

And browse to http://localhost/laraveltest/public/user/1 to see working.

A table called ‘users’ was used for this demo, with an ‘id’ numeric field plus some text fields

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `email` varchar(400) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
INSERT INTO `users` (`id`, `name`, `email`, `description`) VALUES
(1, 'John Smith', 'john@email.com', 'John''s description field...'),
(2, 'Jane Doe', 'jane@email.com', 'Jane''s description field');

More database code at http://laravel.com/docs/queries#selects, and forms code at http://laravel.com/docs/html.

For debugging, look in file c:\wamp\www\laraveltest\app\storage\logs\laravel.log

Currently Laravel supports four database systems: MySQL, Postgres, SQLite, and Microsoft SQL Server, but does not support Oracle http://laravel.com/docs/database. However the Laravel-OracleDB driver package is avilable as an add on https://github.com/jfelder/Laravel-OracleDB.

An alternative to Laravel is Symfony.

Installation of Symfony requires composer – but that should already have been installed along with Laravel above, which means would now just need:

mkdir c:\wamp\www\symfony
cd c:\wamp\www\symfony
composer create-project symfony/framework-standard-edition myproject/ ~2.4
php app/console server:run

[Source: http://symfony.com/doc/current/quick_tour/the_big_picture.html]

Once installed, browse to http://localhost:8000/ and also http://localhost:8000/demo/hello/YourNameHere.

Posted in php | Leave a comment

PL/SQL bulk collect union

PL/SQL bulk collect will work with UNION ALL when written with an inline view:

DECLARE
   TYPE l_type_assoc IS TABLE OF VARCHAR2(14) INDEX BY BINARY_INTEGER ;
   l_big l_type_assoc ;
BEGIN
     WITH q AS (
          SELECT ename AS name
            FROM emp
           UNION ALL
          SELECT dname AS name
            FROM dept
          )
   SELECT name
     BULK COLLECT INTO l_big
     FROM q
    ORDER BY name ;

   FOR indx IN 1..l_big.COUNT
   LOOP
      DBMS_OUTPUT.put_line ( l_big(indx) ) ;
   END LOOP ;

END ;
/

If the array you are using is not an associative array (that is, does not have the INDEX BY clause in its declaration) then an alternative method is to merge arrays together after they are populated with multiset union:

DECLARE
   TYPE l_type_varray IS TABLE OF VARCHAR2(14) ;
   l_tmp l_type_varray ;
   l_big l_type_varray ;
BEGIN

   SELECT ename BULK COLLECT INTO l_tmp FROM emp ;

   l_big := l_tmp ;  -- copy the array

   SELECT dname BULK COLLECT INTO l_tmp FROM dept ;

   l_big := l_big MULTISET UNION ALL l_tmp ;  -- union the arrays

   FOR indx IN 1..l_big.COUNT
   LOOP
      DBMS_OUTPUT.put_line ( l_big(indx) ) ;
   END LOOP ;

END ;
/

PHP’s oci_bind_array_by_name will only work with associative arrays, returns error ‘ORA-06550: PLS-00306: wrong number or types of arguments in call’ if varrays are used.

Posted in Uncategorized | Leave a comment

Extended Hexadecimal in Oracle

Standard decimal > hexadecimal is done with:

select to_char ( 10 , 'x' ) from dual ;

But I wanted a kind of extended hexadecimal that would keep single letters going up beyond 15/f, so would return ‘g ‘for decimal 16 instead of returning hexadecimal ’10′.

Why did I want that? I needed to squeeze a 2 digit number column into a 1 character column in an extract transform load operation. Most of the source data was less than 16, but there were a few values higher than that, and a simple decimal > hexadecimal translation wouldn’t work for those.

The ascii value for ‘a’ is 97, so using the chr function to turn numbers into their ascii character equivalents gives:

SELECT CASE
           WHEN numcol BETWEEN  0 AND  9 THEN TO_CHAR ( numcol )
           WHEN numcol BETWEEN 10 AND 35 THEN CHR ( 87 + numcol )
       END AS extended_hexadecimal
  FROM mytable ;
Posted in Uncategorized | Leave a comment

srvctl start listener -n nodename

Preferred method to stop and start oracle listener in RAC enivronment:

srvctl config listener -n nodename
srvctl stop listener -n nodename
srvctl start listener -n nodename
Posted in RAC | Leave a comment

Denormalise listagg children on single row comma separated

You can denormalise data using the listagg function, so to display users with their roles on a single line comma separated:

SELECT grantee
     , LISTAGG ( granted_role , ' , ' ) WITHIN GROUP ( ORDER BY granted_role ) AS granted_roles
  FROM dba_role_privs
 GROUP BY grantee
 ORDER BY 1 ;

H/t Adrian Billington. Continue reading

Posted in Uncategorized | Leave a comment

Oracle remove duplicates parent child with listagg

De-duplicating parent – child table sets of data here using the listagg function to merge all the child rows into a single denormalised column.

set serverout on
BEGIN
FOR d1 IN (
     WITH denorm AS (
          SELECT parent.aos_code
               , parent.pre_group_id
               , LISTAGG ( child.aos_code || child.stud_pass ) WITHIN GROUP ( ORDER BY child.aos_code || child.stud_pass ) AS entry_value
            FROM StcAPreList parent
            JOIN StcAPreGroupAos child
              ON parent.pre_group_id = child.pre_group_id
           GROUP BY parent.aos_code , parent.pre_group_id
          )
   SELECT a.pre_group_id
     FROM denorm a
    WHERE a.pre_group_id > (
          SELECT MIN ( b.pre_group_id )
            FROM denorm b
           WHERE a.aos_code = b.aos_code
             AND a.entry_value = b.entry_value )
    ORDER BY 1
   )
LOOP
   dbms_output.put_line ( 'deleting ' || d1.pre_group_id ) ;
   DELETE FROM StcAPreGroupAos child WHERE child.pre_group_id = d1.pre_group_id ;
   DELETE FROM StcAPreList parent WHERE parent.pre_group_id = d1.pre_group_id ;
END LOOP ;
END ;
/
Posted in Uncategorized | Leave a comment

Create output XML data files from oracle with dbms_xmlgen

The below code will generate XML format output, just plug in any SQL and it will work without further development.

This is based on three articles by other authors:

  • Tim Hall – but Tim’s code has a bug that stops it outputting after the first 32k of data.
  • Tom Kyte – the fix for that bug.
  • Steve Karam – code to nest XML data.

Performance is kept optimal by using set based SQL statements without cursor loops, and by using utl_file.put to write in 32k chunks rather than line-by-line of dbms_output.put_line or utl_file.put_line.

Before generating XML, ask if this is the best approach for transferring the data. Database to database copying is better done across database link, if possible.

Code: Continue reading

Posted in XML | Leave a comment

Replace all tnsnames.ora files with a single master file using TNS_ADMIN

Maintaining large numbers of client tnsnames.ora files is a pain.  Oracle Internet Directory is the recommended solution to this, but it is not an easy exercise to set up or migrate to.

An easier alternative is to set TNS_ADMIN in all client PCs to use a single common tnsnames.ora file

set TNS_ADMIN=\\network\share\path

All the client PCs need read access to that file, but don’t need write access.
One drawback of doing that is that if the network share is unavailable for any reason then all client connections will fail, so a highly resilient network share would be needed. A safer alternative is to set TNS_ADMIN to the same local disk folder on each PC

set TNS_ADMIN=C:\SecureITFolder

and rely on automated PC management tools like Microsoft System Centre to deploy the master tnsnames file out to each client PC at regular intervals.

The precedence of TNS_ADMIN in Windows is supposedly (I’ve not yet tested this):

  1. In current working directory
  2. TNS_ADMIN defined by the session (set tns_admin= in batch/command prompt)
  3. TNS_ADMIN defined as a global environment variable (control panel > system > advanced system settings > advanced > environment variables)
  4. TNS_ADMIN defined in registry (run “regedit” and look under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\…)
  5. Default location (network\admin directory for that particular Oracle Home)
Posted in SQL*Net, Windows | Leave a comment