How do you call a Procedure in Block SQL

Question

My procedure is below;

I have to create an anonymous block that CALLS the procedure and DISPLAYS all the customers total price and number of cars purchased from that PARTICULAR city Can someone show me how to do this BLOCK

My procedure is as FOLLOW:

CREATE OR REPLACE PROCEDURE getCars 
(   v_custname OUT car.custname%TYPE,
    v_purchcost OUT car.purchcost%TYPE, 
    v_city IN customer.custcity%TYPE, 
    v_count OUT NUMBER, 
    v_total OUT car.purchcost%TYPE  
)  
AS
BEGIN
    Select c.custname, Count(c.custname), SUM(purchcost)   
    INTO   v_custname, v_count, v_total
    From car c
    JOIN customer cs
    ON c.custname = cs.custname
    WHERE cs.custcity = v_city
    Group By c.custname;
END;
/

This is what I have for the BLOCK to call procedure BUT ITS NOT WORKING

SET SERVEROUTPUT ON
SET VER OFF 
ACCEPT p_city PROMPT 'Enter City Name: '; 
DECLARE
CURSOR cname IS 
Select customer.custname
INTO custname
From customer 
Where UPPER(custcity) = UPPER('&p_city');
BEGIN
FOR 
v_car in cname
LOOP 
getCars(v_car.custname, v_count, v_total); 
END LOOP; 
DBMS_OUTPUT.PUT_LINE(v_car.custname||v_count||v_total);
END; 
/

Is [this](https://stackoverflow.com/questions/25182104/how-to-call-a-pl-sql-anonymous-block-from-an-pl-sql-anonymous-block), [this](https://www.oracletutorial.com/plsql-tutorial/plsql-anonymous-block/), [this](https://livesql.oracle.com/apex/livesql/file/content_DGSGFJASF012RB2006BW3WWY5.html) or [this](https://livesql.oracle.com/apex/livesql/file/tutorial_KS0KNKP218J86THKN85XU37.html) useful? — Andrew, Jul 06 '20 at 20:04
No I checked it Out, I am suppose to call the procedure using the block where a user enters the City name and than according to the city name I display Total number of cars each customer bought and total price they spent — user13783775, Jul 06 '20 at 20:07
It seems you don't just want to know how to call a procedure in a block, because that is as simple as `dbms_output.put_line('Hello');` (dbms_output.put_line is a procedure.) You actually want to 'display' the results of a query. How about [this](https://stackoverflow.com/a/40360471/230471)? — William Robertson, Jul 06 '20 at 21:41

score 1 · Answer 1 · answered Jul 06 '20 at 20:29

1

If you only want to display them the following can be done:

CREATE OR REPLACE PROCEDURE getcars (
    v_city IN customer.custcity%TYPE
) AS
BEGIN
    FOR c IN (
        SELECT
            car.custname,
            COUNT(car.custname) count_custname,
            SUM(purchcost) purchcost
        FROM
            car
            JOIN customer cs ON car.custname = cs.custname
        WHERE
            cs.custcity = v_city
        GROUP BY
            car.custname
    ) LOOP
        dbms_output.put_line('Customer '
                             || c.custname
                             || ' has bought '
                             || c.count_custname
                             || ' totaling '
                             || purchcost);
    END LOOP;
END;

Call the procedure:

DECLARE
    v_city customer.custcity%TYPE := 'some city';
BEGIN
    getcars(v_city);
END;

Otherwise if you need to return it for each customer then you should use cursors or more complicated data structures.

answered Jul 06 '20 at 20:29

Ergi Nushi

804
1
6
16

Yeah I need to return for each customer I tried to use Cursors Above but code doesnt seem to work can you check it out – user13783775 Jul 06 '20 at 21:02
ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "GETCARS", line 8 ORA-06512: at line 13 01422. 00000 - "exact fetch returns more than requested number of rows" *Cause: The number specified in exact fetch is less than the rows returned. *Action: Rewrite the query or change number of rows requested – user13783775 Jul 06 '20 at 21:13
No way. SELECT Ergi wrote can't return that error, so it must be something YOU (@user137...) did wrong, so you should fix it. – Littlefoot Jul 07 '20 at 05:01
@user13783775 Yup, as Littlefoot said, there must be something you have wrote and probably you are selecting many rows into an INTO statement. – Ergi Nushi Jul 07 '20 at 08:26

How do you call a Procedure in Block SQL

1 Answers1