on 09-15-2014 2:58 PM
Hello SDN.
I have a stored procedure with inbound table parameter:
PROCEDURE "MY_SCHEMA"."demo.dicts.sql::test" (
IN DICT_DATA MY_SCHEMA."demo.dicts.db::DATA.tt_common_dict_proc"
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS
BEGIN
where MY_SCHEMA."demo.dicts.db::DATA.tt_common_dict_proc" is a defined type in HDBDD file:
...
type tt_common_dict_proc{
ID: DictId;
TYPE_ID: DictId;
PARENT_ID: DictId;
NAME: String255;
CODE_VALUE: DictCodeValue;
SHORT_NAME: String60;
};
...
Please note that I use *.hdbprocedure syntax instead of CREATE PROCEDURE as it recommended for SP7.
I try to call my procedure from SQL console with these commands:
drop table #test1;
create local temporary table #test1 like "MY_SCHEMA"."demo.dicts.db::DATA.tt_common_dict_proc";
insert into #test1 values('1', '', '', 'Dict name 1', 'CODE 1', 'Short name1');
insert into #test1 values('2', '', '', 'Dict name 2', 'CODE 2', 'Short name2');
insert into #test1 values('3', '', '', 'Dict name 3', 'CODE 3', 'Short name3');
call "MY_SCHEMA"."demo.dicts.sql::test"(#test1);
but getting the following error:
Could not execute 'call "MY_SCHEMA"."demo.dicts.sql::test"(#test1)' in 18 ms 815 µs .
SAP DBTech JDBC: [1301]: numeric or value error: [1301] "MY_SCHEMA"."demo.dicts.sql::test":
line 29 col 2 (at pos 987): [1301] (range 3) numeric or value error exception
It seems that in fact parameter DICT_DATA in procedure is not a table-type parameter.
How I can define a table-type parameter based on the type from CDS?
Regards, Lev
In your CDS file, how is DictId defined?
Cheers,
Rich
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
There are definitions of used types:
type Guid32: String(32);
type DictId: String(10);
type DictCodeValue: String(12);
type DictStatus: String(1);
type String255: String(255);
type String60: String(60);
type BoolString: String(1);
type TypeHistory{
CREATE_DATE: UTCTimestamp;
CREATOR: String255;
CHANGE_DATE: UTCTimestamp;
EDITOR: String255;
};
type TypeDict {
TYPE_ID: DictId;
PARENT_ID: DictId;
NAME: String255;
CODE_VALUE: DictCodeValue;
SHORT_NAME: String60;
STATUS: DictStatus;
HISTORY: TypeHistory;
};
type tt_common_dict{
ID: DictId;
DATA: TypeDict;
};
This is my procedure. Actually I just began to develop it and faced with problem of passing table parameter so my procedure is very small:
PROCEDURE "MY_SCHEMA"."demo.dicts.sql::test" (
IN DICT_DATA MY_SCHEMA."demo.dicts.db::DATA.tt_common_dict_proc"
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS
BEGIN
/*****************************
Write your procedure logic
*****************************/
DECLARE v_id VARCHAR(10);
DECLARE v_code VARCHAR(10);
DECLARE lv_code VARCHAR(10);
DECLARE CURSOR c_data for select * from :DICT_DATA;
-- here I need to loop over my table parameter
FOR c_data_row AS c_data DO
-- here will be check for duplicate DICT_DATA-code_value in DB table
END FOR;
END;
By the way, can you please tell me, is there a way in HANA to do like ABAP syntax "SELECT FROM (lv_tablename) INTO..." where lv_tablename is a local variable contains DB table name?
Regards, Lev
User | Count |
---|---|
90 | |
10 | |
10 | |
10 | |
7 | |
7 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.