CODEWITHSUNDEEP
sqloracle-databasestored-proceduresprocedure
Poc - pită cu unsoare
Poc - pită cu unsoare

Reputation: 21

PLS -00103 Error Stored Procedure Oracle SQL

I tried compiling the following procedure and got PLS-00103 error.

create or replace PROCEDURE getFlightDetails
(ticketNumberIn IN integer, fullName OUT varchar2(60), flightNumberOut OUT integer, 
 route OUT varchar2(50), durationInHours OUT number(6,4)) 
IS
BEGIN
  select    
    pe.FirstName || ' ' || pe.LastName into fullName,
    fl.FlightNumber into flightNumberOut,
    fl.DepartureAirport || ' - ' || fl.ArrivalAirport into route,
    ROUND((fl.ArrivalDate - fl.DepartureDate)*24,1) into durationInHours
  from Purchase pu
    inner join Person pe on pe.SSN = pu.SSN
    inner join Ticket ti on ti.TicketNumber = pu.TicketNumber
    inner join Flight fl on fl.FlightNumber = ti.FlightNumber
  where pu.TicketNumber = ticketNumberIn;
END;

What have I done wrong? I mean, I already searched the Error and the found explanations didn't help much.

Upvotes: 0

Views: 54

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

Assuming that your query returns exactly one row, you want just one INTO clause. You don't put the INTO clause in your projection (the list of columns you are selecting). You also can't specify lengths on parameters just the data type.

create or replace PROCEDURE getFlightDetails
(ticketNumberIn IN integer, fullName OUT varchar2, flightNumberOut OUT integer, 
 route OUT varchar2, durationInHours OUT number) 
IS
BEGIN
  select    
    pe.FirstName || ' ' || pe.LastName,
    fl.FlightNumber,
    fl.DepartureAirport || ' - ' || fl.ArrivalAirport,
    ROUND((fl.ArrivalDate - fl.DepartureDate)*24,1)
  into fullName, 
       flightNumberOut, 
       route, 
       durationInHours
  from Purchase pu
    inner join Person pe on pe.SSN = pu.SSN
    inner join Ticket ti on ti.TicketNumber = pu.TicketNumber
    inner join Flight fl on fl.FlightNumber = ti.FlightNumber
  where pu.TicketNumber = ticketNumberIn;
END;

Not related to your issue, I would suggest coming up with a naming convention for your parameters that differentiates them from column names and is used consistently. Having one parameter whose name is Out but not doing the same thing on all the other OUT parameters will likely be confusing. Personally, I'd just use a p_ prefix for parameters

create or replace PROCEDURE getFlightDetails
(p_ticketNumberIn IN integer, 
 p_fullName OUT varchar2, 
 p_flightNumber OUT integer, 
 p_route OUT varchar2, 
 p_durationInHours OUT number)

If you wanted to have pin_ and pout_ to differentiate in and out parameters, that wouldn't be unreasonable.

Upvotes: 1

Related Questions