cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Error when creating a procedure using HANA Studio

Former Member

on ‎07-13-2012 11:45 PM

0 Kudos

Hi,

I am encountering an error while trying to create a procedure using HANA studio.

Please find here with the error details and please suggest.

* error

Repository: Encountered an error in repository runtime extension;error preparing statement create procedure

"_SYS_BIC"."roby-hana-practice/ZPROC" ( out COUNTRY_NAME "_SYS_BIC"."roby-hana-practice/ZPROC/tabletype/COUNTRY_NAME" , in CUSTOMER_ID "_SYS_BIC"."roby-hana-practice/ZPROC/tabletype/CUSTOMER_ID" ) language SQLSCRIPT sql security definer as

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

n   BEGIN

nn SELECT "RM_SD_HANA"."CUSTOMER".COUNTRY_NAME

n   FROM "RM_SD_HANA"."CUSTOMER"

n   WHERE "RM_SD_HANA"."CUSTOMER".CUSTOMER_ID = CUSTOMER_ID

n   INTO COUNTRY_NAME;

nn END;

n  /********* End Procedure Script ************/ for oid {tenant: , package: roby-hana-practice, name: ZPROC, type: 2}

* SQL code

BEGIN

SELECT "RM_SD_HANA"."CUSTOMER".COUNTRY_NAME

FROM "RM_SD_HANA"."CUSTOMER"

WHERE "RM_SD_HANA"."CUSTOMER".CUSTOMER_ID = CUSTOMER_ID

INTO COUNTRY_NAME;

END;

Output Parameter is : COUNTRY_NAME and Input Parameter is : CUSTOMER_ID.

Schema for the Procedure is _SYS_BIC and Default schema is SYSTEM.

Thank you,

Tk.



Show replies
Show replies
Answer

Accepted Solutions (0)

Answers (1)

Answers (1)

pamela_sharma
Discoverer
‎07-19-2012 4:22 PM
0 Kudos

HI Tk,

Have you checked the SQLScript Guide section scalar variables?

Try to change your statement into something closer to the provided example like:

SELECT "RM_SD_HANA"."CUSTOMER"."COUNTRY_NAME"

INTO "COUNTRY_NAME"

FROM "RM_SD_HANA"."CUSTOMER"

WHERE "RM_SD_HANA"."CUSTOMER"."CUSTOMER_ID" = :CUSTOMER_ID

Best regards, Pam

Show replies
Show replies
Former Member
‎07-23-2012 6:56 PM
0 Kudos

Hi Pamela ,

Appreciate your reply, I have  followed the syntax that was advised , but couldn't get it right.

I am enclosing an attachment for you to look at if possible.

As i am new to PL/SQL , I would really appreciate your help on this issue. Thank you.

Tk.

pamela_sharma
Discoverer
‎07-23-2012 7:16 PM
0 Kudos

Hi Tk,

your output parameter is of type table with one column "Country_Name". So you need to have something like this

COUNTRY_NAME = SELECT "RM_SD_HANA"."CUSTOMER"."COUNTRY_NAME"

AS "COUNTRY_NAME"

FROM "RM_SD_HANA"."CUSTOMER"

WHERE "RM_SD_HANA"."CUSTOMER"."CUSTOMER_ID" = :CUSTOMER_ID

I am sure that this will work. Your coding would have worked, if COUNTRY_NAME  would have been defined as scalar variable. But the output of the procedure is of type table. On input parameter level you can distinguish between tables or scalar variable - check it out. 

Cheers,Pam

former_member184768
Active Contributor
‎07-25-2012 1:49 PM
0 Kudos

Hi TK,

Can you please go to your procedure and send the contents from the "Create Statement" tab. The code can be corrected once you post it here.

Regards,

Ravi

Former Member
‎07-30-2012 1:09 PM
0 Kudos

Hey Pam,

I have tried the way you have suggested but I couldn't get it activated.

I am enclosing the error log for your review. Please verify. Thank you.

<?xml version="1.0" encoding="UTF-8" ?>

- <modelerjob id="HAN_Activation_SYSTEM_1343704228063">

- <jobinfo>

  <Jobtype>Activation</Jobtype>

  <startdate>Mon Jul 30 20:10:28 PDT 2012</startdate>

  <enddate>Mon Jul 30 20:10:28 PDT 2012</enddate>

  <elapsedtime>0.68 Seconds</elapsedtime>

  <systemuri>newdb://HAN:HANAS:52:SYSTEM</systemuri>

  <result>ERROR</result>

  </jobinfo>

- <executioninfo>

- <message>

  <status>ERROR</status>

  <info>Activation of object ZPROC</info>

- <message>

  <status>OK</status>

  <info>Validation of roby-hana-practice.ZPROC</info>

- <message>

  <status>OK</status>

  <info>Client side validation</info>

- <message>

  <status>INFORMATION</status>

  <info>No Rule is Registered</info>

  </message>

  </message>

- <message>

  <status>OK</status>

  <info>Server side validation</info>

- <message>

  <status>OK</status>

  <info>Activation ID: 4399</info>

- <message>

  <status>OK</status>

  <info>No error</info>

  </message>

  </message>

  </message>

  </message>

- <message>

  <status>OK</status>

  <info>Activation of Dependent objects</info>

- <message>

  <status>OK</status>

  <info>There are no dependent objects to activate</info>

  </message>

  </message>

- <message>

  <status>ERROR</status>

  <info>Activating at Repository</info>

- <message>

  <status>ERROR</status>

  <info>Activation ID: 4400</info>

- <message>

  <status>ERROR</status>

  <info>Repository: Encountered an error in repository runtime extension</info>

- <message>

  <status>INFORMATION</status>

  <info>No error</info>

  </message>

- <message>

  <status>ERROR</status>

  <info>Repository: Encountered an error in repository runtime extension;error preparing statement create procedure "_SYS_BIC"."roby-hana-practice/ZPROC" ( out COUNTRY_NAME "_SYS_BIC"."roby-hana-practice/ZPROC/tabletype/COUNTRY_NAME" , in CUSTOMER_ID NVARCHAR(10) ) language SQLSCRIPT sql security definer as n /********* Begin Procedure Script ************/ nBEGIN nnCOUNTRY_NAME = SELECT "RM_SD_HANA"."CUSTOMER".COUNTRY_NAMEnAS "COUNTRY_NAME"nFROM "RM_SD_HANA"."CUSTOMER"nWHERE "RM_SD_HANA"."CUSTOMER".CUSTOMER_ID = :CUSTOMER_IDnEND; n /********* End Procedure Script ************/ for oid {tenant: , package: roby-hana-practice, name: ZPROC, type: 2}</info>

  </message>

  </message>

  </message>

  </message>

  </message>

  </executioninfo>

- <objectresults>

  <object result="Completed" type="Activation" uri="newdb://HAN:HANAS:52:SYSTEM/roby-hana-practice/procedures/ZPROC.procedure" />

  </objectresults>

  </modelerjob>

Former Member
‎07-30-2012 1:12 PM
0 Kudos

Hey Ravi,

As suggested by Pam ,I am using the syntax :

COUNTRY_NAME = SELECT "RM_SD_HANA"."CUSTOMER"."COUNTRY_NAME"

AS "COUNTRY_NAME"

FROM "RM_SD_HANA"."CUSTOMER"

WHERE "RM_SD_HANA"."CUSTOMER"."CUSTOMER_ID" = :CUSTOMER_ID

Here the CUSTOMER_ID is of Scalar type and COUNTY_NAME is a Table Type with COUNTRY_NAME as Table element.

While Activating I am encountering an error which i am enclosing for your review. Thank you.

<?xml version="1.0" encoding="UTF-8" ?>

- <modelerjob id="HAN_Activation_SYSTEM_1343704228063">

- <jobinfo>

  <Jobtype>Activation</Jobtype>

  <startdate>Mon Jul 30 20:10:28 PDT 2012</startdate>

  <enddate>Mon Jul 30 20:10:28 PDT 2012</enddate>

  <elapsedtime>0.68 Seconds</elapsedtime>

  <systemuri>newdb://HAN:HANAS:52:SYSTEM</systemuri>

  <result>ERROR</result>

  </jobinfo>

- <executioninfo>

- <message>

  <status>ERROR</status>

  <info>Activation of object ZPROC</info>

- <message>

  <status>OK</status>

  <info>Validation of roby-hana-practice.ZPROC</info>

- <message>

  <status>OK</status>

  <info>Client side validation</info>

- <message>

  <status>INFORMATION</status>

  <info>No Rule is Registered</info>

  </message>

  </message>

- <message>

  <status>OK</status>

  <info>Server side validation</info>

- <message>

  <status>OK</status>

  <info>Activation ID: 4399</info>

- <message>

  <status>OK</status>

  <info>No error</info>

  </message>

  </message>

  </message>

  </message>

- <message>

  <status>OK</status>

  <info>Activation of Dependent objects</info>

- <message>

  <status>OK</status>

  <info>There are no dependent objects to activate</info>

  </message>

  </message>

- <message>

  <status>ERROR</status>

  <info>Activating at Repository</info>

- <message>

  <status>ERROR</status>

  <info>Activation ID: 4400</info>

- <message>

  <status>ERROR</status>

  <info>Repository: Encountered an error in repository runtime extension</info>

- <message>

  <status>INFORMATION</status>

  <info>No error</info>

  </message>

- <message>

  <status>ERROR</status>

  <info>Repository: Encountered an error in repository runtime extension;error preparing statement create procedure "_SYS_BIC"."roby-hana-practice/ZPROC" ( out COUNTRY_NAME "_SYS_BIC"."roby-hana-practice/ZPROC/tabletype/COUNTRY_NAME" , in CUSTOMER_ID NVARCHAR(10) ) language SQLSCRIPT sql security definer as n /********* Begin Procedure Script ************/ nBEGIN nnCOUNTRY_NAME = SELECT "RM_SD_HANA"."CUSTOMER".COUNTRY_NAMEnAS "COUNTRY_NAME"nFROM "RM_SD_HANA"."CUSTOMER"nWHERE "RM_SD_HANA"."CUSTOMER".CUSTOMER_ID = :CUSTOMER_IDnEND; n /********* End Procedure Script ************/ for oid {tenant: , package: roby-hana-practice, name: ZPROC, type: 2}</info>

  </message>

  </message>

  </message>

  </message>

  </message>

  </executioninfo>

- <objectresults>

  <object result="Completed" type="Activation" uri="newdb://HAN:HANAS:52:SYSTEM/roby-hana-practice/procedures/ZPROC.procedure" />

  </objectresults>

  </modelerjob>

pamela_sharma
Discoverer
‎07-30-2012 1:18 PM
0 Kudos

Hi TK, can you quickly just reactivate again?

Maybe you just close the old session and open a new one... I have the feeling that will do the trick...

If  the error still exists, then give me the table definition and I recreate your procedure....

But as I have said it should actually work now, if you you followed the approach I have suggested.

Best regards, Pam

Former Member
‎08-01-2012 7:52 PM
0 Kudos

Hi TK,

I have re-written your create procedure:

create procedure "_SYS_BIC"."roby-hana-practice/ZPROC"

( in CUSTOMER_ID "_SYS_BIC"."roby-hana-practice/ZPROC/tabletype/CUSTOMER_ID",

out COUNTRY_NAME "_SYS_BIC"."roby-hana-practice/ZPROC/tabletype/COUNTRY_NAME")

language SQLSCRIPT sql security definer reads sql data as

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

BEGIN

      COUNTRY_NAME =  SELECT "RM_SD_HANA"."CUSTOMER".COUNTRY_NAME

                                        FROM "RM_SD_HANA"."CUSTOMER"

                                        WHERE "RM_SD_HANA"."CUSTOMER".CUSTOMER_ID = :CUSTOMER_ID;

END;

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

Note: The in and out line got wrapped, so you may want to adjust accordingly in HANA Studio.

Can you try it on your HAN instance?


Regards,

Ferry

Ask a Question