Member Avatar for Prosatanos

I have two tables. MODELS and AUTOMOBILES. And procedure which export all models with name that user inputs and their price(stored in AUTOMOBILES).

create or REPLACE procedure modelzz(   VAR_MODEL IN MODELS.NAME_MODEL%TYPE,
                                       VAR_PRICE OUT AUTOMOBILES.A_PRICE%TYPE )
                                       as
                                       begin
SELECT a.A_PRICE
INTO VAR_PRICE
FROM AUTOMOBILES a
join MODELS m
on a.MODELS_ID_MODEL=m.ID_MODEL
where m.NAME_MODEL=VAR_MODEl;
dbms_output.put_line(VAR_MODEL||'''s price is : '||VAR_PRICE);

procedure start:

var v_modelzz varchar2;
exec modelzz('&model_name',:v_modelzz);

The problem is I want to export all records for certain model. Their's more than one, one model can have two or more records.
Here's the error: exact fetch returns more than requested number of rows

  • 4 Contributors
  • 5 Replies
  • 391 Views
  • 2 Months Discussion Span
  • Latest Post Latest Post by urtrivedi
Member Avatar for pty

Your procedure is returning VAR_PRICE OUT AUTOMOBILES.A_PRICE%TYPE. You should be returning a table.

Member Avatar for Prosatanos

How exactly to do this? Where I should return the table?

Edited by Prosatanos
Member Avatar for AndrisP

Try something like this:

create or REPLACE procedure modelzz(
    VAR_MODEL IN MODELS.NAME_MODEL%TYPE
) as
    cursor c_automobiles is 
    SELECT a.A_PRICE
    FROM AUTOMOBILES a
    join MODELS m
    on a.MODELS_ID_MODEL=m.ID_MODEL
    where m.NAME_MODEL=VAR_MODEl;
    VAR_PRICE AUTOMOBILES.A_PRICE%TYPE;
begin

open c_automobiles;
    loop
        fetch c_automobiles into VAR_PRICE;
        exit when c_automobiles%notfound;
        dbms_output.put_line(
            VAR_MODEL||'''s price is : '||VAR_PRICE
        );
    end loop;
close c_automobiles;

end modelzz;
Edited by AndrisP
Member Avatar for AndrisP

Declare table type variable if you want to return e.g.

type r_model_price is record (
    p_model MODELS.NAME_MODEL%type
    ,p_price AUTOMOBILES.A_PRICE%type
);
type tt_model_price is table of r_model_price index by binary_integer;

create or REPLACE procedure modelzz(
    VAR_MODEL IN MODELS.NAME_MODEL%TYPE
    ,VAR_MODEL_PRICE OUT tt_model_price
) as
    cursor c_automobiles is 
    SELECT m.NAME_MODEL, a.A_PRICE
    FROM AUTOMOBILES a
    join MODELS m
    on a.MODELS_ID_MODEL=m.ID_MODEL
    where m.NAME_MODEL=VAR_MODEl;
    v_model_price c_automobiles%rowtype;
    v_increment integer default 0;
begin
open c_automobiles;
    loop
        fetch c_automobiles into v_model_price;
        exit when c_automobiles%notfound;
        v_increment := v_increment + 1;
        VAR_MODEL_PRICE(v_increment).p_model := v_model_price.NAME_MODEL;
        VAR_MODEL_PRICE(v_increment).p_price := v_model_price.A_PRICE;
    end loop;
close c_automobiles;
end modelzz;
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.