Creation of Read Write Procedures in HANA
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.
CREATE Procedure <SCHEMA_NAME>.<PROC_NAME>
/**Local Variable declarations **/
it_goals1 = Select * from <SCHEMA_NAME>.<SOURCE_TABLE>;
it_goals2 = Select ........from :it_goals1;
INSERT INTO <SCHEMA_NAME>.<TARGET_TABLE> SELECT * from it_goals2;
b) Procedure in Package: I cant activate it as i get below compilation error.
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,
Ravindra Channe replied
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()
vt1 = select * from t1;
vt2 = select c2 from :vt1;
insert into my_log select * from :vt2;
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.