MacLochlainns Weblog

Oracle™ spoils us for other databases. We begin to expect too much of other database products. At least, that’s the way that I felt while working comparative syntax samples out for my students. I assumed wrongly that all strings would compare based on case sensitive strings. I found that Oracle does that, but MySQL and SQL Server don’t do that with an ordinary = (equals) comparison operator.

Oracle

The = (equals) symbol compares case sensitive strings. The following query returns nothing because a title case string isn’t equal to an uppercase string (databases, as they should be).

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE 'String' = 'STRING';

The query only resolves when the strings are case sensitive matches, like this:

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE 'String' = 'String';

MySQL

The = (equals) symbol doesn’t compare case sensitive strings. The following query returns Truth (a case sensitive irony) because a title case string is equal to an uppercase string when you make the comparison with an equality symbol. You may also chuckle that you need the DUAL pseudo table to make this work when there’s a WHERE clause, covered in yesterday’s blog.

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE 'String' = 'STRING';

You can make it case sensitive by using the strcmp function, like this:

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE strcmp('String','STRING');

You can also make it case sensitive by using collation, which is the ability to shift it’s character set. Unfortunately, MySQL doesn’t support casting to a binary string, so you must apply a different character set for the equality comparision.

SQL> SELECT 'Truth' AS OUTPUT
  2> FROM dual WHERE 'String' COLLATE latin1_bin = 'STRING' COLLATE latin1_bin;

SQL Server

The = (equals) symbol doesn’t compare case sensitive strings. The following query returns Truth (another case sensitive irony) because a title case string is equal to an uppercase string when you make the comparison with an equality symbol.

1> SELECT 'Truth' AS Output
2> WHERE  CAST('String' AS VARBINARY) = CAST('STRING' AS VARBINARY);
3> GO

You can make SQL Server resolve case sensitive strings by casting both of the strings to VARBINARY data types, like this:

1> SELECT 'Truth' AS Output
2> WHERE  CAST('String' AS VARBINARY) = CAST('String' AS VARBINARY);
3> GO

There is a clear lack of portability for basic comparison operations. I think its time that folks drop that time worn database agnosticism line because all it means is I don’t want to use that other database. I’ve heard the line too often with regard to PL/SQL, but oddly not about MySQL’s stored procedures or Microsoft’s T-SQL. Maybe I’m an Oracle bigot but it sure seems more ANSI standard like to me to compare strings with a simple = (equals) operator.

A couple students stumbled on the MySQL on-line documentation today. Specifically, they didn’t find instructions on how a non-root user could change their password. I’ve also added this as an explanation on the MySQL documentation for 12.5.1.6. SET PASSWORD Syntax page.

You reset your own password when you’re not the root user with this syntax:

mysql> SET PASSWORD = PASSWORD('new_password');

This is simple syntax but carries a risk if you walk away from your terminal because somebody can run it without needing to know the original user password. Oracle requires that you know the original password if you use this syntax:

SQL> PASSWORD
Changing password FOR PLSQL
OLD password:
NEW password:
Retype NEW password:
Password changed

If you think that you’re safe walking away from an Oracle SQL*Plus console, check this because it doesn’t require knowing the current password either.

SQL> SHOW USER
USER IS "PLSQL"
SQL> ALTER USER plsql IDENTIFIED BY plsql;
USER altered.

Don’t walk away, always lock your terminal or use the QUIT command to exit MySQL. This generally prevents somebody hijacking your password. You can check this post if you’re interested in changing the root password when you don’t have it to begin with. Hope this helps a few folks.

The longest outstanding question I’ve received is on how you can aggregate the number of rows in a nested table (one inside another). If you need a refresher on joining non-collection columns to nested columns, you should check this earlier blog post.

The basic premise is boils down to two points. One are you trying to get a count of the elements in the nested collection, or a count of the elements in the nested collection based on one or more columns in the nested table. There are two ways to perform a basic count of the nested line numbers, and one way to perform the other. You find out how to perform both in the post.

1. You can run the following script to create a sample data environment. It is re-runnable, and designed to run on either Oracle Database 10g or 11g.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

-- Conditionally delete objects in reverse dependency order.
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_type IN ('TABLE','SEQUENCE','TYPE')
            AND      object_name IN ('TELEPHONE_TYPE','TELEPHONE_TABLE'
                                    ,'CONTACT_BOOK','CONTACT_BOOK_S1')
            ORDER BY object_type, object_name DESC) LOOP
    EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
  END LOOP;
END;
/
 
-- Create a composite data type (structure) and collection.
CREATE TYPE telephone_type AS OBJECT
( country_code   VARCHAR2(3)
, prefix_code    VARCHAR2(5)
, phone_number   VARCHAR2(9));
/
 
CREATE TYPE telephone_table AS TABLE OF telephone_type;
/
 
-- Create a table with a nested composite table and sequence.
CREATE TABLE contact_book
( contact_book_id   NUMBER
, first_name        VARCHAR2(30)
, last_name         VARCHAR2(30)
, telephone_numbers TELEPHONE_TABLE )
NESTED TABLE telephone_numbers STORE AS telephone_struct;
 
CREATE SEQUENCE contact_book_s1;
 
-- Insert two rows of data.
INSERT INTO contact_book
VALUES
( contact_book_s1.nextval
,'Harry','Potter'
,telephone_table(telephone_type('44','20','7299 2127')
                ,telephone_type('44','17','8926 8115')
                ,telephone_type('44','18','3344 5566')
                ,telephone_type('44','72','9878 5436')));
 
INSERT INTO contact_book
VALUES
( contact_book_s1.nextval
,'Weasley','Ronald'
,telephone_table(telephone_type('40','021','407 47 46')
                ,telephone_type('44','19','4088 1062')
                ,telephone_type('44','21','4075 5066')));

2. You can query the line numbers of the nested table by the scalar columns two ways. The first uses the CARDINALITY function introduced in Oracle 10g. The second leverages a CROSS JOIN and traditional aggregation tools.

2(a). Using the CARDINALITY function is generally the simplest and best approach to this problem:

1
2
3
4

SELECT first_name
,      last_name
,      CARDINALITY(telephone_numbers) list
FROM   contact_book;

This yield the following data set:

FIRST_NAME   LAST_NAME      LIST
------------ ------------ ------
Harry        Potter            4
Weasley      Ronald            3

2(b). Using the traditional CROSS JOIN and GROUP BY clause:

1
2
3
4
5
6
7

SELECT   cj.first_name
,        cj.last_name
,        COUNT(*)
FROM    (SELECT *
         FROM contact_book cb CROSS JOIN TABLE(cb.telephone_numbers)) cj
GROUP BY cj.first_name
,        cj.last_name;

This yield the following data set:

FIRST_NAME   LAST_NAME      LIST
------------ ------------ ------
Harry        Potter            4
Ronald       Weasley           3

3. If you wanted to know how many numbers where in the nested table by country code, you need to solve the problem through a CROSS JOIN and GROUP BY clause, like this:

1
2
3
4
5
6
7
8
9

SELECT   cj.first_name
,        cj.last_name
,        cj.country_code
,        COUNT(*)
FROM    (SELECT *
         FROM contact_book cb CROSS JOIN TABLE(cb.telephone_numbers)) cj
GROUP BY cj.first_name
,        cj.last_name
,        cj.country_code;

This yield the following data set:

FIRST_NAME   LAST_NAME    COUNTRY_CODE   LIST
------------ ------------ ------------ ------
Harry        Potter       44                4
Ronald       Weasley      44                2
Ronald       Weasley      40                1

If I captured the basics of the question, great. If there are more questions, please let me know.

I’m back from vacation and will start blogging again. I’ve got a couple items folks have asked me to cover and I’ll try to work those in during the next couple of weeks.

My son went over to Beijing to study Chinese this summer. I picked him up at the Airport last Friday. When he was there he found a copy of my Oracle Database 11g PL/SQL Programming in Chinese. He told me about it via Skype. I asked him to bring home a copy since my royalty statements don’t show the book was translated into Chinese. It cost about $12 US (click on the image to see a larger copy). The statements indicate it has only been translated into Russian, Portuguese, and Tata’s Indian Press at this point.

I thought it was an illegal copy, even though it was sold in a mainline store in Beijing as an original. Anyway I sent the scans of the book and ISBN to McGraw-Hill so they could sort it out. They told me it’s a legal copy, translation licensed, and that not all translations fees post to royalty statements before the translation. It’s only the fifth book, and I continue to learn about the process. Tsinghua University Press produces it. If you want a copy in Chinese, you’ll find it at their web site and my book is here. My son said they had an impressive collection of Oracle Press books on sale there too.

Somebody asked me to post some examples of Large Object syntax. I checked Chapter 9 of the Oracle Database SecureFiles and Large Object Developer’s Guide, and I agree an example or two would be helpful. I’ve provided CREATE and ALTER statements.

Here’s a sample CREATE TABLE statement for a CLOB, BLOB, and BFILE:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

CREATE TABLE item
( item_id             NUMBER        CONSTRAINT pk_item    PRIMARY KEY
, item_barcode        VARCHAR2(20)  CONSTRAINT nn_item_1  NOT NULL
, item_type           NUMBER        CONSTRAINT nn_item_2  NOT NULL
, item_title          VARCHAR2(60)  CONSTRAINT nn_item_3  NOT NULL
, item_subtitle       VARCHAR2(60) 
, item_desc           CLOB          CONSTRAINT nn_item_4  NOT NULL
, item_blob           BLOB
, item_photo          BFILE
, item_rating         VARCHAR2(8)   CONSTRAINT nn_item_5  NOT NULL
, item_rating_agency  VARCHAR2(4)   CONSTRAINT nn_item_6  NOT NULL
, item_release_date   DATE          CONSTRAINT nn_item_7  NOT NULL
, created_by          NUMBER        CONSTRAINT nn_item_8  NOT NULL
, creation_date       DATE          CONSTRAINT nn_item_9  NOT NULL
, last_updated_by     NUMBER        CONSTRAINT nn_item_10 NOT NULL
, last_update_date    DATE          CONSTRAINT nn_item_11 NOT NULL
, CONSTRAINT fk_item_1                      FOREIGN KEY(item_type)
  REFERENCES common_lookup(common_lookup_id)
, CONSTRAINT fk_item_2                      FOREIGN KEY(created_by)
  REFERENCES system_user(system_user_id)
, CONSTRAINT fk_item_3                      FOREIGN KEY(last_updated_by)
  REFERENCES system_user(system_user_id))
  LOB (item_desc) STORE AS BASICFILE item_desc
  (TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
   PCTVERSION 10 NOCACHE LOGGING
   STORAGE (INITIAL 1048576
            NEXT 1048576
            MINEXTENTS 1
            MAXEXTENTS 2147483645))  
, LOB (item_blob) STORE AS item_blob
  (TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
   PCTVERSION 10 NOCACHE LOGGING
   STORAGE (INITIAL 1048576
            NEXT 1048576
            MINEXTENTS 1
            MAXEXTENTS 2147483645));

If you attempt to make the CHUNK size greater than 32,768, it will raise an ORA-22851: invalid CHUNK LOB storage option value error.

Here’s a sample ALTER TABLE statement that adds a new large object column to a table. You can’t modify an existing large object column. You raise an ORA-22859: invalid modification of columns error when you try it. If you need to make modifications, check this post form last year on re-organizing LOB indexes.

1
2
3
4
5
6
7
8

ALTER TABLE item ADD (another CLOB)
LOB (another) STORE AS BASICFILE item_clob
(TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
 PCTVERSION 10 NOCACHE LOGGING
 STORAGE (INITIAL 1048576
          NEXT 1048576
          MINEXTENTS 1
          MAXEXTENTS 2147483645));

This all makes sense, but hold on there’s a potential problem when you add a large object column to a table. I’m not sure it’s a bug but I’ll probably open a TAR on it later in the week (arghhhh! I once worked in Oracle Support. :-)). Anyway, here’s what I found:

If you drop and recreate the table, you can encounter an ORA-01430 error. It appears that the large object is really hanging out in the catalog. As soon as you try to re-add it, you get the error.

If you describe the table after recreating it, you’ll see the following:

SQL> DESCRIBE item
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 ITEM_ID                                   NOT NULL NUMBER
 ITEM_BARCODE                              NOT NULL VARCHAR2(20)
 ITEM_TYPE                                 NOT NULL NUMBER
 ITEM_TITLE                                NOT NULL VARCHAR2(60)
 ITEM_SUBTITLE                                      VARCHAR2(60)
 ITEM_DESC                                 NOT NULL CLOB
 ITEM_BLOB                                          BLOB
 ITEM_PHOTO                                         BINARY FILE LOB
 ITEM_RATING                               NOT NULL VARCHAR2(8)
 ITEM_RATING_AGENCY                        NOT NULL VARCHAR2(4)
 ITEM_RELEASE_DATE                         NOT NULL DATE
 CREATED_BY                                NOT NULL NUMBER
 CREATION_DATE                             NOT NULL DATE
 LAST_UPDATED_BY                           NOT NULL NUMBER
 LAST_UPDATE_DATE                          NOT NULL DATE

Also, if you run the following query with the DBMS_METADATA package, the column doesn’t exist in the table defintion:

SELECT dbms_metadata.get_ddl('TABLE','ITEM') FROM dual;

If try to run the ALTER statement to add the column that doesn’t appear to exist, you’ll get the following message in Oracle 11.1.0.7.0:

ALTER TABLE item ADD (another CLOB)
                      *
ERROR at line 1:
ORA-01430: COLUMN being added already EXISTS IN TABLE

If you rerun the DBMS_METADATA.GET_DDL query, it’ll be there in the table definition. Also, in its non-existence but existent state (ghost-state), there’s nothing in the DBA_LOB, or DBA_SEGMENT views. You can find a query to check large object segments and indexes here. My questions is where has it gone, why is it there, and did I do something wrong because this looks like basic functionality.

If you know the mystery or the existence of a bug, please post a comment.

I was playing around with some external table filtering examples, and I stumbled on a restriction that I’d previously missed. You can’t filter external data with SQL functions, like REGEXP_LIKE. Unfortunately, the limitation applies to equality and non-equality validation in combination with the AND and OR operators.

CREATE TABLE item_load
( item_title    VARCHAR2(60)
, item_subtitle VARCHAR2(60)
, release_date  DATE)
  ORGANIZATION EXTERNAL
  ( TYPE oracle_loader
    DEFAULT DIRECTORY upload_source
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      BADFILE     'UPLOAD_LOG':'item_load.bad'
      DISCARDFILE 'UPLOAD_LOG':'item_load.dis'
      LOGFILE     'UPLOAD_LOG':'item_load.log'
      LOAD WHEN (REGEXP_LIKE(item_title,'^Harry.'))
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL )
      LOCATION ('item_load.csv'))
REJECT LIMIT UNLIMITED;

It threw the following exception, which includes a new error message (at least for me), the KUP-01005. I suppose that basically means you can’t use function calls inside external table access parameters but I couldn’t find it in the documentation. Chapter 13 (the lucky number) in the Oracle Database Utilities 11g manual only provides examples of equality and non-equality.

Here’s the raise exception for those using external tables:

SQL> SELECT * FROM item_load;
SELECT * FROM item_load
              *
ERROR at line 1:
ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-29400: DATA cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "(": expecting one OF: "equal, notequal"
KUP-01007: at line 5 COLUMN 29

I couldn’t leave it alone, I tried the LIKE operator and a jackpot – another new error message:

KUP-01008: the bad identifier was: LIKE

It makes sense, it’s the 13th of July and two days before the new Harry Potter flick, therefore it must be Chapter 13 was calling to me. Hope this helps a few folks.

The fact that you could raise an ugly error when you query an external table always bothered me. I looked at Java stored procedures as the best solution initially. That was overkill. This afternoon, while writing about them for the new PL/SQL Workboook, it became clear. The fix is really easy.

If you know little to nothing about external tables, you can go read this earlier post. Likewise, if you don’t know about objects and object collection, you can refer to this post. Having provided you with the context, here’s an example that eliminates errors when querying an external table without an external file.

1. Create an external file, like this character table.

CREATE TABLE CHARACTER
( character_id NUMBER
, first_name VARCHAR2(20)
, last_name VARCHAR2(20))
  ORGANIZATION EXTERNAL
  ( TYPE oracle_loader
    DEFAULT DIRECTORY download
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      BADFILE     'DOWNLOAD':'character.bad'
      DISCARDFILE 'DOWNLOAD':'character.dis'
      LOGFILE     'DOWNLOAD':'character.log'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL )
    LOCATION ('character.csv'))
REJECT LIMIT UNLIMITED;

2. Create a user-defined object type that mirrors your external table defintion, like this:

CREATE OR REPLACE TYPE character_obj IS OBJECT
( character_id NUMBER
, first_name VARCHAR2(20)
, last_name VARCHAR2(20));
/

3. Create a user-defined collection of your object type, like

CREATE OR REPLACE TYPE character_obj_table IS TABLE OF character_obj;
/

4. Create a function that returns the user-defined collection of your object type, like

CREATE OR REPLACE FUNCTION character_source
RETURN character_obj_table IS
  c          NUMBER;
  collection CHARACTER_OBJ_TABLE := character_obj_table();
BEGIN
  FOR i IN (SELECT * FROM CHARACTER) LOOP
    collection.EXTEND;
    collection(c) := character_obj( i.character_id
                                  , i.first_name
                                  , i.last_name);
    c := c + 1;
  END LOOP;
  RETURN collection;
EXCEPTION
  WHEN OTHERS THEN
    RETURN collection;
END;
/

5. Query the function not the table, which returns no rows found when the file doesn’t physically exist, or the file contains no data. Lastly, the function returns the data when it is there.

SELECT * FROM TABLE(character_source);

Hope this helps those using external tables to avoid the typical error stack:

SELECT * FROM CHARACTER
*
ERROR at line 1:
ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-29400: DATA cartridge error
KUP-04040: file CHARACTER.csv IN CHARACTER NOT found

I also wrote this older post about confirming it in the database catalog. If you any follow-up suggestions, please let me know.

I just wanted to see how Zend Server Community Edition might be broken. Performing a full feature install on Windows x64, I confirmed that Zend Server’s Java Bridge depends on the 32-bit JRE (common sense prevails). Installing it against the JRE 64-bit jvm.dll did raised an exception but none of the instructions address the problem.

It’s one of those simplifying assumptions – everybody knows 32-bit software works with 32-bit software. Anybody running on Windows XP x64 should know that they may need a JDK 64-bit and both a JRE 64-bit and JRE 32-bit for some applications. For those who don’t know this, like my students and other newbies, when you run Windows XP the 64-bit stuff goes in the C:\Program Files directory and the 32-bit stuff goes in the C:\Program Files (x86) directory. This lets you develop 32-bit or 64-bit Java applications on the same 64-bit machine.

Another tidbit of interest, don’t choose a full install if you’ve already installed MySQL. The Zend Community Server isn’t smart enough to alter the configuration to another port, and their my.ini points to a 3306 listener port. This causes the MySQL_ZendServer51 service to fail. It also doesn’t uninstall well. If you don’t want to clean the Windows Registry, don’t choose to install a second MySQL.

As an FYI, the Zend installation of MySQL doesn’t put a password on the root account. Don’t forget to add one after the install if you go down the full product road. This has the Zend Server Community Edition installation instructions.

I saw a post in the OTN forum that asked a simple question and had no simple example as an answer, so I thought it would be nice to provide one. Basically, somebody wanted to know how to call into a stored procedure and return a value with more or less one pass-by-value and another pass-by-reference variable.

This defines a simple echo procedure, which takes a message and returns a formatted message:

1
2
3
4
5
6
7

CREATE OR REPLACE PROCEDURE echo
( message IN     VARCHAR2
, reply      OUT VARCHAR2 ) IS
BEGIN
  reply := 'Message ['||message||'] received.';
END;
/

The following PHP calls the procedure and returns the value. It uses the required connection syntax for the Zend Server Community Edition.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "localhost/xe");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    // Initialize incoming message whether or not parameter sent.
    $msg_in = (isset($_GET['msg'])) ? $_GET['msg'] : "Cat got your keyboard?";
 
    // Set the call statement, like a SQL statement.
    $sql = "BEGIN echo(:a,:b); END;";
 
    // Prepare the statement and bind the two strings.
    $stmt = oci_parse($c,$sql);
 
    // Bind local variables into PHP statement, you need to size OUT only variables.
    oci_bind_by_name($stmt, ":a", $msg_in);
    oci_bind_by_name($stmt, ":b", $msg_out, 80, SQLT_CHR);
 
    // Execute it and print success or failure message.
    if (oci_execute($stmt)) {
      print $msg_out;
    }
    else {
      print "Sorry, I can't do that Dave...";
    }
    // Free resources.
    oci_free_statement($stmt);
    oci_close($c);
  }
?>

You can then test it with or without a parameter, like this example with a parameter:

http://localhost/message_reply.php?msg="Sample message"

I put a link in the forum to this, and I hope it helps a few folks.

I got all the screen shots out last night, then I realized that the configuration instructions were missing. They’ve been added now, and you can check if you’re interested.

In the process, I noticed that Zend Server Community Edition is using connection pooling by default with Oracle. This means that the TNS alias must be qualified by a hostname.

For example, on your local PC with Oracle Database 10g Express Edition, the default TNS alias is XE. You can’t use that anymore when you’re running the Zend Server. You must qualify it as localhost/XE or hostname/XE as the connection string. A natural alternative is to insert a fully qualified TNS network map

Here are three possible connection patterns:

1
2
3
4
5
6
7
8
9
10
11
12

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "localhost/xe");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    print "Congrats! You've connected to an Oracle database!";
    oci_close($c);
  }
?>

02-JUL-2009 23:32:11 * (CONNECT_DATA=(SERVICE_NAME=xe)(CID=(PROGRAM=C:\Program?Files\Zend\ZendServer\bin\php-cgi.exe)(HOST=MCLAUGHLINXP32)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1405)) * establish * xe * 0

1
2
3
4
5
6
7
8
9
10
11
12

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "hostname/xe");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    print "Congrats! You've connected to an Oracle database!";
    oci_close($c);
  }
?>

02-JUL-2009 23:29:16 * (CONNECT_DATA=(SERVICE_NAME=xe)(CID=(PROGRAM=C:\Program?Files\Zend\ZendServer\bin\php-cgi.exe)(HOST=MCLAUGHLINXP32)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.153.138)(PORT=1403)) * establish * xe * 0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

<?php
  $tns = "(DESCRIPTION =
              (ADDRESS=(PROTOCOL = TCP)(HOST = mclaughlinxp32)(PORT = 1521))
              (CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = XE)))";
 
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", $tns);
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    print "Congrats! You've connected to an Oracle database!";
    oci_close($c);
  }
?>

02-JUL-2009 23:27:10 * (CONNECT_DATA=(SERVICE_NAME=XE)(CID=(PROGRAM=C:\Program?Files\Zend\ZendServer\bin\php-cgi.exe)(HOST=MCLAUGHLINXP32)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.153.138)(PORT=1401)) * establish * XE * 0

I’m sure it was in the documents but then again, I didn’t read them. 😉