• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Unable to run a simple SP in SQL developer

 
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
sqldev.jpg
[Thumbnail for sqldev.jpg]
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Execute didnt worked,, I am attaching that error as well
err.jpg
[Thumbnail for err.jpg]
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 45
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you please provide the complete SP and how to run. I am still confused Martin.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Heroic work plunger man. Please allow me to introduce you to this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic