Bug when using Template with variables from SQL list

[Philippe Malera]

I've noticed a strange bug when using template with dynamic list and variables.
With this example :

DECLARE
  r_row          lib_dm_columns$t%ROWTYPE;
  l_merge_fields lib_merge#.merge_field_aat;
BEGIN

  r_row.schema      := '&<name="Schema" list="NESSOFT,NESINT" default="NESSOFT">';
  r_row.table_name  := '&<name="Table" list="SELECT name FROM lib_dm_tables$ WHERE SCHEMA = :Schema ORDER BY name">';
  r_row.column_name := '&<name="Column" list="SELECT column_name from lib_dm_columns$ WHERE SCHEMA = :Schema AND table_name = :Table">';
  r_row.object      := '&<name="Object" list="SELECT ID FROM lib_dm_objects$ ORDER BY ID">';
  r_row.comment1    := '&<name="Comment">';
  r_row.nullable    := '&<name="Nullable" list="1,Null,0,Not null" description="yes">';

  INSERT INTO lib_dm_columns$t VALUES r_row;

  lib_datamodel#.validate(pi_file_schema => r_row.schema, pi_compare_db => FALSE, po_error_msg => :err_msg);
  lib_assert#.is_null(pi_value => :err_msg, pi_fault_message => :err_msg);

  l_merge_fields('PROJECT_NR') := '&<name="Project number (SD-?)">';

  lib_datamodel#.get_diff_script(pi_file_schema => r_row.schema, pio_merge_fields => l_merge_fields);

  :new_xml := lib_datamodel#.generate_xml.getclobval();

END;

I get this input popup :

But cursor is blocked on field "Table", impossible to get to next field "Column", without any message.

 

[Marco Kalter]

I tried this and it seems to work fine. I replaced all the queries in the substitution variable expressions with dummy queries though, because I did not have your tables. Can you try with these dummy queries? This way we will know if it's caused by the queries or something else.

DECLARE
  r_row          lib_dm_columns$t%ROWTYPE;
  l_merge_fields lib_merge#.merge_field_aat;
BEGIN

  r_row.schema      := '&<name="Schema" list="NESSOFT,NESINT" default="NESSOFT">';
  r_row.table_name  := '&<name="Table" list="SELECT dummy FROM dual WHERE dummy = :Schema ORDER BY dummy">';
  r_row.column_name := '&<name="Column" list="SELECT dummy from dual WHERE dummy = :Schema AND dummy = :Table">';
  r_row.object      := '&<name="Object" list="SELECT dummy FROM dual ORDER BY dummy">';
  r_row.comment1    := '&<name="Comment">';
  r_row.nullable    := '&<name="Nullable" list="1,Null,0,Not null" description="yes">';

  INSERT INTO lib_dm_columns$t VALUES r_row;

  lib_datamodel#.validate(pi_file_schema => r_row.schema, pi_compare_db => FALSE, po_error_msg => :err_msg);
  lib_assert#.is_null(pi_value => :err_msg, pi_fault_message => :err_msg);

  l_merge_fields('PROJECT_NR') := '&<name="Project number (SD-?)">';

  lib_datamodel#.get_diff_script(pi_file_schema => r_row.schema, pio_merge_fields => l_merge_fields);

  :new_xml := lib_datamodel#.generate_xml.getclobval();

END;

 

[Philippe Malera]

Here is a version of template agnostic from our datamodel :

DECLARE
  l_schema all_tab_cols.owner%TYPE;
  l_table  all_tab_cols.table_name%TYPE;
  l_column all_tab_cols.column_name%TYPE;
BEGIN
  l_schema := '&<name="Schema" list="select username from all_users">';
  l_table  := '&<name="Table" list="select table_name from all_tables where owner = :Schema order by table_name">';
  l_column := '&<name="Column" list="select column_name from all_tab_cols where owner = :Schema and table_name = :Table order by column_name">';
END;

When selecting this template, the cursor is stuck on 2nd field (table), impossible to list column names.

Version 12.0.8.1840 (32 bit)

 

[Marco Kalter]

Thanks. This does indeed reproduce the issue. As soon as anything is entered in the "Table" entry field, the cursor cannot be moved away from the entry field. We'll investigate and fix it.

 

[Marco Kalter]

It turned out that the substitution variable form did not handle SQL errors correctly. The error message was suppressed and you could not move to another entry field. We have fixed this for the next release.

In this case the SQL error is that the :Table bind variable name is a reserved word. If, for example, you replace it with :Table_Name it works correctly. You have to replace the substitution variable name with "Table name" as well:

DECLARE
  l_schema all_tab_cols.owner%TYPE;
  l_table  all_tab_cols.table_name%TYPE;
  l_column all_tab_cols.column_name%TYPE;
BEGIN
  l_schema := '&<name="Schema" list="select username from all_users">';
  l_table  := '&<name="Table name" list="select table_name from all_tables where owner = :Schema order by table_name">';
  l_column := '&<name="Column" list="select column_name from all_tab_cols where owner = :Schema and table_name = :Table_name order by column_name">';
END;

 

[Philippe Malera]

Well done, thanks for the tip :grin: