on 06-01-2014 1:05 PM
Hi Sravan,
Please refer http://help.sap.com/hana/SAP_HANA_Developer_Guide_en.pdf > Page Number 341, TOPIC : SQL Extensions for Procedures
Hope it helps.
Regards
Kumar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Slightly missed the point.
The question was about providing an INPUT variable.
An in fact it is not possible to call a procedure with an table type input variable from the SQL console.
You have to build a wrapper to call such a procedure:
create type myusers as table (user_name nvarchar(60));
create procedure count_users (IN user_tab myusers, OUT user_count bigint )
language sqlscript
as
begin
select count(*) into user_count from :user_tab;
end;
call count_users (? , ?)
Could not execute 'call count_users (? , ?)' .
SAP DBTech JDBC: [7]: feature not supported:
Parameterized input table parameter is not allowed: line 1 col 19 (at pos 18)
A wrapper for this could look like this:
create procedure call_cu (out user_count bigint)
language sqlscript
as
begin
v_users = select user_name from users;
call count_users (:v_users, :user_count);
end;
call call_cu (?)
--> 28
Unlike SQL*Plus for PL/SQL, the SQL console of SAP HANA is not a SQL Script runtime shell.
- Lars
Hi Lars,
Yup my bad, I missed the key point of the question. Thanks for pointing and correcting it.
Regards
Kumar
Hi,
You can call procedures with table type input parameters from SQL Console. But, you cannot specify these as ?s. What you can do is create temporary tables (or physical tables, views etc) and pass these as the parameters. You can find examples for this in PAL user guide. Here is sample from there:
--CREATE TABLES
CREATE COLUMN TABLE PAL_KMEANS_DATA_TBL LIKE PAL_KMEANS_DATA_T;
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (0 , 0.5, 'A', 0.5);
INSERT INTO PAL_KMEANS_DATA_TBL VALUES (1 , 1.5, 'A', 0.5);
-- and more
CREATE LOCAL TEMPORARY COLUMN TABLE #PAL_CONTROL_TBL LIKE PAL_CONTROL_T;
INSERT INTO #PAL_CONTROL_TBL VALUES ('THREAD_NUMBER',2,null,null);
INSERT INTO #PAL_CONTROL_TBL VALUES ('GROUP_NUMBER',4,null,null);
-- and more
CREATE COLUMN TABLE PAL_KMEANS_ASSIGN_TBL LIKE PAL_KMEANS_ASSIGN_T;
CREATE COLUMN TABLE PAL_KMEANS_CENTERS_TBL LIKE PAL_KMEANS_CENTERS_T;
--CALL PROCEDURE 2 input parameters, 2 output parameters
-- 1st parameter is a column table
-- 2nd parameter is a temporary table
-- output parameters are physical tables these could be ?s
CALL _SYS_AFL.PAL_KMEANS_PROC(PAL_KMEANS_DATA_TBL, #PAL_CONTROL_TBL,
PAL_KMEANS_ASSIGN_TBL, PAL_KMEANS_CENTERS_TBL)
with OVERVIEW;
SELECT * FROM PAL_KMEANS_CENTERS_TBL;
SELECT * FROM PAL_KMEANS_ASSIGN_TBL;
Regards,
Dinu
You can also put the call inside a begin end statement and declare your input table variable.
do ()
begin
-- DECLARE TABLE VARIABLE
declare lt_tab "MyType";
-- FILL TABLE VARIABLE
:lt_tab.INSERT(('A', 'B', 4), 1 );
call "MyProcedure" (
IT_TAB => :lt_tab,
ET_DATA => ?
);
end;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This message was moderated.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
There is another scenario where you will be using particular fields from the input table. So it can be done in this way.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi All,
i am creating input parameter in hana graphical represent calculation views.
while creating input parameter i selected derived from stored procedure.
after that i select stored procedure it was giving below error
* Procedures with output parameters of type Table not supported
The below code was my procedure
BEGIN
COLUMN_SEC=SELECT MAX(COLUMN_SEC) AS COLUMN_SEC FROM (
SELECT
CASE WHEN ROLE_NAME LIKE '%ALL%' THEN 1 ELSE 0 END AS COLUMN_SEC
FROM SYS.GRANTED_ROLES
WHERE GRANTEE = SESSION_USER);
END;
and i created out parameter as well as
Please help me.
Regards,
Srini
| User | Count |
|---|---|
| 3 | |
| 3 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.