Changing schema in a stored procedure

[richard_hirsch]

Hi,

is it possible to change the schema in a stored procedure? For example to access a different schema?

Thanks.

D.

[rindia · Accepted solution]

Hi,

In a procedure we can refer to any schema.

For example if table1 exists in different schemas schema1, schema2 then you can refer as

out1 = CE_COLUMN_TABLE(schema1.table1);

out2 = CE_COLUMN_TABLE(schema2.table1);

Regards

Raj

[former_member184768]

Hi D,

Yes it is possible.

2 options:

1) As mentioned by Raj, you can prefix the object name with the schema name you wish to access.

But I have a feeling, you are looking for something else (considering your another question on identifying the current schema). So the second option is as follows:

2)

create procedure pr_chk_schema()

as

v_schema varchar(20);

begin

select current_schema into v_schema from dummy;

insert into my_log values (:v_schema);

exec 'set schema HANATEST16'; --- direct statement is now allowed, so we fool HANA.

select current_schema into v_schema from dummy;

insert into my_log values (:v_schema);

end;

Regards,

Ravi

[richard_hirsch]

We tried this and it didn't work:

MY_SCHEMA_1.TestTable hat 100 rows.

MY_SCHEMA_2.TestTable hat 2 rows.

SP Test4 Output: MY_SCHEMA_1, MY_SCHEMA_2, 100, 100

===================================

CREATE PROCEDURE "Test4"

AS

BEGIN

declare v_schema1 varchar(20);

declare v_schema2 varchar(20);

declare v_count1 integer;

declare v_count2 integer;

exec 'set schema MY_SCHEMA_1';

select count(*) into v_count1 from "TestTable"; -- count of TestTable is correct

select current_schema into v_schema1 from dummy; -- current_schema is MY_SCHEMA_1

exec 'set schema MY_SCHEMA_2';

select count(*) into v_count2 from " TestTable"; -- count of TestTable is not correct

select current_schema into v_schema2 from dummy; -- current_schema is MY_SCHEMA_2

select :v_schema1, :v_schema2, :v_count1, :v_count2 from dummy;

END;

[former_member184768]

, looks like we really cannot fool HANA. The session context is not set to the different session. Just the parameter is set to the different value.

In this case, you may have to go with the first option. But if your schema name is expected to be dynamic or parameterized, then it may not be possible. Or you can check with SAP on that.

May be, if you provide more details on the requirement, we can look into the alternative.

BTW, you might have noticed that after you execute the procedure, the schema is set to MY_SCHEMA_2 and you may not be able to access the objects from MY_SCHEMA_1 without switching the schema back to schema 1.

Regards,

Ravi

[rindia]

Yes you are right. I too getting incorrect results,

I thought of initializing the variables will solve. But nope.

Finally I modified the code to remove dynamic stmt and its working as expected with this code.

CREATE PROCEDURE "Test4"

AS

BEGIN

declare v_schema1 varchar(20);

declare v_schema2 varchar(20);

declare v_count1 integer ;

declare v_count2 integer ;

--exec 'set schema MY_SCHEMA_1';

v_count1 := 0;

  select count(*) into v_count1 from MY_SCHEMA_1."TestTable"; 

select current_schema into v_schema1 from dummy;    

--exec 'set schema MY_SCHEMA_2';

v_count2 := 0;

select count(*) into v_count2 from MY_SCHEMA_2."TestTable"; 

select current_schema into v_schema2 from dummy;                    

select :v_schema1, :v_schema2, :v_count1, :v_count2 from dummy;

END

;

Please let us know what exactly your requirement is, so that someone can help you with probable workarounds....

Regards

Raj