Oracle Apex编译包体时遇ORA-24344:编译成功但存在错误求助

Hey there, let's walk through the issues in your package body that's causing the ORA-24344: success with compilation error message. The root problems are related to how you're referencing table columns without actually querying the table data for your specific band_id.

Key Issues in Your Original Code #

1. Directly referencing table columns in agent_present without a query #

Your function tries to check BAND.Agent_firstname IS NULL directly, but Oracle has no way of knowing which row in the BAND table you're referring to. You need to fetch the specific record for the provided band_id first before evaluating the agent fields.

2. Unqualified column reference in get_band_cost #

Similarly, BOOKING.Agreed_band_price is referenced without specifying which booking (or which band's booking) you want to use. You need to retrieve the base price for the given band_id from the BOOKING table first.

Corrected Package Body Code #

Here's the fixed version with explanations of the changes:

CREATE OR REPLACE PACKAGE BODY band_price_package AS
-- Function that checks if a band has a manager
FUNCTION agent_present(band_id BAND.Band_id%TYPE) RETURN BOOLEAN IS
  -- Declare variables to hold the agent details from the BAND table
  v_agent_firstname BAND.Agent_firstname%TYPE;
  v_agent_lastname BAND.Agent_lastname%TYPE;
  v_agent_phone BAND.Agent_phone%TYPE;
  v_agent_email BAND.Agent_email%TYPE;
BEGIN
  -- Fetch the agent details for the specified band_id
  SELECT Agent_firstname, Agent_lastname, Agent_phone, Agent_email
  INTO v_agent_firstname, v_agent_lastname, v_agent_phone, v_agent_email
  FROM BAND
  WHERE Band_id = band_id;
  
  -- Check if all agent fields are null
  IF v_agent_firstname IS NULL 
     AND v_agent_lastname IS NULL 
     AND v_agent_phone IS NULL 
     AND v_agent_email IS NULL THEN
    RETURN FALSE;
  ELSE
    RETURN TRUE;
  END IF;
EXCEPTION
  -- Handle case where the band_id doesn't exist in the BAND table
  WHEN NO_DATA_FOUND THEN
    RETURN FALSE;
END agent_present;

-- Procedure that gets the band hire price including agent fee
PROCEDURE get_band_cost(band_id IN BAND.Band_id%TYPE, band_cost OUT BOOKING.Agreed_band_price%TYPE) IS
  v_base_price BOOKING.Agreed_band_price%TYPE;
BEGIN
  -- Fetch the agreed band price for the specified band_id
  -- Note: If a band has multiple bookings, adjust this query to get the correct one (e.g., latest booking)
  SELECT Agreed_band_price
  INTO v_base_price
  FROM BOOKING
  WHERE Band_id = band_id
  FETCH FIRST 1 ROW ONLY; -- Use this to get the first matching record, adjust as needed
  
  -- Calculate the final cost based on agent presence
  IF agent_present(band_id) THEN
    band_cost := v_base_price * 1.25;
  ELSE
    band_cost := v_base_price;
  END IF;
EXCEPTION
  -- Handle case where no booking exists for the band_id
  WHEN NO_DATA_FOUND THEN
    band_cost := 0; -- Assign a default value or raise an error based on your business logic
    -- Uncomment below to throw a custom error instead:
    -- RAISE_APPLICATION_ERROR(-20001, 'No booking record found for band ID: ' || band_id);
END get_band_cost;
END band_price_package;
/

Additional Notes #

• Exception Handling: The NO_DATA_FOUND exceptions are added to gracefully handle cases where the provided band_id doesn't exist in the BAND table or has no associated bookings in the BOOKING table. Adjust this logic to match your project's requirements.
• Booking Price Logic: If a band has multiple bookings, you'll need to modify the SELECT in get_band_cost to target the correct price (e.g., using ORDER BY Booking_date DESC FETCH FIRST 1 ROW ONLY to get the most recent booking price).

内容的提问来源于stack exchange，提问作者Mate92