Oracle insert stored procedure

The question title says: "Oracle insert stored procedure" but then you ask for: "an oracle stored procedure that will update a table.  But to an SQL, purist "inserts" and "updates" are two very different activities.

Do you want to add new records to tables (insert) or do you want to change existing records (update) or do you really want to do both?  What about removing information that is no longer needed (delete)? Do you need to supports deletes also?

Are you hoping for a generic procedure that can accept a table name as a variable?  (Oracle stored procedures are not optimized for that).  Or, do you plan to write a separate procedure for each table?  (That is what Oracle stored procedures do very well.)

The simple solution:

SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
 
Table created.
 
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
  3  /
 
1 row created.
 
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
  3  /
 
1 row created.
 
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
  3  /
 
1 row created.
 
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
  3  /
 
1 row created.
 
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
  3  /
 
1 row created.
 
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York',  'Tester')
  3  /
 
1 row created.
 
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York',  'Manager')
  3  /
 
1 row created.
 
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('08','James',    'Cat',     to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
  3  /
 
1 row created.
 
SQL>
SQL> CREATE OR REPLACE PROCEDURE update_employee_salary(
  2    p_factor IN NUMBER
  3  ) AS
  4    v_employee_count INTEGER;
  5  BEGIN
  6      UPDATE employee
  7      SET salary = salary * p_factor;
  8      COMMIT;
  9  EXCEPTION
 10    WHEN OTHERS THEN
 11      ROLLBACK;
 12  END update_employee_salary;
 13  /
 
Procedure created.
 
SQL>
SQL> select * from employee;
 
ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04   Celia      Rice       24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda      Green      30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98    7897.78 New York   Manager
08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver  Tester
 
8 rows selected.
 
SQL>
SQL> CALL update_employee_salary(1.5);
 
Call completed.
 
SQL>
SQL> select * from employee;
 
ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason      Martin     25-JUL-96 25-JUL-06    1851.84 Toronto    Programmer
02   Alison     Mathews    21-MAR-76 21-FEB-86    9992.67 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    9817.17 Vancouver  Tester
04   Celia      Rice       24-OCT-82 21-APR-99    3517.17 Vancouver  Manager
05   Robert     Black      15-JAN-84 08-AUG-98    3502.17 Vancouver  Tester
06   Linda      Green      30-JUL-87 04-JAN-96    6484.17 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98   11846.67 New York   Manager
08   James      Cat        17-SEP-96 15-APR-02    1849.17 Vancouver  Tester
 
8 rows selected.
 
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
 
Table dropped.
 
SQL>

Select allOpen in new window

I apologize for improperly naming the question. I do want a stored procedure to update a table. I plan to write separate procedures for each table I'm working with.

To to this parametric you have to deliver the procedure a list of parameters that say:
1. what is the operation - INSERT or UPDATE
2. which is the table affected
3. Which are the columns
4. which are the values

After that you can construct a string like:

v_sql := 'UPDATE ' || p_table || 'SET ' || p_column || '=' || p_value;

execute immediate v_sql;

The problem are the parameters of the procedure.

I think you are much better off using a separate procedure for each table than to try a generic approach that uses "execute immediate".  If you try that generic approach, you will find that "immediate" may not be quite as fast as you expect, especially if the application supports lots of concurrent users.

What about record locks?  Do you need the update procedures to check for the possibility of a record lock first, or will your application handle those so the update procedure can assume it will not encounter a record lock being held by another user?

With the above code for "Create or Replace Procedure" statement in Oracle you can create the procedures for each table.

You can use the VB code below to run the procedure from .NET.  Pay close attention to each parameter as they must match type and direction (IN or OUT).

'OracleConn in Web.Config
Dim conn As New OracleConnection(ConfigurationManager.ConnectionStrings("OracleConn").ToString)
Dim cmd As New OracleCommand()
Try
    conn.Open()
    cmd.Connection = conn
    cmd.CommandText = "ADD_APPLICATION"
 
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add("p_APPLICATION_ID", OracleDbType.Double, 10, ParameterDirection.Output)
    cmd.Parameters.Add("p_FNAME", OracleDbType.Char, 20, Me.txtStuFirstName.Text.ToUpper, ParameterDirection.Input)
    cmd.Parameters.Add("p_LNAME", OracleDbType.Char, 25, Me.txtStuLastName.Text.ToUpper, ParameterDirection.Input)
    cmd.Parameters.Add("p_MINIT", OracleDbType.Char, 7, Me.txtInitial.Text.ToUpper, ParameterDirection.Input)
    cmd.ExecuteNonQuery()
    Dim sapplicationID As String
    Dim sDistrictID As String
    sapplicationID = cmd.Parameters("p_APPLICATION_ID").Value.ToString
 
    lblMessage.Text = "Application record " & sapplicationID & " has been saved." & vbCrLf
Catch ex as oracleexception
     lblmessage.Text = "Save failed " & ex.message
Finally
    conn.Close()
End Try

Select allOpen in new window

I'm not concerned about record locks at this point. My stored procedure looks like this so far,

create or replace procedure sp_update_hist_records
(
h_link in varchar2,
h_date in date,
h_source in varchar2,
h_document in CLOB
)
as
begin
update hist_recrods
set link=h_link, date=h_date, source=h_source, document=h_document
where id=1254;
commit;
end;

I'd like to set the id to a vaiable I can set in code or whe I run it in sql developer. Am I on the right track here?

This procedure will work.
But it looks very strange.
There is no logics that really needs the to apply
procedures and PL/SQL. There is only an UPDATE statemnts
and it is also bad written - there is no EXCEPTION section.
The procedure should return HOW the update has finished -
successfull or failed.
Most of the programattic environments using ODBC and JDBC
can send the UPDATE statement to Oracle server without
using the procedure.
So possibly you have to analyze what stays behind this

Thanks, I have it compiling properly now. What is the syntax for testing it in sql developer? I mean the exec statement.

create or replace procedure sp_update_hist_records
(
h_link in varchar2,
h_date in date,
h_source in varchar2,
h_document in CLOB,
h_id number
)
as
begin
update hist_recrods
set link=h_link,
date=h_date,
source=h_source,
document=h_document
where id= h_id;
end;

begin sp_update_hist_records('[link]','[date]','[source]','[document]',[id]); end;
/

Note that varchar2 and date parameter values must be enclosed in single quotes, and number parameter values must not have quotes.  I have no experience with using the CLOB datatype.  I'm not sure if that is supported just like the "simple" datatypes or not.

Thanks for all of the help.