Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Creation of Read Write Procedures in HANA

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

Tags:
Former Member
replied

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

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question