MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle Stored Procedure

with 5 comments

Somebody felt that I’d short changed Oracle by providing only an example for calling a stored procedure in MySQL. So, here’s an equivalent post to the MySQL sample that works in an Oracle database with PHP.

The largest difference between the two approaches is that Oracle is transactional by default while MySQL isn’t. However, the use of savepoints and rollbacks are shown in the procedure, which are the appropriate transaction controls in Oracle.

Here are the detailed steps, even though there are other blog entries with information on related subjects.

1. Sign on as the system user, and create a new user. Users in Oracle have their own schema or work area, and they don’t require a database like MySQL or SQL Server.

SQL> CREATE USER student IDENTIFIED BY student;
SQL> GRANT CONNECT, resource, CREATE any VIEW TO student;

2. Create a create_oracle_procedure.sql file with the following contents:

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
52
53
54
55
56
57
58
59
60
61
62
-- Conditionally drop the objects to make this rerunnable.
BEGIN
  FOR i IN (SELECT table_name
            FROM   user_tables
            WHERE  table_name IN ('A','B')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN ('A_SEQ','B_SEQ')) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/  
 
-- Create the tables and sequences.
CREATE TABLE a
( a_id   NUMBER CONSTRAINT a_pk PRIMARY KEY
, a_text VARCHAR2(12));
 
CREATE SEQUENCE a_seq;
 
CREATE TABLE b
( b_id   NUMBER CONSTRAINT b_pk PRIMARY KEY
, a_id   NUMBER 
, b_text CHAR(12)
, CONSTRAINT fk_a FOREIGN KEY(a_id) REFERENCES a (a_id));
 
CREATE SEQUENCE b_seq;
 
-- Create a stored procedure with IN-only (default) mode parameters.
CREATE OR REPLACE PROCEDURE double_insert
( input_a VARCHAR2, input_b VARCHAR2) IS
BEGIN
  SAVEPOINT starting_point;
  INSERT INTO a VALUES (a_seq.NEXTVAL, input_a);
  INSERT INTO b VALUES (b_seq.NEXTVAL, a_seq.CURRVAL, input_b);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO starting_point;
    RETURN;
END;
/
 
-- Define a couple local session variables.
VARIABLE text1 VARCHAR2(12)
VARIABLE text2 VARCHAR2(12)
 
-- Assign values to session variables.
BEGIN
  :text1 := 'This is one.';
  :text2 := 'This is two.';
END;
/
 
-- Call the local procedure.
EXECUTE double_insert(:text1,:text2);
 
-- Select the data set. 
SELECT * FROM a;
SELECT * FROM b;

3. Quit the session as the system user. You can simply reconnect to the new schema like this:

SQL> CONNECT student/student@orcl

4. Run the file from the relative directory where you started the sqlplus executable.

SQL> @create_oracle_procedure.SQL

You see the following query results at the end of the script:

      A_ID A_TEXT
---------- ------------
         1 This IS one.
 
 
      B_ID       A_ID B_TEXT
---------- ---------- ------------
         1          1 This IS two.

5. Write the following into a call_oracle_procedure.php:

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "orcl");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    // Declare two variables for the test procedure call.
    $val1 = "Hello Hal!";
    $val2 = "Hello Dave!";
 
    // Set the call statement, like a SQL statement.
    $sql = "BEGIN double_insert(:a,:b); END;";
 
    // Prepare the statement and bind the two strings.
    $stmt = oci_parse($c,$sql);
 
    // Bind local variables into PHP statement.
    oci_bind_by_name($stmt, ":a", $val1);
    oci_bind_by_name($stmt, ":b", $val2);
 
    // Execute it and print success or failure message.
    if (oci_execute($stmt)) {
      print "Congrats! You've executed a Oracle stored procedure from PHP!";
    }
    else {
      print "Sorry, I can't do that Dave...";
    }
    // Free resources.
    oci_free_statement($stmt);
    oci_close($c);
  }
?>

6. Run the call_oracle_procedure.php from the command line, like this:

php call_oracle_procedure.php

7. Now you can requery the database to see the inserts made by the PHP program.

SQL> SELECT * FROM a;
 
      A_ID A_TEXT
---------- ------------
         1 This IS one.
         2 Hello Hal!
 
SQL> SELECT * FROM b;
 
      B_ID       A_ID B_TEXT
---------- ---------- ------------
         1          1 This IS two.
         2          2 Hello Dave!

There’s now a sample file for both approaches. As always, I hope this helps some folks.

Written by maclochlainn

June 15th, 2009 at 12:50 am

Posted in OPAL,Oracle,pl/sql,sql

5 Responses to 'Oracle Stored Procedure'

Subscribe to comments with RSS or TrackBack to 'Oracle Stored Procedure'.

  1. The answer in Step #2 is cool but I think it will be better with more columns and improve everybody’s understanding.

    steago

    17 Mar 11 at 6:33 am

  2. Can somebody mail me on store procedure to insert data into multiple tables?

    steago

    17 Mar 11 at 6:35 am

  3. I’m not sure that more columns help with understanding but they make editing a sample to fit a specific need much easier. Any way, here’s a more complete example:

    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
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    
    -- Transaction Management Example.
    CREATE OR REPLACE PROCEDURE contact_insert
    ( pv_member_type         VARCHAR2
    , pv_account_number      VARCHAR2
    , pv_credit_card_number  VARCHAR2
    , pv_credit_card_type    VARCHAR2
    , pv_first_name          VARCHAR2
    , pv_middle_name         VARCHAR2 := ''
    , pv_last_name           VARCHAR2
    , pv_contact_type        VARCHAR2
    , pv_address_type        VARCHAR2
    , pv_city                VARCHAR2
    , pv_state_province      VARCHAR2
    , pv_postal_code         VARCHAR2
    , pv_street_address      VARCHAR2
    , pv_telephone_type      VARCHAR2
    , pv_country_code        VARCHAR2
    , pv_area_code           VARCHAR2
    , pv_telephone_number    VARCHAR2
    , pv_created_by          NUMBER   := 1
    , pv_creation_date       DATE     := SYSDATE
    , pv_last_updated_by     NUMBER   := 1
    , pv_last_update_date    DATE     := SYSDATE) IS
     
      -- Local variables, to leverage subquery assignments in INSERT statements.
      lv_address_type        VARCHAR2(30);
      lv_contact_type        VARCHAR2(30);
      lv_credit_card_type    VARCHAR2(30);
      lv_member_type         VARCHAR2(30);
      lv_telephone_type      VARCHAR2(30);
     
    BEGIN
      -- Assign parameter values to local variables for nested assignments to DML subqueries.
      lv_address_type := pv_address_type;
      lv_contact_type := pv_contact_type;
      lv_credit_card_type := pv_credit_card_type;
      lv_member_type := pv_member_type;
      lv_telephone_type := pv_telephone_type;
     
      -- Create a SAVEPOINT as a starting point.
      SAVEPOINT starting_point;
     
      INSERT INTO member
      ( member_id
      , member_type
      , account_number
      , credit_card_number
      , credit_card_type
      , created_by
      , creation_date
      , last_updated_by
      , last_update_date )
      VALUES
      ( member_s1.NEXTVAL
      ,(SELECT   common_lookup_id
        FROM     common_lookup
        WHERE    common_lookup_context = 'MEMBER'
        AND      common_lookup_type = lv_member_type)
      , pv_account_number
      , pv_credit_card_number
      ,(SELECT   common_lookup_id
        FROM     common_lookup
        WHERE    common_lookup_context = 'MEMBER'
        AND      common_lookup_type = lv_credit_card_type)
      , pv_created_by
      , pv_creation_date
      , pv_last_updated_by
      , pv_last_update_date );
     
      INSERT INTO contact
      VALUES
      ( contact_s1.NEXTVAL
      , member_s1.CURRVAL
      ,(SELECT   common_lookup_id
        FROM     common_lookup
        WHERE    common_lookup_context = 'CONTACT'
        AND      common_lookup_type = lv_contact_type)
      , pv_first_name
      , pv_middle_name
      , pv_last_name
      , pv_created_by
      , pv_creation_date
      , pv_last_updated_by
      , pv_last_update_date );  
     
      INSERT INTO address
      VALUES
      ( address_s1.NEXTVAL
      , contact_s1.CURRVAL
      ,(SELECT   common_lookup_id
        FROM     common_lookup
        WHERE    common_lookup_context = 'MULTIPLE'
        AND      common_lookup_type = lv_address_type)
      , pv_city
      , pv_state_province
      , pv_postal_code
      , pv_created_by
      , pv_creation_date
      , pv_last_updated_by
      , pv_last_update_date );  
     
      INSERT INTO street_address
      VALUES
      ( street_address_s1.NEXTVAL
      , address_s1.CURRVAL
      , pv_street_address
      , pv_created_by
      , pv_creation_date
      , pv_last_updated_by
      , pv_last_update_date );  
      DBMS_OUTPUT.put_line('c5');
      INSERT INTO telephone
      VALUES
      ( telephone_s1.NEXTVAL                              -- TELEPHONE_ID
      , contact_s1.CURRVAL                                -- CONTACT_ID
      , address_s1.CURRVAL                                -- ADDRESS_ID
      ,(SELECT   common_lookup_id                         -- ADDRESS_TYPE
        FROM     common_lookup
        WHERE    common_lookup_context = 'MULTIPLE'
        AND      common_lookup_type = lv_telephone_type)
      , pv_country_code                                   -- COUNTRY_CODE
      , pv_area_code                                      -- AREA_CODE
      , pv_telephone_number                               -- TELEPHONE_NUMBER
      , pv_created_by                                     -- CREATED_BY
      , pv_creation_date                                  -- CREATION_DATE
      , pv_last_updated_by                                -- LAST_UPDATED_BY
      , pv_last_update_date);                             -- LAST_UPDATE_DATE
     
      COMMIT;
    EXCEPTION 
      WHEN OTHERS THEN
        ROLLBACK TO starting_point;
        RETURN;
    END contact_insert;
    /

    You would call it like this:

    1
    2
    
    -- Insert complete contact information using stored procedure.
    EXECUTE contact_insert('INDIVIDUAL','R11-514-34','1111-1111-1111-1111','VISA_CARD','Goeffrey','Ward','Clinton','CUSTOMER','HOME','Provo','Utah','84606','118 South 9th East','HOME','011','801','423-1234');

    maclochlainn

    17 Mar 11 at 8:42 am

  4. I noticed that in the last multi-column example that the parameters types for the stored procedure ( in,out,etc..) were missing. If these are not declared explicitly, is (in out) assumed for each parameter?

    Jim Harvey

    4 Aug 11 at 2:36 pm

  5. Great catch, I improved the comment before the create statement for the procedure. Oracle assumes IN-only mode as the default parameter mode of operation. The example above is a pass-by-value model, where the parameters can be values, variables, or expressions (a value returned by a function or math operation).

    maclochlainn

    4 Aug 11 at 2:55 pm

Leave a Reply