How to get testcase path from QC DB

Sometimes you may need to report testcases those are in a specific folder with some custom format. This can be done from the analysis view of the dashboard easily. But sometimes getting the testcases from the DB with some SQLs may be the way you might prefer.
When querying from the DB you have to use some complicated SQLs to accomplish this.

The function that i am sharing is a simple util function that will make this easier. To do this first you have to have the create function previlage on the DB side. Then you can create the GetTestCasePath function with this PlSQL.

Note: The t_crs is a ref cursor that is declared any pkg spec of your util package. It is declared like this: type t_crs is ref cursor;

CREATE OR REPLACE FUNCTION <<<YOUR_QC_SCHEME>>>.GetTestCasePath (
   p_db_name    VARCHAR2,
   p_test_id    NUMBER)
   RETURN VARCHAR2
IS
   PATH     VARCHAR2 (500);
   sqlStr   VARCHAR2 (9999);
   tc       <<<YOUR_QC_SCHEME>>>.kpi_pkg.t_crs;
BEGIN
   PATH := '';
   sqlStr :=
      'SELECT listagg (al_description, ''\'') WITHIN GROUP (ORDER BY aa.aaa DESC) PATH
  FROM (    SELECT al_item_id, LEVEL aaa, al_description
              FROM '
      || p_db_name
      || '.ALL_LISTS
        START WITH al_item_id = (select ts_subject from '
      || p_db_name
      || '.TEST where ts_test_id='
      || p_test_id
      || ')
        CONNECT BY PRIOR al_father_id = al_item_id) aa';

   OPEN tc FOR sqlStr;

   LOOP
      EXIT WHEN tc%NOTFOUND;

      FETCH tc INTO PATH;
   END LOOP;

   CLOSE tc;

   RETURN PATH;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END GetTestCasePath;

For the testcase above getting path is something like this:

SELECT <<<YOUR_QC_SCHEMA_NAME>>>.
   GETTESTCASEPATH ('<<<YOUR_QC_SCHEMA_NAME>>>', 15396) TestCasePath
  FROM DUAL 

And the output will be like this: