Skip to Content
avatar image
Former Member

HANA Procedure Basics

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Apr 07, 2012 at 09:55 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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