bigjim-network

If you have a fresh install of OEL 4.
If you have installed the Oracle grid control agent.
If you have allready executed « root.sh » script but you’re sticked with an « NMO not setuid-root » error while cloning Oracle Home or anything else.
Then you have to set correct ownership to some files :

$ chown root $ORACLE_HOME/bin/nmo
$ chmod 6750 $ORACLE_HOME/bin/nmo
$ chown root $ORACLE_HOME/bin/nmb
$ chmod 6750 $ORACLE_HOME/bin/nmb

now « Try Again »!

Here is a PL/SQL package that I’ve made to check and convert Belgian’s BBANs and IBANs codes whitin SQL queries.

package : BBAN_IBAN_BIC.sql (159)
package body : BBAN_IBAN_BIC_Body.sql (156)

You’ll need a table of BICs codes to get the BIC code out of BBAN and IBAN.

Download an updated version from :
http://www.bnb.be/ « Systèmes de paiement »> »Standards de paiements »> »Codes d’identification des banques »

or you could use directly this one (at youre own risk) : BIC_Table.sql (247)

Here is script that I’ve created to make a logical backup of the entire database every day

######################### START OF SCRIPT #########################

#!/bin/bash
#Backup script by Cédric Sougné.
ORACLE_BASE=/opt/oracle
ORACLE_HOME=$ORACLE_BASE/product/db10g
ORACLE_SID=<yoursid>
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME PATH ORACLE_SID

ADMIN_EMAIL= »<yourdbamail> »
LOGFILE=$ORACLE_BASE/admin/$ORACLE_SID/backup_logs/expdp-`date +%d%m%y%H%M%S`.log

EXPORT_PATH=/opt/oracle/export_backup
DMP_FILE= »expdp-`date + »%d%m%Y_%H%M%S »`.dmp »
LOG_FILE= »expdp-`date + »%d%m%Y_%H%M%S »`.log »

echo « Starting … » > $LOGFILE
expdp \’sys/<yousyspassword> as sysdba\’ FULL=y DIRECTORY=EXPORT_BACKUP_DIR DUMPFILE=$DMP_FILE LOGFILE=$LOG_FILE
#expdp mrmrs/mrmrs TABLES=resident,sejour DIRECTORY=EXPORT_BACKUP_DIR DUMPFILE=$DMP_FILE LOGFILE=$LOG_FILE
cat $EXPORT_PATH/$LOG_FILE >> $LOGFILE
echo « Backup Completed. » >> $LOGFILE

echo « gzipping … » >> $LOGFILE
gzip $EXPORT_PATH/$DMP_FILE >> $LOGFILE
echo « gzipping Completed. » >> $LOGFILE

echo « Deleting old files » >> $LOGFILE
find $EXPORT_PATH -type f -mtime +31 | xargs rm >> $LOGFILE
echo « Delete Completed » >> $LOGFILE

cat $LOGFILE | mail -s « expdp-`date + »%d%m%Y_%T »` » $ADMIN_EMAIL

######################### END OF SCRIPT #########################

here is for the cron tab:

00 22 * * * /opt/oracle/admin/acisora1/scripts/export_backup.sh >> /dev/null

the script will be executed avery at 10 pm

1) install apache, php …

yum install httpd php php-mysql php-pear php-devel zlib zlib-devel
yum groupinstall « Development Tools »

2) Download and install oracle instant client (basic and devel) from there:

http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linuxsoft.html

rpm -Uvh oracle-instantclient-basic-<VERSION>.i386.rpm
rpm -Uvh oracle-instantclient-devel-<VERSION>.i386.rpm

3) Add the Oracle instant client to dynamic linker

echo /usr/include/oracle/<VERSION>/client/lib/ > /etc/ld.so.conf.d/oracle_client.conf
ldconfig -v

4) Untar the PECL package and prepare it for compiling

tar -xzvf oci-<VERSION>.tgz
cd oci-<VERSION>
phpize

5) Configure the PECL package as a shared object using the instant client and specifying where the Oracle client libraries are. Then build and install it.

./configure –with-oci8=shared,instantclient,/usr/lib/oracle/<VERSION>/client/lib
make
make install

6) Add the library shared object for PHP

echo extension=oci8.so > /etc/php.d/oci8.ini

7) Start Apache

’service httpd start’ _or_ ‘/etc/init.d/httpd restart’

Create a PHP info page and checked to see if the Oracle (oci8) driver is listed:

echo <? phpinfo(); ?> > /var/www/html/phpinfo.php

Functions gived here comes initially from developpez.com but has been modified to convert numbers in the belgian way (septante,nonante) then I’ve added possibility to add devise like Euro.

Here comes downloadable files:
english numbers to words function(needed) : to_word_en (111)
belgian translation : translate_be (261)

examples:

TRANSLATE_BE(TO_WORD_EN(156.2),’euro’)
—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-
cent cinquante-six euros et deux cents

1 rows selected

TRANSLATE_BE(TO_WORD_EN(1.01),’euro’)
—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-
un euro et un cents

1 rows selected

Here is a way to add the group_concat function to ORACLE. After that you’ll be able to do just like in MySQL.

first you’ll need to create a new object type:


create or replace TYPE t_string_agg AS OBJECT
(
g_string VARCHAR2(32767),

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
ctx2 IN t_string_agg)
RETURN NUMBER
);


then create a function using the newly created type :


create or replace FUNCTION tring_concat (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;


now try it with SCOTT’s schema


COLUMN employees FORMAT A50

3 rows selected.


that could be quite useful to add this function to the SYS schema and then create a public synonym. this way the function will be available to any user of the database.

Here is a package containing functions to interact with delimited strings.

the package is named STR and has to be placed in the SYS schema to be used anywhere in the database. Just compile it as sys then make a public synonym of it. Then you will be able to use functions this way:

select str.<thefunction>(<parameters>) from dual;

Here is a couple of cool stuffs that could be realized with this package (for full details download the package)

—————————————————————————————————–

Function will split a string.  The maximum size returnable
is 4000 bytes and up to 15 fields.
For Exammple:

SQL> select str.split(’a,b,c,d’,3,’,') from dual;
STR.SPLIT(’A,B,C,D’,3,’,')
——————————————————————————–
c

select str.split(’a,b,c,d’,3,’,') from dual;
@param in_del_field             string to be split
@param in_position              position of split character(s) to return
@param in_del                   delimter to split by
@return Returns a single value from a string, up to 4K long

FUNCTION split
(in_del_field  IN     VARCHAR2,
in_pos        IN     NUMBER,
in_del        IN     VARCHAR2)
RETURN VARCHAR2;

Type is a collection/array of values returned from the split_array function

TYPE tab_split IS TABLE OF VARCHAR2(4000);

—————————————————————————————————–

Funciton takes a string passed to it and returns it as a collection of the
type tab_split.  For Example

DECLARE
t_split str.tab_split := str.split_array('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',',');
i       INTEGER;
BEGIN
FOR i IN t_split.FIRST .. t_split.LAST LOOP
DBMS_OUTPUT.PUT_LINE(t_split(i));
END LOOP;
END;

@param in_del_field             string to be split
@param in_del                   delimter to split by
@return Returns an Array of type str.tab_split with a row for each delimited value in in_del_field.

FUNCTION split_array
(in_del_field  IN     VARCHAR2,
in_del        IN     VARCHAR2)
RETURN tab_split;

—————————————————————————————————–

Funciton takes a table of string passed to it and returns it as a delimited string.  For Example
DECLARE
t_split str.tab_split := str.split_array('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',',');
i       INTEGER;
BEGIN
FOR i IN t_split.FIRST .. t_split.LAST LOOP
DBMS_OUTPUT.PUT_LINE(t_split(i));
END LOOP;
END;

@param in_tab                   table to unsplit
@param in_del                   delimter to concat
@return Returns an Varchar2 string delimited by the in_del var passed.

FUNCTION unsplit_array
(in_tab  IN     tab_split,
in_del        IN     VARCHAR2)
RETURN VARCHAR2;

—————————————————————————————————–

Function replace a string in a delimited string at the position passed in parameter.  For Example
select str.replace_at_pos(’a,b,c,d,e,f,g,h,i’,2,’z',’,') from dual;
/
a,z,c,d,e,f,g,h,i
@param in_del_field             delimited string
@param in_pos                   position where to replace string
@param in_text                  string to put in place at pos
@param in_del                   delimter to split by
@return Returns an Varchar2 string delimited by the in_del var passed.

FUNCTION replace_at_pos
(in_del_field  IN     VARCHAR2,
in_pos        IN     NUMBER,
in_text       IN     VARCHAR2,
in_del        IN     VARCHAR2)
RETURN VARCHAR2;

—————————————————————————————————–

Pipelined Function returns a table of one column with the deleminted string
passed to it.  For example, you could do the following to return a table of
the alphabet…

SELECT *
FROM TABLE(str.split_pipe('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',','))

@param in_del_field             string to be split
@param in_del                   delimter to split by
@return Returns a Piplined Table of Table Type tab_str with a row for each delimited value in in_del_field.
FUNCTION split_pipe
(in_del_field  IN     VARCHAR2,
in_del        IN     VARCHAR2)
RETURN tab_str PIPELINED;

download the package Download STR package (204)

Here is a way to know « when » and « who » change datas in a table. This mothed imply to four collums to your table:

1. Two for users that have created or modified datas in a row : USERCREA, USERMOD
2. Two mores to store dates of changes : DATECREA, DATEMOD

USERCREA and DATECREA will be filled simply with default values of columns and have to be declared this way:

• USERCREA VARCHAR2(20 BYTE) Yes SYS_CONTEXT(’USERENV’,'OS_USER’)

This will search the current os’s user of the client.

• DATECREA DATE Yes SYSDATE

Sysdate simply return the actual date of the server

That show that some functions could be used as default value in a column but do not expect to much of that as users function could not be used. As I know, only sysdate and SYS_CONTEXT work but here is some other intersting stuff:

• – host varchar2(256) default SYS_CONTEXT(’USERENV’,'HOST’)
• ip_address varchar2(256) default SYS_CONTEXT(’USERENV’,'IP_ADDRESS’)
• language varchar2(256) default SYS_CONTEXT(’USERENV’,'LANGUAGE’)
• protocol varchar2(200) default SYS_CONTEXT(’USERENV’,'NETWORK_PROTOCOL’)
• terminal varchar2(200) default SYS_CONTEXT(’USERENV’,'TERMINAL’)

Next to that we will need a trigger to fill USERMOD and DATEMOD columns :

CREATE OR REPLACE TRIGGER "TRIGGER_YOURTABLE_UPDATE"
BEFORE UPDATE
ON YOURTABLE
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
v_OSUSER VARCHAR2(25);
BEGIN
v_OSUSER := SYS_CONTEXT('USERENV','OS_USER');

:NEW.DATEMOD := SYSDATE;
:NEW.USERMOD := v_OSUSER;

EXCEPTION
WHEN OTHERS THEN
RAISE;
END TRIGGER_YOURNAME_UPDATE;
ALTER TRIGGER « MRMRS ». »TRIGGER_YOURTABLE_UPDATE » ENABLE

Now you have four columns letting you know at any time who and when your datas have been created or updated.

this function retrun the date of the last day in the month

CREATE OR REPLACE FUNCTION "GET_LAST_DAY_OF_MONTH"
( p_dateinmonth in date
) return date as
v_datereturn DATE;
begin
select (TRUNC( ADD_MONTHS(p_dateinmonth,1), 'MM' ))-1 into v_datereturn
from dual;
return v_datereturn;
end get_last_day_of_month;
/