When working with your own arrays in Qnxo, you end up with all kinds of views in the schema that you actually don’t want to be left there. For one because they don’t serve any purpose, except for the CGML code generation, and you don’t want any other code to depend on them, because they might change during another generation run. 

CGML doesn’t provide a utility to clean up these views, but you can call PL/SQL code in the database to have the job done for you.

I have created a package like this:

CREATE OR REPLACE PACKAGE cgml_tools AUTHID CURRENT_USER
IS
  v_string                                 VARCHAR2( 32767 );
  FUNCTION drop_view
    RETURN NUMBER;
END cgml_tools;
/
CREATE OR REPLACE PACKAGE BODY cgml_tools
IS
  FUNCTION drop_view
    RETURN NUMBER
  IS
  BEGIN
    EXECUTE IMMEDIATE ‘drop view ‘ || v_string;
    RETURN 0;
  END drop_view;
BEGIN
  — Initialization
  NULL;
END cgml_tools;

Now, when I create an array like this:

[DEFARRAY]my_array[AS]
SELECT ‘Hello world’
  FROM DUAL
[ENDDEFARRAY]

then a view called my_array is created in the current schema.
When I issue this code at the end of my script:

# clean up after yourself!!!
[SET]cgml_tools.v_string[TO]MY_ARRAY
[SETALIAS]_dummy[TO]{cgml_tools.drop_view}

Then the view is removed after it has served it’s purpose.

Maybe you are wondering why I created a package and not just a function with a single parameter. The reason for this is that CGML can call PL/SQL Code, but cannot send in parameters (for as far as I know, at least). Using a package gives me the opportunity to use a package variable as a parameter.

Hope this helps other people too.