You can translate the question and the replies:

Stored VQL procedure executing dynamic VQL

I'm trying to build a VQL procedure that can accept parameters to dynamically run GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW () to extract the creation_vql of whatever base view I pass in the parameters for. The purpose is to be able to schedule jobs to rebuild/replace a base view in the case where a column was added or altered in some way, or even if a database in the underlying datasource has had a table added. I am so far not having much luck getting any sort of way to dynamically creating or executing anything other than hard coded values. Here is a simple one I wrote to get the tables I want to pass to GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW () to build the creation vql: CREATE OR REPLACE VQL PROCEDURE procGetTables (dsName IN VARCHAR, sName IN VARCHAR, vqlOut OUT VARCHAR) AS ( CURSOR cursorData IS 'SELECT table_name FROM GET_JDBC_DATASOURCE_TABLES() WHERE input_datasource_name = ''xxxx'' AND input_schema_name = ''XXXXXX'' limit 1'; rtables cursorData%ROWTYPE; ) BEGIN OPEN cursorData; LOOP FETCH cursorData INTO rtables; RETURN ROW (vqlOut) VALUES (rtables.table_name); EXIT WHEN cursorData%NOTFOUND; END LOOP; CLOSE cursorData; END ; This returns a table_name value I need for creating the VQL, however, I want to actually use the parameters and have it dynamically get a specified table_name value instead of all the tables. Example, I want to be able to do this: SELECT procGetTables ("dataSource1", "schemaName1") and get the table_name I need to rebuild.
user
16-06-2023 15:22:45 -0400
code

1 Answer

Hi, As per my understanding, you would like to set up a way to automate the creation or alteration of your base views whenever there are changes in the underlying data sources by generating the VQL create statements in Denodo. It is recommended to reuse the predefined stored procedures provided by Denodo whenever possible. In the case of your sample stored procedure `procGetTables`, I believe the main functionality of this stored procedure can already be satisfied by the predefined stored procedure [GET_JDBC_DATASOURCE_TABLES](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/stored_procedures/predefined_stored_procedures/get_jdbc_datasource_tables), which also accepts data source, schema name and table_name parameters. Combining this with another predefined stored procedure [GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/stored_procedures/predefined_stored_procedures/generate_vql_to_create_jdbc_base_view), you can automate the process of creation or alteration of base views for all the tables/views of a source database. You can refer to the answer in this community question [Automating creation of base views using Generate VQL Script and Get Source Table stored procs](https://community.denodo.com/answers/question/details?questionId=906Du00000000SRIAY&title=Automating+creation+of+base+views+using+Generate+VQL+Script+and+Get+Source+Table+stored+procs) for a solution on how you can combine these two stored procedures. You can also refer to this article [How To Detect Changes In Data Sources](https://community.denodo.com/kb/en/view/document/How%20to%20detect%20changes%20in%20data%20sources) for a detailed guide on how you can set up a Denodo Scheduler job to automate the detection of changes in your data sources. Hope this helps!
Denodo Team
21-06-2023 03:14:03 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here