call of procedures in Hana

[martin_lindner]

Hi experts,

I have some trouble with HANA/SQLScript.

I created a procedure like that one:

After creation I've tryed to call the procedure with something like

call "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES"( "IA_PARENT_OF_BC", out_table ); or

call "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES"( "IA_PARENT_OF_BC", :out_table );. or

call "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES"( iv_linke_type_id="IA_PARENT_OF_BC", out_table );

But all the time I've got only error messages.

You find an example here.

Does anybody know why tha call statement in the embeded SQL editor doesn't work or what I've done wrong?

Thanks to your for helpfull every feedback,

Martin

[former_member184768 · Accepted solution]

Hi Martin,

Few points:

1) The input value of "IA_PARENT_OF_BC", which I believe is for LINK_TYPE_ID should be in single quotes 'IA_PARENT_OF_BC' and not double quotes " ".

2) If you are trying to call this from SQL editor then you might want to use the result view (I hope the procedure is defined with result view) like

select * from "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES"

with parameters

(

   'placeholder' = ('$$iv_link_type_id$$', 'IA_PARENT_OF_BC')

)

Regards,

Ravi

[martin_lindner · Accepted solution]

Hi Ravi,

thanks to your post.

If I use your statement I got a new error:

Could not execute 'select * from "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES" with parameters ( 'placeholder' = ...'

SAP DBTech JDBC: [259] (at 25): invalid table name: Could not find table/view d052319/GET_RELEVANT_ENTITIES in schema _SYS_BIC: line 1 col 26 (at pos 25)

So I think it isn't with result view. How could I define the result view output for the procedure? The creation script in the _SYS_BIC table is like the following:

create procedure "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES" ( in iv_link_type_id VARCHAR(255) ,

  out ot_relevant_entries "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES/tabletype/ot_relevant_entries" ) language SQLSCRIPT sql security definer reads sql data as /*********

Begin Procedure Script ************/

BEGIN ot_relevant_entries = SELECT

  "SOURCE_MANDT",

"SOURCE_GUID_1",

"SOURCE_GUID_2",

"SOURCE_ID_1",

"SOURCE_ID_2",

"SOURCE_TYPE_ID",

  "DESTINATION_MANDT",

"DESTINATION_GUID_1",

"DESTINATION_GUID_2",

"DESTINATION_ID_1",

"DESTINATION_ID_2",

"DESTINATION_TYPE_ID"

FROM "D052319"."ZTN_ENTITY_LINK"

WHERE 'LINK_TYPE_ID' = :iv_link_type_id

;

END

;

/*********

End Procedure Script ************/

Thanks for your help,

Martin

[former_member184768 · Accepted solution]

Hi Martin,

Can you please modify your create procedure statement to add RESULT VIEW like below. With the result view option, you would be able to fire SELECT * statement against the result view generated by the procedure.  Please refer to page 16 of SQL script guide for the syntax details: http://help.sap.com/hana/hana_dev_sqlscript_en.pdf

create procedure "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES" ( in iv_link_type_id VARCHAR(255) ,

  out ot_relevant_entries "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES/tabletype/ot_relevant_entries" )

  language SQLSCRIPT sql security definer reads sql data WITH RESULT VIEW v_relevant_entries

  as

  /*********

Begin Procedure Script ************/

BEGIN ot_relevant_entries = SELECT

  "SOURCE_MANDT",

"SOURCE_GUID_1",

"SOURCE_GUID_2",

"SOURCE_ID_1",

"SOURCE_ID_2",

"SOURCE_TYPE_ID",

  "DESTINATION_MANDT",

"DESTINATION_GUID_1",

"DESTINATION_GUID_2",

"DESTINATION_ID_1",

"DESTINATION_ID_2",

"DESTINATION_TYPE_ID"

FROM "D052319"."ZTN_ENTITY_LINK"

WHERE 'LINK_TYPE_ID' = :iv_link_type_id

;

END

;

/*********

End Procedure Script ************/

Regards,

Ravi

[martin_lindner · Accepted solution]

Hi Ravi,

thanks for your help, but by executing

select * from "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES"

with parameters

(

   'placeholder' = ('$$iv_link_type_id$$', 'IA_PARENT_OF_BC')

)

I still got the same error like before:

Could not execute 'select * from "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES" with parameters ( 'placeholder' = ...'

SAP DBTech JDBC: [259] (at 25): invalid table name: Could not find table/view d052319/GET_RELEVANT_ENTITIES in schema _SYS_BIC: line 1 col 26 (at pos 25)

Could it be a problem that the HANA instance is on revision level 34 and the studio is on 38?

Did you have any other suggestions?

Best regards,

Martin

[former_member184768 · Accepted solution]

Hi Martin,

You need to SELECT from the RESULT VIEW and not from the procedure. The procedure will be automatically executed when you select from the result view.

Regards,

Ravi

[martin_lindner · Accepted solution]

Hi Ravi,

thanks for your help but my problem isn't solved. My procedure is know that one:

create procedure "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES" ( in iv_link_type_id VARCHAR(255) ,

  out ot_relevant_entries "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES/tabletype/ot_relevant_entries" ) language SQLSCRIPT sql security definer reads sql data with result view v_relevant_entries as /*********

Begin Procedure Script ************/

BEGIN ot_relevant_entries = SELECT

  "SOURCE_MANDT",

  "SOURCE_GUID_1",

  "SOURCE_GUID_2",

  "SOURCE_ID_1",

  "SOURCE_ID_2",

  "SOURCE_TYPE_ID",

  "DESTINATION_MANDT",

  "DESTINATION_GUID_1",

  "DESTINATION_GUID_2",

  "DESTINATION_ID_1",

  "DESTINATION_ID_2",

  "DESTINATION_TYPE_ID"

FROM "D052319"."ZTN_ENTITY_LINK"

WHERE 'LINK_TYPE_ID' = :iv_link_type_id

;

END

;

So the result view sholud be created I think.

Now I try to select from it via:

select * from v_relevant_entries

with parameters

(

   'placeholder' = ('$$iv_link_type_id$$', 'IA_PARENT_OF_BC')

)

I try to recompile the procedure with

ALTER PROCEDURE "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES" RECOMPILE;

I try again to call the procedure, but the error message

Could not execute 'select * from v_relevant_entries with parameters ( 'placeholder' = ('$$iv_link_type_id$$', ...'

SAP DBTech JDBC: [259] (at 14): invalid table name:  Could not find table/view V_RELEVANT_ENTRIES in schema D052319: line 1 col 15 (at pos 14)

still exist. Also if I qoute the name of the result view with single or double quotes I got this error.

Any solution for that?

Best regards,

Martin

[former_member184768 · Accepted solution]

Hi Martin,

You are creating the procedure in _SYS_BIC schema, so I think the v_relevant_entries will be created in the same schema. But you are executing the procedure in D052319 schema hence it is not able to locate the result view.

You can add the schema name in the create procedure like

reads sql data with result view D052319.V_RELEVANT_ENTRIES as

...

And then try to select from the result view. BTW, one question, why did you create the procedure in _SYS_BIC schema ?

Regards,

Ravi

[martin_lindner · Accepted solution]

Hi Ravi,

thanks a lot.

To answear your question:

A colleague told me that every procedure would at the end be created in schema _SYS_BIC so I can also do it directly.

But finally many thanks to your good answers. It was really helpfull to me.

Thans a lot,

Martin

[Former Member]

Hi Martin,

You have to use single quotes to pass values instead of double quotes..

call "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES"( 'IA_PARENT_OF_BC', out_table );

Can you try the above?

thanks,

Anooj

[martin_lindner]

Hi Anooj,

no that doesn't help me. Now I receive the message:

Could not execute 'call "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES"( 'IA_PARENT_OF_BC', out_table )'

SAP DBTech JDBC: [1287]: identifier must be declared:

Any other suggestions?

Best regards,

Martin