Home » SQL & PL/SQL » SQL & PL/SQL » Test Case Builder (V11+)
Test Case Builder [message #568553] Sat, 13 October 2012 18:11 Go to next message
BlackSwan
Messages: 21945
Registered: January 2009
Senior Member
http://optimizermagic.blogspot.com/2008_03_01_archive.html

     dbms_sqldiag.export_sql_testcase(
       directory  => 'TCB_DIR_EXP',
       sql_text   => 'select count(*) from sales',
       testcase   => tco)



I just came across the above.

It might prove helpful here; then again it might not.

Enjoy!
Re: Test Case Builder [message #568562 is a reply to message #568553] Sun, 14 October 2012 03:16 Go to previous messageGo to next message
John Watson
Messages: 4082
Registered: January 2010
Location: Global Village
Senior Member
I teach dbms_sqldiag in the 11g New Features course, but I've never actually used it.
Let's try it out. Here is the scenario:

OP: This query is too slow. What must I do to fix it? Urgent!
Me: Why is it urgent for me to fix your problem?
OP: We do not pay for support, so I need free support from you.
Me: What does your DBA say?
OP: I am employed as the DBA, but I am in fact an ice cream salesman.
Me: How did you get the job?
OP: I paid a friend to do the interview for me.
Me: Have you had any training?
OP: I have memorized some illegal brain dumps, and slept with a copy of the docs CD under my pillow.
Me: I see. You had better upload a SQL Test Case.

Testcase generated like this:

declare tco clob;
begin
dbms_sqldiag.export_sql_testcase(
directory=> 'TESTCASE',
sql_text=>'select dname from emp natural join dept group by deptno,dname having sum(sal) > 10000',
user_name => 'SCOTT',
exportdata => false,
testcase => tco);
end;
/


I tried to do a testcase based on a realistic EBS query, but the export file was too big.
  • Attachment: testcase.zip
    (Size: 62.15KB, Downloaded 21 times)
Re: Test Case Builder [message #568657 is a reply to message #568562] Mon, 15 October 2012 09:31 Go to previous messageGo to next message
joy_division
Messages: 4454
Registered: February 2005
Location: East Coast USA
Senior Member
John Watson wrote on Sun, 14 October 2012 04:16

Me: How did you get the job?
OP: I paid a friend to do the interview for me.


John, I know your post was tongue in cheek (a little, but with some truth to it). This never even crossed my mind. Do you think this is a common occurrence, that people pay someone else to interview for them either on the phone or in person?
I know I never even stretch the truth on my resume for fear I will be asked a question about something I said I was an expert on, only to be exposed.
I have however seen all too many times people who cannot answer the most basic of questions claiming 5+ years of experience.

Sorry to post this here rather than in General, but I didn't want to move the topic based on BlackSwan initial post.
Re: Test Case Builder [message #568659 is a reply to message #568657] Mon, 15 October 2012 09:49 Go to previous messageGo to next message
John Watson
Messages: 4082
Registered: January 2010
Location: Global Village
Senior Member
I think that some of the body shop agencies and off-shore suppliers may have a tendency to present better qualified staff when bidding for work than they actually put on the job when they have won it. One reason I like working with Americans is that (generally speaking - there are of course exceptions) standards for business ethics are higher than in some other parts of the world: people tend to be straightforward and honest. Relatively speaking, anyway.

But in the meantime, did you try to download and re-create my test case? Did it actually work?? If so, it could be a pretty useful facility.
Re: Test Case Builder [message #568660 is a reply to message #568659] Mon, 15 October 2012 10:12 Go to previous messageGo to next message
joy_division
Messages: 4454
Registered: February 2005
Location: East Coast USA
Senior Member
John Watson wrote on Mon, 15 October 2012 10:49

But in the meantime, did you try to download and re-create my test case? Did it actually work?? If so, it could be a pretty useful facility.


Unfortunately I do not have an 11g database to test with.
Re: Test Case Builder [message #568661 is a reply to message #568660] Mon, 15 October 2012 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 21945
Registered: January 2009
Senior Member
>Unfortunately I do not have an 11g database to test with.
It is trivial to install VirtualBox on Windows PC & then load preconfigured V11 DB into it.
Re: Test Case Builder [message #568671 is a reply to message #568661] Mon, 15 October 2012 13:02 Go to previous message
Littlefoot
Messages: 18830
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right, but the host PC has to be powerful enough to actually run a virtual machine having Oracle installed on it. From my own experience, a dual core processor, 2 GB RAM and a decent HD (160 GB) is the minimum. For example, I successfully installed VirtualBox onto an older PC under Windows 7, put XP onto the virtual machine, it went up & was running ... kind of - eating 100% CPU and it was absolutely unusable.
Previous Topic: How to join two where?
Next Topic: Automation script related to creation of tablespace
Goto Forum:
  


Current Time: Sat Apr 19 07:10:20 CDT 2014

Total time taken to generate the page: 0.06588 seconds