Oracle Db

1
Oracle Db

• Using SQLPlus to Interact with Oracle
• User student accounts S12344567 (Sstudentno)
• Password student
• setenv ORACLE_HOME /usr/local/oracle8i/product/8.1
  .6
• setenv LD_LIBRARY_PATH /usr/libORACLE_HOME/lib
• set path(path ORACLE_HOME/bin)
• Connection to cse2030 DB
• sqlplus S1234567/student_at_cse2030
• SQLgt (prompt)

2
SQLPlus

• CREATE TABLE Emp(EmpID NUMBER, Name
  VARCHAR2(30), PRIMARY KEY (EmpID))
• INSERT INTO Emp VALUES(1, 'Alan Turing')
• Another way is to use substitution variables.
  INSERT INTO Emp VALUES(id, 'name')
• You will be prompted to enter a value for id,
  type in 2 and hit enter. Next you will be
  prompted to enter a value for name, type in
  Samual Morse and hit enter

3
SQLplus cont.

• To repeat the command without re-typing it, type
  the slash character ( / ) and then hit enter.
• Your tables should have a primary key and Foreign
  key for referential integrity in one-to-many
  relationships. EG Authors Book
• Select from emp
• To retrieve all data

4
Primary keys by Ashley Cambrell

• CREATE TABLE test1(testid NUMBER CONSTRAINT
  pk_test1_testid PRIMARY KEY, testtext
  VARCHAR(255))
• CREATE SEQUENCE testid_seq INCREMENT BY 1
• SELECT testid_seq.nextval FROM DUAL
• NEXTVAL
• ------- 1
• 1 row selected (0.00 seconds)
• SELECT testid_seq.currval FROM DUAL
• CURRVAL
• -------
• 1
• 1 row selected (0.00 seconds)

5
Primary Keys cont.

• CREATE TABLE test1(testid NUMBER CONSTRAINT
  pk_test1_testid PRIMARY KEY, testtext
  VARCHAR(255))
• INSERT INTO test1(testid, testtext)
  VALUES(testid_seq.nextval, 'The lazy dog jumped
  over the brown fox')
• SELECT FROM test1
• TESTID TESTTEXT
• ------ --------------------------------------
• 2 The lazy dog jumped over the brown fox
• 1 row selected (1.00 second)

6
Primary Keys cont.

• INSERT INTO test1(testid, testtext)
  VALUES(testid_seq.nextval, 'The lazy fox jumped
  over the purple dog')
• SELECT FROM test1
• TESTID TESTTEXT
• ------ ---------------------------------------
• 2 The lazy dog jumped over the brown fox
• 3 The lazy fox jumped over the purple dog
• 2 rows selected (0.00 seconds)
• Do a Commit before logout or data will be lost
  !!!!

7
Oracle Call Interface-OCI

• OCIPLogon -- Connect to an Oracle database
• OCILogOff -- Disconnects from Oracle
• OCIParse -- Parse a query and return a statement
• OCIExecute -- Execute a statement
• OCIError -- Return the last error
• OCIFetchInto -- Fetches the next row into
  varresult
• OCIFetch -- Fetches the next row into
  result-buffer
• OCIResult -- Returns column value for fetched row
• OCICommitt Save transaction data to database
• http//www.php.net/manual/en/ref.oci8.php

8
Sample using OCICommitt
lt?php   // Login to Oracle server   conn
OCILogon('scott', 'tiger')   // Parse SQL  
stmt OCIParse(conn, "INSERT INTO employees
(name, surname) VALUES ('Maxim', 'Maletsky')") 
// Execute statement   OCIExecute(stmt)   //
Commit transaction   committed
OCICommit(conn)   // Test whether commit was
successful. If error occurred, return error
message   if (!committed)        error
OCIError(conn)       echo 'Commit failed.
Oracle reports ' . error'message'     
// Close connection   OCILogoff(conn)?gt
9
OCIPLogon

• OCIPLogon -- Connect to an Oracle database and
  log on using a persistent connection. Returns a
  new session.
• int OCIPLogon ( string username, string password
  , string db)
• CIPLogon() creates a persistent connection to an
  Oracle 8 database and logs on. The optional third
  parameter can either contain the name of the
  local Oracle instance or the name of the entry in
  tnsnames.ora to which you want to connect. If the
  optional third parameter is not specified, PHP
  uses the environment variables ORACLE_SID
  (Oracle instance) or TWO_TASK (tnsnames.ora) to
  determine which database to connect to.

10
OCILogOff

• OCILogOff -- Disconnects from Oracle
• int OCILogOff ( int connection)
• PHP oracle function documentation at
  http//www.php.net/manual/en/function.ociplogon.ph
  p

11
OCIParse

• OCIParse -- Parse a query and return a statement
• int OCIParse ( int conn, string query)
• OCIParse() parses the query using conn. It
  returns the statement identity if the query is
  valid, FALSE if not. The query can be any valid
  SQL statement or PL/SQL block.
• Don't forget that the queries on PHP DON'T use
  the semicolon at the end of the SQL
  command.Putting a semicolon at the end will give
  an "Invalid Character" error.

12
OCIExecute

• OCIExecute -- Execute a statement
• int OCIExecute ( int statement , int mode)
• OCIExecute() executes a previously parsed
  statement. (see OCIParse()). The optional mode
  allows you to specify the execution-mode (default
  is OCI_COMMIT_ON_SUCCESS). If you don't want
  statements to be committed automatically specify
  OCI_DEFAULT as your mode.
• Returns TRUE on success, FALSE on failure.

13
OCIError

• OCIError -- Return the last error of
  stmtconnglobal. If no error happened returns
  FALSE.
• array OCIError ( int stmtconnglobal)
• OCIError() returns the last error found. If the
  optional stmtconnglobal is not provided, the
  last error encountered is returned. If no error
  is found, OCIError() returns FALSE. OCIError()
  returns the error as an associative array. In
  this array, code consists the oracle error code
  and message the oracle errorstring.

14
OCIError eg.

• c ocilogon("u","p")
• if (! c) var_dump(ocierror()) // we have no
  connection yet
• 
  // so the error is stored global. s
  ociparse(c,"select from tubu")
• if (! s) var_dump(ocierror(c)) // parse failed
  - error is
• 
  // stored in connection handle
• err ociexecute(s)
• if ( !err) var_dump(ocierror(s)) // error code
  for ociexecute()
  // is stored in the statement handle

15
OCIFetchInto

• OCIFetchInto -- Fetches the next row into
  result-array (can be generic without using
  column names)
• int OCIFetchInto ( int stmt, array result ,
  int mode)
• OCIFetchInto() fetches the next row (for SELECT
  statements) into the result array.
  OCIFetchInto() will overwrite the previous
  content of result. By default result will contain
  a zero-based array of all columns that are not
  NULL. The mode parameter allows you to change the
  default behaviour.

16
OCIFetch

• OCIFetch -- Fetches the next row into
  result-buffer
• int OCIFetch ( int statement)
• OCIFetch() fetches the next row (for SELECT
  statements) into the internal
  result-buffer.
• Used with OCIResult() to return individual
  columns.

17
OCIResult

• OCIResult -- Returns column value for fetched row
• mixed OCIResult ( int statement, mixed column)
• OCIResult() will return everything as strings
  except for abstract types (ROWIDs, LOBs and
  FILEs).
• NOTE OCIResult() requires the column name to be
  written in capitals, so
  OCIResult(stmt,"column") won't work, but
  OCIResult(stmt,"COLUMN")
  will.

18
PHP / Oracle Example

• ltHTMLgt ltHEADgt
• ltTITLEgtUsing PHP to Read a Table in Oracle
  lt/TITLEgt lt/HEADgt
• ltBODY BGCOLOR"FFFFFF"gt
  
• ltCENTERgtltBgtAuthorslt/Bgt ltBRgtltBRgt
• lt?php
• DB_USERNAME'jceddia'
• DB_PASSWORD'jpasswd'
• DB_DATABASE'cse2030'
• connection ocilogon(DB_USERNAME,
  DB_PASSWORD, DB_DATABASE)
• stmt OCIParse(connection, "SELECT FROM
  author")
• err OCIExecute(stmt)
  

19
PHP / Oracle Example cont.

• // Start of table and column headings (ID and
  Name)
• print "ltTABLE CELLSPACING\"0\"
  CELLPADDING\"3\" BORDER\"1\"gt\n"
• print " ltTRgtltTHgtAIDlt/THgtltTHgtFirstnamelt/THgtltTHgtSu
  rnamelt/THgtlt/TRgt\n"
  
  
• // Loop through results
  
• while(OCIFetch(stmt))
  
• 
  
• print " ltTRgt\n"
  
• print " ltTDgt" . OCIResult(stmt, "AID")
  . "lt/TDgt\n"
• print " ltTDgt" . OCIResult(stmt,
  "FNAME") . "lt/TDgt\n"
• print " ltTDgt" . OCIResult(stmt,
  "SNAME") . "lt/TDgt\n"
• print " lt/TRgt\n"
  
• 
  
• print "lt/TABLEgt\n"
• OCIFreeStatement(stmt)
  
• OCILogoff(connection)
  
• ?gt

20
Putting it all together

• It refers to HTML forms, php and Oracle
• Two scenarios
• a. Input scenario
• form input is to be added/deleted/lookup/etc to
  database eg. User login, add customer details etc
• b. Output scenario
• database contents is to be displayed on the form
  eg. Once user logs in, display a welcome message
  with their name from the database or display a
  product list in the selection box etc

21
Input Scenario

• Define your HTML form to capture the data you
  want (to be stored in the db).
• Design your php script to be called from the
  form submit to do initial input validation (eg.
  Name not blank). If input OK, connect to database
  and insert/amend data.
• EG. See p-6-2.html for the form (calls)
• p-6-2.php This would validate data as per the lab
  plus connect to db and issue an insert statement
  using the sequence for the primary key and the
  form variables as the insert VALUES

22
Input Scenario eg.

• Form has two variables name and occupation
• Database has table occupation with attributes
  PersonId,Oname, Ooccupation and personseq as the
  sequence to use for PKs
• Insert statement (after connection)
• sqlstr INSERT INTO occupation(Personid,
  Oname, Ooccupation) VALUES(personseq.nextval, .
  name. ,.occupation. )
• stmt OCIParse(connection, sqlstr)
  
• err OCIExecute(stmt)
• if ( !err) var_dump(ocierror(stmt))

23
Output Scenario

• Earlier example showed listing the table contents
  as a HTML table.
• If you want to display data in a drop down list
  box then change the HTML
• echo ltselect name"cars"gt
• while(OCIFetch(stmt))
  
• 
  
• echo " ltoption gt. OCIResult(stmt,
  CARMAKE") .\n"
  
• 
  
• echo "lt/selectgt\n"
• For a textfield, the VALUE would be set to
  OCIResult() etc

24
Design Considerations

• Database should be specified first according to
  functionality
• HTML forms designed to fulfil part of
  functionality
• Navigation flow of forms
• Graphic content/CSS added