I'm trying to use a variable from a cursor in the select statement of another cursor in pl/sql

Question

I need to run a query that finds me the first 20 vehicles that are as close to another vehicle with specific terms. I'm writing this in SQL Developer on an oracle database.

Here's what I have so far:

DECLARE
    TYPE cur_type IS REF CURSOR;

    CURSOR lost_vehicles_cur IS
       select 
          v.vin,
          V.VEHICLE_ID
       from d_vehicles V
       where v.system_id =4  ;

    make_cur cur_type;
    l_cur_string VARCHAR2(2000);
    l_make  <type>;
    l_model <type>;
    l_vin <type>;
BEGIN
    FOR vehicle IN lost_vehicles_cur LOOP
        dbms_output.put_line('lost vehicle is  '|| vehicle.vehicle_id);
        l_cur_string := 'SELECT make_name, model_name,vin FROM vehicles where make=(select make from vehicles where vehicle_id= '
            ||  vehicle.vehicle_id || 'and rownum<=20 and system_id=3 and vehicle_status_id in (13,14) ';
        OPEN make_cur FOR l_cur_string;
        LOOP
            FETCH make_cur INTO l_make, l_model, L_vin;
            EXIT WHEN make_cur%NOTFOUND;
            dbms_output.put_line('Related vehicles are ' || l_make || l_model || L_vin);
        END LOOP;
        CLOSE make_cur;
    END LOOP;
END;

I used a previous answer to get to this however I get the following error when I run this:

ORA-06550: line 32, column 13: PLS-00103: Encountered the symbol "<" when expecting one of the following: constant exception table long double ref char time timestamp interval date binary national character nchar 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. –

What are "some errors"? Why are you using dynamic SQL for the second cursor? It appears that static SQL would be sufficient. My immediate guess is that your dynamic SQL is missing a space between the vehicle_id and the keyword "and" but there may well be other errors. Taking a step back, why are you using two nested cursors rather than simply joining the tables? — Justin Cave
Also the dynamic query seems to be missing a closing bracket. Print it out or run the whole thing through a debugger to check it's generating what you think it should. But I agree with Justin, there's no reason for the dynamic cache-shredder. — William Robertson
@JustinCave , I errased some of the query so it's easier to get a solution. The error I'm getting is : ORA-06550: line 32, column 13: PLS-00103: Encountered the symbol "<" when expecting one of the following: constant exception <an identifier> <a double-quoted delimited-identifier> table long double ref char time timestamp interval date binary national character nchar 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. — DrakoG
@JustinCave the reson I'm not using a join is because I need a bunch of fields to match. I posted a simple version of the query. — DrakoG
What is <type>? That should probably be something like vehicle.make_name%type. btw if by 'fields' you mean 'columns' then joins are pretty good at matching them. — William Robertson

SkyWalker SkyWalker · Accepted Answer · 2016-09-15T16:16:00

You have to use parametrized cursor or dbms_sql:

DECLARE
    TYPE cur_type IS REF CURSOR;

    CURSOR lost_vehicles_cur IS
       select 
          v.vin,
          V.VEHICLE_ID
       from d_vehicles V
       where v.system_id =4  ;

    cursor l_cur (ip_id in d_vehicles.VEHICLE_ID%type)
    SELECT make_name, model_name,vin 
     FROM vehicles 
    where make in (select make 
                from vehicles 
                where vehicle_id= ip_id
                  and rownum<=20 
                  and system_id=3 
                  and vehicle_status_id in (13,14));

    make_cur cur_type;
    l_cur_string VARCHAR2(2000);
    l_make  <type>;
    l_model <type>;
    l_vin <type>;
BEGIN
    FOR vehicle IN lost_vehicles_cur LOOP
        dbms_output.put_line('lost vehicle is  '|| vehicle.vehicle_id);

        OPEN make_cur FOR l_cur (vehicle.vehicle_id);
        LOOP
            FETCH make_cur INTO l_make, l_model, L_vin;
            EXIT WHEN make_cur%NOTFOUND;
            dbms_output.put_line('Related vehicles are ' || l_make || l_model || L_vin);
        END LOOP;
        CLOSE make_cur;
    END LOOP;
END;

I'm trying to use a variable from a cursor in the select statement of another cursor in pl/sql

1 Answers