Stored Procedure

[Anur Huilgol]

CREATE OR REPLACE PACKAGE BODY contracts
AS

PROCEDURE display_contract(contract_test_record IN contract%ROWTYPE)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('_______________________________________________');
DBMS_OUTPUT.PUT_LINE('Contract record is...');
DBMS_OUTPUT.PUT_LINE('Cont_no: ' || contract_test_record.cont_no);
DBMS_OUTPUT.PUT_LINE('Cont_status: ' || contract_test_record.cont_status) ;
DBMS_OUTPUT.PUT_LINE('Subdivision: ' || contract_test_record.subdivision) ;
DBMS_OUTPUT.PUT_LINE('Lot_no: ' || contract_test_record.lot_no);
DBMS_OUTPUT.PUT_LINE('Cust_no: ' || contract_test_record.cust_no);
DBMS_OUTPUT.PUT_LINE('Des_no: ' || contract_test_record.des_no);
DBMS_OUTPUT.PUT_LINE('Dev_no: ' || contract_test_record.dev_no);
DBMS_OUTPUT.PUT_LINE('Sub_no: ' || contract_test_record.build_no);
DBMS_OUTPUT.PUT_LINE('_______________________________________________');
END;

PROCEDURE display_error_message(error_code IN NUMBER,
error_message IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('_______________________________________________');
DBMS_OUTPUT.PUT_LINE('Error code is... ' || error_code);
DBMS_OUTPUT.PUT_LINE('Error message is... ' || error_message);
DBMS_OUTPUT.PUT_LINE('_______________________________________________');
END;

PROCEDURE create_new_contract
(submit_rec IN contract%ROWTYPE,
error_code OUT NUMBER,
operation_result OUT VARCHAR2)
IS
BEGIN

NULL;
END;

PROCEDURE amend_contract_details
(submit_rec IN contract%ROWTYPE,
error_code OUT NUMBER,
operation_result OUT VARCHAR2)
IS
BEGIN

NULL;
END;


PROCEDURE cancel_contract
(submit_rec IN OUT contract%ROWTYPE,
error_code OUT NUMBER,
operation_result OUT VARCHAR2)
IS
BEGIN

NULL;
END;

PROCEDURE sign_contract
(submit_rec IN OUT contract%ROWTYPE,
error_code OUT NUMBER,
operation_result OUT VARCHAR2)
IS
BEGIN

NULL;
END;


PROCEDURE complete_contract
(submit_rec IN OUT contract%ROWTYPE,
error_code OUT NUMBER,
operation_result OUT VARCHAR2)
IS
BEGIN

NULL;
END;


END contracts; -- package body



I am trying to write the stored procedures for the above block of code...and since I am very new to this I am not successful...can anyone please help


I have taken the following values and the tables

SET TERMOUT ON
PROMPT Building Assignment 1 tables. Please wait.
SET TERMOUT OFF

CREATE TABLE DESIGN
(DES_NO VARCHAR2(10) NOT NULL,
DES_NAME VARCHAR2(50) NOT NULL,
LEVELS VARCHAR2(10) NOT NULL CHECK(LEVELS IN ('single','two','split')),
PRIMARY KEY (DES_NO));

CREATE TABLE DEV_SITE
(DEV_NO VARCHAR2(10) NOT NULL,
DEV_NAME VARCHAR2(50) NOT NULL,
PRIMARY KEY (DEV_NO));

CREATE TABLE CUSTOMER
(CUST_NO VARCHAR2(10) NOT NULL,
CUST_NAME VARCHAR2(50) NOT NULL,
CUST_ADDR VARCHAR2(100),
PRIMARY KEY (CUST_NO));

CREATE TABLE BUILDER
(BUILD_NO VARCHAR2(10) NOT NULL,
BUILD_NAME VARCHAR2(50) NOT NULL,
CONTACT_NAME VARCHAR2(50) NOT NULL,
BUILD_ADDR VARCHAR2(100),
PRIMARY KEY (BUILD_NO));

CREATE TABLE CONTRACT
(CONT_NO VARCHAR2(10) NOT NULL,
CONT_STATUS VARCHAR2(10) NOT NULL CHECK(CONT_STATUS IN ('draft','approved','commited','cancelled','completed')),
SUBDIVISION VARCHAR2(50),
LOT_NO VARCHAR2(10),
CUST_NO VARCHAR2(10) NOT NULL,
DES_NO VARCHAR2(10) NOT NULL,
DEV_NO VARCHAR2(10) NOT NULL,
BUILD_NO VARCHAR2(10),
PRIMARY KEY (CONT_NO),
CONSTRAINT contracted_cust FOREIGN KEY (CUST_NO) REFERENCES CUSTOMER(CUST_NO),
CONSTRAINT contracted_design FOREIGN KEY (DES_NO) REFERENCES DESIGN(DES_NO),
CONSTRAINT contracted_site FOREIGN KEY (DEV_NO) REFERENCES DEV_SITE(DEV_NO),
CONSTRAINT contracted_builder FOREIGN KEY (BUILD_NO) REFERENCES BUILDER(BUILD_NO));

CREATE TABLE APPR_builder
(DES_NO VARCHAR2(10) NOT NULL,
BUILD_NO VARCHAR2(10) NOT NULL,
PRIMARY KEY (DES_NO,BUILD_NO),
CONSTRAINT appr_build_des FOREIGN KEY (DES_NO) REFERENCES DESIGN(DES_NO),
CONSTRAINT appr_build_builder FOREIGN KEY (BUILD_NO) REFERENCES BUILDER(BUILD_NO));

CREATE TABLE APPR_site
(DES_NO VARCHAR2(10) NOT NULL,
DEV_NO VARCHAR2(10) NOT NULL,
PRIMARY KEY (DES_NO,DEV_NO),
CONSTRAINT appr_site_des FOREIGN KEY (DES_NO) REFERENCES DESIGN(DES_NO),
CONSTRAINT appr_site_site FOREIGN KEY (DEV_NO) REFERENCES DEV_SITE(DEV_NO));

[Deepak A]

what is the error message??
Which procedure within the package is causing the problem ???

Which lines of code have u added last for the compilation error to occur ?