Home » SQL & PL/SQL » SQL & PL/SQL » stored procedures
stored procedures [message #184822] Fri, 28 July 2006 03:52 Go to next message
balmcacit05
Messages: 6
Registered: July 2006
Junior Member
whenever i try to create a stored procedure in isqlplus it is showing me the following error

Warning:Procedure created with compilation errors.

i tried to create a simple stored procedure which is as follows

create or replace procedure proc1
begin
select * from tab;
end;

i read the reply to another user who faced the similar problem but still i require the clarification as to why it could not be done can somebody help out please
Re: stored procedures [message #184824 is a reply to message #184822] Fri, 28 July 2006 04:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A useful diagnostic tool is:
SELECT * 
FROM   user_errors
WHERE  name = <name of the thing that's broken>; 


In this particular case, I suspect that you need to add and IS or AS (your choice).
You'll then need to define set of variables or a rowtype to select the data from TAB into.

Re: stored procedures [message #184830 is a reply to message #184824] Fri, 28 July 2006 04:25 Go to previous messageGo to next message
balmcacit05
Messages: 6
Registered: July 2006
Junior Member
sir,
thanks for your immediate reply. sir actually the problem is i am a student and i do not have access to sqlplus so all everything i have to do only through isqlplus .

i am able to create triggers, views etc but only for procedures i get the error messages help me out
Re: stored procedures [message #184833 is a reply to message #184830] Fri, 28 July 2006 04:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Did you actually try running the query I sugested?
What did it return?

SELECT * 
FROM   user_errors
WHERE  name = <name of the thing that's broken>; 
Re: stored procedures [message #184835 is a reply to message #184822] Fri, 28 July 2006 04:34 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
obviousely you would get an error, because inside the proc you are just selecting the records. but you did not store the selected records in any variable/record types.

please modify the proc as below

create or replace procedure proc1
is
tab_rec tab%ROWTYPE;
begin
select t.* INTO tab_rec from tab t;
dbms_output.put_line(tab_rec.table_column_name);
end;
/

please make sure that your table tab is having only one records. if it is more than one you have to manipulate differently because you will get an error message. otherwise just limit the rows return using rownu<2 or any conditions.

[Updated on: Fri, 28 July 2006 04:35]

Report message to a moderator

Re: stored procedures [message #184837 is a reply to message #184835] Fri, 28 July 2006 04:47 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem with your response @gold_orcl is that you have told him how to fix the problems with his code.
You haven't told him how to find out what the problems with his code are in the first place.
The OP needs to be able to work these things out for themselves, otherwise we will be endlesly innundated with the most trivial problems (<Insert obvious sarcastic comment here>)
Previous Topic: primary- foreign keys problem...helppppppppppp
Next Topic: Analyze table problem in oracle
Goto Forum:
  


Current Time: Sat Dec 03 17:58:03 CST 2016

Total time taken to generate the page: 0.11438 seconds