You can translate the question and the replies:

Store proc throwing an error after few table update

I have below store procedure to update Table and column descrption. It worked fine for about 6 tables then I am getting error message while I try to execute store proc. This store proc works for those 6 tables regardless of how many times I run but I get erorr when I use any other table. Could you please help me figure out why I am getting this error and how can I fix this error. Error message that I am getting: Finished with error: There was an error while loading the type of the field '_register_col_name_col_descr_table_namee_table_introduction_CHRONICLES_MF' Store proc: CREATE OR REPLACE FOLDER '/Stored_Procedure' ; DROP PROCEDURE IF EXISTS update_table_and_column_descriptions CASCADE; CREATE VQL PROCEDURE update_table_and_column_descriptions FOLDER = '/stored_procedure'(input_database_name IN varchar, input_table_name IN varchar, db_name OUT varchar, tbl_name OUT varchar, tbl_desc OUT varchar, colmn_nam OUT VARCHAR, colmn_desc OUT varchar) --Variable definitions AS ( CURSOR cursor_tables IS 'SELECT distinct "CLARITY_COL"."COLUMN_NAME" as col_name, "CLARITY_TBL"."TABLE_NAME" as table_namee, REGEXP("CLARITY_TBL"."TABLE_INTRODUCTION",''[^0-9a-zA-Z\p{Blank}\p{Space}\.\_}]'', '''') AS table_introduction, "CLARITY_TBL"."CHRONICLES_MF", --"CLARITY_COL"."DESCRIPTION" AS col_descr REGEXP("CLARITY_COL"."DESCRIPTION",''[^0-9a-zA-Z\p{Blank}\p{Space}\.\_]'', '''') AS col_descr FROM "CLARITY_TBL" LEFT OUTER JOIN "CLARITY_TBL_2" ON "CLARITY_TBL"."TABLE_ID"="CLARITY_TBL_2"."TABLE_ID" LEFT OUTER JOIN "CLARITY_COL" ON "CLARITY_COL"."TABLE_ID" = "CLARITY_TBL"."TABLE_ID" AND ("CLARITY_COL"."RECORD_STATUS_C"=0 OR "CLARITY_COL"."RECORD_STATUS_C" IS NULL) AND "CLARITY_COL"."IS_EXTRACTED_YN" = ''Y'' WHERE "CLARITY_TBL"."IS_EXTRACTED_YN"=''Y'' AND "CLARITY_TBL"."DEPRECATED_YN"=''N'' AND "CLARITY_TBL_2"."EHI_ENABLED_C" =''1'' AND "TABLE_NAME" ='':param1'' ORDER BY "CLARITY_TBL"."TABLE_NAME", "CLARITY_COL"."COLUMN_NAME" '; cursor_table cursor_tables%ROWTYPE; column_name VARCHAR; table_name varchar; table_description VARCHAR; column_description VARCHAR; ) BEGIN OPEN cursor_tables PARAMETERS (param1) VALUES (input_table_name); LOOP FETCH cursor_tables INTO cursor_table; column_name:=cursor_table.col_name; Table_name:=cursor_table.table_namee; table_description:=cursor_table.table_introduction; column_description:=cursor_table.col_descr; -- Executing "Connect" statement to connect with database EXECUTE 'CONNECT DATABASE ":param3";' PARAMETERS(param3) VALUES ( input_database_name); -- Executing Alter statement to update table description EXECUTE 'ALTER TABLE ":param2" (ALTER COLUMN ":param4" ADD ( DESCRIPTION = ":param5" ))'';' PARAMETERS( param2, param4, param5 ) VALUES (input_table_name, column_name, column_description); EXECUTE 'ALTER TABLE ":param2" DESCRIPTION = '':param4'';' PARAMETERS(param2, param4 ) VALUES (input_table_name, table_description); --Output the records RETURN ROW (db_name, tbl_name, tbl_desc,colmn_nam,colmn_desc) VALUES (input_database_name, input_table_name, table_description,column_name,column_description); --Exit when the there are no more rows EXIT WHEN cursor_tables%NOTFOUND; END LOOP; CLOSE cursor_tables; END;
user
05-01-2024 12:40:14 -0500
code

1 Answer

Hi, On seeing the error, I think there was a typo while creating the data type. To resolve this error, I would use the right data type name while creating the data type. For more information about data type, please refer to the [Defining a Data Type](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/defining_other_elements_of_the_catalog/defining_a_data_type/defining_a_data_type) section of the Virtual DataPort VQL Guide. Hope this helps!
Denodo Team
22-01-2024 04:18:16 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here