call procedure created with xs project

[Former Member]

Hi,

I am very comfortable with creating procedures as catalog objects using SQL console and calling procedures using the SQL Console.

Today, for the first time I'm trying create a procedure using the Project Explorer, using an XS project and a .PROCEDURE file within that project.

My procedures are active and have the appropriate decorator icon to indicate they are active, as shown in this screen shot:

The TEST_PROJECT_PROC also shows in thh Repositories, as seen here:

Now, I am having trouble calling these procedures.  I cannot find them in the _SYS_BIC schema, and cannot call them.

I've tried the following:

CALL "_SYS_BIC"."TEST_JEFFSK.PROCEDURE/TEST_PROJECT_PROC";

CALL "_SYS_BIC"."TEST_JEFFSK.PROC/TEST_PROJECT_PROC";

CALL "_SYS_BIC"."TEST_JEFFSK/TEST_PROJECT_PROC";

Among many other variations.  I've tried everything I can think of.

Each time I get "Invalid name of function or procedure error".

So, how do I call this procedure?

Sorry if this is a dumb question.  Again, I cannot find these procedures in any schema in the catalog, so I have no idea where they are sitting and the proper address to call them.

[Former Member · Accepted solution]

Hi,

I'm informally closing this thread as a need, but the question is still open from a curiosity standpoint.

I'm able to do what I need to do using a .hdbprocedure file instead of a .procedure file.

Still weird though that a .procedure file goes off into nowhere for me... I guess I'll stay away from those.

[lbreddemann · Accepted solution]

The .procedure format files are deprecated and shouldn't be used anymore.

Concerning where the objects go:

You can always check system views like OBJECTS or PROCEDURES to look for objects, e.g. like this:

select * from procedures where procedure_name like '%TEST_PROJECT_PROC3%';

If the procedure has been activated successfully, you should find it in the _SYS_BIC schema with the naming pattern

_SYS_BIC.<package_name>/<procedure_name>

So, if you stuck with the TEST_JEFFSK package for procedure TEST_PROJECT_PROC3 then the resulting active catalog object name is:

_SYS_BIC."TEST_JEFFSK"/"TEST_PROJECT_PROC3"

Be aware that you can also right click on the schema folder icon and set a filter for easier object retrieval.

[Former Member · Accepted solution]

Hi Lars,

Thanks as always for your extremely thorough answer, and sorry for my delay in getting back to this post. 

I am marking this as the correct answer because I believe that you have 100% correctly answered the question of where the objects go.

There must be some strange security in place on my system where either I cannot see them, or I cannot activate them.  After activating a .PROCEDURE file with no activation errors, I execute the SQL:  select * from procedures; I can see all procedures in my database except for the ones that were created via .procedure file.

It's weird, but oh well, I just won't use the .procdure files anymore.  I have no issues with the .hdbprocedure files.  I can see those just fine!

So, thanks for the great answer!

[lbreddemann · Accepted solution]

Happy to help when I can.

Appreciate the kind feedback!

[Former Member]

I have more information on this question.

I have attempted to use a .HDBPROCEDURE file instead of a .PROCEDURE file.

The procedure has activated correctly and I can see it in the catalog.

Using this non-sense procedure with .hdbprocedure file, I'm having success activating in the JEFFSK Schema:

Using the same procedure with a .procedure file, I do not find it in the _SYS_BIC Schema:

Not found in _SYS_BIC.

[DeepakVarandani]

Hi Jeff,

Can you can try this.

CALL "_SYS_BIC"."TEST_JEFFSK/TEST_PROJECT_PROC"(inp1,inp2,?);

by giving input (if required) and output parameters.

Please check Procedures folder in _SYS_BIC schema for the above procedure in the Catalog.

Best Regards,

Deepak

[Former Member]

Hi Deepak,

This procedure has no input or output parameters.

Also, as stated, I cannot find the procedure in the _SYS_BIC schema in the Procedures folder.

I wonder if this is activating to another area of the catalog?  I have not specified a schema in my create procedure statement.  I'm expecting it to be in _SYS_BIC as shown here:  HANA Academy - HANA Native Development Workshop: SQL Script Editor - YouTube