Get a delimited list in Oracle SQL

There are many times that I need to retrieve a delimited list in SQL and every time I have to track the solution down.

 

So here it is.

SELECT RTRIM (
 rtrim(XMLAGG (XMLELEMENT (e, column_name || ',')).EXTRACT ('//text()'), ',') name
 FROM table_name;

 

Just to break this down a little

rtrim(XMLELEMENT (e, column_name || 'This is the delimiter')).EXTRACT 
('//text()'), 'This is the delimiter')



Advertisements

Calling a Linux bash script from Oracle PLSQL

Here is a little cool bit of PLSQL that allows you to call anything really from PLSQL.

This script is to FTP files. There are methods of using FTP direct from PLSQL, the issue that I had was that I had to FTP from one file name to another.

I have left the variable names in the SET_JOB_ARGUMENT_VALUE just to illustrate the connection between the package and bash script

job_name_ varchar2(100);

begin

-- Create the job_name_ in what ever means you want, either hard codes or a date selection. 
Mine is passed from another PLSQL package
DBMS_SCHEDULER.DROP_JOB(job_name_);
exception
 when others then
dbms_output.put_line('There is no job to drop');
End;

dbms_scheduler.create_job(
job_name=>job_name_,
 job_type=>'executable',
 job_action=>'PATH TO SCRIPT',
 number_of_arguments=>7,
 enabled=>FALSE);

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(job_name => 
job_name_,argument_position =>1,argument_value => v_server_address);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(job_name => 
job_name_,argument_position =>2,argument_value => v_username);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(job_name => 
job_name_,argument_position =>3,argument_value => v_password);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(job_name => 
job_name_,argument_position =>4,argument_value => v_local_file_);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(job_name => 
job_name_,argument_position =>5,argument_value => v_put_file_);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(job_name => 
job_name_,argument_position =>6,argument_value => v_local_directory_);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(job_name => 
job_name_,argument_position =>7,argument_value => v_remote_directory_);
DBMS_SCHEDULER.ENABLE(job_name_);

The bash script is as follows,  as you can see you change the number of arguments to match

your script. In this instance there are 7 parameters to pass.

#!/bin/sh -vx 
# Script to FTP data to server 
# Paramters: host FTP Server 
# user - FTP Username 
# passwd - FTP Password 
# file - File to send/put 
# toname - this is a special if you want to call the file a different 
# name as you put it up
# local_dir - where the local file can be found
# remote_dir - where to put the local file
# type of FTP PUT / GET
############################################################################ 
# Variables 
HOST=$1
USER=$2
PASSWD=$3
FILE=$4
TONAME=$5
LOCAL_DIR=$6
REMOTE_DIR=$7
DATE=`date +%Y-%m-%d`

ftp -inv $HOST<<END_SCRIPT>>/u12/transfer/logs/ftp_log_$DATE
quote USER $USER 
quote PASS $PASSWD 
lcd $LOCAL_DIR
cd $REMOTE_DIR
put $FILE $TONAME 
quit 
END_SCRIPT 
exit 0

To get this working there are a number of elements that you will need to setup.

First of all you need to allow job and external job creation in Oracle

grant create job to USERNAME;
grant create external job to USERNAME ;

If you notice in the bash script the FTP job needs to write out to a log file where the path is /u12/transfer/logs.

This means that you need to know who the user will be to run the script, this can be found by reading the following file

$ORACLE_HOME/rdbms/admin/externaljob.ora

Mine looks like this

# This configuration file is used by dbms_scheduler when executing external
# (operating system) jobs. It contains the user and group to run external
# jobs as. It must only be writable by the owner and must be owned by root.
# If extjob is not setuid then the only allowable run_user
# is the user Oracle runs as and the only allowable run_group is the group
# Oracle runs as.

run_user = nobody
run_group = nobody

The lines that you need to look at are run_user and run_group

So you would create the directory

mkdir -p /u12/transfer/logs
chown nobody:nobody /u12/transfer/logs

This would then allow the PLSQL package to create the log file.

If you want to look at the schedule details then you can use the following SQL

select 
log_date,
job_name, 
status, 
req_start_date, 
actual_start_date, 
run_duration,
additional_info
from dba_scheduler_job_run_details
where job_name = '&job_name'
order by log_date

Display exception messages

OK when you write a PLSQL procedure or function you should trap for exceptions.

Sometimes you might want to display these exceptions to the user. In this example it is the logging into the application and displaying if the user is locked.

So here is the snipped of the PLSQL code. Do not worry about what it is doing the main line is highlighted

if v_locked = 'N' then
 v_authenticated := authenticate_password(p_username => p_username,p_password => p_password);
 return(v_authenticated);
 else

 apex_util.set_session_state('LOGIN_MESSAGE','Your account is currently locked');

 return false;

 end if;

The important part here is the LOGIN_MESSAGE this will be used later on.

 

OK now go to the APEX application and open the application in question that you want to add a message to

There are a few screen grabs to make the teach in a bit easier

Please note that LOGIN_MESSAGE is used throughout and when creating the region the source is &LOGIN_MESSAGE.

Please take note of the . after MESSAGE.

 

Create the application item first

 

Now create a region

 

Testing the application

 

There is more to come, I am just getting started. This may not be the best way to do things but it is a start 🙂

 

 

 

 

 

 


Apex ORA-44004: invalid qualified SQL name error

Arrrrrggggghhhhhh!!!!!!!

 

5 hours spent trying to debug this.  I am starting of in the wonderful world of Apex and need a custom login.

 

So I read the books, watched the Youtube clips, all of which did not really cover Apex 4.1, but hey there can not be that much difference.

Well there is!!!!!!!!!

The code for the login I will post separately but just to let you know where you need to add the function name and in what format.

My function is in a package ans is called pkg_auth.authenticate.

And here is where you put it.

Shared components –> Authentication Schemes

Choose the authentication method in place and change to below

Apex_Error1

 

Note the place it is held and the format.

You place it in the Authentication Function Name and as you can see it is call pkg_auth.authenticate.

You will see many references that would indicate that you need to enter

return pkg_auth.authenticate

return pkg_auth.authenticate;

or pkg_auth.authenticate;

You do not

 

Good luck with any development

 

Al

 

 

 

 

 

 

 

 


SYS.UTL_FILE write error

ARRRRGGGGGHHHHHHHHHH!!!!!!!!! I feel better for that now.

I have just spent the last hour trying to figure this oooohhhhh so simple problem out.

I and using Oracles UTL_FILE plsql packages to write files out to a directory.

 

So you create the directory on the Oracle server

mkdir /u12/transfer

 

You go through the norm of creating said directory in Oracle

create or replace directory TRANSFER as '/u12/transfer'
grant read, write on directory transfer to XXX;

When you try to write to the directory you get

ERROR at line x:
ORA-29283: invalid file operation
ORA-06512: at “SYS.UTL_FILE”, line xxx
ORA-29283: invalid file operation

Well the fix is easy when you know how. You have to make the Linux directory globally read and writeable

chmod 777 /u12/transfer

 

Easy when you know how 🙂

 

 

 


Configuring the APEX workspace

This is the third part of the puzzle to get APEX working in the AWS

If you got here directly have a look at Part 1 and 2

APEX within the Oracle RDS environment

Installing a Glassfish Server for APEX access in Amazon AWS

We now need to create a workspace so that we can start working.

Navigate to

https://FQDN:8181/apex/apex_admin

When you configured your environment. This maybe different, so go with the ports you were given.

Enter the username and password!!!!!

I have installed Apex a few times now and it is always different. I spent 30 minutes trying to figure out what this password was. I eventually gave up and ran the apxchpwd.sql script again. I then noticed the user name.

Everywhere else it is admin, except here its ADMIN arrrrrgggghhhh!!!!!!!!!!!!!!!!!!!!!!!

 

You will be asked to change the password to match the new 11g complexity passwords.

You now should have a working APEX installation

 

You may have a working environment but you will not be able to do anything with it. You need to check a setting. Luckily mine was already set to 1000 but you need to check

Directly from one of the installation guides

"JOB_QUEUE_PROCESSES determine the maximum number of concurrently running jobs. In Oracle Application Express release 4.1, transactional support and SQL scripts require jobs.

If JOB_QUEUE_PROCESSES is not enabled and working properly, you cannot successfully execute a script.”

To see how many JOB_QUEUE_PROCESSES there are, can be checked in three ways. The easiest is from the command line

sqlplus sys/ORACLE_PASSWORD@localhost as sysdba

SELECT VALUE FROM v$parameter WHERE NAME = ‘job_queue_processes’;

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = <number>

commit;

 

You can now use Apex to your hearts content. Good luck and happy developing


Installing SQL*plus instant client on Linux

We back to the wonderful world of Oracle for a few hours. I have written a few articles on how to install APEX and Oracle XE on the same server, well now its time to run the APEX web server and Oracle server separate. I am again going to leverage the  power of Amazon AWS infrastructure as per usual, more about setting up the Oracle server over at my AWS tips n tricks blog.

I need to create a web server to access the APEX oracle back end server for this you need to get SQL*PLUS working to help you get things moving.

Well you can read the helpful documents from Oracle if you want, to be fare they get you most of the way there and in fact would get you working if the install did not do something a little strange, more about this at the end.

SQPLUS first this can be down loaded from http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

You will need :

oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm

 oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm

These were the latest at the time of writing, basically you need the basic and sqlplus rpm files for your environment.

Download them and transfer them  to the server, I have yet to workout how to download these with wget.

It is assumed that you have the files in the /opt/software directory

cd /opt/software

rpm -i oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm

rpm -i oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm

You then need to create a script or add the following lines to the .bashrc file.

I created a small script file to set all of the path statements.

mkdir /opt/oracle

cd /opt/oracle

vi set_env

Add these lines

LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib:${LD_LIBRARY_PATH}

export LD_LIBRARY_PATH

SQLPATH=/usr/lib/oracle/11.2/client64/lib:${SQLPATH}

export SQLPATH

ORACLE_HOME=/usr/lib/oracle/11.2/client64

export ORACLE_HOME

TNS_ADMIN=/u01/app/oracle/product/11.2.0/general/network/admin

export TNS_ADMIN

LIB_PATH=$ORACLE_HOME/lib
PATH=$ORACLE_HOME/bin:$PATH

 

To explain what these are variables are

LD_LIBRARY_PATH :

this is where the .so files are, that allows the connection to the database, to find your path

cd /

find . -name glogin.sql

SQLPATH :

this is the same location as the LD_LIBRARY_PATH set, not sure what it is used for, I just know it is needed

ORACLE_HOME :

This is where the directory prior to the oracle binaries and the libraries needed, this is standard practice in the Oracle world ???? beats me some of this does.

LIB_PATH:

again another Oracle thing, not going to argue as long as it works
PATH:

this is the important one, this indicates where the sqlplus binary can be found note the /bin after ORACLE_HOME

Every time that you want to use sqlplus you will need to run this script

TNS_ADMIN:

This will be explained below 🙂

To make it easier to use the sqlplus command line it is best to create a tnsnames.ora file, this can be stored anywhere, but based on the standards when installing Oracle locally it is best to store it in /u01/app/oracle/product/{VERSION}/{DATABASENAME}/network/admin/

e.g.

/u01/app/oracle/product/11.2.0/general/network/admin/

So

mkdir -p /u01/app/oracle/product/11.2.0/general/network/admin/

vi tnsnames.ora

Insert the following

DATABASE NAME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ENDPOINT FROM THE RDS CONSOLE)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DATABASE NAME WHEN CREATED)
)
)

e.g.

This is based on the database name of general and the Oracle instance being called companyname-general-orc

GENERAL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = companyname-general-orc.ckh5bdmhapmu.eu-west-1.rds.amazonaws.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = GENERAL)
)
)

SQLPLUS now need to know where the tnsnames file is

vi /opt/oracle/set_env

Add these lines

TNS_ADMIN=/u01/app/oracle/product/11.2.0/general/network/admin

export TNS_ADMIN

Now to try to connect to the database, make sure that you run the set_env script

cd /opt/oracle

. set_env

Then run the sqplus command, in this example the database is called general

sqlplus username/password@general

Easy I here you say, well if you get this dreaded error

sqlplus username@general
 
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 17 15:25:04 2012
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Enter password: 
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
 
 
Enter user-name: 
Its easy to fix he says, well the issue that I had was the symbolic link in /usr/bin, I deleted this and set the path to sqlplus in the set_env file to take care of it, so make sure you delete the offending entry
rm /usr/bin/sqlplus
rm /usr/bin/sqlplus64
Another error encountered when I was also trying to get this to work on another Linux version was: (It takes several attempts to get stuff working, this is why these documents are written 🙂 )

/usr/bin/sqlplus: /lib/ld-linux.so.2: bad ELF interpreter: No such file or directory

To fix this

yum install glibc.i686

One trick to make sure you can connect to the database is this,

/usr/lib/oracle/11.2/client64/bin/sqlplus username@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL = TCP)(HOST = database server name or ip address)(PORT = 1521)))(CONNECT_DATA=(SERVICE_NAME=database name)))’

So in our example

/usr/lib/oracle/11.2/client64/bin/sqlplus username@’GENERAL =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = companyname-general-orc.ckh5bdmhapmu.eu-west-1.rds.amazonaws.com)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = GENERAL)))’

I can not take credit for this it was a helpful DBA at Inoapps

On more just test connectivity, especially if have routers, firewalls, vlans or any other anti connection equipment it a simple telnet to the database

telnet Oracle server IP address 1521

If you are successful in the connection then you will get the following prompt

Escape character is ‘^]’.

If you don’t have telnet install on the server

yum install telnet