How to insert the result from a procedure into a table or table variable ?

[Former Member]

Can I insert the result from a procedure into a table?

For example:

CREATE PROCEDURE MYPROC

AS

BEGIN

  /* I need the result from the system procedure "GET_OBJECT_DEFINITION" */

  CALL GET_OBJECT_DEFINITION('MySchema','"MySourceTable"');

  /* But how to insert the result from procedure "GET_OBJECT_DEFINITION" into a table or table variable ? */

END;

Best regards

Y. Hu

[Former Member]

Hi Yujun,

You can do it easily like the example below.

CREATE PROCEDURE MYPROC

AS

BEGIN

CALL GET_OBJECT_DEFINITION('MySchema', '"MySourceTable"', Some_Output_Variable);

INSERT INTO ANY_TABLE SELECT cols FROM :Some_Output_Variable;

END;

1. You can directly insert into any table you want inside the GET_OBJECT_DEFINITION itself. In that case there is no need of this above MYPROC procedure.

2. Suppose you don't want to do that, then you can follow the above approach and alter the GET_OBJECT_DEFINITION procedure to return an output table variable and collect those values inside

table variable (Some_Output_Variable) and insert into the required table over in this procedure.

Do let me know if you get any doubts in the above explanation.

Regards,

Anil

[nithinu]

Hi Anil,

Does the GET_OBJECT_DEFINITION procedure have an output parameter defined ?

Regards,

Nithin

[Former Member]

Hi Anil,

Thank you for your help.

But GET_OBJECT_DEFINITION is a HANA system procedure. It doesn’t have output parameter and I can not modify it.

Best regards,

Y. Hu

[Former Member]

Hi Yujun,

My bad, I thought GET_OBJECT_DEFINITION was custom procedure.

Since it is a system procedure and we cannot alter it, we have to find a workaround in order to access the definition of objects. One such work around I could think of is represented as below procedure.

CREATE PROCEDURE MYPROC(IN OBJECT_TYPE NVARCHAR(100), IN SCHEMA_NAME NVARCHAR(100), IN OBJECT_NAME NVARCHAR(100))

AS

BEGIN

IF :OBJECT_TYPE  = 'PROCEDURE' THEN

INSERT INTO ANY_TABLE

SELECT  DEFINITION FROM sys.procedures

where schema_name = :SCHEMA_NAME and procedure_name = :OBJECT_NAME;

ELSEIF :OBJECT_TYPE  = 'FUNCTION'  THEN

INSERT INTO ANY_TABLE

SELECT  DEFINITION FROM sys.functions

where schema_name = :SCHEMA_NAME and function_name = :OBJECT_NAME;

ELSEIF :OBJECT_TYPE  = 'VIEWS'  THEN

INSERT INTO ANY_TABLE

SELECT  DEFINITION FROM sys.views

where schema_name = :SCHEMA_NAME and view_name = :OBJECT_NAME;

ELSEIF :OBJECT_TYPE  = 'TRIGGERS'  THEN

INSERT INTO ANY_TABLE

SELECT  DEFINITION FROM sys.views

where schema_name = :SCHEMA_NAME and trigger_name = :OBJECT_NAME;

END IF;

END;

where sys.procedures, sys.functions, sys.views are the inbuilt hana views which contain the definitions for the same.

Regards,

Anil

[nithinu]

Hi Anil,

I think for the Trigger definition we need to look into the sys.triggers, right ? You might have misplaced it.

Also, is there a way to get the Table definition as in the SP written by you, or we need to create a SP which builds a Create Statement for the input table, using the views sys.columns, sys.constraints etc.

Regrads,

Nithin

[Former Member]

Hi Nithin,

Yes, The Trigger definition is available from sys.triggers. That was a typo.

Talking about definitions of tables or sequences, since they are created using json kind of syntax in .hdbtable or.hdbsequence respectively, I think there is no hana system view which is reflecting there exact definition.

But we can always create the definitions of tables or sequences combining bunch of sql statements in a procedure using available hana views such as m_cs_tables, m_cs_columns etc..

Regards,

Anil

[Former Member]

Hi Anil, hi Nithin,

Thank you for your help.

Yes, the definition for procedures, functions, views etc. can be received from the system views PROCEDURES, FUNCTION, VIEWS etc., but not for Tables.

The good feature from GET_OBJECT_DEFINITION is the definition for tables. I would like to avoid implementing the complicated definition for the tables. Unfortunately I cannot store this from GET_OBJECT_DEFINITION in my procedure.

Best regards,

Y. Hu

[nithinu]

Hi Yujun,

I just played around on creating the below procedure which returns the 'Create Table' script. I haven't considered the constraints/indexes part on this. Also there might be missing in data type considerations.Can you please have a look on this.. Not sure whether this is helpful or not.

DROP PROCEDURE GET_TABLE_DEFINITION_SP;

CREATE PROCEDURE GET_TABLE_DEFINITION_SP(IN _schema_name NVARCHAR(256), IN _table_name NVARCHAR(256), OUT _tab_def NCLOB,OUT _comment_def NCLOB)

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

-- DEFAULT SCHEMA

AS

BEGIN

  DECLARE is_exist TINYINT;

  DECLARE table_type VARCHAR(10);

  DECLARE init_create VARCHAR(400);

  DECLARE tab_cols NCLOB;

  DECLARE col_comment NCLOB;

  DECLARE CURSOR cur_tab_cols FOR

  SELECT t.COLUMN_NAME,t.DATA_TYPE_NAME,t.LENGTH AS COL_LENGTH,t.SCALE AS COL_SCALE,t.IS_NULLABLE,t.DEFAULT_VALUE,t.COMMENTS,t.CS_DATA_TYPE_NAME

  FROM SYS.TABLE_COLUMNS t

  WHERE t.SCHEMA_NAME = _schema_name AND t.TABLE_NAME = _table_name

  ORDER BY POSITION;

  SELECT COUNT(1),MAX(TABLE_TYPE) INTO is_exist,table_type  FROM SYS.TABLES WHERE SCHEMA_NAME = _schema_name AND TABLE_NAME = _table_name;

  IF is_exist = 0 THEN

  _tab_def := ' table not found ';

  RETURN;

  END IF;

  init_create := 'CREATE ' || :table_type || ' TABLE "' || :_schema_name || '"."' || :_table_name || '" (';

  FOR cur_tab_cols_rec AS cur_tab_cols DO

  tab_cols := CASE WHEN cur_tab_cols::ROWCOUNT = 1 THEN TO_CHAR('') ELSE TO_CHAR(tab_cols) || TO_CHAR(',') END ||

             '"'|| cur_tab_cols_rec.COLUMN_NAME || '"' || ' ' || cur_tab_cols_rec.DATA_TYPE_NAME ||

             CASE WHEN cur_tab_cols_rec.DATA_TYPE_NAME = 'NVARCHAR' OR

                       cur_tab_cols_rec.DATA_TYPE_NAME = 'VARCHAR'  OR

                       cur_tab_cols_rec.DATA_TYPE_NAME = 'CHAR' OR

                       cur_tab_cols_rec.DATA_TYPE_NAME = 'SHORTTEXT' OR

                       cur_tab_cols_rec.DATA_TYPE_NAME = 'ALPHANUM' OR

                       cur_tab_cols_rec.DATA_TYPE_NAME = 'VARBINARY' OR

                       cur_tab_cols_rec.DATA_TYPE_NAME = 'BINARY'

                  THEN '(' || TO_CHAR(cur_tab_cols_rec."COL_LENGTH") || ')'

                  WHEN cur_tab_cols_rec.DATA_TYPE_NAME = 'DECIMAL'

                     THEN '(' || TO_CHAR(cur_tab_cols_rec."COL_LENGTH") || ',' || TO_CHAR(cur_tab_cols_rec.COL_SCALE) ||  ')'

                  ELSE ''

             END ||

             CASE WHEN cur_tab_cols_rec.IS_NULLABLE = 'FALSE' THEN ' NOT NULL ' ELSE '' END ||

             CASE WHEN cur_tab_cols_rec.DEFAULT_VALUE IS NOT NULL THEN ' DEFAULT ' ||

                        CASE WHEN cur_tab_cols_rec.DATA_TYPE_NAME = 'NVARCHAR' OR

                                  cur_tab_cols_rec.DATA_TYPE_NAME = 'VARCHAR'  OR

                                  cur_tab_cols_rec.DATA_TYPE_NAME = 'CHAR' OR

                                  cur_tab_cols_rec.DATA_TYPE_NAME = 'SHORTTEXT' OR

                                  cur_tab_cols_rec.DATA_TYPE_NAME = 'ALPHANUM' THEN '''' || cur_tab_cols_rec.DEFAULT_VALUE || ''''

                             ELSE cur_tab_cols_rec.DEFAULT_VALUE END

                  ELSE ''

             END;

  col_comment :=  IFNULL(TO_CHAR(col_comment),TO_CHAR(''))   ||

                 CASE WHEN cur_tab_cols_rec.COMMENTS IS NULL THEN ''

                      ELSE

                           'COMMENT ON COLUMN ' || '"' || :_schema_name || '".' || '"' || :_table_name || '"."' || cur_tab_cols_rec.COLUMN_NAME || '" IS ' ||

                                    '''' || cur_tab_cols_rec.COMMENTS || '''' || ';'

                      END  ;

  END FOR;

  _tab_def :=  init_create || ' ' ||  tab_cols || ' );' ;

  _comment_def := col_comment;

END;

CALL GET_TABLE_DEFINITION_SP('schema_name','table_name',?,?)  ;

Regards,

Nithin

[Former Member]

Hi Nithin,

Thank you very much again for your help!

Best regards,

Y. Hu