Click here to Skip to main content
15,885,915 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
I have two tables. MODELS and AUTOMOBILES. And procedure which export all models with name that user inputs and their price(stored in AUTOMOBILES).

What I have tried:

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);end;


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
Posted
Updated 4-Dec-17 6:57am
v3
Comments
Kornfeld Eliyahu Peter 3-Dec-17 5:54am    
Don't use OUT parameter, but a simple SELECT statement inside the procedure...
Jörgen Andersson 24-Dec-17 7:12am    
He's using Oracle, not Sql Server
Jörgen Andersson 24-Dec-17 7:14am    
You need to output a collection type, an array or a cursor.

1 solution

As mentioned in the comments, you don't want to use a variable to output the data. Use a normal SELECT statement.
 
Share this answer
 
Comments
shihappns 5-Dec-17 9:13am    
Thanks. How exactly to do this? I mean... Where in the procedure should I put the SELECT.
ZurdoDev 5-Dec-17 9:21am    
It is actually one of the most basic items in Database development. Just google how to do an oracle stored procedure. You have most of it but are using a variable instead of outputting a result set.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900