Blogger List

Steven Feuerstein Indicates Oracle ACE director status
PL/SQL Obsession

Guy Harrison Indicates Oracle ACE status
Database topics

Bert Scalzo Indicates Oracle ACE status
Toad for Oracle, Data Modeling, Benchmarking
Dan Hotka Indicates Oracle ACE director status
SQL Tuning & PL/SQL Tips

Valentin Baev
It's all about Toad

Ben Boise
Toad SC Discussions

Dan Clamage

Kevin Dalton
Benchmark Factory

Peter Evans 
Business Intelligence, Data Integration, Cloud and Big Data

Vaclav Frolik  
Toad Data Modeler, Toad Extension for Eclipse

Devin Gallagher
Toad SC discussions

Stuart Hodgins
JProbe Discussions

Julie Hyman
Toad for Data Analysts

  Henrik "Mauritz" Johnson
Toad Tips & Tricks on the "other" Toads
  Mark Kurtz
Toad SC discussions
Daniel Norwood
Tips & Tricks on Toad Solutions
Amit Parikh
Toad for Oracle, Benchmark Factory,Quest Backup Reporter
Debbie Peabody
Toad Data Point
Gary Piper
Toad Reports Manager
John Pocknell
Toad Solutions
Jeff Podlasek
Toad for DB2
Kuljit Sangha
Toad SC discussions
Michael Sass 
Toad for DB2
Brad Wulf
Toad SC discussions
Richard To
SQL Optimization
  Toad Data Modeler Opens in a new window
Data Modeling
  Toad Higher Education
How Hi-Ed Uses Toad
  Real Automated Code Testing for Oracle
Quest Code Tester blog


Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies.  Have some views of your own to share?  Post your comments!  Note:  Comments are restricted to registered Toad World users.

Do you have a topic that you'd like discussed?  We'd love to hear from you.  Send us your idea for a blog topic.

Feb 3

Written by: QCTO Blog
Thursday, February 03, 2011  RssIcon

Written by Finn Ellebaek Nielsen

This blog post is a follow up to my blog post Continuous Integration (CI), in which I described how to execute test cases in Code Tester (CT) in a CI environment. What I didn't explain in the afore mentioned blog post is how to deploy the test definitions (TD), only how to execute them. Some users have notified in the forums that I missed out on deployment and this blog post rectifies this. 

This blog post assumes that you're using the last beta of CT ( but also offers some hints on how to accomplish the same with earlier versions.

Command Line

You can import a given TD through the following command line parameter: /i or /Import.

However, there are several issues with this approach:

  • Prior to CT 2.0.0 the exit code was never reflecting errors that occurred -- it always returned zero.
  • Prior to CT 2.0.0 the application could show message boxes reflecting issues with the import, which obviously isn't useful in an automated environment.
  • The command line of the Windows application isn't easily available from CI software running on other OSs like Linux or Solaris.

However, if you do have access to the Windows application in your CI environment and if you're using CT 2.0.0 or newer, you can make use of the new CT command line interface offered by the new executable QctoCmd.exe, which differs from the "old" command line offered through the GUI executable QuestCodeTesterOracle.exe (or QctoBeta.exe) in the following ways:

  • It is a Windows Console application, such that you will no longer get GUI message boxes popping up with error messages.
  • The exit code is now non-zero in case of an error, always zero for success. The error codes are documented in the on-line help system.

Example command line for 2.0.0 for importing an XML file (note how you no longer need the /Close parameter that you had to include previous to 2.0.0 in order to close down the application after the operation):

"C:\Program Files (x86)\Quest Software\Quest Code Tester for Oracle 2.0.0 Beta 5\QctoCmd.exe" 
/u=QCTO200B5 /p=o112 /d=o112 /DBHome=o112_32 /i=C:\MyProject\tests\Q##MY_FUNCTION.xml /po=SCOTT /to=QCTO200B5

which imports the file C:\MyProject\tests\Q##MY_FUNCTION.xml into the repository owned by QCTO200B5, test program generated in the schema QCTO200B5 and program code is owned by SCOTT.

Standard output from the command line shown above could be:

Quest Code Tester for Oracle command line utility. Version

Importing started
   C:\MyProject\tests\Q##MY_FUNCTION.xml - SUCCESS
Importing ended
Exit code 0

Calling with a parameter of /? or /h will show all the available parameters.


CT 2.0.0 added a new PL/SQL API that is readily available on any Oracle-supported platform where you have SQL*Plus installed. Prior to CT 2.0.0 the XML import was handled by the Windows client application.

There is one top-level API you can call to import the XML -- you "just" need to get the XML into a CLOB, set some options and then import. This is demonstrated in the following. Also, two different ways of reading the file into a CLOB are demonstrated, depending on where you can access the XML file from.

In any case, it's important that the XML file is transferred binary from where it was exported to where it's referenced for import, in order to avoid issues with unwanted conversions of CR/LF in string literals etc.

Access to XML File on Database Server

You can use BFILE and DBMS_LOB.LOADCLOBFROMFILE to read a file into a CLOB. Here's an example SQL*Plus script that reads a given file into a CLOB SQL*Plus variable, assuming that you have read access to an Oracle directory MY_PROJECT_TESTS that in turn points to an OS directory like C:\MyProject\tests (Windows) or /user/ci/myproject/tests (Linux, UNIX):

variable xml_import clob

 file       bfile := bfilename('MY_PROJECT_TESTS', 'Q##MY_FUNCTION.xml');
  dst_offset integer := 1;
  src_offset integer := 1;
  lang       integer := 0;
  warn       integer;
  dbms_lob.createtemporary(:xml_import, true, dbms_lob.call);
  dbms_lob.fileopen(file, dbms_lob.file_readonly);
    :xml_import, file, dbms_lob.lobmaxsize,
    dst_offset, src_offset, nls_charset_id('al32utf8'), lang, warn
 dbms_output.put_line('Read ' || length(:xml_import) || ' character(s)');

Please note how it's specified that the XML file is in UTF-8 encoding (which is always the case, irrespective of database/national character set and NLS_LANG). This is done through the usage of the AL32UTF8 character set.

You can also use UTL_FILE to read the file line-by-line but this is less effective than the solution above.

Access to XML File on CI Server

If the XML file cannot be accessed from the Oracle database server but instead is accessible from your CI server, you can write a script, Java program or similar that creates an anonymous PL/SQL block that first builds a BLOB from the file's bytes, then converts the result to a CLOB using AL32UTF8 as the character set. This way you avoid issues with NLS_LANG, what your Command Prompt/shell uses as code page/TERM, character set etc. Such an anonymous block could look like (including the declaration of the CLOB variable):

variable xml_import clob
set linesize 220

  xml_import_binary blob;
  src_offset integer := 1;
  dst_offset integer := 1;
  lang       integer := 0;
  warn       integer;
  dbms_lob.createtemporary(xml_import_binary, true, dbms_lob.call);
  dbms_lob.append(xml_import_binary, hextoraw('3C5143544F5F4558504F52543E0D0A093C212D2D0D0A546F2061766F69642070617273696E67206572726F72732077697468206E657374656420434441544120746167732C2077652068617665207065'));
  dbms_lob.append(xml_import_binary, hextoraw('3C2F554E49545F54455354533E0D0A09093C2F51555F4841524E4553533E0D0A093C2F544553545F444546494E4954494F4E533E0D0A3C2F5143544F5F4558504F52543E0D0A'));

  dbms_lob.createtemporary(:xml_import, true, dbms_lob.call);
    :xml_import, xml_import_binary, dbms_lob.lobmaxsize,
    dst_offset, src_offset, nls_charset_id('al32utf8'), lang, warn

Here's a small Java program that converts a given text file to a SQL*Plus script similar to the one above (written on standard output). The end result is that we have the file represented in the CLOB variable :xml_import:

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

public class FileToClob {
   * The hexadecimal characters.
  private static final char HEX_CHARS[] = {
    '0', '1', '2', '3', '4', '5', '6', '7', '8', '9',
    'A', 'B', 'C', 'D', 'E', 'F'
  public FileToClob(String filePathName)
    throws FileNotFoundException, IOException {
    InputStream iS = new FileInputStream(filePathName);

      "variable xml_import clob\n" +
      "set linesize 220\n" +
      "\n" +
      "declare\n" +
      " xml_import_binary blob;\n" +
      " src_offset integer := 1;\n" +
      " dst_offset integer := 1;\n" +
      " lang       integer := 0;\n" +
      " warn       integer;\n" +
      "begin\n" +
      " dbms_lob.createtemporary(xml_import_binary, true, dbms_lob.call);\n"
    int n;
    byte[] bytes = new byte[80];
    while ((n = iS.read(bytes)) > 0) {
      print(" dbms_lob.append(xml_import_binary, hextoraw('");
      for (int i = 0; i < n; i++) {
        print("" + HEX_CHARS[(bytes[i] & 0xF0) >> 4] + HEX_CHARS[bytes[i] & 0x0F]);
      " dbms_lob.createtemporary(:xml_import, true, dbms_lob.call);\n" +
      " dbms_lob.converttoclob(\n" +
      "    :xml_import, xml_import_binary, dbms_lob.lobmaxsize,\n" +
      "    dst_offset, src_offset, nls_charset_id('al32utf8'), lang, warn\n" +
      " );\n" +
      "end;\n" +

  private static void print(String s) {
  public static void main(String[] args)
    throws FileNotFoundException, IOException {
    FileToClob fileToClob = new FileToClob(args[0]);

Calling Import PL/SQL API

Now we have read the XML file into our :xml_import CLOB variable, we can simply call the CT PL/SQL API for importing this CLOB. This is divided into 4 parts: Parsing the XML, setting import options (2 parts) and importing:

 -- Load the export, preparing for import.
  -- Set options for the import.
    include_results_in => false,
    include_program_source_in => false,
    tdg_conflict_handling_in => qu_xmldom_import.tdg_skip,
    td_merge_in => false,
    harness_guid_for_merge_in => null,
    skip_new_test_cases_in => null
  -- Set program and test code owner.
    prog_owner_in => 'SCOTT',
    harn_owner_in => USER
  -- Perform the import.

This example assumes that your program owner is 'SCOTT'. The last API call performs an implicit commit. Exceptions can be raised by any of these calls, eg:

ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00225: end-element tag "QCTO_EXPORT_HEADER" does not match start-element tag "QCTO_EXPORT_HEADERx"
Error at line 19
ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 974
ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 1002
ORA-06512: at "QCTO200B5.QU_XMLDOM_IMPORT", line 554
ORA-06512: at "QCTO200B5.QU_XMLDOM_IMPORT", line 576
ORA-06512: at "QCTO200B5.QU_XMLDOM_IMPORT", line 914
ORA-06512: at "QCTO200B5.QU_XMLDOM_IMPORT", line 974*
ORA-06512: at line 3
Deleting Test Definitions

Let's say that you no longer need a TD that you created earlier. You then need to make sure that this change is deployed to your CI environments.

There are two ways of accomplishing this:

  • Calling CT with specific command line options: /T=<test_definition_name> and /Delete. You need to be aware that the exit code of the application is non-zero if the TD isn't found but that's to be expected as you get the same when trying to delete a file in the file system with an OS command.
  • Using one of the PL/SQL APIs provided:
    • Find the UNIVERSAL_ID in QU_HARNESS table for applicable NAME/TEST_NAME/PROGRAM_OWNER/PROGRAM_NAME and then call the following procedure in QU_HARNESS_XP:
procedure del(
 universal_id_in   in qu_harness_tp.universal_id_t,
  rows_out          out pls_integer,
  handle_error_in   in boolean := true,
  del_test_pkg_in   in boolean default false,
  part_of_import_in in boolean default false
    • Use another of the DEL or DEL_% subprograms in QU_HARNESS_XP with parameters appropriate for your requirements.

However, I would argue that it's not necessary to delete the TD specifically, since -- in my view -- the XML file containing the TD (produced by exporting it from CT) must always reflect the current state of the TD in your revision control system, such that you:

  • Avoid problems when somebody manually deploys it to a given environment.
  • Don't have to program specific rules into your CI scripts for handling removal of TDs -- you can always deploy the latest version of any given TD XML (unless you start the CI deployment by removing all TDs in a "pre burner" step).

You can remove all the TDs in a given repository through the following anonymous block:

 n pls_integer;
  for td in (
        select universal_id,
        from   qu_harness
        where name != 'IMPLICIT_'
      ) loop
      dbms_output.put_line('Deleting TD "' || td.test_name || '"...');
        universal_id_in => td.universal_id,
        rows_out => n,
        handle_error_in => true,
        del_test_pkg_in => true,
        part_of_import_in => false
      dbms_output.put_line('Done. ' || n || ' row(s) deleted.');
      when others then
  end loop;


 (omit using TEST_NAME if you're using CT 1.9.1 or earlier as that's a new feature of CT 2.0.0).

You can remove all the test cases for a given TD in various different ways, including:

  • Edit the XML file and remove all TEST_CASES fragments.
  • Use Test Editor to remove all test cases and export again.
  • Use Test Builder to remove all test cases and export again.

You then need to commit/check in the new XML file to your revision control system.

This way, you still have the TD in the CT repository but it doesn't have any test cases defined. Arguably, it would be better to get rid of the TD but you can't do this through the XML file. Perhaps Quest should consider a mean of doing so through the XML format, eg by looking for an optional element /QCTO_EXPORT/TEST_DEFINITIONS/QU_HARNESS/DELETE with a value of "Y".

Continuous Integration Part 2
del.icio.usFacebookDiggGoogleLive BookmarksNewsvineStumbleUponTechnoratiYahooDotNetKicks

5 comment(s) so far...


Re: Continuous Integration Part 2

Hi Finn,
I am quite new to Continuous integration and Code tester and have read your posts concerning QCTO and CI.
Can you please explain the background and advantage to store the Import File as CLOB in the database? (and the relationship to CI ?)
Thanks ,
Best Reiner

By Anonymous on   Tuesday, July 05, 2011

Re: Continuous Integration Part 2

Hi Reiner

I'm not sure what you mean by storing the import file as a CLOB in the database?

1. Is that what you intend to do instead of keeping it on the file system? I wouldn't recommend that as you would have to version the data to keep the history of changes and you would have the burden of updating it every time you export it from QCTO.
2. Is that what you mean I do in my code? That's not correct - I read it into a CLOB variable, not into a table column.

Hope this helps.

Finn Ellebaek Nielsen
Oracle Test Coach

By QCTOblog on   Tuesday, August 09, 2011

Re: Continuous Integration Part 2

Thanks Finn, that was helpful.
In my effort to use Quest Code Tester in a CI environment, there is still one step missing: After having imported a test definition XML-File successfully on the command line
the test definition is properly defined. But the Test Code Package has not been generated yet during the import.
Before I can run the test definition I need to Generate the test Code Package - and I wouldn't want to do it in the GUI.
How can I accomplish this from the command line (after having imported the XML-File) using the PL/SQL API?
The PL/SQL API should offer a way but I am unable to figure it out (found no appropriate documentation).
Do you have a tip?

By Reiner on   Tuesday, January 24, 2012

Re: Continuous Integration Part 2

Hi Rainer

Try to call QU_GENERATE.GENERATE_AND_COMPILE with the GUID of the test definition for which you would like to run, eg:


You find the GUID of the test definition in the XML with the XPath expression '/QCTO_EXPORT/QCTO_EXPORT_HEADER/TEST_GUID/text()', eg (not sure the XML will be shown correct in this comment):

xml xmltype := xmltype('



Hope this helps.



Finn Ellebaek Nielsen | Oracle Test Coach | oracletesting.com

By FinnEllebaekNielsen on   Tuesday, January 24, 2012

Re: Continuous Integration Part 2

Great thanks!
qu_generate.generate_and_compile worked for (BTW: I have experimented with qu_generate.generate_test_code without success).
To get the GUID I used a simple SQL-query (I am not so familiar with XML DB stuff):
select qh.universal_id into my_test_guid from qu_harness qh
where qh.program_name = upper(my_test_name);
(where my_test_name is the name of the program to be tested)

Do you know if there is a documentation concerning the PL/SQL API of Code Tester ?

By Reiner on   Wednesday, January 25, 2012
Search Blog Entries
Blog Archives
<May 2013>
April, 2013 (13)
March, 2013 (10)
February, 2013 (5)
January, 2013 (7)
December, 2012 (6)
November, 2012 (10)
October, 2012 (8)
September, 2012 (6)
August, 2012 (8)
July, 2012 (8)
June, 2012 (12)
May, 2012 (21)
April, 2012 (10)
March, 2012 (16)
February, 2012 (19)
January, 2012 (20)
December, 2011 (19)
November, 2011 (14)
October, 2011 (12)
September, 2011 (17)
August, 2011 (15)
July, 2011 (16)
June, 2011 (13)
May, 2011 (15)
April, 2011 (8)
March, 2011 (21)
February, 2011 (17)
January, 2011 (16)
December, 2010 (13)
November, 2010 (13)
October, 2010 (7)
September, 2010 (15)
August, 2010 (11)
July, 2010 (13)
June, 2010 (12)
May, 2010 (14)
April, 2010 (12)
March, 2010 (13)
February, 2010 (12)
January, 2010 (7)
December, 2009 (10)
November, 2009 (12)
October, 2009 (15)
September, 2009 (18)
August, 2009 (13)
July, 2009 (23)
June, 2009 (14)
May, 2009 (17)
April, 2009 (7)
March, 2009 (14)
February, 2009 (7)
January, 2009 (12)
December, 2008 (7)
November, 2008 (11)
October, 2008 (19)
September, 2008 (14)
August, 2008 (11)
July, 2008 (14)
June, 2008 (19)
May, 2008 (12)
April, 2008 (18)
March, 2008 (13)
February, 2008 (8)
January, 2008 (7)
December, 2007 (5)
November, 2007 (8)
October, 2007 (13)
September, 2007 (13)
August, 2007 (16)
July, 2007 (11)
June, 2007 (6)
May, 2007 (5)
April, 2007 (5)
March, 2007 (8)
February, 2007 (6)
January, 2007 (6)
December, 2006 (5)
November, 2006 (8)
October, 2006 (4)
August, 2006 (3)