cancel
Showing results for 
Search instead for 
Did you mean: 

call of procedures in Hana

martin_lindner
Explorer
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Ravi_Channe
Active Contributor
0 Kudos

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
Explorer
0 Kudos

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

Ravi_Channe
Active Contributor
0 Kudos

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
Explorer
0 Kudos

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

Ravi_Channe
Active Contributor
0 Kudos

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
Explorer
0 Kudos

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

Ravi_Channe
Active Contributor
0 Kudos

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
Explorer
0 Kudos

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

Answers (1)

Answers (1)

Former Member
0 Kudos

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
Explorer
0 Kudos

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