Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    57

    Unanswered: when to use view, function and when to code in the SQL query?

    hey all,
    what are the advantages/disadvantages of using Oracle views or function as opposed to simply putting the SQL query for the action you want to perform in your code? let me clarify with an example: i am working on a VB.NET application and i need to get a count on a field from one of my Oracle 8.1.7 database tables. Now, i could simply put the SQL query in my VB.NET code or alternatively i could create an Oracle View or a Function to get the same result. The query is kind of big, it pulls from 4 tables and has a bunch of ANDs. Any tips? Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: when to use view, function and when to code in the SQL query?

    When writing VB it is good practice to use stored procedures and functions rather than embed the SQL directly in the app. This makes maintenance/tuning much easier in the long run.

    And never concatenate user-supplied values into the procedure calls or SQL in your VB - always use bind variables (prepared statements).

    i.e. don't do this:

    sql = "myproc(" + intEmpID + ",'" + strName + ")"

    Do this:

    sql = "myproc(?,?)"

  3. #3
    Join Date
    Oct 2003
    Posts
    57
    I agree in principle, but how do you know when an SQL statement is too big to be embedded in the VB code? For an example, it would be wasteful to write an Oracle function for something like this: "SELECT TEXT FROM MYTABLE WHERE COLOR = RED".

    I think that as long as you can do what you want to do in a single SQL query, and provided that query is shorter than..say 10 lines, it'd probably be a good idea to embed it in the VB code. What do you think?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well I guess it depends on how "serious" your system is. If you are just "knocking up" a simple database application, then maybe embedding SQL code in your VB is OK. But if this is a system on a medium to large scale (like more than 1 developer involved), I would definitely go for a clear separation between database code (in packages) and application code (in VB) - no question.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •