Using 2 explicit cursors.

Question

*/I am trying to use 2 explicit cursors, it shows these errors

{:ERROR at line 42: ORA-06550: line 42, column 1: PLS-00306: wrong number or types of arguments in call to 'PUT_LINE' ORA-06550: line 42, column 1: PL/SQL: Statement ignored } */

declare

   v_CITY AIRPORT.CITY%TYPE;
   v_ARRIVAL_TIME SCHEDULE.ARRIVAL_TIME%TYPE;
   v_fnumber  FLIGHT.FNUMBER%TYPE;
   v_CITY1 AIRPORT.CITY%TYPE;
   v_ARRIVAL_TIME1 SCHEDULE.ARRIVAL_TIME%TYPE;
   v_fnumber1  FLIGHT.FNUMBER%TYPE;


   /* First cursor */
   CURSOR get_tables IS
select    airp2.CITY  , S. ARRIVAL_TIME,S.fnumber  
from   schedule S, FLIGHT F, airport airp1, airport airp2
where airp1.apcode =  DEPARTURE_APCODE and airp2.apcode =  ARRIVAL_APCODE AND ARRIVAL_APCODE ='BWI'and S.FNUMBER= F.FNUMBER AND 
TO_CHAR(TRUNC(S.DEPARTURE_TIME)) = date'2017-11-12'
ORDER BY  airp1.CITY,  ARRIVAL_TIME ; 

   /* Second cursor */
   CURSOR get_columns IS
     select DISTINCT   airp2.CITY  , S. DEPARTURE_TIME,S.fnumber  
from   schedule S, FLIGHT F, airport airp1,FLIGHT_RESERVATION FL, airport airp2
where airp1.apcode =  ORIGIN_APCODE and airp2.apcode =  DESTINATION_APCODE AND ORIGIN_APCODE ='BWI'and S.FNUMBER= F.FNUMBER AND 
TO_CHAR(TRUNC(S.DEPARTURE_TIME)) = date'2017-11-12'
ORDER BY  airp2.CITY,  DEPARTURE_TIME ;

   BEGIN

   -- Open first cursor
   OPEN get_tables;
   LOOP
      FETCH get_tables INTO v_CITY, v_ARRIVAL_TIME, v_fnumber;



      -- Open second cursor
      OPEN get_columns;

      LOOP
         FETCH get_columns INTO v_CITY1, v_ARRIVAL_TIME1, v_fnumber1;

DBMS_output.put_line(v_CITY1, v_ARRIVAL_TIME1, v_fnumber1);


CLOSE get_tables;

      END LOOP;

      CLOSE get_columns;

   END LOOP;
 END;

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2017-10-08T23:55:32

DBMS_OUTPUT.PUT_LINE takes only one argument, so to output multiple values you have to convert them to character strings and concatenate them together. Change your PUT_LINE call to

DBMS_output.put_line('CITY1=' || TO_CHAR(v_CITY1) ||
                     ' ARRIVAL_TIME=' || TO_CHAR(v_ARRIVAL_TIME1) ||
                     ' FNUMBER=' || TO_CHAR(v_fnumber1));

Best of luck.

Using 2 explicit cursors.

1 Answers