Re: [ask]: what wrong with my store procedure

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Wed, 17 Mar 1999 21:53:14 +0100
Message-ID: <36f0163d$0$22441@newton>

earthlink wrote
>p.s. I got compilation error from oracle.

*What* error?

>create or replace procedure t1
>is
>begin
> select count(*)
> from t_customer
> where person = 'father';
>end;
>/

That's a very basic one: what do you expect Oracle to do with the query result? PL/SQL procedures can be used anywhere in the database. For example: in a trigger, which would execute when a GUI user inserts some data into a table. This GUI might not expect any feedback from Oracle, so basically: PL/SQL code cannot generate output (unless you use the package dbms_output).

Anyway: you should store your query results somewhere, using into:

    create or replace function T1

        return integer
    as

        iCount integer;
    begin

        select count(*)
        into iCount
        from t_customer
        where person = 'father';

        return iCount;

    end T1;
    /
    show errors

Now you may call this function like:

    select T1
    from dual;

It gets more difficult if your query returns more than one row... Time to get yourself a good book!

Just to get you started using dbms_ouptut:

    create or replace procedure T2 as
    begin

        dbms_output.enable;
        for r in
            ( select person, city
              from t_customer
            )
        loop
            dbms_output.put_line( 'Person: ' || r.person );
            dbms_output.put_line( 'City: ' || r.city );
        end loop;

    end T2;
    /
    show errors

    set serveroutput on
    exec T2;

Arjan. Received on Wed Mar 17 1999 - 14:53:14 CST