Creation of Read Write Procedures in HANA

[Former Member]

Hello Experts,

I've a scenario wherein, I need to take data from one of the table and do some modification to couple of cols and then insert the resultset into another table.

I believe to achieve this, the Procedure has to be a read-write procedure.

First of all, where to write this kind of procedure. Is it within SCHEMA or within the package ?

I don't get any option to select read write procedure within the SCHEMA but I do get option of read write procedure within my package as an "access mode".

Anyways, I did wrote the procedure at both places but here is the issue:

a) Procedure in SCHEMA: Executed fine but Doesn't populate data in the target table.

Snippet:

CREATE Procedure <SCHEMA_NAME>.<PROC_NAME>

LANGUAGE SQLSCRIPT

AS

/**Local Variable declarations **/

v_fyear nvarchar(4);

v_period nvarchar(7);

BEGIN

  it_goals1 = Select * from <SCHEMA_NAME>.<SOURCE_TABLE>;

  it_goals2 = Select ........from :it_goals1;

   INSERT INTO <SCHEMA_NAME>.<TARGET_TABLE> SELECT * from it_goals2;

END;

b) Procedure in Package: I cant activate it as i get below compilation error.

Message :

          Repository: Internal error during statement execution, please see the database error traces for additional details;error executing statement; insufficient privilege: Not authorized at ptime/query/checker/query_check.cc:2137

Can you please help me here in resolving the issue ?

Thanks & regards,

Jomy

[former_member184768 · Accepted solution]

Hi Jomy,

You can write the procedures at both the places.

Regarding the schema based procedure, can you please check if your table variables actually contain any data. Please try to execute the SELECT statements in the SQL editor to see if they produce any output.

Raj already provided you one option and I also tried to write a small procedure as mentioned below and it works just fine for me.

create procedure PR_R_LOAD_DATA()

as

begin

vt1 = select * from t1;

vt2 = select c2 from :vt1;

insert into my_log select * from :vt2;

end;

So, I doubt the SELECT in it_goals1 and it_goals2 really generate any data set.

Regarding package based procedure issue, please refer to the following threads and check the access for _SYS_REPO.

http://scn.sap.com/thread/3420096

http://scn.sap.com/thread/3420051

Regards,

Ravi

[Former Member · Accepted solution]

Hello Ravi,

I didnt get what you mean by :

So, I doubt the SELECT in it_goals1 and it_goals2 really generate any data set.

As you also did the same

insert into my_log select * from :vt2;

Ofcourse when we just write SELECT * from it_goals1 in an SQL editor we will get error messages as it_goals is just a table variable and not a physical table.

Now the insertion is working because i was having some authorization issues, whcih is now fixed.

But what is most convenient way of debugging these SQL Procedures.

Can you please explain that ?

Thanks & regards,

Jomy

[former_member184768 · Accepted solution]

Hi Jomy,

What I meant is try the first select statement for it_goals1 in SQL editor with all the where conditions you mentioned in the code. You just provided the outline of the of the procedure and not the exact statements, whereas you also have declared some local variables like year and period. So please check if the select statement has some where conditions and if they are producing any results.

Regarding the debugging of procedure, the package way is the only way to debug. Alternatively you can use some log tables and try to insert data to the log table (very crude way be debugging) or insert the count(*) from it_goals1, it_goals2 to the log table. The right way is any way, the package based development.

Regards,

Ravi

[rindia]

Hi Jomy,

For your scenario, I created 2 tables:

Table1: BOM, Table2: BOM_2 whose structure is shown below.

Data in table BOM:

 

Data in table BOM_2 is empty.

In second table, there is an additional column AVBL_FOR_ISSU_QTY which  is based on calculation.

For simplicity I am hard coded it to 35 in procedure.

Then I wrote the procedure using SQL console as:

DROP PROCEDURE RAJ.INSERT_BOM_2_PROC;
CREATE PROCEDURE RAJ.INSERT_BOM_2_PROC
LANGUAGE SQLSCRIPT AS
V_AVBL_FOR_ISSU_QTY  INTEGER := 0;
BEGIN
  DECLARE CURSOR C_INSERT_BOM_2 FOR
    SELECT * FROM RAJ.BOM
    ORDER BY MTRL_ID;
    
  FOR CUR_ROW AS C_INSERT_BOM_2 DO
    V_AVBL_FOR_ISSU_QTY := 35; /* hard coded value to 35 */
    INSERT INTO RAJ.BOM_2 VALUES (CUR_ROW.MTRL_ID,CUR_ROW.MTRL_NAME,
        CUR_ROW.WHSE,CUR_ROW.AVBL_QTY,CUR_ROW.ISSU_QTY,V_AVBL_FOR_ISSU_QTY);
  END FOR;  
END;

Once the procedure is created, we can invoke the procedure as

CALL RAJ.INSERT_BOM_2_PROC;

My table is populated with values:

Regards

Raj