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
v_string VARCHAR2( 32767 );
CREATE OR REPLACE PACKAGE BODY cgml_tools
EXECUTE IMMEDIATE ‘drop view ‘ || v_string;
Now, when I create an array like this:
SELECT ‘Hello world’
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!!!
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.