Hi everyone, this is the SP that creates the view interface and it's intended to be created in the indicated database specified in the parameter. How can I do that?
[quarta-feira 17:22] Diogo Goncalves - Passio Consulting
CREATE OR REPLACE PROCEDURE create_interface_view (
vdb_name IN VARCHAR,
derived_view_name IN VARCHAR,
interface_name IN VARCHAR,
prt OUT VARCHAR
) AS (
vdb VARCHAR;
d_name VARCHAR;
i_name VARCHAR;
frst VARCHAR;
decimals VARCHAR;
radix VARCHAR;
size VARCHAR;
remarks VARCHAR;
code VARCHAR;
result VARCHAR;
max_rows INTEGER;
max_linha INTEGER;
rown INTEGER;
vql_inter VARCHAR;
prt VARCHAR;
CURSOR get_max_rows IS 'SELECT COUNT(*) FROM GET_VIEW_COLUMNS()
WHERE input_database_name = :vdb
AND input_view_name = :d_name';
CURSOR get_data IS 'SELECT CONCAT(column_name, '':'', column_vdp_type), column_decimals, column_radix, column_size, column_remarks,
column_sql_type_code
FROM GET_VIEW_COLUMNS()
WHERE input_database_name = :vdb
AND input_view_name = :d_name';
) BEGIN
vdb := vdb_name;
d_name := derived_view_name;
i_name := interface_name;
max_linha := 1;
vql_inter := '(';
OPEN get_max_rows PARAMETERS (vdb, d_name) VALUES (vdb_name, derived_view_name);
FETCH get_max_rows INTO max_rows;
CLOSE get_max_rows;
RETURN ROW (prt) VALUES (max_rows);
OPEN get_data PARAMETERS (vdb, d_name) VALUES (vdb_name, derived_view_name);
LOOP
FETCH get_data INTO frst, decimals, radix, size, remarks, code;
result := CONCAT(frst, ' (sourcetypedecimals = ''', decimals, ''', sourcetyperadix = ''', radix, ''',
sourcetypesize = ''', size, ''', description = ''', remarks, ''',
sourcetypeid = ''', code, ''')');
IF max_linha < max_rows THEN
vql_inter := CONCAT(vql_inter, result, ',');
ELSE
vql_inter := CONCAT(vql_inter, result, ')');
END IF;
max_linha := max_linha + 1;
EXIT WHEN max_linha > max_rows;
END LOOP;
CLOSE get_data;
vql_inter := CONCAT(interface_name, ' ', vql_inter, ' SET IMPLEMENTATION ', derived_view_name, ' FOLDER = ''/03 - business entities''');
RETURN ROW (prt) VALUES (vql_inter);
EXECUTE 'CREATE OR REPLACE INTERFACE VIEW :param' PARAMETERS (param) VALUES(vql_inter);
END
