Automatic configuration of SQL Plus on start-up

This is something I picked up from Oracle PL/SQL Programming by Steven Fewerstein and Bill Prybil.

When you start sqlplus the program reads configuration data from two files:

• $ORACLE_HOME/sqlplus/admin/glogin.sql
• ./login.sql

Since starting sqlplus from a directory that always contains an appropriate login.sql file isn't always possible, Bill recommends editing the glogin.sql. This is what the last lines my glogin sql look like :

---

-- Additional Settings by Bill Prybil
SET LINESIZE 132
SET PAGESIZE 999

-- Change the editor to vi if you don't do that sqlplus will use the editor from the $EDITOR
-- environment variable 
DEFINE _EDITOR = /usr/bin/vim

-- Format columns commonly retrieved from data dictionary
COLUMN segment_name FORMAT A30 WORD_WRAPP
COLUMN object_name FORMAT A30 WORD_WRAPP

-- set the sql prompt
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER >"

Selecting af:tableSelectOne by clicking anywhere in the table row

Just picked it up from the Oracle Java Developer Newsletter. It's sure worth it. By the way I just added Veniamin Goldin's Blog to my Oracle Links. He offers lots of goodies and unlike many others his tips tricks and examples are ready to be used

ABAP Transalation of text elements created when implementing user-exits

When you create text elements during implementation of a SAP user exit then — for reasons beyond my understanding — these texts are not directly translatable. So when you try to user the goto translation menu SAP pops up the dialog that allows you to choose the "from" and "to" languages but when you press ok, then you get an error message of the form "Please use transaction SE63.

Following is the exact list of actions that should be followed (Thanks Marilena)

1. Open the Function Module containing the user-exit using either SE80 or SE37.
2. From the program main menu use Goto → Text Elements → Text Symbols
3. Copy the program name that is displayed on the top of the screen just above the texts table
4. Goto transaction SE63.
5. From the program main menu click Translation → Short Texts → ABAP → Program Texts
6. Paste into the field marked Program name the text you copied from step 3.
7. Select source and target languages
8. Press the big button labeled Edit

Linux: Format and examples of the crontab command

Whatever there is to find out about crontab is right there in the man pages. Paul Vixie explains everything crystal clear. Here are the main points to remember.

The cron(8) daemon examines cron entries once every minute.

Display of the user's crontab file is performed by crontab -l while editing is done with the -e switch. (Make sure that the EDITOR environment variable is set to your favorite editor program)

cronatb entries are date time fields followed by the command to be executed at that time. The time and date fields are:

field	allowed values
minute	0-59
hour	0-23
day of month	1-31
month	1-12 (or names, see below)
day of week	0-7 (0 or 7 is Sun, or use names)

A field may be an asterisk (*), which always stands for ``first-last''.

Ranges of numbers are allowed. Ranges are two numbers separated with a hyphen. The specified range is inclusive. For example, 8-11 for an ``hours'' entry specifies execution at hours 8, 9, 10 and 11.

Lists are allowed. A list is a set of numbers (or ranges) separated by commas. Examples: ``1,2,5,9'', ``0-4,8-12''.

Step values can be used in conjunction with ranges. Following a range with ``/'' specifies skips of the number's value through the range. For example, ``0-23/2'' can be used in the hours field to specify command execution every other hour (the alternative in the V7 standard is ``0,2,4,6,8,10,12,14,16,18,20,22''). Steps are also permitted after an asterisk, so if you want to say ``every two hours'', just use ``*/2''.

Names can also be used for the ``month'' and ``day of week'' fields. Use the first three letters of the particular day or month (case doesn't matter). Ranges or lists of names are not allowed.

---

EXAMPLE CRON FILE

# use /bin/sh to run commands, no matter what /etc/passwd says
SHELL=/bin/sh
# mail any output to `paul', no matter whose crontab this is
MAILTO=paul
#
# run five minutes after midnight, every day
5 0 * * *       $HOME/bin/daily.job >> $HOME/tmp/out 2>&1
# run at 2:15pm on the first of every month -- output mailed to paul
15 14 1 * *     $HOME/bin/monthly
# run at 10 pm on weekdays, annoy Joe
0 22 * * 1-5   mail -s "It's 10pm" joe%Joe,%%Where are your kids?%
23 0-23/2 * * * echo "run 23 minutes after midn, 2am, 4am ..., everyday"
5 4 * * sun     echo "run at 5 after 4 every sunday"

Finally here is my DB Server crontab which backs up the entire database every day at 6:00pm and backups up the archive logs at past 35 every two hours.

---

oracle@poulcheria:~> crontab -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
# (/tmp/crontab.21301 installed on Fri Feb  2 09:45:47 2007)
# (Cron version -- $Id: crontab.c,v 2.13 1994/01/17 03:20:37 vixie Exp $)
0 18 * * * /home/oracle/backup_jobs/perform_backup
35 */2 * * * /home/oracle/backup_jobs/perform_archlog_backup

PHP Redirecting to a relative URL

This comes directly from the PHP Manual, but I thought I might keep it here as a reference.

Note: HTTP/1.1 requires an absolute URI as argument to » Location: including the scheme, hostname and absolute path, but some clients accept relative URIs. You can usually use $_SERVER['HTTP_HOST'], $_SERVER['PHP_SELF'] and dirname() to make an absolute URI from a relative one yourself:

---

<?php

/* 
 * Redirect to a different page in the current directory that was requested 
 */
$host  = $_SERVER['HTTP_HOST'];
$uri  = rtrim(dirname($_SERVER['PHP_SELF']), '/\\');
$extra = 'mypage.php';  // change accordingly

header("Location: http://$host$uri/$extra");
exit;

PHP Destroying the current session completely

Here is a code snipper I got out from Janet Valade's PHP & MySQL Visual Blueprint book and this is what I use it to implement logout pages

---

session_start();
$_SESSION = array();
if (isset($_COOKIE['PHPSESSID']))
    setcookie('PHPSESSID', '', time() - 42000, '/');
session_destroy();

$return_url = 'login.php';
header("Location: http://".$_SERVER['HTTP_HOST']
                      .dirname($_SERVER['PHP_SELF'])
                      ."/".$return_url);
exit;

Linux: Changing file and dirctory permisions for an entire directory tree

Suppose you have a directory named /mydir and you need to change the permissions on all the files for the entire directory tree. Using a command like chmod -R 644 * might get you into trouble since this may change execute (i.e. traversal) access even for existing directories.

A friend from Germany (thanks Antoni) gave me the fhis solution :

First the files

find /mydir -type f -exec chmod 0644 {} \;

... and then the directories

find /mydir -type d -exec chmod 0755 {} \;

Note: The previous commands reset the file permissions completely. If you want to just add read access, while preserving the rest, you might prefer a u+r or g+r command line to chmod as in the following example which adds read and execute access to /mydir and all it's subdirectories.

find /mydir -type d -exec chmod u+rx {} \;

By the way, since I have lately found to need this more and more - as I move data to and from USB sticks -- I created a simple script that given a directory gives all files permissions 0644 and all directories 0755

#!/bin/bash
# File resetPerms.sh

if [ -d $1 ]
then
  echo Reseting file and directory access on $1
  find $1 -type f -exec chmod 0644 {} \;
  find $1 -type d -exec chmod 0755 {} \;
fi

Oracle 10g SQL Script to list the connected users

OK, I know that EM can give you the same result in much prettier form, but let's keep that just in case.

---

SET LINESIZE 200
SET PAGESIZE 66

SELECT s.username, s.logon_time, s.machine, s.osuser, s.program
FROM v$session s, v$process p, sys.v_$sess_io si
WHERE s.paddr = p.addr(+) AND si.sid(+) = s.sid AND s.type='USER';

Stop Screen Flickering in JDeveloper 10.1.3.0.4 (SU5)

After I installed SU5 to my openSUSE Linux installation of JDeveloper 10.1.3.0.4, I run into extreme screen flickering, especially while updating JSF diagrams. Then I remembered a hint I got from Oracle support a while back while we were investigating something else. Well this did it and although I don't know if this works on Windows, I 'm putting it here for anybody in the same kind of trouble.

Open file $JDEV_HOME/jdev/bin/jdev.conf and add the following line

AddVMOption -Dsun.java2d.ddoffscreen=false

Note: JDeveloper 10.1.3.2.0 does not require this.

Linux System Info commands

Here is a list of commands that display various system parameters. To display all memory parameters enter :

thanassis@lxBakalidis:~> cat /proc/meminfo
MemTotal:      2074792 kB
MemFree:        844804 kB
Buffers:        207508 kB
Cached:         548016 kB
SwapCached:          4 kB
Active:         718768 kB
Inactive:       379272 kB
HighTotal:     1178048 kB
HighFree:       314284 kB
LowTotal:       896744 kB
LowFree:        530520 kB
SwapTotal:     1534168 kB
SwapFree:      1534068 kB
Dirty:              12 kB
Writeback:           0 kB
AnonPages:      342464 kB
Mapped:         122896 kB
Slab:            53424 kB
PageTables:       2412 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:   2571564 kB
Committed_AS:   633536 kB
VmallocTotal:   114680 kB
VmallocUsed:      8600 kB
VmallocChunk:   105820 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     4096 kB

The following command can be combined with grep to display only the requested result. So in order to determine the amount of free swap space a command like :

thanassis@lxBakalidis:~> grep SwapFree  /proc/meminfo
SwapFree:      1534068 kB

CPU information is located at /proc/cpuinfo.

thanassis@lxBakalidis:~> cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 15
model           : 2
model name      : Intel(R) Pentium(R) 4 CPU 2.80GHz
stepping        : 9
cpu MHz         : 2793.351
cache size      : 512 KB
physical id     : 0
siblings        : 2
core id         : 0
cpu cores       : 1
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 2
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid xtpr
bogomips        : 5590.66

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 15
model           : 2
model name      : Intel(R) Pentium(R) 4 CPU 2.80GHz
stepping        : 9
cpu MHz         : 2793.351
cache size      : 512 KB
physical id     : 0
siblings        : 2
core id         : 0
cpu cores       : 1
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 2
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid xtpr
bogomips        : 5586.76

The number of a available disks, their capacity and mount points is given by df. Remember to use -h in order for df to display data in hman readable form.

thanassis@lxBakalidis:~> df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/hda2              20G  5,5G   14G  30% /
udev                 1014M  100K 1013M   1% /dev
/dev/hda3              16G  4,7G   12G  30% /home
//thersephassa/Data    75G   74G  1,6G  98% /mnt/Culture

I 'll get back to this post as soon as I have mor commands.

ABAP Calculating Date-Ranges

Here is an example that given a date it returns the date of the same day one month later. It demonstrates the concepts of subfields and converting a date to an integer and vice versa. It also provides an implementation of the isLeap() function in ABAP. Note that ABAP does not allow intermediate calculations in logical expressions, so all the intermediate modules operations have to be performed beforehand and stored in separate variables.

---

FORM get_month_range CHANGING p_start_date TYPE d
                      p_end_date TYPE d.
DATA :
 month TYPE i,
 year TYPE i,
 y_m_4 TYPE i,
 y_m_100 TYPE i,
 y_m_400 TYPE i.

IF p_start_date IS INITIAL.
 p_start_date = sy-datum.
ENDIF.

month = p_start_date+4(2).
CASE month.
 WHEN 1 OR 3 OR 5 OR 7 OR 8 OR 10 OR 12.
   p_end_date = p_start_date + 31.
 WHEN 2.
   year = p_start_date(4).
   y_m_4 = year MOD 4.
   y_m_100 = year MOD 100.
   y_m_400 = year MOD 400.
   IF ( y_m_4 = 0 ) AND
      ( ( y_m_100 <> 0 ) OR ( y_m_400 = 0 )
        ).
     p_end_date = p_start_date + 29.
   ELSE.
     p_end_date = p_start_date + 28.
   ENDIF.
 WHEN OTHERS.
   p_end_date = p_start_date + 30.
ENDCASE.

ENDFORM.                    " get_month_range

Oracle ADF Accessing the Current Date and Time

This is an extract from section 9.11 of the Oracle ADF Developer's Guide for Forms and 4/GL developers. It provides two functions for getting the current date and the current date and time from within code written inside the BC4J layer.

/**
 * requires import oracle.jbo.domain.Date;
 */
protected Date getCurrentDate()
{
    return new Date( Date.getCurrentDate());
}

/**
 * requires import oracle.jbo.Domain.Date and java.sql.Timestamp
 */
protected Date getCurrentDateWithTime()
{
    return new Date( new Timestamp( System.currentTimeMillis()));
}

Conventing from a date of type java.util.Date to an oracle.jbo.domain.Date can be achieved using the following technique (the code also demonstrates the use of a date parser to convert arbitrary date formats to valid Java dates.) :

protected static final DateFormat dateFormater = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");

public void setDateFromString( String value)
{                 
    try {
        java.util.Date javaDate = dateFormater.parse(value);
        Timestamp curTimestamp = new Timestamp( javaDate.getTime());
        oracle.jbo.domain.Date oracleDate = new oracle.jbo.domain.Date(curTimestamp);
        this.setAccidentDate( oracleDate);
    } catch (Exception e) {
        e.printStackTrace();    
        System.out.println("Unable to parse date value " + value);
    }   
}

SQL Replacing a NOT IN with an Outer Join

I came across this code while searching through the "Oracle 10g Complete Reference" by Kevin Loney. It provides a much faster approach to finding out records that belonging to one table and not to an other.

So the query:

select TITLE
from BOOKSHELF
where TITLE not in (Select TITLE from BOOKSHELF_CHECKOUT)
order by TITLE;

can be replaced by

select distinct B.TITLE
from BOOKSHELF_CHECKOUT BC right outer join BOOKSHELF B
     on BC.TITLE = B.TITLE
where BC.TITLE = NULL
order by TITLE;

Boolean Expression to Determine if a Year is Leap

The following is a C function that determines if a year is leap. Comes in pretty handy and is easy to convert to other languages as well.

    int isLeap[int year)
    {
       return (year % 4 == 0) && (year % 100 != 0 || year % 400 == 0);
    }

Oracle 10g Moving Data across tablespaces

Moving a table named my_table to a new tablespace named dest_data is a new feature of Oracle 10g and can be accomplished using the following command in SQLPlus:

SQL> alter table my_table move tablespace my_data;

Moving an index to a different table space is performed as follows :

SQL>alter index my_index rebuild tablespace my_data;

ABAP Date Time Variables and Addressing Subfields

Date and Time Data types.

Type d is the data type used for dates and type t for time. Both consist of string representations of dates (in a YYYMMDD format) and times (HHMMSS) with which calculations can be performed. Adding an integer value to a type d variable affects the day part of the date while adding an integer to a time value affects seconds. Assigning a date or time value to an integer yields the number of days since 01.01.0001 or the number of seconds since 00:00:00. The system date is provided by the sy-datum system field while the system time by sy-uzeit.

Addressing subfields

Extracting date and time fields from date and time variables can be achieved by using the SAP sub-field syntax which -- given a filed named f -- goes like this :

subfield = f[+offset][(length)]

if you specify an offset without length then the entire string starting from offset is addressed. if no offset is specified then 1 is implied. ABAP strings always start at index 1.

Examples

Keeping in mind that the internal format of a date field is YYYYMMDD we may declare some variables using the following syntax

DATA :
  my_birthday TYPE d VALUE '19681120',
  cur_year TYPE i VALUE sy-datum(4),    " get the first four characters of the sy-datum field
  cur_month TYPE i VALUE sy-datum+4(2), " get the two characters after position 4 i.e. 5 and 6
  cur_day TYPE i VALUE sy-datum+6(2).    " get the two characters after position 6 i.e. 7 and 8

Oracle 10g on Linux Shell Variables required for proper operation

This is an extract from the .profile of the oracle user on a Linux machine (running SLES9) where Oracle 10g Release 2 (10.2.0.1.0) database enterprise edition is already installed. The variables required for correct operation of the database are the following

---

#
# Oracle Database 10g client related entries
#
export ORACLE_OWNER=oracle
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib
export LIB_PATH=$LD_LIBRARY_PATH:/usr/lib
export NLS_LANG=AMERICAN_GREECE.EL8ISO8859P7
export NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS'
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/ctx/bin:$ORACLE_HOME/OPatch:$PATH
export ORACLE_SID=mydb

Notes

1. NLS_DATE_FORMAT is to allow rman display time information when listing backups
2. NLS_LANG is to allow oracle software to display messages in a specific language (here American English), use regional settings for a specific country (Greece) and handle data encoded in a specific format (here iso8859-7).
3. Forgetting to set the LD_LIBRARY_PATH has proved the root of many evil things
4. The same values can also be used for installations of an oracle application server version 10.1.3.x. Just remove the $ORACLE_HOME/ctx/lib for the $LD_LIBRARY_PATH variable

Starting and stopping a version 10g (10.1.2 & 10.1.3) Oracle Application server

Starting up an infrastructure

I have always wanted to put that down since I keep opening my note book each time I haven't done something like that for quite some time. so here it goes 1) Start the listener

% lsnrctl start

2) Start the Infrastructure database using sqlplus

% sqlplus / as sysdba
startup open;
quit 

3) Start the Oracle processes

 %> opmnctl startall

4) Start the enterprise manager console. (this consumes a lot of memory and system recourses, so it is better to avoid it unless you have to)

% emctl start iasconsole 

Starting up an OC4J Server

In this case just leave out steps 1) and 2) and use a command line like :

% opmnctl startall
% emctl start iasconsole

Note: When starting up a version 10.1.3 server then the emctl command does not need to be used at all. The management website is now a true J2EE application deployed by default and is always accessible is http://myhost:myport/em