When accessing the database using stored procedures for basic Create, Read, Update and Delete (CRUD) functions, or when you want to write triggers that capture the before and after values in tables, or when you want to create Plain Old Java Objects (POJO’s) that match the database objects, writing these by hand takes a lot of effort and also since the requirements are changing in an agile projects at a frequent rate, there will be design changes to meet the requirement changes, so the triggers, CRUD stored procedures or Data Access Objects (DAO) are going to be out of data, instead of hand coding, its better to generate the code, using the metadata of the database
How
When writing code, there needs to be a way to make sure that, there is no effort spent writing code that keeps changing constantly because its tightly coupled to other objects under development, data access layer is one example of this
Given a customer table in the script below and the need to write Create, Read, Update and Delete (CRUD) stored procedures, to access the database.
CREATE TABLE customer
(
customerid NUMBER(18,0) NOT NULL,
name VARCHAR2(64) NOT NULL,
isactive NUMBER(1) DEFAULT 1 NOT NULL,
CONSTRAINT pk_customer PRIMARY KEY (customerid)
);
The create customer stored procedure
CREATE OR REPLACE PROCEDURE Customer_Create
(inName Customer.Name%type, inIsActive Customer.isActive%type)
AS
BEGIN
IF inName IS NULL THEN
raise_application_error(-21003, 'Name cannot be null');
END IF;
IF inIsActive IS NULL THEN
raise_application_error(-21002, 'isActive Cannot be null');
END IF;
INSERT INTO Customer (CustomerId,Name,isActive)
VALUES (Sequence_Customer.nextval,inName,inIsActive);
EXCEPTION
WHEN others THEN
raise_application_error(-21111, sqlerrm);
END;
read customer stored procedure
CREATE OR REPLACE PROCEDURE GetCustomerInformation (
inCustomerId IN Customer.CustomerId%type,
customerRecord OUT SYS_REFCURSOR)
AS
BEGIN
OPEN customerRecord FOR SELECT CustomerId, Name, isActive
FROM Customer
WHERE CustomerId = inCustomerId;
EXCEPTION
WHEN no_data_found THEN
raise_application_error(-21021, 'CustomerId not found');
WHEN others THEN
raise_application_error(-21000, sqlerrm);
end;
/
Update
CREATE OR REPLACE PROCEDURE UpdateCustomer (
inCustomerId Customer.CustomerId%type,
inName Customer.Name%type,
inIsActive Customer.isActive%type)
AS
BEGIN
IF inName IS NULL THEN
raise_application_error(-21011, 'Name cannot be null');
END IF;
IF inCustomerId IS NULL THEN
raise_application_error(-21010, 'CustomerId cannot be null');
END IF;
BEGIN
SELECT CustomerID FROM Customer
WHERE CustomerID = inCustomerId;
EXCEPTION
WHEN no_data_found THEN
raise_application_error(-21012, 'CustomerId not found');
WHEN others THEN
raise_application_error(-21000, sqlerrm);
END;
UPDATE Customer SET Name=inName
WHERE CustomerID = inCustomerID;
IF sql%rowcount=0 THEN
raise_application_error(-21012, 'CustomerId not found');
END IF;
EXCEPTION
WHEN others THEN
raise_application_error(-21000, sqlerrm);
END;
/
and now Delete
CREATE OR REPLACE PROCEDURE DeleteCustomer (
inCustomerId Customer.CustomerId%type)
AS
BEGIN
IF inCustomerId IS NULL THEN
raise_application_error(-21020, 'CustomerId cannot be null');
END IF;
DELETE FROM Customer
WHERE CustomerID = inCustomerID;
IF sql%rowcount=0 THEN
raise_application_error(-21021, 'CustomerId not found');
END IF;
EXCEPTION
WHEN others THEN
raise_application_error(-21000, sqlerrm);
end;
/
procedures are shown. Hand coding these stored procedures every time the table changes is really error prone and time consuming. To avoid the errors, its best to generate these stored procedures using the metadata of the database from the USER_TABLES,USER_CONSTRAINTS, USER_TAB_COLUMNS and USER_CONS_COLUMNS in the Oracle database as the code fits a template. Stored procedures can be used to generate this code as shown below
CREATE OR REPLACE PROCEDURE InformationGenerator(inTableName VARCHAR2) AS
BEGIN
write('CREATE OR REPLACE PROCEDURE get'||inTableName||'Information (');
write('in'||getPrimaryKeyColumn(inTableName)||' '||inTableName||'.'||getPrimaryKeyColumn(inTableName)||'%type,');
write(inTableName||'Record OUT SYS_REFCURSOR)');
write('AS');
write('BEGIN');
write(' OPEN '||inTableName||'Record FOR SELECT '||getColumnsFor(inTableName));
write(' FROM '||inTableName);
write(' WHERE '||getPrimaryKeyColumn(inTableName)||' = in'||getPrimaryKeyColumn(inTableName)||';');
write('EXCEPTION');
write(' WHEN no_data_found THEN');
write(' raise_application_error(-21021,'|| '''Search key cannot be null'');');
write(' WHEN others THEN');
write(' raise_application_error(-21000, sqlerrm);');
write('END;');
write('/');
END;
/
This InformationGenerator procedure generates the Read customer stored procedure. Once the generator code is written, all that needs to be done is use it on all the tables of the project as shown below
DECLARE
BEGIN
FOR tablesList IN (SELECT table_name FROM user_tables) LOOP
InformationGenerator(tablesList.table_name);
END LOOP;
END;
/
Whenever a table changes all that needs to be done is run the code generator and get up-to-date code. This generated code can then be checked in or distributed as an artifact of the Continuous Integration instance of your team. The same code generator can also be done using a scripting language like ruby as shown below.
def generateInformationProcedure(tableName)
writeLine("CREATE OR REPLACE PROCEDURE get"+tableName+"Information (")
writeLine("in"+getPrimaryKeyColumn(tableName)+" "+tableName+"."+getPrimaryKeyColumn(tableName)+"%type,");
writeLine(tableName.capitalize+"Record OUT SYS_REFCURSOR)");
writeLine("AS");
writeLine("BEGIN");
writeLine(" OPEN "+tableName.capitalize+"Record FOR SELECT "+getColumnsFor(tableName));
writeLine(" FROM "+tableName);
writeLine(" WHERE "+getPrimaryKeyColumn(tableName)+" = in"+getPrimaryKeyColumn(tableName)+";");
writeLine("EXCEPTION");
writeLine(" WHEN no_data_found THEN");
writeLine(" raise_application_error(-21021,'Search key cannot be null');");
writeLine(" WHEN others THEN");
writeLine(" raise_application_error(-21000, sqlerrm);");
writeLine("END;");
writeLine("/");
writeLine("");
end
@connection = OCI8.new(schemaName, dbPassword,dbName)
@proceduresFile = File.open('crudProcedures.sql', 'w')
@connection.exec("SELECT table_name FROM user_tables") do | row |
generateInformationProcedure(row[0])
end
@connection.logoff
