SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 5 of 5

Thread: PL/SQL Testing

  1. #1
    Junior Member
    Join Date
    May 2003
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    PL/SQL Testing

    1)Can any one suggest pl/sql testing checklist?
    2)I have some 2-3 main stored procedure, which are very big and they call 2-3 stored procedures and has nearly 150 tables. I wanted to know how to test pl/sql and the best methods, and any standard methods to test them.
    Thanks [img]images/icons/rolleyes.gif[/img]

  2. #2
    Moderator JakeBrake's Avatar
    Join Date
    Dec 2000
    Location
    St. Louis - Year 2025
    Posts
    15,609
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: PL/SQL Testing

    If you are going to view the code and create tests to exercise that code, I would suggest that you go to the unit testing forum.


    http://www.qaforums.com/cgi-bin/foru...ubb=forum;f=35

    If not, then it may be best to determine your high level test requirements and work them into test cases - from a non-code view.

    Some of many questions to answer as you journey into this:
    Do you need to verify:
    Tables copied?
    New tables?
    Deleted tables?
    New colums?
    Column widths?
    Data types?
    Nulls allowed - not null?
    Unique identifiers?
    Custom identifiers that roll over?
    Deleted columns?
    Foreign key integrity?
    Orphaned records?
    Record counts?
    Date/Time stamp agreement?
    Column padding?
    Justification?
    Query times?
    Permissions/Grants?
    If/Else/Case logic?

    These are a few of many things to determine. If this is where you are headed, then you are probable going to end up writing queries of your own to validate any/all of the above - or more.

    Perhaps you could cite a specific example of what you believe you need to do and we can provide more help.

    It seems that this is a candidate for automation. Do you have a commercial automation tool that is typically associated with this type of testing?

    If so, I believe most of them support database access. If you do not have a tool of this calibre, do have VB, VBScript or MS-Access? If so, this type of testing can be automated in those tools. Also, you can automate in PL/SQL by writing your own stored procs.

    [ 05-27-2003, 05:10 PM: Message edited by: jpensyl ]

  3. #3
    Junior Member
    Join Date
    May 2003
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: PL/SQL Testing

    Originally posted by jpensyl:
    If you are going to view the code and create tests to exercise that code, I would suggest that you go to the unit testing forum.


    http://www.qaforums.com/cgi-bin/foru...ubb=forum;f=35

    If not, then it may be best to determine your high level test requirements and work them into test cases - from a non-code view.

    Some of many questions to answer as you journey into this:
    Do you need to verify:
    Tables copied?
    New tables?
    Deleted tables?
    New colums?
    Column widths?
    Data types?
    Nulls allowed - not null?
    Unique identifiers?
    Custom identifiers that roll over?
    Deleted columns?
    Foreign key integrity?
    Orphaned records?
    Record counts?
    Date/Time stamp agreement?
    Column padding?
    Justification?
    Query times?
    Permissions/Grants?
    If/Else/Case logic?

    These are a few of many things to determine. If this is where you are headed, then you are probable going to end up writing queries of your own to validate any/all of the above - or more.

    Perhaps you could cite a specific example of what you believe you need to do and we can provide more help.

    It seems that this is a candidate for automation. Do you have a commercial automation tool that is typically associated with this type of testing?

    If so, I believe most of them support database access. If you do not have a tool of this calibre, do have VB, VBScript or MS-Access? If so, this type of testing can be automated in those tools. Also, you can automate in PL/SQL by writing your own stored procs.
    <font size="2" face="Verdana, Arial, Helvetica">1)I need to test record counts.
    2)Date/Time stamp agreement.
    3)Data types
    and also the logic part which is involed in the PL/SQL flow.
    We do not use any automated tool. can you please suggest the automation by using Ms-Access,VB and writing own stored procs.
    Thanks

  4. #4
    Moderator JakeBrake's Avatar
    Join Date
    Dec 2000
    Location
    St. Louis - Year 2025
    Posts
    15,609
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: PL/SQL Testing

    Any of those tools can be used. You may already have MS-Access with VBA. If you have MS-Office, there's a chance you have Access.

    Starting with record counts, you need to know when you would count relative to other procs running. In the case of comparing table counts - you simply count one, then the other - assuming you will compare counts before and after a stored proc runs and on the same or mirrored tables.

    For date/time stamp, generally you would compare as well. Certain date/time functions can pose a challenge with the way the date/time are stored. Odds are you have some date/time diff functions that you can call in PL.

    For data types, you may want to use some of the db admin utilities that provide descriptions of tables/columns, etc. I'm sure there are many methods. I would recommend looking for a function or creating a stored proc to dump descriptors to a delimited file and then repeat this as needed to compare expected with actual results. Or there might be something that allows you to view the descriptions in a spreadsheet-like format, that you can save as a baseline for later comparison after stored proc processing.

    All kinds of possibilites here. I hope this has helped.

    Here is a short test to see if you have VBScript. 1) Open up NotePad.
    2) Type only the following:
    msgbox "I have vbscript."

    Save/Close this as c:\myvbapp.vbs

    Using a file browser - locate this file and double-click on it to run it. If you get the popup, you have vbscript.

    With vbscript, you can connect to databases, run queries, do file i/o and the list goes on.

  5. #5
    Advanced Member
    Join Date
    Jan 2002
    Location
    ma
    Posts
    531
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: PL/SQL Testing

    My last job, we did exactly that, where we tested hundred of stored procedures. For that, we used perl's DBI interface as it offered 99.9% of what we needed. The only limitation, at that time, was it could not call stored procedures that required BLOBs directly.

    You can validate all that, as long as you can code it. If it's a strict record comparison, you can do it quickly. Also, you can test the individual stored procedure that it calls as well.

    DBI supports multiple databases and if you write an abstration layer between the database and the test code itself, you will limit re-work required, if the project's database supports expands, or changes.

    Iggy

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 11.54%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 03:54 AM.

Copyright BetaSoft Inc.