• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Ron McLeod
  • Tim Cooke
Sheriffs:
  • Devaka Cooray
  • paul wheaton
  • Mark Herschberg
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Frits Walraven
  • Jj Roberts
Bartenders:
  • Carey Brown
  • salvin francis
  • Piet Souris

Function for logged in user

 
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So im fairly new to sql so this function needs to be quite simple. My code example is this:



When i call it like this:

It should return 1 if found and 0 if not found. I just cant figure it out
 
Master Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
And what problems do you have with that code?
Does it compile/run.
If so what response do you get when you make that call?
 
Saloon Keeper
Posts: 23263
158
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't know what problem Hugo has, but I have a problem. This is a stored procedure implementation of the classic safe validation of login credentials. That is:

And unless the intent is to call it from some other stored procedure, it's doing the login check the hard way. Actually, it's probably over-complex even for a stored procedure.

Bust as I've said before, stored procedures are something that should be avoided whenever possible. It's better to use simple SQL.
 
Hugo Nilsson
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Dave: I get: SQL Statement ignored. And: ORA-00947: not enough values.
Tim: I understand what you say and i agree with you but i need to make it a function
 
Tim Holloway
Saloon Keeper
Posts: 23263
158
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I do have to wonder why you're doing all this work in stored procedure code if you are "fairly new to SQL". Stored procedures are in the moderate-to-advanced range, not beginning-level stuff.

Nevertheless. Look at my SQL and compare to the SQL in your procedural code. See the difference? It's why you're getting the "Not enough values" message. Again, Oracle is being less helpful than most newer databases are, but despite that the error does accurately, if not precisely indicate what's wrong.
 
Hugo Nilsson
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes sir i hear you. The thing is that i need to practice funtions(i thought this was mandatory stuff so maybe im no fairly new). So when a pnr and passwd get insertet and i call the function it should return 1 or 0 depending on how it went. Hope you understand it
 
Tim Holloway
Saloon Keeper
Posts: 23263
158
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ah. I understand. No, I go for years at a time without using stored procedures. If you are just starting with databases, don't bother about them. Stored procedures are just programs and many databases actually allow stored procedures to be written in a choice of languages. For example, use Java instead of PL/SQL.

SQL, on the other hand, is really tricky. Stuff that seems straightforward in a program can require a lot of thought to express in SQL. And since at the root, all database operations are SQL, it is therefore essential to know how to use SQL.

Here's a copy-and-paste of something I said about stored procedures a while back:

Tim Holloway wrote:
I don't recommend using stored procedures as a general rule. There's no standard for them. They vary considerably between different brands of databases. And if you don't think that's a problem consider that Amazon used to use Oracle extensively until last year, when they completely switched over to a different DBMS platform. Straight SQL is easier to transport, especially if you're using an ORM to front for it.

Maintaining source code control for stored procedures is also a problem. Often there is little or no versioning available for the code stored in the DBMS and tools to work with code versions can be hard to come by.

Use of stored procedures in application programs can also make the applications harder/more expensive to maintain, as you may end up going on "treasure hunts" to find out what logic is in the app and what logic is in the database (stored procedure).

So when should you use stored procedures?

Well in some cases, you cannot do what you need efficiently using plain SQL. In cases like that, code that runs entirely on the server may make it worth the disadvantages I outlined above.

Another instance is when you have a critical business process that operates according to a complex set of rules and you have many applications using those rules. In cases like that, it's certainly more efficient to keep one set of rules (the stored procedure) rather than having to maintain multiple copies of them.

Still another case would be where you have users who use some sort of command-line utility to issue database commands directly such as sql*plus and you want to make those commands simple and easy to use.

So there are some good reasons for using stored procedures. But try to keep them to a minumum. Because, as I said there are some very good reasons not to.

 
Hugo Nilsson
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Im sorry to be a pain in the butt. This is a school task(maybe i should have said that earlier) so i really need to do it
 
Tim Holloway
Saloon Keeper
Posts: 23263
158
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
School is forgivable. It is, as I said, useful to know stored procedures. Just that in actual practice, it's good to know when to avoid them.

But again, look at the difference between my SQL statement and the SQL Select that you coded and see if you can spot what caused the error.
 
Listen. That's my theme music. That's how I know I'm a super hero. That, and this tiny ad told me:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic