Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136

    Unanswered: Some useful tips for developing of intranet application with Oracle database

    Hello All!

    i have to develope a web-based Oracle-Database application with ASP.NET Technology.
    My whole logical programm-part (Functions, Procedures..) i've already realized in my Oracle Database.
    My whole (Grafical) User Interface i want to realize with .NET-Technology, in that i don't have any expierience.

    Some features my application have to do:
    - Read from ascii-file and input this data into DB
    - Export data from DB to ascii-File
    - Administration of DB-Data using my (later) .NET-Application (Logical part already exists).


    It would be great if you could give some tips & links for me!

    Thanks in advance!
    Last edited by julla27; 05-10-04 at 10:02.
    Regards,
    Julia

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The first important point, which you seem to already have covered, is to put all of your database logic in stored packages, so that your .NET application just handles the user interface side of things.

    The next important point it to ensure that you always use bind variables. This means using "prepared statements", which look something like this:

    strSQL = "select ename from emp where empno = ?" /* Good */

    and not concatenating values into the string like this:

    strSQL = "select ename from emp where empno = " & intEmpno /* Bad */

    I have written some ASP but am no expert. You should probably pick up one of the many books written about ASP programming that covers database access using ADO; but beware: these are often written from an ASP programmer point of view (of course), and they couldn't care less about things like using stored procedures and bind variables!

  3. #3
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hello Andrewst,

    thank you very much for your answer!

    One thing i want to concretise is:

    The next important point it to ensure that you always use bind variables. This means using "prepared statements", which look something like this:

    strSQL = "select ename from emp where empno = ?" /* Good */

    and not concatenating values into the string like this:

    strSQL = "select ename from emp where empno = " & intEmpno /* Bad */
    You mean i should to use bind variables.

    If i have a procedure like this:
    PHP Code:
    CREATE OR REPLACE PROCEDURE PRC_INS_UPD_TEST
    (
           
    pName     VIEW_TEST.V_NAME%TYPE,
           
    pDesc     VIEW_TEST.V_DESCRIPTION%TYPE,
           
    pComm      VIEW_TEST.V_COMMENTS%TYPE
    )
    AS
           
    LIC       NUMBER;
    BEGIN     
         
        UPDATE VIEW_TEST SET V_DESCRIPTION 
    pDesc,  
                                
    V_COMMENTS pComm 
               WHERE V_NAME 
    pName;
               
        IF 
    SQL%ROWCOUNT 0 THEN
             INSERT INTO VIEW_TEST 
    (V_NAMEV_DESCRIPTIONV_COMMENTS
                   
    VALUES (pNamepDescpComm);
        
    END IF;
    END;

    Must i modify it into:

    PHP Code:
    CREATE OR REPLACE PROCEDURE PRC_INS_UPD_TEST
    (
           
    pName     VIEW_TEST.V_NAME%TYPE,
           
    pDesc     VIEW_TEST.V_DESCRIPTION%TYPE,
           
    pComm      VIEW_TEST.V_COMMENTS%TYPE
    )
    AS
           
    LIC       NUMBER;
    BEGIN     
         
        UPDATE VIEW_TEST SET V_DESCRIPTION 
    = ?,  
                                
    V_COMMENTS = ? 
               
    WHERE V_NAME = ?;
               
        IF 
    SQL%ROWCOUNT 0 THEN
             INSERT INTO VIEW_TEST 
    (V_NAMEV_DESCRIPTIONV_COMMENTS
                   
    VALUES (?, ?, ?);
        
    END IF;
    END;


    Is it correct?


    Thanks in advance!
    Regards,
    Julia

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, I didn't mean that. Your procedure was fine - PL/SQL always uses bind variables automatically - and PL/SQL won't understand "?, ?, ?"!

    The issue is when you are writing VBScript or whatever in your .NET program - like when you call this procedure. It is then that you must use Parameter objects to bind values instead of constructing strings from your data values like:

    strSQL = "PRC_INS_UPD_TEST (''" & strName & "'',''" * strDesc & "'',''" * strComments & "'')" /* Bad */

    Apart from not using bind variables, that is also hell to read. This is much easier, isn't it? :

    strSQL = "PRC_INS_UPD_TEST (?, ?, ?)"

    ...followed by the creation of 3 parameters to bind strName, strDesc and strComments to the arguments.

  5. #5
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Ok, then i'm calmed down!(I thought i must alter each procedure, function, package on my database!)
    Now i have just to learn asp.net...

    Andrewst, Thank you very much for your help!
    Regards,
    Julia

  6. #6
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89
    Its quite good that you are going to use .NET technology which is web based and more user friendly. There is something called as PL/SQL Server Pages (PSP) in Oracle which has been used very less by people and even Oracle Corporation sidelined the technology when it started supporting JSP.
    Thanks and Regards,

    Praveen Pulikunnu

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    My tool of choice would be Oracle's HTML DB. You can try it out for free here:

    http://otn.oracle.com/products/datab...ldb/index.html

  8. #8
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hello Praveen and andrewst,

    thanks for your tips.
    I'll try it.
    Regards,
    Julia

  9. #9
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hello All!

    Yesterday i heard that my application must be platform independent,
    it must be able to run on Windows(NT/XP/2000...) and Unix -stations.

    Now i have the next question:

    Is ASP.NET the ideal solution for the GUI of my application?
    What languages would you prefer?

    Thanks in advance!
    Regards,
    Julia

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I believe .NET will only run on Windows, since it is a MS product. Perhaps you need to look at something like Apache, which I think is open source? (I may be wrong!)

    Also, I mentioned Oracle's HTML DB tool earlier. This provides a browser-based interface to the database, and I would be very surprised if it wasn't platform-independent. Of course, it does tie you to Oracle as the DBMS!

Posting Permissions

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