Home » SQL & PL/SQL » SQL & PL/SQL » What is wrong with this procedure
What is wrong with this procedure [message #245565] Mon, 18 June 2007 02:39 Go to next message
Superchick
Messages: 5
Registered: May 2007
Location: Singapore
Junior Member
Hi Guys,

Im trying to write a stored procedure.. Just for testing I did this one:

CREATE OR REPLACE PROCEDURE demoproc
AS
BEGIN
Select sysdate from dual;
END demoproc;

When I run this it says:
Warning: execution completed with warning
PROCEDURE demoproc Compiled.

If I execute with this: Execute demoproc;
I get: Invalid SQL Statement.

I have searched everywhere. I can't find the answer. Please help?

Oh one more thing I noticed is that the procedure is marked as invalid. Is this because it has warnings on compilation? How do I get it valid?

I would really appreciate any help.



Re: What is wrong with this procedure [message #245572 is a reply to message #245565] Mon, 18 June 2007 02:55 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

you need to use the "into clause" in your select statement.
something like :

select sysdate into l_var_name from dual;


regards,
Re: What is wrong with this procedure [message #245587 is a reply to message #245565] Mon, 18 June 2007 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please always post your Oracle version (4 decimals).
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.

Please copy and paste your execution (in a formatted way in addition of describing it.

I recommend you to read PL/SQL User's Guide and Reference

Regards
Michel
Re: What is wrong with this procedure [message #245595 is a reply to message #245587] Mon, 18 June 2007 03:31 Go to previous messageGo to next message
Superchick
Messages: 5
Registered: May 2007
Location: Singapore
Junior Member
Hi Guys,

Thank you for your responses.

dhananjay: Is there then no way I can just select something in a procedure?
How do you then have a procedure that shows the results of a query?

Michael: I have read the PL/SQL guide.
But I didn't find a very clear answer on what I was looking for. There is no sample of how to select anything
in a stored procedure.
Unless I missed it.
Could you be more specific?

Im using Oracle 10 G. How do I see the exact 4 digits?

I really appreciate your help.

Thank you,

Re: What is wrong with this procedure [message #245603 is a reply to message #245595] Mon, 18 June 2007 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I opened the documenation, clicked on "Index", clicked on "S", found "SELECT INTO statement", "syntax", clicked on it and got the page: SELECT INTO Statement with many examples.

Quote:
Im using Oracle 10 G. How do I see the exact 4 digits?

Few ways:
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod

1 row selected.

SQL> def _O_RELEASE
DEFINE _O_RELEASE      = "1002000300" (CHAR)
SQL> def _O_VERSION
DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options" (CHAR)
SQL> declare
  2     vers varchar2(30);
  3     comp varchar2(30);
  4  begin
  5     dbms_utility.db_version(vers,comp);
  6     dbms_output.put_line('version: '||vers);
  7  end;
  8  /
version: 10.2.0.3.0

PL/SQL procedure successfully completed.

Regards
Michel
Re: What is wrong with this procedure [message #245605 is a reply to message #245603] Mon, 18 June 2007 03:57 Go to previous messageGo to next message
Superchick
Messages: 5
Registered: May 2007
Location: Singapore
Junior Member
Hi Michael,

Thanks once again. So to make that into a stored procedure.
Is this then the right way?

Create procedure MyVerion
as
declare
2 vers varchar2(30);
3 comp varchar2(30);
4 begin
5 dbms_utility.db_version(vers,comp);
6 dbms_output.put_line('version: '||vers);
7 end;
End MyVerion;

Execute MyVerion;

If I create this procedure. I still get the same error which is:

Invalid SQL Statement.

Thanks.
Re: What is wrong with this procedure [message #245615 is a reply to message #245605] Mon, 18 June 2007 04:05 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus, copy and paste your screen.
Format your code (follow the link I posted).

DON'T YOU SEE WHAT I DID?
Isn't it easier to read it?
Doesn't this show you EXACTLY what I executed?
How can I know what you did?

Regards
Michel

[Updated on: Mon, 18 June 2007 04:06]

Report message to a moderator

Previous Topic: Compare Database users
Next Topic: Problem in query........
Goto Forum:
  


Current Time: Thu Dec 08 04:04:03 CST 2016

Total time taken to generate the page: 0.16204 seconds