Home » SQL & PL/SQL » SQL & PL/SQL » compiling a stored procedure
compiling a stored procedure [message #35745] Thu, 11 October 2001 11:31 Go to next message
Rahul
Messages: 94
Registered: December 1998
Member
hi
i have oracle 9.0.1 installed on sun ultra ox.

I am trying to create a simple stored procedure as below:

create or replace procedure test (id in number)
is
begin
select name from product_version where id = id;
end test;

Now when i try to compile it using either:

SQLPLUS>@test
or
sqlplus <name>/<password> @test

it just sits there without doing anything
and if then i type in /.. it says "Warning: Procedure created with compilation errors."

Pls let me know why this behaviour??
thanx
rahul

----------------------------------------------------------------------
Re: compiling a stored procedure [message #35746 is a reply to message #35745] Thu, 11 October 2001 11:36 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
where is INTO clause in your code?
create or replace procedure test (id in number)
l_name productt_version.name%type;
is
begin
select name into l_name from product_version where id = id;
end test;

----------------------------------------------------------------------
Re: compiling a stored procedure [message #35748 is a reply to message #35745] Thu, 11 October 2001 12:10 Go to previous message
Robert Moy
Messages: 15
Registered: December 2000
Junior Member
Hello:

Below is example of a way to use procedure

SQL> create or replace procedure p2(
2 enoin in emp1.eno%type:=&a,
3 ageout out emp1.age%type)
4 is
5 begin
6 select age into ageout from emp1 where eno=enoin;
7 end;
8 /
old 2: enoin in emp1.eno%type:=&a,
new 2: enoin in emp1.eno%type:=12,

Procedure created.

SQL> declare
2 enoin2 emp1.eno%type:=&a;
3 ageout2 emp1.age%type;
4 begin
5 p2(enoin2,ageout2);
6 dbms_output.put_line('My age is ' || ageout2);
7 end;
8 /
old 2: enoin2 emp1.eno%type:=&a;
new 2: enoin2 emp1.eno%type:=12;
My age is 34

Good Luck

----------------------------------------------------------------------
Previous Topic: Coping database objects from one schema to another - Urgent Please help
Next Topic: No of rows
Goto Forum:
  


Current Time: Sat May 25 08:21:36 CDT 2024