Using count(*) to fetch more than one row in a SQL Procedure

Question

I'm trying to return the number of rows per invoice_id using a function and procedure. Some invoice_id's have more than one row and I'm not sure how to fetch the count when I execute my procedure. As an example invoice_id(7) has just one row, but invoice_id(100) has four rows of information.

Create or replace function return_num_rows_function(invoice_id_text in varchar2)
    Return varchar2
    Is inv_id varchar2(20);
    Begin
    Select count(*)invoice_id into inv_id from invoice_line_items where invoice_id=invoice_id_text;
    Return inv_id;
    End;



 Create or replace procedure return_num_rows (invoice_id_text in varchar2)
    Is inv_id varchar(20);
    line_item_desc invoice_line_items.line_item_description%type;
    Begin
    inv_id := return_num_rows_function(invoice_id_text);
    If inv_id is not null then
    Select count(*)invoice_id, line_item_description into inv_id,line_item_desc
    From invoice_line_items where invoice_id = inv_id;
    dbms_output.put_line('The number of rows returned:'|| inv_id);
    dbms_output.put_line('Item description(s):'|| line_item_desc);
    End if;
    End;

    set serveroutput on;
    execute return_num_rows(7);

If you want to return the number of records then the retrun type of the function should be a `Number`. Why you made it a `varchar`? Also if your function query is returning more then 1 rows then you need a table function which return all the rows. You can also use a `sys_refcursor` to get your result. — XING, Nov 27 '18 at 04:33
Read this https://stackoverflow.com/questions/52850468/convert-a-piece-of-sql-into-an-oracle-function/52851223#52851223 — XING, Nov 27 '18 at 04:52
Possible duplicate of [Convert a piece of SQL into an Oracle function](https://stackoverflow.com/questions/52850468/convert-a-piece-of-sql-into-an-oracle-function) — XING, Nov 27 '18 at 04:52

score 1 · Accepted Answer · answered Nov 27 '18 at 07:38

First of all do not use a string type variable for a numeric one (invoice_id_text).
For your case it's better to use a procedure instead of called function ( return_num_rows_function ), since you need two out arguments returned.

A SQL Select statement cannot be used without Group By with aggegated and non-aggregated columns together ( i.e. don't use this one :

  Select count(*) invoice_id, line_item_description 
    into inv_id,line_item_desc
    From invoice_line_items 
   Where invoice_id = inv_id;

)

So, Try to create below procedures :

    SQL> CREATE OR REPLACE Procedure 
    return_num_rows_proc(
                         i_invoice_id        invoice_line_items.invoice_id%type,
                         inv_id          out pls_integer,
                         line_item_desc  out invoice_line_items.line_item_description%type
                        ) Is
    Begin
    for c in
     (
      Select line_item_description
        into line_item_desc
        From invoice_line_items
       Where invoice_id = i_invoice_id
     )
     loop
       line_item_desc := line_item_desc||'  '||c.line_item_description;
       inv_id := nvl(inv_id,0) + 1;
     end loop;
    End;
    /

    SQL> CREATE OR REPLACE Procedure 
       return_num_rows(
                      i_invoice_id pls_integer
                      ) Is

      inv_id         pls_integer;
      line_item_desc invoice_line_items.line_item_description%type;
    Begin
       return_num_rows_proc(i_invoice_id,inv_id,line_item_desc);
      If inv_id is not null then
        dbms_output.put_line('The number of rows returned:' || inv_id);
        dbms_output.put_line('Item description(s):' || line_item_desc);
      End if;
    End;
    /

and call as in your case :

SQL> set serveroutput on;
SQL> execute return_num_rows(7);

score 0 · Answer 2 · answered Nov 27 '18 at 10:47

0

Replace inv_id varchar2(20) with inv_id number; and also if you want to get two outputs from procedure better to use refcursor.

answered Nov 27 '18 at 10:47

Rajesh G

44
4

Using count(*) to fetch more than one row in a SQL Procedure

2 Answers2