aspose file tools*
The moose likes JDBC and the fly likes Unable to run a simple SP in SQL developer Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Unable to run a simple SP in SQL developer" Watch "Unable to run a simple SP in SQL developer" New topic
Forums: JDBC Oracle/OAS
Author

Unable to run a simple SP in SQL developer

Vivek Hingorani
Ranch Hand

Joined: Jul 08, 2011
Posts: 38
I created a simple test_sp

I have some records in the Employee table. Now I compile it using f5 and it compiles successfully.
while i right click on run and the window opens I am unable to add any parameter( name which I want to send to get details)
I have attached the screenshot as wel.
ALso if I run it thru Execute Vivek.TEST_SP '<any name>'

It gives me invalid SQL statement error. Can someone pitch in and help as I want to understand stored procedures in detail and am stuck on simple SP as of now.


[Thumbnail for sqldev.jpg]

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

I'm not sure about running the procedure via an SQL Developer form, but it should be possible with the call statement. You need to put the parameter list into parentheses, though:


(Execute might work too. I'm just more used to call in this context).
Vivek Hingorani
Ranch Hand

Joined: Jul 08, 2011
Posts: 38
Execute didnt worked,, I am attaching that error as well


[Thumbnail for err.jpg]

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Yeah. You don't define any parameter to your procedure, I've overlooked it.

You declare the (uninitialized) local variable v_name. You probably want to declare a parameter (say, p_name) and use that in the where condition of your select:


Vivek Hingorani wrote:Can someone pitch in and help as I want to understand stored procedures in detail and am stuck on simple SP as of now.

You might enjoy the following link then: http://docs.oracle.com/cd/E11882_01/server.112/e26088/toc.htm

Also check out all the development guides: http://www.oracle.com/pls/db111/portal.portal_db?selected=5

Oracle has really good documentation for the database. In my opinion, you don't really need other books. Be sure to read the Concepts Guide too, though. It covers the database basics and is the most important book for anyone dealing with Oracle database. (If you're on a different version of the database, find the docs corresponding to your version).
Vivek Hingorani
Ranch Hand

Joined: Jul 08, 2011
Posts: 38
Can you please provide the complete SP and how to run. I am still confused Martin.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Well, it doesn't work quite like that on this site.

Did you try to incorporate the changes I've advised to your code? Can you post what you have now and which difficulties you've met?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Unable to run a simple SP in SQL developer