0.00%
Search · Index

Weblog Page

Filtered by category beginner, 1 - 10 of 42 Postings (all, summary)

Quick Install of OpenACS

Created by Malte Sussdorff, last modified by Gustaf Neumann 29 Sep 2019, at 10:19 AM

Setup user

Especially for incoming e-mail, each webserver should run with it's own user. We are going to use the service name "service0" throughout this script to denote a couple of things needed to run your webserver "www.service0.com": 

  • username to run the service
  • username in the database
  • database name in postgresql
  • service name for aolserver

First add the unix user:

export SERVICE0=service0
sudo useradd $SERVICE0
sudo mkdir /var/lib/aolserver/$SERVICE0/
sudo chown -R $SERVICE0.web /var/lib/aolserver/$SERVICE0
sudo usermod -g web $SERVICE0
sudo usermod -d /var/lib/aolserver/$SERVICE0 $SERVICE0

Now it is time to prepare the database for the OpenACS System:

PostgreSQL 

Make sure to have PostgreSQL installed 

/usr/local/pgsql/bin/createuser -s $SERVICE0 -U postgres
/usr/local/pgsql/bin/createdb -E UNICODE -U $SERVICE0 $SERVICE0

Oracle

Make sure to have Oracle installed 

sqlplus system@XE
SQL> create tablespace $SERVICE0
datafile '/usr/lib/oracle/xe/oradata/XE/$SERVICE0.dbf'
size 50M
autoextend on;

SQL> create user $OPENACS_SERVICE_NAME identified by password default tablespace $OPENACS_SERVICE_NAME
SQL> grant connect, resource, ctxapp, javasyspriv, query rewrite to questaims;
SQL> revoke unlimited tablespace from questaims;
SQL> alter user questaims quota unlimited on questaims;
SQL> exit;

After the user is setup, login as this user and get the source files. Furthermore configure the config file.

 sudo su - $SERVICE0 

If you do this directly from OpenACS you can run:

cvs -d :pserver:anonymous@cvs.openacs.org:/cvsroot co -r oacs-5-3 openacs-4

mv openacs-4/* . 

If you want to use SVN you can run

svn co https://svn.cognovis.de/openacs/branches/oacs-5-3 .

If you are working within cognovis and start a new client project do the following

export REPOS=https://svn.cognovis.de/
svn mkdir $REPOS/$SERVICE0 $REPOS/$SERVICE0 $REPOS/$SERVICE0/branches $REPOS/$SERVICE0/tags -m "Creating clientname directory structure"
svn copy $REPOS/openacs/branches/oacs-5-3 $REPOS/$SERVICE0/trunk
svn co $REPOS/$SERVICE0/trunk .
Now you have your checkout, time to configure

cp etc/config.tcl etc/`whoami`.tcl perl -pi*.bak -e "s/service0/`whoami`/g" etc/`whoami`.tcl perl -pi*.bak -e "s/service0/`whoami`/g" etc/daemontools/run perl -pi*.bak -e "s/config/`whoami`/g" etc/daemontools/run

This will get the latest openacs from the current release branch into your service name. If you need certain packages to come from HEAD you can still update them later.

As root make sure the system is under daemontools control:

# Logout to become root again exit sudo ln -s /var/lib/aolserver/$SERVICE0/etc/daemontools /service/$SERVICE0 sudo svgroup web /service/*


You are now configured to start your OpenACS installation on http://127.0.0.1:8000 unless some other server has been running there.

If you want to install .LRN issue the following command:

su - $SERVICE0
cp packages/dotlrn/install.xml .
svc -du /service/`whoami`

You can verify that your server is running by looking at /var/lib/aolserver/$SERVICE0/log/error.log

If you want to edit your config files, here is their location

  • /var/lib/aolserver/SERVICE0/etc/SERVICE0.tcl
    This contains the configuration parameters and is usually referred to as "config" file.
  • /var/lib/aolserver/SERVICE0/etc/daemontools/run
    If you made changes to the IP Address and the port you will need to edit this file:
    • add the "-b" switch to match your ip address and port right after "nsd-postgres", before the "-it ..." part


Last but not least make sure incoming E-Mail will work (for a full installation overview look at incoming_email. First add your domain to the virtual_domains in /etc/postfix/main.cf then execute the following commands:


echo "@www.$SERVICE0.com $SERVICE0" >>/etc/postfix/virtual
mkdir /web/$SERVICE0/mail
chown $SERVICE0.web /web/$SERVICE0/mail
postmap /etc/postfix/virtual
/etc/init.d/postfix restart

Now it is time to setup the backup and keepalive correctly. First edit /var/lib/aolserver/$SERVICE0/etc/keepalive/keepalive-config.tcl to include your server.

Then copy /var/lib/aolserver/$SERVICE0/etc/backup.sh to /var/lib/aolserver/$SERVICE0/etc/backup-$SERVICE0.sh and edit it to reflect your environment.

Last but not least login as $SERVICE0 and edit the crontab

su - $SERVICE0
export EDITOR=emacs
crontab -e

File in the following data, replaceing service0 where occuring.

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=<youremail>
HOME=/var/lib/aolserver/service0

# m h  dom mon dow   command
02 4 * * *           /var/lib/aolserver/service0/etc/backup-service0.sh
*/7 * * * *          /var/lib/aolserver/service0/etc/keepalive/keepalive-cron.sh

Webservices Support (TWiST)

To support webservices there exists the tool called "TWiST". To download and install it onto your server the following steps need to be taken:

cd /var/lib/aolserver/service0/tcl
svn checkout http://twsdl.googlecode.com/svn/tags/twist-0.9.9 twist-0.9.9
mv twist-0.9.9 twsdl

Then you need to edit your config file at /var/lib/aolserver/SERVICE0/etc/SERVICE0.tcl by adding the following in the modules section:

ns_section ns/server/${server}/modules
ns_param twsdl tcl

Basic String Operations

Created by Hal Abelson, Philip Greenspun, and Lydia Sandon, last modified by Gustaf Neumann 03 Mar 2017, at 08:42 AM

If your program receives data from a Web client, it comes in as a string. If your program sends an HTML page back to a Web client, it goes out as a string. This puts the string data type at the heart of Web page development:
set whole_page "some stuff for the top of the page\n\n"
append whole_page "some stuff for the middle of the page\n\n"
append whole_page "some stuff for the bottom of the page\n\n"
# done composing the page, let's write it back to the user
ns_return 200 text/html $whole_page
If you're processing data from the user, typically entered into an HTML form, you'll be using a rich variety of built-in string-handling procedures. Suppose that a user is registering at your site with the form variables first_names, last_name, email, password. Here's how we might build up a list of exceptions (using the Tcl lappend command, described in the chapter on lists):
# compare the first_names value to the empty string
if { [string compare $first_names ""] == 0 } {
    lappend exception_list "You forgot to type your first name"
}

# see if their email address has the form
#   something at-sign something
if { ![regexp {.+@.+} $email] } {
    lappend exception_list "Your email address doesn't look valid."
}

if { [string length $password] > 20 } {
    lappend exception_list "The password you selected is too long."
}
If there aren't any exceptions, we have to get these data ready for insertion into the database:
# remove whitespace from ends of input (if any)
set last_name_trimmed [string trim $last_name]

# escape any single quotes with an extra one (since the SQL
# string literal quoting system uses single quotes)
regsub -all ' $last_name_trimmed '' last_name_final

set sql_insert "insert into users (..., last_name, ...) 
values 
(..., '$last_name_final', ...)"

Looking for stuff in a string

The simplest way to look for a substring within a string is with the string first command. Some users of photo.net complained that they didn't like seeing classified ads that were simply pointers to the eBay auction site. Here's a simplified snippet from http://software.arsdigita.com/www/gc/place-ad-3.tcl:

if { [string first "ebay" [string tolower $full_ad]] != -1 } {
    # return an exception
    ...
}
an alternative formulation would be
if { [regexp -nocase {ebay} $full_ad] } {
    # return an exception
    ...
}
Both implementations will catch any capitalization variant of "eBAY". Both implementations will miss "e-bay" but it doesn't matter because if the poster of the ad includes a link with a URL, the hyperlink will contain "ebay". What about false positives? If you visit www.m-w.com and search for "*ebay*" you'll find that both implementations might bite someone selling rhododendrons or a water-powered mill. That's why the toolkit code checks a "DisalloweBay" parameter, set by the publisher, before declaring this an exception.

If you're just trying to find a substring, you can use either string first or regexp. If you're trying to do something more subtle, you'll need regexp (described more fully in the chapter "Pattern Matching"):

if { ![regexp {[a-z]} $full_ad] } {
    # no lowercase letters in the ad!
    append exception_text "
      
      
            
      
  • Your ad appears to be all uppercase. ON THE INTERNET THIS IS CONSIDERED SHOUTING. IT IS ALSO MUCH HARDER TO READ THAN MIXED CASE TEXT. So we don't allow it, out of decorum and consideration for people who may be visually impaired." incr exception_count }
  • Using only part of a string 

    In the ArsDigita Community System, we have a page that shows a user's complete history with a Web service, e.g., http://photo.net/shared/community-member.tcl?user_id=23069 shows all of the postings by Philip Greenspun. If a comment on a static page is short, we want to show the entire message. If not, we want to show just the first 1000 characters.

    In http://software.arsdigita.com/www/shared/community-member.tcl, we find the following use of the string range command:

    if { [string length $message] > 1000 } {
        set complete_message "[string range $message 0 1000]... "
    } else {
        set complete_message $message
    }
    

    Fortran-style formatting and reading of numbers 

    The Tcl commands format and scan resemble C's printf and scanf commands. That's pretty much all that any Tcl manual will tell you about these commands, which means that you're kind of S.O.L. if you don't know C. The basic idea of these commands comes from Fortran, a computer language developed by John Backus at IBM in 1954. The FORMAT command in Fortran would let you control the printed display of a number, including such aspects as spaces of padding to the left and digits of precision after the decimal point.

    With Tcl format, the first argument is a pattern for how you'd like the final output to look. Inside the pattern are placeholders for values. The second through Nth arguments to format are the values themselves:

    format pattern value1 value2 value3 .. valueN
    
    We can never figure out how to use format without either copying an earlier fragment of pattern or referring to the man page (http://www.tcl.tk/man/tcl8.4/TclCmd/format.htm). However, here are some examples for you to copy:
    % # format prices with two digits after the point
    % format "Price:  %0.2f" 17
    Price:  17.00
    % # pad some stuff out to fill 20 spaces
    % format "%20s" "a long thing"
            a long thing
    % format "%20s" "23"
                      23
    % # notice that the 20 spaces is a MINIMUM; use string range
    % # if you might need to truncate
    % format "%20s" "something way longer than 20 spaces"
    something way longer than 20 spaces
    % # turn a number into an ASCII character
    % format "%c" 65
    A
    
    The Tcl command scan performs the reverse operation, i.e., parses an input string according to a pattern and stuffs values as it finds them into variables:
    % # turn an ASCII character into a number
    % scan "A" "%c" the_ascii_value
    1
    % set the_ascii_value
    65
    % 
    
    Notice that the number returned by scan is a count of how many conversions it was able to perform successfully. If you really want to use scan, you'll need to visit the man page: http://www.tcl.tk/man/tcl8.4/TclCmd/scan.htm. For an idea of how useful this is for Web development, consider that the entire 250,000-line ArsDigita Community System does not contain a single use of the scan command.


    Reference: String operations

      A.) Commands that don't start with string

    • append variable_name value1 value2 value3 ... valueN
      sets the variable defined by variable_name to the concatenation of the old value and all the remaining arguments (http://www.tcl.tk/man/tcl8.4/TclCmd/append.htm)
    • regexp ?switches? expression string ?matchVar? ?subMatchVar subMatchVar ...?
      Returns 1 if expression matches string; 0 otherwise. If successful, regexp sets the match variables to the parts of string that matches the corresponding parts of expression.
      % set fraction "5/6"
      5/6
      % regexp {(.*)/(.*)} $fraction match num denom
      1
      % set match
      5/6
      % set num
      5
      % set denom
      6
      
      (more: the pattern matching chapter and http://www.tcl.tk/man/tcl8.4/TclCmd/regexp.htm)
    • regsub ?switches? expression string substitution_spec result_variable_name
      Returns a count of the number of matching items that were found and replaced. Primarily called for its effect in setting result_variable_name.

      Here's an example where we ask a user to type in keywords, separated by commands. We then expect to feed this list to a full-text search indexer that will throw an error if handed two commas in a row. We use regsub to clean up what the user typed:

      # here we destructively modify the variable $query_string'
      # replacing every occurrence of one or more commas with a single
      # command 
      % set query_string "samoyed,, sledding, harness"
      samoyed,, sledding, harness
      % regsub -all {,+} $query_string "," query_string
      2
      % set query_string
      samoyed, sledding, harness
      
      (more: the pattern matching chapter and http://www.tcl.tk/man/tcl8.4/TclCmd/regsub.htm)

      were dramatically improved with the Tcl 8.1 release. For a Web developer the most important feature is the inclusion of non-greedy regular expressions. This makes it easy to match the contents of HTML tags. See http://www.scriptics.com/services/support/howto/regexp81.html for a full discussion of the differences.

      B.) Commands that start with string

      (all of which are documented at http://www.tcl.tk/man/tcl8.4/TclCmd/string.htm)


    • string compare string1 string2
      returns 0 if the two strings are equal, -1 if string1 sorts lexicographically before string2, 1 if string2 sorts lexicographically before string1:
      string compare apple applesauce  ==> -1
      string compare apple Apple ==> 1
      
    • string first string1 string2
      returns -1 if string1 is not within string2, else returns the index of the first occurrence. Indices start from zero, e.g.,
      string first tcl catclaw  ==> 2
      
    • string last string1 string2
      -1 if string1 is not within string2, else index of last occurrence.
      string last abra abracadabra ==> 7
      
    • string match pattern string
      1 if string matches pattern, 0 if not. See the chapter on pattern matching for an explanation of patterns.
    • string range string i j
      range of characters in string from index i to j, inclusive.
      string range catclaw 2 4 ==> tcl
      
    • string tolower string
      string in lower case.
      string compare weBmaster Webmaster => 1
      
      string compare [string tolower weBmaster] \
                     [string tolower Webmaster] => 0
      
    • string toupper string
      string in upper case.
      set password "ferrari"
      string compare "FERRARI" [string toupper $password] ==> 0
      
    • string trim string ?chars?
      trims chars from right and left side of string; defaults to whitespace.
      set password [string trim $form_password] ; # see above example
      
    • string trimleft string ?chars?
      trims chars from left of string; defaults to whitespace.
      set password [string trimleft $form_password] 
      
    • string trimright string ?chars?
      trims chars from right of string; defaults to whitespace.
      set password [string trimright $form_password] 
      
    • string wordend string index
      index of the first character after the last character of the word containing index.
      string wordend "tcl is the greatest" 0 ==>3
      
    • string wordstart string index
      index of the first char of the word containing index.
      string wordstart "tcl is the greatest" 5 ==> 4
      



    Exercises ( see section V.3  List Operations )

    ---

    based on Tcl for Web Nerds 


    Install Oracle 8.1.7

    Created by Gustaf Neumann, last modified by Gustaf Neumann 05 Dec 2016, at 08:09 AM

    By Vinod Kurup

    OpenACS docs are written by the named authors, and may be edited by OpenACS documentation staff.

    If you are installing PostGreSQL instead of Oracle, skip this section.

    OpenACS 5.2.3rc1 will install with Oracle 9i but has not been extensively tested so may still have bugs or tuning issues. See Andrew Piskorski's Oracle 9i notes for guidance.

    This installation guide attempts to present all of the information necessary to complete an OpenACS installation. We try hard to make all of the steps possible in one pass, rather than having a step which amounts to "go away and develop a profound understanding of software X and then come back and, in 99% of all cases, type these two lines." The exception to our rule is Oracle production systems. This page describes a set of steps to get a working Oracle development server, but it is unsuitable for production systems. If you will be using OpenACS on Oracle in a production environment, you will experience many problems unless you develop a basic understanding of Oracle which is outside the scope of this document. T

    This document assumes that you'll be installing Oracle on the same box as AOLserver. For more details on a remote Oracle installation, see Daryl Biberdorf's document.

    Useful links to find help on how to set up Oracle under Linux are:

    Production Oracle systems should run on certified platforms. Follow the metalink note 223718.1to find certified platforms. If you don't have metalink access, take a look at the Oracle on Linux FAQ: Which Linux Distributions Are Directly Supported By Oracle?. In summary, free and inexpensive Linux distributions are not certified.

    If you don't have an account at OTN get one: you can download the Oracle software from the Oracle Downloads page. It is also get the CDs shipped to you for a nominal fee from the Oracle Store.

    Each Oracle release comes with extensive and usually quite well-written documentation. Your first step should be to thoroughly read the release notes for your operating system and your Oracle version. Find the docs here:

    It is generally useful to run a particular Oracle version with its latest patchset. At the time of writing these were 8.1.7.4 and 9.2.0.5, both of which are considered to be very stable.

    To be able to download a patchset, you need a (to-pay-for) account on Metalink. You may find the appropriate patchset by following Andrew's suggestion.

    Oracle is very well-documented software, the online documentation comes with printable PDFs and full-text search. Altogether there is more than 20.000 pages of documentation, so do not expect to understand Oracle within in a few hours. The best starting pointing into Oracle is the Concepts book. Here's the 8i version and the 9.2 version.

    To give you an idea of how configurable Oracle is and how much thought you may need to put into buying the proper hardware and creating a sane setup, you should thoroughly read Cary Millsap's Configuring Oracle Server for VLDB and the Optimal Flexible Architecture standard.

    Throughout these instructions, we will refer to a number of configurable settings and advise certain defaults. With the exception of passwords, we advise you to follow these defaults unless you know what you are doing. Subsequent documents will expect that you used the defaults, so a change made here will necessitate further changes later. For a guide to the defaults, please see the section called “Defaults”.

    In order for OpenACS to work properly you need to set the environment appropriately.

    export ORACLE_BASE=/ora8/m01/app/oracle
    export ORACLE_HOME=$ORACLE_BASE/product/8.1.7
    export PATH=$PATH:$ORACLE_HOME/bin
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
    export ORACLE_SID=ora8
    export ORACLE_TERM=vt100
    export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
    
    umask 022
    open_cursors = 500
    nls_date_format = "YYYY-MM-DD"

    For additional resources/documentation, please see this thread and Andrew Piskorski's mini-guide.

    Though Oracle 8.1.7 has an automated installer, we still need to perform several manual, administrative tasks before we can launch it. You must perform all of these steps as the root user. We recommend entering the X window system as a normal user and then doing a su -. This command gives you full root access.

    • Login as a non-root user and start X by typing startx

      [joeuser ~]$ startx

    • Open a terminal window type and login as root

      [joeuser ~]$ su -
      Password: ***********
      [root ~]#

    • Create and setup the oracle group and oracle account

      We need to create a user oracle, which is used to install the product, as well as starting and stopping the database.

      [root ~]# groupadd dba
      [root ~]# groupadd oinstall
      [root ~]# groupadd oracle
      [root ~]# useradd -g dba -G oinstall,oracle -m oracle
      [root ~]# passwd oracle

      You will be prompted for the New Password and Confirmation of that password.

    • Setup the installation location for Oracle. While Oracle can reside in a variety of places in the file system, OpenACS has adopted /ora8 as the base directory.

      Note: the Oracle install needs about 1 GB free on /ora8 to install successfully.

      [root ~]# mkdir /ora8
      root:/ora8# cd /ora8
      root:/ora8# mkdir -p m01 m02 m03/oradata/ora8
      root:/ora8# chown -R oracle.dba /ora8
      root:/ora8# exit
    • Set up the oracle user's environment

      • Log in as the user oracle by typing the following:

        [joeuser ~]$ su - oracle
        Password: ********
      • Use a text editor to edit the .bash_profile file in the oracle account home directory.

        [oracle ~]$ emacs .bash_profile

        You may get this error trying to start emacs:

        Xlib: connection to ":0.0" refused by server
        Xlib: Client is not authorized to connect to Server
        emacs: Cannot connect to X server :0.
        Check the DISPLAY environment variable or use `-d'.
        Also use the `xhost' program to verify that it is set to permit
        connections from your machine.

        If so, open a new terminal window and do the following:

        [joeuser ~]$ xhost +localhost

        Now, back in the oracle terminal:

        [oracle ~]$ export DISPLAY=localhost:0.0
        [oracle ~]$ emacs .bash_profile

        Try this procedure anytime you get an Xlib connection refused error.

      • Add the following lines (substituting your Oracle version number as needed) to .bash_profile:

        export ORACLE_BASE=/ora8/m01/app/oracle
        export ORACLE_HOME=$ORACLE_BASE/product/8.1.7
        export PATH=$PATH:$ORACLE_HOME/bin
        export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
        export ORACLE_SID=ora8
        export ORACLE_TERM=vt100
        export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
        
        umask 022

        Save the file by typing CTRL-X CTRL-S and then exit by typing CTRL-X CTRL-C. Alternatively, use the menus.

      Make sure that you do not add any lines like the following

      # NLS_LANG=american
      # export NLS_LANG

      These lines will change the Oracle date settings and will break OpenACS since OpenACS depends on the ANSI date format, YYYY-MM-DD dates.

    • Log out as oracle

      [oracle ~]$ exit
    • Log back in as oracle and double check that your environment variables are as intended. The env command lists all of the variables that are set in your environment, and grep shows you just the lines you want (those with ORA in it).

      [joeuser ~]$ su - oracle
      [oracle ~]$ env | grep ORA

      If it worked, you should see:

      ORACLE_SID=ora8
      ORACLE_BASE=/ora8/m01/app/oracle
      ORACLE_TERM=vt100
      ORACLE_HOME=/ora8/m01/app/oracle/product/8.1.7
      ORA_NLS33=/ora8/m01/app/oracle/product/8.1.7/ocommon/nls/admin/data

      If not, try adding the files to ~/.bashrc instead of .bash_profile. Then logout and log back in again. Also, be certain you are doing su - oracle and not just su oracle. The - means that .bashrc and .bash_profile will be evaluated.

      Make sure that /bin, /usr/bin, and /usr/local/bin are in your path by typing:

      [oracle ~]$ echo $PATH
      /bin:/usr/bin:/usr/local/bin:/usr/bin/X11:/usr/X11R6/bin:/home/oracle/bin:/ora8/m01/app/oracle/product/8.1.7/bin

      If they are not, then add them to the .bash_profile by changing the PATH statement above to PATH=$PATH:/usr/local/bin:$ORACLE_HOME/bin

    • Log in as oracle and start X if not already running. Start a new terminal:

      [joeuser ~]$ xhost +localhost
      [joeuser ~]$ su - oracle
      Password: **********
      [oracle ~]$ export DISPLAY=localhost:0.0
    • Find the runInstaller script

      • If you are installing Oracle from a CD-ROM, it is located in the install/linux path from the cd-rom mount point

        [oracle ~]$ su - root
        [root ~]# mount -t iso9660 /dev/cdrom /mnt/cdrom
        [root ~]# exit
        [oracle ~]$ cd /mnt/cdrom
      • If you are installing from the tarball, the install script is located in the Oracle8iR2 directory that was created when you expanded the archive.

        [oracle ~]$ cd /where/oracle/Disk1

      Check to make sure the file is there.

      oracle:/where/oracle/Disk1$ ls
      doc  index.htm  install  runInstaller  stage  starterdb

      If you don't see runInstaller, you are in the wrong directory.

    • Run the installer

      oracle:/where/oracle/Disk1$ ./runInstaller

      A window will open that welcomes you to the 'Oracle Universal Installer' (OUI). Click on "Next"

      Note

      Some people have had trouble with this step on RedHat 7.3 and 8.0. If so, try the following steps before calling ./runInstaller:

      1. Execute the following command: /usr/i386-glibc21-linux/bin/i386-glibc21-linux-env.sh

      2. Type export LD_ASSUME_KERNEL=2.2.5

    • The "File Locations" screen in the OUI:

      • "Source" path should have been prefilled with "(wherever you mounted the CDROM)/stage/products.jar"

      • "destination" path says "/ora8/m01/app/oracle/product/8.1.7"

        If the destination is not correct it is because your environment variables are not set properly. Make sure you logged on as oracle using su - oracle. If so, edit the ~/.bash_profile as you did in the section called “Pre-Installation Tasks”

      • Click "Next" (a pop up window will display Loading Product information).

    • The "Unix Group Name" screen in the OUI:

      • The Unix Group name needs to be set to 'oinstall' ( we made this Unix group earlier ).

      • Click "Next"

      • A popup window appears instantly, requesting you to run a script as root:

        • Debian users need to link /bin/awk to /usr/bin/awk before running the script below

          [joueser ~]$ su -
          [root ~]# ln -s /usr/bin/awk /bin/awk
      • Open a new terminal window, then type:

        [joeuser ~]$ su -
        [root ~]# cd /ora8/m01/app/oracle/product/8.1.7
        [root ~]# ./orainstRoot.sh  
        ; You should see:
        Creating Oracle Inventory pointer file (/etc/oraInst.loc)
        Changing groupname of /ora8/m01/app/oracle/oraInventory to oinstall.
        [root ~]# mkdir -p /usr/local/java
        [root ~]# exit
        [joeuser ~]$ exit
      • Click "Retry"

    • The "Available Products" screen in the OUI:

      • Select "Oracle 8i Enterprise Edition 8.1.7.1.0"

      • Click "Next"

    • The "Installation Types" screen

      • Select the "Custom" installation type.

      • Click "Next"

    • The "Available Product Components" screen

      • In addition to the defaults, make sure that "Oracle SQLJ 8.1.7.0," "Oracle Protocol Support 8.1.7.0.0," and "Linux Documentation 8.1.7.0.0" are also checked.

      • Click "Next"

      • A progress bar will appear for about 1 minute.

    • The "Component Locations" screen in the OUI

      • Click on the "Java Runtime Environment 1.1.8" It should have the path "/ora8/m01/app/oracle/jre/1.1.8"

      • Click "Next"

      • A progress bar will appear for about 1 minute.

    • The "Privileged Operation System Groups" screen in the OUI

      • Enter "dba" for "Database Administrator (OSDBA) Group"

      • Enter "dba" for the "Database Operator (OSOPER) Group"

      • Click "Next"

      • A progress bar will appear for about 1 minute.

    • The "Authentication Methods" screen

      • Click "Next"

    • The next screen is "Choose JDK home directory"

      • Keep the default path: /usr/local/java

      • Click "Next"

    • The "Create a Database" screen in the OUI

      • Select "No" as we will do this later, after some important configuration changes.

      • Click "Next"

    • The next screen is "Oracle Product Support"

      • TCP should be checked with "Status" listed as Required

      • Click "Next"

    • The "Summary" screen in the OUI

      • Check the "Space Requirements" section to verify you have enough disk space for the install.

      • Check that "(144 products)" is in the "New Installations" section title.

      • Click "Install"

      • A progress bar will appear for about 20 - 30 minutes. Now is a good time to take a break.

      • A "Setup Privileges" window will popup towards the end of the installation asking you to run a script as root

      • Run the script. Switch to the oracle user first to set the environment appropriately and then do su to get root privileges, while keeping the oracle user's enviroment.

        [joeuser ~]$ su - oracle
        Password: *********
        [oracle ~]$ su
        Password: *********
        [root ~]# /ora8/m01/app/oracle/product/8.1.7/root.sh
        ; You should see the following.   
        
        Creating Oracle Inventory pointer file (/etc/oraInst.loc)
        Changing groupname of /ora8/m01/app/oracle/oraInventory to oinstall.
        # /ora8/m01/app/oracle/product/8.1.7/root.sh
        Running Oracle8 root.sh script...
        The following environment variables are set as:
            ORACLE_OWNER= oracle
            ORACLE_HOME=  /ora8/m01/app/oracle/product/8.1.7
            ORACLE_SID=   ora8
        
        Enter the full pathname of the local bin directory: [/usr/local/bin]: 
        
        Press ENTER here to accept default of /usr/local/bin
              
        
        Creating /etc/oratab file...
        Entry will be added to the /etc/oratab file by
        Database Configuration Assistants when a database is created
        Finished running generic part of root.sh script.
        Now product-specific root actions will be performed.
        IMPORTANT NOTE: Please delete any log and trace files previously
                        created by the Oracle Enterprise Manager Intelligent
                        Agent. These files may be found in the directories
                        you use for storing other Net8 log and trace files.
                        If such files exist, the OEM IA may not restart.
      • Do not follow the instructions on deleting trace and log files, it is not necessary.

      [root ~]# exit
      [joeuser ~]$ exit
    • Go back to the pop-up window and click "OK"

    • The "Configuration Tools" screen in the OUI

      • This window displays the config tools that will automatically be launched.

    • The "Welcome" screen in the "net 8 Configuration Assistant"

      • Make sure the "Perform Typical installation" is not selected.

      • Click "Next"

      • The "Directory Service Access" screen in the "Net 8 Configuration Assistant"

      • Select "No"

      • Click "Next"

    • The "Listener Configuration, Listener Name" screen in the "Net 8 Configuration Assistant"

      • Accept the default listener name of "LISTENER"

      • Click "Next"

    • The "Listener Configuration, Select Protocols" screen in the "Net 8 Configuration Assistant"

      • The only choice in "Select protocols:" should be "TCP/IP"

      • Click "Next"

    • The "Listener Configuration TCP/IP Protocol" screen in the "Net 8 Configuration Assistant"

      • Default Port should be 1521 and selected.

      • Click "Next"

    • The "Listener Configuration, More Listeners" screen in the "Net 8 Configuration Assistant"

      • Select "No"

      • Click "Next"

    • The "Listener Configuration Done" screen in the "Net 8 Configuration Assistant"

      • Click "Next"

    • The "Naming Methods Configuration" screen in the "Net 8 Configuration Assistant"

      • Select "No"

      • Click "Next"

    • The "Done" screen in the "Net 8 Configuration Assistant"

      • Click "Finish"

    • The "End of Installation" screen in the OUI

      • Click "Exit"

      • Click "Yes" on the confirmation pop up window.

      • The Oracle Universal Installer window should have disappeared!

    Congratulations, you have just installed Oracle 8.1.7 Server! However, you still need to create a database which can take about an hour of non-interactive time, so don't quit yet.

    This step will take you through the steps of creating a customized database. Be warned that this process takes about an hour on a Pentium II with 128 MB of RAM.

    Note

    RedHat 7.3 and 8.0 users: Before running dbassist, do the following.

    1. Download the glibc patch from Oracle Technet into /var/tmp.

    2. cd $ORACLE_HOME

    3. tar xzf /var/tmp/glibc2.1.3-stubs.tgz

    4. ./setup_stubs

    • Make sure you are running X. Open up a terminal and su to oracle and then run the dbassist program.

      [joeuser ~]$ xhost +localhost
      [joeuser ~]$ su - oracle
      Password: *********
      [oracle ~]$ export DISPLAY=localhost:0.0
      [oracle ~]$ dbassist
    • The "Welcome" screen in the Oracle Database Configuration Agent (ODCA)

      • Select "Create a database"

      • Click "Next"

    • The "Select database type" screen in the ODCA

      • Select "Custom"

      • Click "Next"

    • The "Primary Database Type" window in ODCA

      • Select "Multipurpose"

      • Click "Next"

    • The "concurrent users" screen of the ODCA

      • Select "60" concurrent users.

      • Click "Next"

    • Select "Dedicated Server Mode", click "Next"

    • Accept all of the options, and click Next Oracle Visual Information Retrieval may be grayed out. If so, you can ignore it; just make sure that everything else is checked.

    • For "Global Database Name", enter "ora8"; for "SID", also enter "ora8" (it should do this automatically). Click "Change Character Set and select UTF8. Click "Next".

    • Accept the defaults for the next screen (control file location). Click "Next"

    • Go to the "temporary" and "rollback" tabs, and change the Size (upper-right text box) to 150MB. Click "Next"

    • Increase the redo log sizes to 10000K each. Click "Next"

    • Use the default checkpoint interval & timeout. Click "Next"

    • Increase "Processes" to 100; "Block Size" to 4096 (better for small Linux boxes; use 8192 for a big Solaris machine).

    • Accept the defaults for the Trace File Directory. Click "Next"

    • Finally, select "Save information to a shell script" and click "Finish" (We're going to examine the contents of this file before creating our database.)

    • Click the "Save" button. Oracle will automatically save it to the correct directory and with the correct file name. This will likely be /ora8/m01/app/oracle/product/8.1.7/assistants/dbca/jlib/sqlora8.sh

    • It will alert you that the script has been saved successfully.

    • Now we need to customize the database configuration a bit. While still logged on as oracle, edit the database initialization script (run when the db loads). The scripts are kept in $ORACLE_HOME/dbs and the name of the script is usually initSID.ora where SID is the SID of your database. Assuming your $ORACLE_HOME matches our default of /ora8/m01/app/oracle/product/8.1.7, the following will open the file for editing.

      [oracle ~]$ emacs /ora8/m01/app/oracle/product/8.1.7/dbs/initora8.ora
    • Add the following line to the end:

      nls_date_format = "YYYY-MM-DD"
    • Now find the open_cursors line in the file. If you're using emacs scroll up to the top of the buffer and do CTRL-S and type open_cursors to find the line. The default is 100. Change it to 500.

      open_cursors = 500
    • Save the file. In emacs, do CTRL-X CTRL-S to save followed by CTRL-X CTRL-C to exit or use the menu.

    • At this point, you are ready to initiate database creation. We recommend shutting down X to free up some RAM unless you have 256 MB of RAM or more. You can do this quickly by doing a CRTL-ALT-BACKSPACE, but make sure you have saved any files you were editing. You should now be returned to a text shell prompt. If you get sent to a graphical login screen instead, switch to a virtual console by doing CRTL-ALT-F1. Then login as oracle.

    • Change to the directory where the database creation script is and run it:

      [oracle ~]$ cd /ora8/m01/app/oracle/product/8.1.7/assistants/dbca/jlib
      oracle:/ora8/m01/app/oracle/product/8.1.7/assistants/dbca/jlib$ ./sqlora8.sh

      In some instances, Oracle will save the file to /ora8/m01/app/oracle/product/8.1.7/assistants/dbca Try running the script there if your first attempt does not succeed.

    • Your database will now be built. It will take > 1 hour - no fooling. You will see lots of errors scroll by (like: "ORA-01432: public synonym to be dropped does not exist") Fear not, this is normal.

      Eventually, you'll be returned to your shell prompt. In the meantime, relax, you've earned it.

    For this step, open up a terminal and su to oracle as usual. You should be running X and Netscape (or other web browser) for this phase.

    • You need to download the "Oracle Acceptance Test" file. It's available here and at http://philip.greenspun.com/wtr/oracle/acceptance-sql.txt. Save the file to /var/tmp

    • In the oracle shell, copy the file.

      [oracle ~]$ cp /var/tmp/acceptance-sql.txt /var/tmp/acceptance.sql
    • Once you've got the acceptance test file all set, stay in your term and type the following:

      [oracle ~]$ sqlplus system/manager

      SQL*Plus should startup. If you get an ORA-01034: Oracle not Available error, it is because your Oracle instance is not running. You can manually start it as the oracle user.

      [oracle ~]$ svrmgrl
      SVRMGR> connect internal
      SVRMGR> startup
    • Now that you're into SQL*Plus, change the default passwords for system, sys, and ctxsys to "alexisahunk" (or to something you'll remember):

      SQL> alter user system identified by alexisahunk;
      SQL> alter user sys identified by alexisahunk;
      SQL> alter user ctxsys identified by alexisahunk;
    • Verify that your date settings are correct.

      SQL> select sysdate from dual;

      If you don't see a date that fits the format YYYY-MM-DD, please read the section called “Troubleshooting Oracle Dates”.

    • At this point we are going to hammer your database with an intense acceptance test. This usually takes around 30 minutes.

      SQL> @ /var/tmp/acceptance.sql
      
      ; A bunch of lines will scroll by.  You'll know if the test worked if
      ; you see this at the end:
      
      SYSDATE
      ----------
      2000-06-10
      
      SQL>

      Many people encounter an error regarding maximum key length:

      ERROR at line 1:
      ORA-01450: maximum key length (758) exceeded

      This error occurs if your database block size is wrong and is usually suffered by people trying to load OpenACS into a pre-existing database. Unfortunately, the only solution is to create a new database with a block size of at least 4096. For instructions on how to do this, see the section called “Creating the First Database” above. You can set the parameter using the dbassist program or by setting the DB_BLOCK_SIZE parameter in your database's creation script.

      If there were no errors, then consider yourself fortunate. Your Oracle installation is working.

    You will want to automate the database startup and shutdown process. It's probably best to have Oracle spring to life when you boot up your machine.

    • Oracle includes a script called dbstart that can be used to automatically start the database. Unfortunately, the script shipped in the Linux distribution does not work out of the box. The fix is simple. Follow these directions to apply it. First, save dbstart to /var/tmp. Then, as oracle, do the following:

      [oracle ~]$ cp /var/tmp/dbstart.txt /ora8/m01/app/oracle/product/8.1.7/bin/dbstart 
      [oracle ~]$ chmod 755 /ora8/m01/app/oracle/product/8.1.7/bin/dbstart
    • While you're logged in as oracle, you should configure the oratab file to load your database at start. Edit the file /etc/oratab:

      • You will see this line.

        ora8:/ora8/m01/app/oracle/product/8.1.7:N

        By the way, if you changed the service name or have multiple databases, the format of this file is:

        service_name:$ORACLE_HOME:Y || N (for autoload)

      • Change the last letter from "N" to "Y". This tells Oracle that you want the database to start when the machine boots. It should look like this.

        ora8:/ora8/m01/app/oracle/product/8.1.7:Y
      • Save the file & quit the terminal.

    • You need a script to automate startup and shutdown. Save oracle8i.txt in /var/tmp. Then login as root and install the script. (Debian users: substitute /etc/init.d for /etc/rc.d/init.d throughout this section)

      [oracle ~]$ su -
      [root ~]# cp /var/tmp/oracle8i.txt /etc/rc.d/init.d/oracle8i
      [root ~]# chown root.root /etc/rc.d/init.d/oracle8i
      [root ~]# chmod 755 /etc/rc.d/init.d/oracle8i
    • Test the script by typing the following commands and checking the output. (Debian Users: as root, do mkdir /var/lock/subsys first)

      [root ~]# /etc/rc.d/init.d/oracle8i stop
      Oracle 8i auto start/stop
      Shutting Oracle8i:
      Oracle Server Manager Release 3.1.7.0.0 - Production
      
      Copyright (c) 1997, 1999, Oracle Corporation.  All
      Rights Reserved.
      
      Oracle8i Enterprise Edition Release 8.1.7.0.1 -
      Production
      With the Partitioning option
      JServer Release 8.1.7.0.1 - Production
      
      SVRMGR> Connected.
      SVRMGR> Database closed.
      Database dismounted.
      ORACLE instance shut down.
      SVRMGR>
      Server Manager complete.
      Database "ora8" shut down.
            
      [root ~]# /etc/rc.d/init.d/oracle8i start
      Oracle 8i auto start/stop
      Starting Oracle8i: 
      SQL*Plus: Release 8.1.7.0.0 - Production on Wed Mar 6 17:56:02 2002
      
      (c) Copyright 2000 Oracle Corporation.  All rights reserved.
      
      SQL> Connected to an idle instance.
      SQL> ORACLE instance started.
      
      Total System Global Area   84713632 bytes
      Fixed Size                    73888 bytes
      Variable Size              76079104 bytes
      Database Buffers            8388608 bytes
      Redo Buffers                 172032 bytes
      Database mounted.
      Database opened.
      SQL> Disconnected
      
      Database "ora8" warm started.
      
      Database "ora8" warm started.
    • If it worked, then run these commands to make the startup and shutdown automatic.

      • Red Hat users:

        [root ~]# cd /etc/rc.d/init.d/                      
        [root ~]# chkconfig --add oracle8i
        [root ~]# chkconfig --list oracle8i
        ; You should see:
        oracle8i        0:off   1:off   2:off   3:on    4:on    5:on    6:off
      • Debian users:

        [root ~]# update-rc.d oracle8i defaults
         Adding system startup for /etc/init.d/oracle8i ...
           /etc/rc0.d/K20oracle8i -> ../init.d/oracle8i
           /etc/rc1.d/K20oracle8i -> ../init.d/oracle8i
           /etc/rc6.d/K20oracle8i -> ../init.d/oracle8i
           /etc/rc2.d/S20oracle8i -> ../init.d/oracle8i
           /etc/rc3.d/S20oracle8i -> ../init.d/oracle8i
           /etc/rc4.d/S20oracle8i -> ../init.d/oracle8i
           /etc/rc5.d/S20oracle8i -> ../init.d/oracle8i
      • SuSE users:

        [root ~]# cd /etc/rc.d/init.d
        root:/etc/rc.d/init.d# ln -s /etc/rc.d/init.d/oracle8i K20oracle8i
        root:/etc/rc.d/init.d# ln -s /etc/rc.d/init.d/oracle8i S20oracle8i
        root:/etc/rc.d/init.d# cp K20oracle8i rc0.d
        root:/etc/rc.d/init.d# cp S20oracle8i rc0.d
        root:/etc/rc.d/init.d# cp K20oracle8i rc1.d
        root:/etc/rc.d/init.d# cp S20oracle8i rc1.d 
        root:/etc/rc.d/init.d# cp K20oracle8i rc6.d
        root:/etc/rc.d/init.d# cp S20oracle8i rc6.d
        root:/etc/rc.d/init.d# cp K20oracle8i rc2.d
        root:/etc/rc.d/init.d# cp S20oracle8i rc2.d
        root:/etc/rc.d/init.d# cp K20oracle8i rc3.d
        root:/etc/rc.d/init.d# cp S20oracle8i rc3.d 
        root:/etc/rc.d/init.d# cp K20oracle8i rc4.d  
        root:/etc/rc.d/init.d# cp S20oracle8i rc4.d  
        root:/etc/rc.d/init.d# cp K20oracle8i rc5.d
        root:/etc/rc.d/init.d# cp S20oracle8i rc5.d
        root:/etc/rc.d/init.d# rm K20oracle8i
        root:/etc/rc.d/init.d# rm S20oracle8i
        root:/etc/rc.d/init.d# cd
        [root ~]# SuSEconfig
        Started the SuSE-Configuration Tool.
        Running in full featured mode.
        Reading /etc/rc.config and updating the system...
        Executing /sbin/conf.d/SuSEconfig.gdm...   
        Executing /sbin/conf.d/SuSEconfig.gnprint...
        Executing /sbin/conf.d/SuSEconfig.groff...   
        Executing /sbin/conf.d/SuSEconfig.java...    
        Executing /sbin/conf.d/SuSEconfig.kdm...   
        Executing /sbin/conf.d/SuSEconfig.pcmcia...
        Executing /sbin/conf.d/SuSEconfig.perl...
        Executing /sbin/conf.d/SuSEconfig.postfix...
        Executing /sbin/conf.d/SuSEconfig.sendmail...
        Executing /sbin/conf.d/SuSEconfig.susehilf...
        Executing /sbin/conf.d/SuSEconfig.susehilf.add...
        Executing /sbin/conf.d/SuSEconfig.susewm...
        Executing /sbin/conf.d/SuSEconfig.tetex...
        Executing /sbin/conf.d/SuSEconfig.ypclient...
        Processing index files of all manpages...
        Finished.
    • You also need some scripts to automate startup and shutdown of the Oracle8i listener. The listener is a name server that allows your Oracle programs to talk to local and remote databases using a standard naming convention. It is required for Intermedia Text and full site search.

      Download these three scripts into /var/tmp

      Now issue the following commands (still as root).

      [root ~]# su - oracle
      [oracle ~]$ cp /var/tmp/startlsnr.txt /ora8/m01/app/oracle/product/8.1.7/bin/startlsnr
      [oracle ~]$ cp /var/tmp/stoplsnr.txt /ora8/m01/app/oracle/product/8.1.7/bin/stoplsnr    
      [oracle ~]$ chmod 755 /ora8/m01/app/oracle/product/8.1.7/bin/startlsnr
      [oracle ~]$ chmod 755 /ora8/m01/app/oracle/product/8.1.7/bin/stoplsnr
      [oracle ~]$ exit
      [root ~]# cp /var/tmp/listener8i.txt /etc/rc.d/init.d/listener8i
      [root ~]# cd /etc/rc.d/init.d
      root:/etc/rc.d/init.d# chmod 755 listener8i

      Test the listener automation by running the following commands and checking the output.

      root:/etc/rc.d/init.d# ./listener8i stop
      Oracle 8i listener start/stop
      Shutting down Listener for 8i: 
      LSNRCTL for Linux: Version 8.1.7.0.0 - Production on 06-MAR-2002 18:28:49
      
      (c) Copyright 1998, Oracle Corporation.  All rights reserved.
      
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
      The command completed successfully
      
          
      root:/etc/rc.d/init.d# ./listener8i start
      Oracle 8i listener start/stop
      Starting the Listener for 8i: 
      LSNRCTL for Linux: Version 8.1.7.0.0 - Production on 06-MAR-2002 18:28:52
      
      (c) Copyright 1998, Oracle Corporation.  All rights reserved.
      
      Starting /ora8/m01/app/oracle/product/8.1.7/bin/tnslsnr: please wait...
      
      TNSLSNR for Linux: Version 8.1.7.0.0 - Production
      System parameter file is /ora8/m01/app/oracle/product/8.1.7/network/admin/listener.ora
      Log messages written to /ora8/m01/app/oracle/product/8.1.7/network/log/listener.log
      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
      
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
      STATUS of the LISTENER
      ------------------------
      Alias                     LISTENER
      Version                   TNSLSNR for Linux: Version 8.1.7.0.0 - Production
      Start Date                06-MAR-2002 18:28:53
      Uptime                    0 days 0 hr. 0 min. 0 sec
      Trace Level               off
      Security                  OFF
      SNMP                      OFF
      Listener Parameter File   /ora8/m01/app/oracle/product/8.1.7/network/admin/listener.ora
      Listener Log File         /ora8/m01/app/oracle/product/8.1.7/network/log/listener.log
      Services Summary...
        PLSExtProc        has 1 service handler(s)
        ora8      has 1 service handler(s)
      The command completed successfully

      This test will verify that the listener is operating normally. Login into the database using the listener naming convention.

      sqlplususername/password/@SID

      [root ~]# su - oracle
      [oracle ~]$ sqlplus system/alexisahunk@ora8
      
      SQL> select sysdate from dual;
      
      SYSDATE
      ----------
      2002-02-22
      
      SQL> exit
      [oracle ~]$ exit
      [root ~]#
      • RedHat users:

        Now run chkconfig on the listener8i script.

        [root ~]# cd /etc/rc.d/init.d/
        root:/etc/rc.d/init.d# chkconfig --add listener8i
        root:/etc/rc.d/init.d# chkconfig --list listener8i
        listener8i      0:off   1:off   2:off   3:on    4:on    5:on    6:off
      • Debian users:

        Now run update-rc.d on the listener8i script.

        [root ~]# update-rc.d listener8i defaults 21 19
         Adding system startup for /etc/init.d/listener8i ...
           /etc/rc0.d/K19listener8i -> ../init.d/listener8i
           /etc/rc1.d/K19listener8i -> ../init.d/listener8i
           /etc/rc6.d/K19listener8i -> ../init.d/listener8i
           /etc/rc2.d/S21listener8i -> ../init.d/listener8i
           /etc/rc3.d/S21listener8i -> ../init.d/listener8i
           /etc/rc4.d/S21listener8i -> ../init.d/listener8i
           /etc/rc5.d/S21listener8i -> ../init.d/listener8i
    • Test the automation

      As a final test, reboot your computer and make sure Oracle comes up. You can do this by typing

      [root ~]# /sbin/shutdown -r -t 0 now

      Log back in and ensure that Oracle started automatically.

      [joeuser ~]$ su - oracle
      [oracle ~]$ sqlplus system/alexisahunk@ora8
      
      SQL> exit

    Congratulations, your installation of Oracle 8.1.7 is complete.

    Oracle has an internal representation for storing the data based on the number of seconds elapsed since some date. However, for the purposes of inputing dates into Oracle and getting them back out, Oracle needs to be told to use a specific date format. By default, it uses an Oracle-specific format which isn't copacetic. You want Oracle to use the ANSI-compliant date format which is of form 'YYYY-MM-DD'.

    To fix this, you should include the following line in $ORACLE_HOME/dbs/initSID.ora or for the default case, $ORACLE_HOME/dbs/initora8.ora

    nls_date_format = "YYYY-MM-DD"

    You test whether this solved the problem by firing up sqlplus and typing:

    SQL> select sysdate from dual;

    You should see back a date like 2000-06-02. If some of the date is chopped off, i.e. like 2000-06-0, everything is still fine. The problem here is that sqlplus is simply truncating the output. You can fix this by typing:

    SQL> column sysdate format a15
    SQL> select sysdate from dual;

    If the date does not conform to this format, double-check that you included the necessary line in the init scripts. If it still isn't working, make sure that you have restarted the database since adding the line:

    [joeuser ~]$ svrmgrl
    SVRMGR> connect internal
    Connected.
    SVRMGR> shutdown
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SVRMGR> startup
    ORACLE instance started.

    If you're sure that you have restarted the database since adding the line, check your initialization scripts. Make sure that the following line is not included:

    export nls_lang = american

    Setting this environment variable will override the date setting. Either delete this line and login again or add the following entry to your login scripts after the nls_lang line:

    export nls_date_format = 'YYYY-MM-DD'

    Log back in again. If adding the nls_date_format line doesn't help, you can ask for advice in our OpenACS forums.

    • Dropping a tablespace

      • Run sqlplus as the dba:

        [oracle ~]$ sqlplus system/changeme
      • To drop a user and all of the tables and data owned by that user:

        SQL> drop user oracle_user_name cascade;
      • To drop the tablespace: This will delete everything in the tablespace overriding any referential integrity constraints. Run this command only if you want to clean out your database entirely.

        SQL> drop tablespace table_space_name including contents cascade constraints;

    For more information on Oracle, please consult the documentation.

    We used the following defaults while installing Oracle.

    Variable Value Reason
    ORACLE_HOME /ora8/m01/app/oracle/product/8.1.7 This is the default Oracle installation directory.
    ORACLE_SERVICE ora8 The service name is a domain-qualified identifier for your Oracle server.
    ORACLE_SID ora8 This is an identifier for your Oracle server.
    ORACLE_OWNER oracle The user who owns all of the oracle files.
    ORACLE_GROUP dba The special oracle group. Users in the dba group are authorized to do a connect internal within svrmgrl to gain full system access to the Oracle system.

    here

    Created by Gustaf Neumann, last modified by Gustaf Neumann 06 Nov 2016, at 09:43 AM

    -- acceptance-sql.txt by philg@mit.edu and jsc@arsdigita.com
    --                      (Philip Greenspun and Jin Choi)
    
    -- SQL commands to test an Oracle installation
    -- for adequate privileges and capacity
    -- run as the same user as the Web user
    
    -- creating a table
    create table foo (
    	foo_key	integer primary key,
    	random	varchar(1000)
    );
    
    -- creating an index
    create index foo_on_random on foo ( random );
    
    -- inserting some rows
    insert into foo values (1, '1');
    insert into foo values (2, '2');
    insert into foo values (3, '3');
    insert into foo values (4, '4');
    insert into foo values (5, '5');
    insert into foo values (6, '6');
    insert into foo values (7, '7');
    insert into foo values (8, '8');
    insert into foo values (9, '9');
    insert into foo values (10, '10');
    insert into foo values (11, null);
    insert into foo values (12, null);
    insert into foo values (13, null);
    insert into foo values (14, null);
    insert into foo values (15, null);
    insert into foo values (16, null);
    insert into foo values (17, null);
    insert into foo values (18, null);
    insert into foo values (19, null);
    insert into foo values (20, null);
    
    -- create another table to work with
    create table bar as select foo_key + 1 as bar_key, random from foo;
    
    -- joins
    select b.random from foo f, bar b where f.foo_key = b.bar_key and f.random like '3%';
    
    -- update
    update foo set foo_key = foo_key + 100 where random is null;
    
    -- should return 10
    select count(*) from foo where foo_key > 100;
    
    -- create a sequence
    create sequence foo_sequence start with 200;
    
    -- test whether truncate works
    truncate table bar;
    
    drop table bar;
    
    
    -- test 1) whether has privileges to create a procedure
    --  and 2)  whether rollback segments are adequately sized
    
    -- create a pl/sql procedure
    create or replace procedure thrash_database(v_number_of_rows IN integer)
    AS
      i	integer;
    BEGIN
      FOR i IN 1..v_number_of_rows LOOP
        insert into foo (foo_key, random) values (foo_sequence.nextval, 'abcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghij');
      END LOOP;
    END thrash_database;
    /
    show errors
    
    -- we think any Oracle installation ought to be able to handle
    -- 100,000 rows of 500 bytes each
    
    execute thrash_database(100000);
    
    select count(*) from foo;
    
    commit;
    
    delete from foo;
    commit;
    
    drop table foo;
    drop sequence foo_sequence;
    drop procedure thrash_database;
    
    -- make sure that NLS_DATE_FORMAT is correct by 
    -- seeing that the following command returns
    -- YYYY-MM-DD (e.g., 1999-05-22)
    
    select sysdate from dual;
    
    NameContent TypeLast ModifiedBy UserSize (Bytes)
    acceptance-sql.txttext/plain2016-11-06 09:43:31+01Gustaf Neumann2915

    Escaping to the procedural world

    Created by Anett Szabo, last modified by Gustaf Neumann 13 Feb 2009, at 09:37 AM

    Declarative languages can be very powerful and reliable, but sometimes it is easier to think about things procedurally. One way to do this is by using a procedural language in the database client. For example, with AOLserver we generally program in Tcl, a procedural language, and read the results of SQL queries. For example, in the /news module of the ArsDigita Community System, we want to

    • query for the current news
    • loop through the rows that come back and display one line for each row (with a link to a page that will show the full story)
    • for the first three rows, see if the news story is very short. If so, just display it on this page
    The words above that should give a SQL programmer pause are in the last bullet item: if and for the first three rows. There are no clean ways in standard SQL to say "do this just for the first N rows" or "do something special for a particular row if its data match a certain pattern".

    Here's the AOLserver Tcl program. Note that depending on the contents of an item in the news table, the Tcl program may execute an SQL query (to figure out if there are user comments on a short news item).


    set selection [ns_db select $db "select *
    from news
    where sysdate between release_date and expiration_date
    and approved_p = 't'
    order by release_date desc, creation_date desc"]

    while { [ns_db getrow $db $selection] } {
    set_variables_after_query
    # we use the luxury of Tcl to format the date nicely
    ns_write "<li>[util_AnsiDatetoPrettyDate $release_date]: "
    if { $counter <= 3 && [string length $body] < 300 } {
    # it is one of the top three items and it is rather short
    # so, let's consider displaying it right here
    # first, let's go back to Oracle to find out if there are any
    # comments on this item
    set n_comments [database_to_tcl_string $db_sub "select count(*) from general_comments where on_what_id = $news_id and on_which_table = 'news'"]
    if { $n_comments > 0 } {
    # there are some comments; just show the title
    ns_write "<a href=\"item.tcl?news_id=$news_id\">$title</a>\n"
    } else {
    # let's show the whole news item
    ns_write "$title\n<blockquote>\n[util_maybe_convert_to_html $body $html_p]\n"
    if {[parameter::get -parameter SolicitCommentsP -default 1]} {
    ns_write "<br><br>\n<A HREF=\"comment-add.tcl?news_id=$news_id\">comment</a>\n"
    }
    ns_write "</blockquote>\n"
    }
    } else {
    ns_write "<a href=\"item.tcl?news_id=$news_id\">$title</a>\n"
    }
    }

    Suppose that you have a million rows in your news table, you want five, but you can only figure out which five with a bit of procedural logic. Does it really make sense to drag those million rows of data all the way across the network from the database server to your client application and then throw out 999,995 rows?

    Or suppose that you're querying a million-row table and want the results back in a strange order. Does it make sense to build a million-row data structure in your client application, sort them in the client program, then return the sorted rows to the user?

    Visit http://www.scorecard.org/chemical-profiles/ and search for "benzene". Note that there are 328 chemicals whose names contain the string "benzene":


    select count(*)
    from chemical
    where upper(edf_chem_name) like upper('%benzene%');

    COUNT(*)
    ----------
    328
    The way we want to display them is
    • exact matches on top
    • line break
    • chemicals that start with the query string
    • line break
    • chemicals that contain the query string
    Within each category of chemicals, we want to sort alphabetically. However, if there are numbers or special characters in front of a chemical name, we want to ignore those for the purposes of sorting.

    Can you do all of that with one query? And have them come back from the database in the desired order?

    You could if you could make a procedure that would run inside the database. For each row, the procedure would compute a score reflecting goodness of match. To get the order correct, you need only ORDER BY this score. To get the line breaks right, you need only have your application program watch for changes in score. For the fine tuning of sorting equally scored matches alphabetically, just write another procedure that will return a chemical name stripped of leading special characters, then sort by the result. Here's how it looks:


    select edf_chem_name,
    edf_substance_id,
    score_chem_name_match_score(upper(edf_chem_name),upper('%benzene%'))
    as match_score
    from chemical
    where upper(edf_chem_name) like upper('%benzene%');
    order by score_chem_name_match_score(upper(edf_chem_name),upper('benzene')),
    score_chem_name_for_sorting(edf_chem_name)

    We specify the procedure score_chem_name_match_score to take two arguments: one the chemical name from the current row, and one the query string from the user. It returns 0 for an exact match, 1 for a chemical whose name begins with the query string, and 2 in all other cases (remember that this is only used in queries where a LIKE clause ensures that every chemical name at least contains the query string. Once we defined this procedure, we'd be able to call it from a SQL query, the same way that we can call built-in SQL functions such as upper.

    So is this possible? Yes, in all "enterprise-class" relational database management systems. Historically, each DBMS has had a proprietary language for these stored procedures. Starting in 1997, DBMS companies began to put Java byte-code interpreters into the database server. Oracle added Java-in-the-server capability with its 8.1 release in February 1999. If you're looking at old systems such as Scorecard, though, you'll be looking at procedures in Oracle's venerable PL/SQL language:


    create or replace function score_chem_name_match_score
    (chem_name IN varchar, query_string IN varchar)
    return integer
    AS
    BEGIN
    IF chem_name = query_string THEN
    return 0;
    ELSIF instr(chem_name,query_string) = 1 THEN
    return 1;
    ELSE
    return 2;
    END IF;
    END score_chem_name_match_score;
    Notice that PL/SQL is a strongly typed language. We say what arguments we expect, whether they are IN or OUT, and what types they must be. We say that score_chem_name_match_score will return an integer. We can say that a PL/SQL variable should be of the same type as a column in a table:

    create or replace function score_chem_name_for_sorting (chem_name IN varchar)
    return varchar
    AS
    stripped_chem_name chem_hazid_ref.edf_chem_name%TYPE;
    BEGIN
    stripped_chem_name := ltrim(chem_name,'1234567890-+()[],'' #');
    return stripped_chem_name;
    END score_chem_name_for_sorting;
    The local variable stripped_chem_name is going to be the same type as the edf_chem_name column in the chem_hazid_ref table.

    If you are using the Oracle application SQL*Plus to define PL/SQL functions, you have to terminate each definition with a line containing only the character "/". If SQL*Plus reports that there were errors in evaluating your definition, you then have to type "show errors" if you want more explanation. Unless you expect to write perfect code all the time, it can be convenient to leave these SQL*Plus incantations in your .sql files. Here's an example:



    -- note that we prefix the incoming arg with v_ to keep it
    -- distinguishable from the database column of the same name
    -- this is a common PL/SQL convention
    create or replace function user_group_name_from_id (v_group_id IN integer)
    return varchar
    IS
    -- instead of worrying about how many characters to
    -- allocate for this local variable, we just tell
    -- Oracle "make it the same type as the group_name
    -- column in the user_groups table"
    v_group_name user_groups.group_name%TYPE;
    BEGIN
    if v_group_id is null
    then return '';
    end if;
    -- note the usage of INTO below, which pulls a column
    -- from the table into a local variable
    select group_name into v_group_name
    from user_groups
    where group_id = v_group_id;
    return v_group_name;
    END;
    /
    show errors

    Choosing between PL/SQL and Java

    How to choose between PL/SQL and Java? Easy: you don't get to choose. In lots of important places, e.g., triggers, Oracle forces you to specify blocks of PL/SQL. So you have to learn at least the rudiments of PL/SQL. If you're going to build major packages, Java is probably a better long-term choice.

    Reference

     

     

    ---

    based on  SQL for Web Nerds

    Control Structure

    Created by Anett Szabo, last modified by Gustaf Neumann 13 Feb 2009, at 09:27 AM

    Control structures let you say "run this fragment of code if X is true" or "do this a few times" or "do this until something is no longer true". The available control structures in Tcl may be grouped into the following categories:
    • conditional
    • looping (iteration)
    • error-handling
    • miscellaneous (non-local exit)

     

    The Fundamental Conditional Command: if

    The most basic Tcl control structure is the if command:

    if boolean ?then? body1 ?else? ?body2?
    Note that the words "then" and "else" are optional, as is the entire else clause. The most basic if statement looks like this:
    if {condition} {
    body }
    In the ArsDigita Community System, we always leave out the "then", but if we include an else or elseif clause, we put in those optional words. Consistency is the hobgoblin of little minds...
    if {condition} {
    body } elseif {other_condition} {
    alternate_body } else { another_body }
    Note how the curly braces and keywords are artfully positioned so that the entire if statement is on one line as far as the interpreter is concerned, i.e., all the newlines are grouped within curly braces. An easy way to break your program is to rewrite the above statement as follows:
    if {condition} {
    body } elseif {other_condition} {
    alternate_body } else { another_body }
    The Tcl interpreter will think that the if statement has ended after the first body and will next try to evaluate "elseif" as a procedure.

    Let's look at an example from http://software.arsdigita.com/www/register/user-login.tcl. At this point in the ArsDigita Community System flow, a user has already typed his or her email address.

    # Get the user ID
    set selection [ns_db 0or1row $db "select user_id, user_state, converted_p from users \
    where upper(email)=upper('$QQemail')"]

    if {$selection == ""} {
    # Oracle didn't find a row; this email addres is not in the database
    # redirect this person to the new user registration page
    ns_returnredirect "user-new.tcl?[export_url_vars return_url email]"
    return
    }
    The same page provides an example both of nested if and if then else:
    if {[parameter::get -parameter AllowPersistentLoginP -default 1]} {
    # publisher has elected to provide an option to issue
    # a persistent cookie with user_id and crypted password
    if {[parameter::get -parameter PersistentLoginDefaultP -default 1]} {
    # persistent cookie shoudl be the default
    set checked_option "CHECKED"
    } else {
    set checked_option ""
    }
    ns_write "<input type=checkbox name=persistent_cookie_p value=t $checked_option>
    Remember this address and password?
    (<a href=\"explain-persistent-cookies.adp\">help</a>)"
    }
    Notice that the conventional programming style in Tcl is to call if for effect rather than value. It would work just as well to write the inner if in a more Lisp-y style:
    set checked_option [if {[parameter::get ...]} {
    subst "CHECKED"
    } else {
    subst ""
    }]
    This works because if returns the value of the last expression evaluated. However, being correct and being comprehensible to the community of Tcl programmers are different things. It is best to write code adhering to indentation and other stylistic conventions. You don't want to be the only person in the world capable of maintaining a service that has to be up 24x7.

     

    Another Conditional Command: switch

    The switch dispatches on the value of its first argument: particular variable as follows:

    switch flags value {
    pattern1 body1 pattern2 body2 ... }
    If http://software.arsdigita.com/www/register/user-login.tcl finds a user in the database, it uses a switch on the user's state to decide what to do next:
    switch $user_state {
    "authorized" { # just move on }
    "banned" {
    ns_returnredirect "banned-user.tcl?user_id=$user_id"
    return
    }
    "deleted" {
    ns_returnredirect "deleted-user.tcl?user_id=$user_id"
    return
    }
    "need_email_verification_and_admin_approv" {
    ns_returnredirect "awaiting-email-verification.tcl?user_id=$user_id"
    return
    }
    "need_admin_approv" {
    ns_returnredirect "awaiting-approval.tcl?user_id=$user_id"
    return
    }
    "need_email_verification" {
    ns_returnredirect "awaiting-email-verification.tcl?user_id=$user_id"
    return
    }
    "rejected" {
    ns_returnredirect "awaiting-approval.tcl?user_id=$user_id"
    return
    }
    default {
    ns_log Warning "Problem with registration state machine on user-login.tcl"
    ad_return_error "Problem with login" "There was a problem authenticating the account: $user_id. Most likely, the database contains users with no user_state."
    return
    }
    }
    In this case, we're using the standard switch behavior of matching strings exactly. We're also provide a "default" keyword at the end that indicates some code to run if nothing else matched.

    It is possible to use more sophisticated patterns in switch. Here's a fragment that sends different email depending on the pattern of the address:

    switch -glob $email {
    {*mit.edu} { ns_sendmail $email $from $subject $body }
    {*cmu.edu} { ns_sendmail $email $from $subject "$body\n\nP.S. Consider applying to MIT. Boston is much nicer than Pittsburgh"}
    {*harvard.edu} { ns_sendmail $email $from $subject "$body\n\nP.S. Please ask your parents to invest in our tech startup."}
    }

    The third behavior for switch is invoked using the "-regexp" flag. See the pattern matching chapter for more on how these patterns work.

    More: http://www.tcl.tk/man/tcl8.4/TclCmd/switch.htm

     

    Looping commands while, foreach, and for

    The while command in Tcl operates as follows:

    while { conditional_statement } {
    loop_body_statements }
    The conditional statement is evaluated; if it is true, the loop body statement is executed, and then the conditional statement is reevaluated and the process repeats. If the conditional statement is ever false, the interpreter does not execute the loop body statements, and continues to the next line after the conditional.

    Here is a while statement used to display the last name, first name of each MIT nerd using a Web service. The conditional is the result of calling AOLserver's ns_db getrow API procedure. This procedure returns 1 if it can fetch the next row from the SQL cursor, 0 if there aren't any more rows to fetch.

    set selection [ns_db select $db "select first_names, last_name from users \
    where lower(email) like '%mit.edu'"]

    while { [ns_db getrow $db $selection] } {
    # set local variables to the SQL column names
    set_variables_after_query
    ns_write "<LI>$last_name, $first_names"
    }

    More: http://www.tcl.tk/man/tcl8.4/TclCmd/while.htm

    The Tcl foreach command loops through the elements of a list, setting a loop variable to each element in term:

    foreach variable_name list {
    body }
    Here's an example from http://software.arsdigita.com/www/monitor.tcl, a page that displays current server activity:
    # ask AOLserver to return a list of lists, one for each current connection
    set connections [ns_server active]

    foreach connection $connections {
    # separate the sublist elements with "

    " tags
    ns_write $conn "



    [join $connection "

    "]"
    }

    The program http://sofware.arsdigita.com/www/admin/static/link-check.tcl checks every HTML file in an ArsDigita Community System for dead links. Here's a helper procedure that works on one file:
    proc check_file {f} {
    # alert the administrator that we're working on this file
    ns_write "<li>$f\n<ul>\n"
    # read the contents into $content
    set stream [open $f]
    set content [read $stream]
    close $stream
    # loop through each reference, relying on API call ns_hrefs
    # to parse the HTML and tell us where this file points
    foreach url [ns_hrefs $content] { # do all the hard work ... } ns_write "</ul>\n" }

    Notice how easy this procedure was to write thanks to the AOLserver developers thoughtfully providing us with ns_hrefs, which takes an HTML string and returns a list of every HREF target.

    More: http://www.tcl.tk/man/tcl8.4/TclCmd/foreach.htm

    The last looping command, for, is good for traditional "for i from 1 to 10" kind of iteration. Here's the syntax:

    for start test next body
    
    We use this control structure in the winner picking admin page of the ArsDigita Comunity System's contest module: http://software.arsdigita.com/www/admin/contest/pick-winners.tcl. The input to this page specifies a time period, a contest, and how many winners are to be picked. Here the result of executing the for loop is a list of N elements, where N is the number of desired winners:
    for {set i 1} {$i <= $n_winners} {incr i} {
    # we'll have winner_numbers between 0 and $n_contestants - 1
    # because randomRange returns a random integer between 0
    # and its argument
    lappend winner_numbers [randomRange $n_contestants]
    }


    More: http://www.tcl.tk/man/tcl8.4/TclCmd/for.htm


    Error-handling command: catch

    If a Tcl command throws an error in a CGI script or an AOLserver API page, by default the user will be presented with an error page. If you don't want that to happen, fix your bugs! Sometimes it isn't possible to fix your bugs. For example, the ns_httpget API procedure fetches a Web page from the wider Internet. Under certain network-dependent conditions, it may throw an error. If you don't want your users to be exposed to that as an error, put in a catch:

    catch script ?variable_name?
    
    catch returns 1 if script threw an error, 0 otherwise. If you supply the second argument (variable_name), catch will set that variable to the result of executing script, whether or not the script threw an error.

    Our classic example always involves ns_httpget. Here's one from http://www.webho.com/WealthClock:

    # define a procedure that computes the entire page
    proc wealth_ReturnWholePage {} {
    # do a couple of ns_httpgets and some arithmetic
    # to produce the user-visible HTML
    ...
    }

    # this is the procedure registered to http://www.webho.com/WealthClock
    proc wealth_Top {ignore} {
    if [catch {set moby_string [Memoize wealth_ReturnWholePage]} errmsg] {
    # something went wrong with our sources
    ... return an apology message to the users
    } else {
    # we computed the result (or Memoize got it from the cache)
    ns_return 200 text/html $moby_string
    }
    }

    Sending email is another time that a Web server has to go outside its carefully controlled world and might experience an error. Here is the entire http://software.arsdigita.com/tcl/ad-monitor.tcl, which implements a central facility for other sections in the ArsDigita Community System. The idea is that programmers can put in "email the administrator if broken" instructions on pages that won't result in a nightmare for the administrator if the page is getting hit every few seconds.

    # the overall goal here is that the ad_host_administrator gets
    # notified if something is horribly wrong, but not more than once
    # every 15 minutes

    # we store the last [ns_time] (seconds since 1970) notification time
    # in ad_host_administrator_last_notified

    ns_share -init { set ad_host_administrator_last_notified 0 } ad_host_administrator_last_notified

    proc ad_notify_host_administrator {subject body {log_p 0}} {
    ns_share ad_host_administrator_last_notified
    if $log_p {
    # usually the error will be in the error log anyway
    ns_log Notice "ad_notify_host_administrator: $subject\n\n$body\n\n"
    }
    if { [ns_time] > [expr $ad_host_administrator_last_notified + 900] } {
    # more than 15 minutes have elapsed since last note
    set ad_notify_host_administrator [ns_time]
    if [catch { ns_sendmail [ad_host_administrator] [ad_system_owner] $subject $body } errmsg] {
    ns_log Error "failed sending email note to [ad_host_administrator]"
    }
    }
    }

    Make sure that you don't overuse catch. The last thing that you want is a page failing silently. Genuine errors should always be brought to a user's attention and ideally to the site administrator's. Users should not think that a server has done something on their behalf when in fact the task was not accomplished.

    More: http://www.tcl.tk/man/tcl8.4/TclCmd/catch.htm

     

    Miscellaneous commands: break, continue, return, and error

    When inside a looping command, it is sometimes desirable to get the command to stop looping or to stop executing the current iteration but to continue on the next one. The break command is used to permanently escape the loop; the continue command is used to escape the current iteration of the loop but to start again at the next iteration. The syntax for each consists only of the appropriate word written on a line by itself within a loop.

    We often use the break command when we want to limit the number of rows to display from the database. Here's an example from the photo.net neighbor-to-neighbor system. By default, we only want to show a "reasonable" number of postings on one page:

    set selection [ns_db select $db ... big SQL query ... ]

    set list_items ""
    # see what the publisher thinks is a reasonable number (default to 100)
    set n_reasonable [parameter::get -parameter NReasonablePostings -default 100]

    # initialize a counter of the number of rows displayed so far
    set counter 0
    while {[ns_db getrow $db $selection]} {
    set_variables_after_query
    incr counter
    if { $counter > $n_reasonable) } {
    # append ellipses
    append list_items "<p>\n..."
    # flush the database cursor (tell Oracle that we don't need the
    # rest of the rows)
    ns_db flush $db
    # break out of the loop
    break } append list_items "<li><a href=\"view-one.tcl ..." }


    More: http://www.tcl.tk/man/tcl8.4/TclCmd/break.htm

    The return command has been shown before. It quits the proc it's in and returns the supplied value. Remember that any procedure lines after return aren't executed. Too many times we've seen code of the following form:

    proc a_new_programmers_proc {} {
    set db [ns_db gethandle]
    # do a bunch of stuff with the database
    return $result
    # release the database handle
    ns_db releasehandle $db
    }
    The most interesting thing that you can do with return is write procedures that force their callers to return as well. Here's an example from http://software.arsdigita.com/tcl/ad-security.tcl:
    proc ad_maybe_redirect_for_registration {} {
    if { [ad_verify_and_get_user_id] != 0 } {
    # user is in fact logged in, return happiness
    return
    } else {
    ns_returnredirect "/register/index.tcl?return_url=[ns_urlencode [ns_conn url]$url_args]"
    # blow out of 2 levels
    return -code return
    }
    }
    A .tcl page can simply call this in-line
    ad_maybe_redirect_for_registration 

    # the code below will never get executed if the user isn't registered
    # ... update the database or whatever ...



    More: http://www.tcl.tk/man/tcl8.4/TclCmd/return.htm

    The error command returns from a proc and and raises an error that, if not caught by a catch statement, will result in the user seeing a server error page. The first argument to error is displayed in the debugging backtrace:

    proc divide {x y} {
    if {$y == 0} {
    error "Can't divide by zero."
    } else {
    return [expr {$x / $y}]
    }
    }

     

    More: http://www.tcl.tk/man/tcl8.4/TclCmd/error.htm

    ---

    based on Tcl for Web Nerds 

    Basic infrastructure

    Created by Anett Szabo, last modified by Anett Szabo 14 Aug 2007, at 05:12 PM

    2.1. AOLserver


    OpenACS is built atop the mighty AOLserver, the open source, multithreaded HTTP
    server that powers http://www.aol.com. AOLserver provides a rich Tcl API, server-side
    processing of custom tags via AOLserver Dynamic Pages (ADP), database connection
    pooling and a cron-like service for running scheduled code in the background. For more
    about AOLserver, please see the AOLserver home page (http://www.aolserver.com).


    2.2. Templating system

     
    OpenACS divides responsibility for serving most requests among two or three file types
    having distinct purposes. The basic split is between a script file that sets up dynamic
    variables and a markup file that renders them. The script file is a Tcl script having a .tcl
    extension. The markup file has an .adp extension and looks much like standard HTML.
    In fact, HTML is valid ADP, providing a gentle slope for programmers and designers
    who are just getting started with the framework. Because they resemble HTML, ADP
    files may be edited in standard tools such as Dreamweaver.
    The OpenACS templating system supports the ability to work with page fragments via
    the <include> tag. The same Tcl/ADP file pair idiom operates at this level. Hence if a
    page contains a tag
    <include src=”motd”>
    the template system will search the current filesystem directory for motd.tcl to set up the
    dynamic variables and motd.adp for the markup.
    More interestingly, the OpenACS templating system supports a master/slave relationship
    among page fragments. A page fragment that specifies a <master> will have its contents
    embedded into the master template at a location designated by the <slave> tag in that
    template. Master templates are typically used to standardize headers and footers across a
    site. A nice property of the master/slave system is that the master template provides a
    bird’s eye view of the entire page in a single file. And of course, the same Tcl/ADP file
    pair idiom applies to master templates as well.
    Before we get too far down the two-file path it’s worth pointing out a third type of file
    that also participates in serving most requests: the query file. Query files have an .xql
    extension and contain SQL that is specific to the template. The function of these files
    will be explored further in the next section.


    2.3. Database API


    The OpenACS database API is designed to maximize Tcl integration with the database,
    allowing data to pass back and forth as simply and naturally as possible. Let’s dive in
    and consider a code example:

    set title "Late Night With Conan O'Brien"
    db_foreach get_matches {
    select description, tvchannel, when_start, when_stop
    from xmltv_programmes
    where title = :title
    } {
    do_something_with $title $description $tvchannel
    do_something_else_with $when_start $when_stop
    }

    This code block loops through a tv listings database looking for airings of Late Night
    with Conan O’Brien. The db_foreach command runs a loop, just like Tcl’s
    foreach command, but also fetches column variables for each row result, sets them in
    the code block environment and finally executes the code block. Note the appearance of
    :title in the SQL query, which is the second argument to db_foreach. The colon
    (:) syntax serves to pass the value of the Tcl variable title to the database. This
    mechanism automatically handles any quoting that may be necessary (in this case the
    single quote in Conan O’Brien’s last name), an added security feature.
    The first argument to db_foreach, get_matches, is a required query name. While
    the above example specifices a SQL query for compactness, OpenACS scripts typically
    specify their queries by name, leaving the SQL block empty. Query names are used to
    look up SQL statements in special query files having an .xql extension. These sit in the
    same directory next to the .tcl and .adp files, and themselves come in several flavors. For
    the “motd” page fragment, standard SQL statements are places in motd.xql, Postgresspecific
    statements are places in motd-postgresql.xql and Oracle-specific statements are
    placed in motd-oracle.xql. This arrangement provides just enough database abstraction to
    allow reuse of Tcl scripts with multiple RDBMSes, while stopping short of attempting
    much larger and more difficult problems such as object-relational mapping.
    The database API encompasses a number of commands that work similarly, including
    db_string, db_1row, db_list, db_list_of_lists, db_multirow, etc., all
    of them naturally mix with Tcl, dramatically simplifying the development of Webdatabase
    applications.
    The database API functions also accept an optional database name argument, to allow
    applications to connect to multiple databases from the same Tcl script, if necessary.
    More information: http://openacs.org/api-doc/procsearch?
    query_string=db_&search_type=All+matches&name_weight=5&param_weight=3&doc_
    weight=2

     


    2.4. Declarative programming


    Among other Web development frameworks, OpenACS is still unique, powerful and
    simple, and that’s based on the programming advantages created within the OpenACS,
    such as declarative programming, which is understood as the opposite of writing the
    logic of the program using the normal procedural programming, instead use an special
    declarative syntax to reflect how the program will act, based on a possible entry, but not
    relied only on that. A positive effect of writing in a declarative syntax is that the
    programs usually are more robust in its behavior and more readable by other developers,
    since the declarative syntax is standard and always more ordered.


    2.4.1. Form processing, ad_form

    As an example, for web form management, OpenACS has ad_form. This procedure
    implements a high-level, declarative syntax for the generation and handling of HTML
    forms. It includes special syntax for the handling of forms tied to database entries,
    including the automatic generation and handling of primary keys generated from
    sequences. You can declare code blocks to be executed when the form is submitted, new
    data is to be added, or existing data modified.

    ad_form -name form_name -export {foo {bar none}} -form {

    my_table_key:key(my_table_sequence)

    {value:text(textarea) {label "Enter text"}

    {html {rows 4 cols 50}}}

    } -select_query {

    select value from my_table where my_table_key = :my_table_key

    } -validate {

    {value

    {[string length $value] >= 3}

    "\"value\" must be a string containing three or more

    characters"

    }

    } -new_data {

    db_dml do_insert "

    insert into my_table

    (my_table_key, value)

    values

    (:key, :value)"

    } -edit_data {

    db_dml do_update "

    update my_table

    set value = :value

    where my_table_key = :key"

    } -after_submit {

    ad_returnredirect "somewhere"

    ad_script_abort

    }

     


    In this example, ad_form will first check to see if my_table_key was passed to the
    script. If not, the database will be called to generate a new key value from
    my_table_sequence. If defined, the query defined by -select_query will be
    used to fill the form elements with existing data.
    On submission, the validation block checks that the user has entered at least three
    characters into the textarea. If the validation check fails the value element will be
    tagged with the error message, which will be displayed in the form when it is rendered. If
    the validation check returns true, one of the new_data or edit_data code blocks will
    be executed depending on whether or not my_table_key was defined during the initial
    request. my_table_key is passed as a hidden form variable and is signed and verified.
    This example illustrates how to declare a form, define validation and define a set of
    actions to be taken on standard events.
    More information about ad_form can be found here: http://openacs.org/api-doc/procview?
    proc=ad%5fform, from which part of this sub section has been taken.


    2.4.2. List builder

    The list-builder is used create sophisticated table-like reports with many popular
    capabilities, here is an example.


    template::list::create \
    -name packages \
    -multirow packages \
    -elements {
    instance_name {
    label {Service}
    }
    www {
    label "Pages"
    link_url_col url
    link_html { title "Visit service pages" }
    display_template {<if @packages.url@ not nil>Pages</if>}
    }
    admin {
    label "Administration"
    link_url_col admin_url
    link_html { title "Service administration" }
    display_template {<if @packages.admin_url@ not
    nil>Administration</if>}
    }
    sitewide_admin {
    label "Site-Wide Admin"
    link_url_col sitewide_admin_url
    link_html { title "Service administration" }
    display_template {<if @packages.sitewide_admin_url@ not
    nil>Administration</if>}
    hide_p {[ad_decode $swadmin_p 1 0 1]}
    }
    parameters {
    label "Parameters"
    link_url_col param_url
    link_html { title "Service parameters" }
    display_template {<if @packages.param_url@ not
    nil>Parameters</if>}
    }
    }

    (taken from packages/acs-admin/lib/service-parameters.tcl)

     


    For this example you see first the -name of the list-builder. Then the declaration of the
    -multirow name used for populating the report with data, which is usually extracted
    from the database. Then the -elements (columns) to be displayed in the report. Each
    element is defined as a name of the variable that is set at the multirow in the case
    that the variable will be used as data to be displayed in that column, like
    instance_name. Also the element name can be an arbitrary name in the case that
    is used the parameter display_template, which is used to include HTML tags or
    ADP logic when displaying data passed by the multirow. For each element you always
    define label which is the title of the column, and more special parameters such as:
    link_url_col that expect a variable name which must be set at the multirow that
    contains a link for automatically display the data of that column as a link.
    The list builder has many more important features like order by column, filters, bulk
    actions, pagination, etc.
    And this is the result seen in the browser produce by the list-builder example:


    2.4.3. Upgrades

    Since OpenACS is a modular system consisting on independent packages, each package
    has its own version and can require upgrade scripts when updating the package in a given
    OpenACS installation. This is a simple example of a declarative upgrade syntax:


    ad_proc -public mypackage::apm::after_upgrade {
    {-from_version_name:required}
    {-to_version_name:required}
    } {
    apm_upgrade_logic \
    -from_version_name $from_version_name \
    -to_version_name $to_version_name \
    -spec {
    2.0.3 2.1.0 {
    ….upgrade code here….
    }
    2.1.0 2.1.1 {
    …. More upgrade code here….
    }
    }
    }


     

    Configuring an OpenACS package

    Created by Malte Sussdorff, last modified by Malte Sussdorff 07 Aug 2007, at 04:33 PM

    by Jade Rubick

    OpenACS docs are written by the named authors, and may be edited by OpenACS documentation staff.

    After you've installed and mounted your package, you can configure each instance to act as you would like.

    This is done from the Applications page. Log in, go to the Admin or Control Panel, click on the subsite the application is in, and click on Applications. If you click on the 'Parameters' link, you will see a list of parameters that you can change for this application.

    Configure OpenACS look and feel with templates

    Created by Malte Sussdorff, last modified by Malte Sussdorff 07 Aug 2007, at 04:28 PM

    To change the look and feel of OpenACS you will need to dig into the ACS Templating system and change at least some of the given templates and CSS files to suit your needs. Reuven Lerner has written a good Introduction to OpenACS Templates for the Linux Journal.

    Configuring a new OpenACS Site

    Created by Malte Sussdorff, last modified by Malte Sussdorff 07 Aug 2007, at 04:26 PM

    by Joel Aufrecht

    OpenACS docs are written by the named authors, and may be edited by OpenACS documentation staff.

    In this chapter, Configuring refers to making changes to a new OpenACS site through the web interface. In crude terms, these changes happen in the database, and are upgrade-safe. Customizing refers to changes that touch the file system, and require some planning if easy upgradability is to be maintained.

    An introductory article was written be Reuven Lerner for the Linux Journal which is a good read.  

    Next Page