HANA Procedure Basics

[Former Member]

Hi All

  I am trying to create a basic procedure in  HANA which has one input parameter and no output parameters..But it is throwing error as below

Repository: Encountered an error in repository runtime extension;error preparing statement create procedure "_SYS_BIC"."zcrmcoeteam/COUNTR" (  ) language SQLSCRIPT sql security definer reads sql data as  for oid {tenant: , package: zcrmcoeteam, name: COUNTR, type: 2}: sql syntax error: line 1 col 113 (at pos 113) at qp_gram.y:27854.

my code is as below.

BEGIN

for cnt in 0..5 DO

CALL ins_msg_proc('loop iteration:'||:cnt);

  END FOR;

END;

request your help in finding the problem.Also I have few questions

1.Is it possible to pass tables as IN arguments or only table type

2.How can we create a table type of some existing table.

3. Is it mandatory to have IN and OUT arguments.

Thanks

Santosh

[Former Member]

Hi Santosh,

I was able to create and activate the following procedure:

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

i integer;

  BEGIN

for i in 0..5 DO

SELECT * FROM CIS_COURSE where COURSEID=:course;

end for;

END;

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

1. The 'i' used in FOR loop had to be defined initially.

2. 'course' is the Input parameter being used

3. No output parameter was defined for this procedure.

4.You can only define the table type in Input Parameter.

Reason: While deployment it tries to create the following entry in _SYS_BIC :: "_SYS_BIC"."rp/PROC2/tabletype/TEST_IPT_TABLE" .

"TEST_IPT_TABLE"  is my defined table . You may check SQL Script guide for this.

5.You can check SQL Reference guide for details:

CREATE TYPE my_type AS TABLE ( column_a DOUBLE );

6. It is not mandatory to have IN or OUT argument

Tip: Sometimes in procedures screen you might get activation failures even though your script is perfect!! What worked for me was adding and deleting a space anywhere in the screen..and reactivating

Regards, Rahul

[Former Member]

Hi Rahul

Thanks for sharing your wisdom , see I tried two scenarios as mentioned  below .

Scenario 1:-

-----------

BEGIN

for cnt in 0..5 DO

select 3*2 from dummy;

END FOR;

END;

the above scenario works fine.

when I use CALL ins_msg_porc statement in procedure as mentioned below.

Scenario2:-

-----------------

BEGIN

for cnt in 0..5 DO

CALL ins_msg_proc('This is iteration number'||cnt);

END FOR;

END; 

where cnt is input parameter

the validation of procedure is successful,but when I activate it throws an error as below.

invalid name of function or procedure: INS_MSG_PROC.

It is inbuilt procedure , I could not understand what is wrong with this procedure.Awaiting feedback.

Thanks

Santosh

[Former Member]

Hi Santosh,

I might be wrong here, but are you sure the ins_msg_proc() is a predefined procedure. This procedure is mentioned in the SQL Script guide as an example of aclling a procedure from a loop..but I doubt if this is an inbuilt procedure.

Let me know where you see this mentioned..

Regards, Rahul

[Former Member]

Hi Rahul

You might be correct, I saw that method in SQL Script guide, I tried to find it in *SYS* folders,but I could not find it .May be that method is obsolete now.But if we want to write some thing to the screen which method can we use any idea on this?

Thanks

Santosh