To: Mahmood Hossain
On Thu, 22 Jul 1999, Mahmood Hossain wrote:
> Hi
>
> I've been trying to create a stored procedure to retrieve data from a very
> simple table. The table creating script is:
>
> create table student
> ( sid char(9) not null,
> name char(25),
> city char(15),
> state char(2),
> zip char(5),
> primary key(sid));
>
>
>
> First I was trying to create one with the following:
> ----------------------------------------------------
>
> CREATE PROCEDURE studat(id IN CHAR,sname OUT CHAR) AS
> BEGIN
> SELECT name INTO sname FROM student WHERE sid=id;
> END studat;
>
>
>
> I got the following response:
> -----------------------------
>
> SQL> start stuproc.sql;
> 6
> 7
> 8
> 9
>
>
> I stored the create procedure script in a file called stuproc.sql
>
>
>
> Then I tried to create one with the following:
> ----------------------------------------------
>
> CREATE OR REPLACE PROCEDURE studat() as
>
> DECLARE
> sname student.name%TYPE;
> CURSOR c_stu IS
> SELECT name INTO sname FROM student;
> BEGIN
> OPEN c_stu;
> LOOP
> FETCH c_stu INTO sname;
> EXIT WHEN c_stu%NOTFOUND;
> DBMS_OUTPUT.PUT_LINE('The student name is:'||sname);
> END LOOP;
> CLOSE c_stu;
> END;
>
>
> I got the following response:
> -----------------------------
>
> SQL> start stuproc1.sql;
> 16
> 17
> 18
> 19
> 20
>
>
>
> I don't know why it isn't working. It would be great if you can help in
> this regard.
>
> Take care.
>
> ______________________________________________________
> Mahmood Hossain Residence:
> Department of Computer Science
> Mississippi State University 26I Wallace Circle
> Starkville, MS 39762 Starkville, MS 39759
> Tel: 662-325-3349 Tel: 662-325-5994
> ______________________________________________________
>
Mahmood,
thanks for your email. I believe that your script needs to have a slash on
its own line at the end. For example, the first one should be:
CREATE PROCEDURE studat(id IN CHAR,sname OUT CHAR) AS
BEGIN
SELECT name INTO sname FROM student WHERE sid=id;
END studat;
/
Note the "/" at the end.
Best regards,
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 310+ Oracle tips, visit my Web Page: <->
<-> <->
<-> http://www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page