Re: Stored Procedure with NOT PL/SQL

From: Quentin Sherman Xue <qxue_at_sii.cl>
Date: Tue, 24 Nov 1998 18:04:47 GMT
Message-ID: <01be17dd$698b4a20$ec011592@qspc.sii.cl>

Use the function put_line of the package DBMS_OUTPUT. But you have to do some "select into" memory variable first and then display it on the screen. Because as I know, stored procedure of Oracle doesn't allow you to display message directly on the screen.

Here is complet example:

---

create or replace procedure mystoredproc is                                                                                      

begin     

        for emp_rec in (select * from emp) loop     

                dbms_output.put_line(emp_rec.ename||' '||                  
    
                  to_char(emp_rec.sal,'999,999'));                         
    
        end loop;                                                          
    
end;                                                                       
    
/                                                                          
    
show error                                                                 
    

SQL> set serveroutput on size 10000
SQL> exec mystoredproc
SQL> exec mystoredproc                                                     
    

ALLEN    1,600                                                             
    
WARD    1,250                                                              
    
MARTIN    1,250                                                            
    
BLAKE    2,850                                                             
    
TURNER    1,500                                                            
    
JAMES      950                                                             
    
                                                                           
    
PL/SQL procedure successfully completed.                                   
    

--------------------------------------------------------------

There is not any "INTO" in the context.

Good luck. Quentin.

Frank Meng <frankmeng_at_usa.net> escribió en artículo <365AC672.C13F90D1_at_usa.net>...
> I want to create a stored procedure for
> SELECT * from MYTABLE
> When I use BEGIN & END command, Oracle asked me using INTO.
> I don't want to INTO some table, but only display it on the screen.
> When I don't use BEGIN & END with SQL Plus 8.0, I got the message:
> -------------------------------------
> PLS-00103: Encountered the symbol "SELECT" when expecting one of
> the following:
> begin function package pragma procedure subtype type use ...
> -------------------------------------
> Someone told me I must use a cursor to select only one record each time
> and
> use INTO.
> I can't believe it, because I can do it very easily with Microsoft SQL
> Server.
> Please tell me what is wrong to frankmeng_at_usa.net
> Thanks in advance.
> Frank
>
>
Received on Tue Nov 24 1998 - 12:04:47 CST