Write an Oracle procedure

Question

I must create an oracle procedure to display a list of persons (parlimentaries) with an index for tuples.

For now, I wrote this piece of code (I haven't implemented the index)

create or replace procedure parlamentarieslist as
    begin

    select
        ssn,
        name, 
        surname,


    from  
        parlimentaries p, 
        mandate m 
    where 
        p.ssn = m.parlamentaries AND m.legislature= (select
        max(legislature) "m" 
        from mandate);
end parlamentarieslist;

However, oracle give me these errors

 Error(5,3): PL/SQL: SQL Statement ignored
 Error(12,3): PL/SQL: ORA-00936: missing expression

Why?

Because, INTO clause is missing, and as type there's an extra comma after `surname` in the uppermost select list. By the way try to use ANSI JOIN instead of joining by commas. — Barbaros Özhan, Jun 23 '18 at 13:27
[See the documentation](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/static-sql.html#GUID-93E44519-5370-4E5B-B157-4C12509DFDEF) to get you started. — Alex Poole, Jun 23 '18 at 13:57
INTO should be stated just before the first `from` clause. By the way I had a `typo` as I wrote `type` instead of `typo` :) — Barbaros Özhan, Jun 23 '18 at 14:05
you can do what you want with SQL. PL/SQL assumes if you're running a query, you must want to do something with it - like process the results. That's why you need an INTO. Once the results are in an object, you can perform actions against it. Even if that's to print it out using HTP or DBMS_OUTPUT...but if you can do something in SQL, do it in SQL. — thatjeffsmith, Jun 23 '18 at 15:01
Possible duplicate of [PLS-00428: an INTO clause is expected in this SELECT statement](https://stackoverflow.com/questions/25486543/pls-00428-an-into-clause-is-expected-in-this-select-statement), or any of these: https://stackoverflow.com/search?q=%5Bplsql%5D+an+into+clause — William Robertson, Jun 23 '18 at 16:43
*"The extra comma is an error of copy."* Programming is all about correctness, which means paying attention to the details. Asking questions on StackOverflow is a good opportunity to practice correctness. If you don't post code which accurately reflects the code that is giving you grief you're wasting everybody's time, including your own (because you're less likely to get a helpful answer in a timely fashion). — APC, Jun 24 '18 at 13:27

score 0 · Answer 1 · answered Jun 23 '18 at 15:08

As I mentioned before in the comment part, the problem is due to

the missing INTO clause
existing typo(comma) after surname column in the uppermost select list.

Mostly, Procedures are used to return one column or single row and in results of SELECT statements may be returned to the output parameters by INTO clause. But, If you want to return list of persons (multi-rows), the following style may be more suitable :

    SQL> set serveroutput on;
    SQL> create or replace procedure parlamentarieslist as
    begin
     for c in
     (  
     select p.ssn, p.name, p.surname,
            max(m.legislature) over (partition by p.ssn ) m
       from parlimentaries p inner join mandate m
         on ( p.ssn = m.parlamentaries )
      order by m.legislature desc
     )
     loop
      dbms_output.put_line(' SSN : '||c.ssn||' Name : '||c.name||' Surname : '||c.surname); 
     end loop;  
    end parlamentarieslist;
    /
    SQL> exec parlamentarieslist;

Where Use a SQL of explict ANSI JOIN style, instead of old-fashioned comma seperated JOIN style.

Your solution was a source of inspiration. I solved my problem without into clause and I added the row's number in this mode: create or replace PROCEDURE parlamentarieslist AS BEGIN FOR c IN (SELECT rownum, p.ssn, p.name, p.surname FROM parlamentaries p, mandate m WHERE p.ssn = m.parlamentaries AND m.legislature = (SELECT MAX(legislature) "m" FROM mandate ) ) LOOP dbms_output.put_line('n°'||c.rownum||' SSN : '||c.ssn||' Surname : '||c.surname|| ' Name : '||c.name); END LOOP; END parlamentarieslist; — keiichi, Jun 24 '18 at 13:42
Your solution was a source of inspiration. I solved my problem without into clause and I added the row's number with a virtual column rownum but my solution is a little difference from your. I prefered use my where clause because now it is more understable for me. Thanks — keiichi, Jun 24 '18 at 13:53

Write an Oracle procedure

1 Answers1