oracle pl/sql compilation errors needs some help !?

chin

2010-11-06 23:31:02 UTC

i got this two script of pl/sql function that output the result

" SQL> warning compilation error "

so pls advice me what to do ???

here's the script ...

-- calling script below
CREATE OR REPLACE FUNCTION getContWages(Contr_id IN Contractor.Contractor_id%TYPE)

RETURN Contract.paymentMode%TYPE
AS cpaymentMode Contract.paymentMode%TYPE;

BEGIN
SELECT paymentMode
INTO cpaymentMode
FROM Contractor, Contract
WHERE Contractor.Contractor_id=Contract.Contra…
AND paymentMode=cpaymentMode;
RETURN(cpaymentMode);

END;
/

-- execution function script below
-- this is pl/sql contractorwages
-- it requires 2 fields named skills_price from Skills table and No_hrs from Daily_Work table
-- the function will calculate skills_price MULTIPLE (*) No_hrs with condition of StartDate and FinishDate IS NOT NULL
-- the function should return wages of the contractor in numbers value
-- in between salary & commission field require to create via alter session table table_name add column_name data type

CREATE OR REPLACE FUNCTION ContractorWages ( -- this function requires Contractor_id with its data type
i_Contractor_id Contract.Contractor_… ) -- to match valid identifier for this function.

RETURN NUMBER
IS

v_Contract_wages Contract.wages%TYPE; -- a variable Contract_wages created from the matching
-- of table contractor and wages field in Contract table.

-- begin procedural sql execution

BEGIN

-- sql statement execution

-- FIRST select statement is to retrieve require attributes for comparison

SELECT SkillsPrice, No_hrs, StartDate, FinishDate
FROM Skills, Daily_Work, Contract;

-- SECOND select statement is to calculate salary

SELECT SUM(SkillsPrice*No_hrs) as SALARY
INTO v_Contract_wages
FROM Skills, Daily_Work, Contract
WHERE (StartDate IS NOT NULL AND FinishDate IS NOT NULL);

-- THIRD select statement is to calculate wages

SELECT SUM(SkillsPrice*No_hrs+NVL(comm,0)) -- the NVL means function lets you substitute a value when a null value is encountered, hence it will later convert NULL value to zero '0'
INTO v_Contract_wages -- here it will display contractor's wages from this sql statement
FROM Daily_Work, Skills, Contract
WHERE Contractor_id = i_Contractor_id
AND (StartDate IS NOT NULL AND FinishDate IS NOT NULL);

-- perform/check errors/exceptions

IF v_Contract_wages IS NULL THEN -- here is to display wages that is value ZERO, values that are not ZERO will display as
v_Contract_wages := 0;
END IF;

RETURN v_Contract_wages;
END;
/

how to solve this oracle pl/sql function script??? anyone ??