• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Junilu Lacar
  • Martin Vashko
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Scott Selikoff
  • salvin francis
  • Piet Souris

Stored Procedure

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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));
 
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ?
 
Don't destroy the earth! That's where I keep all my stuff! Including this tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!