Pass CLOB to system procedure SAP HANA

Question

How do I pass a clob to system procedure without getting an error?

I am trying to pass:

   CALL SYS.GET_PROCEDURE_OBJECTS(CREATE PROCEDURE "IA_ATP"."ia_xsjob.xsjob_procedures_folder::atp_procedure" ( ) 
        LANGUAGE SQLSCRIPT
        SQL SECURITY INVOKER 
    
    AS BEGIN
    
    DROP TABLE IA_ATP.ATP;
    
    CREATE COLUMN TABLE IA_ATP.ATP AS (SELECT ATP_VIEW.*, UTCTOLOCAL(CURRENT_UTCTIMESTAMP,'EST') AS CREATE_TS FROM IA_ATP.ATP_VIEW WHERE  
    OPEN_PRICE_REQUESTS <>0
    OR TOTAL_ON_HAND <> 0 
    OR OPEN_CUSTOMER_ORDERS <> 0  
    OR OPEN_CUSTOMER_DELIVERIES <> 0 
    OR OPEN_OUTBOUND_STOS <> 0 
    OR ATP <> 0 
    OR NEGATIVE_ATP <> 0
    OR BLOCK_STOCK <> 0
    );END;

Are you trying to call CALL SYS.GET_PROCEDURE_OBJECTS passing the create procedure string? — Abdel Dadouche, Dec 20 '18 at 14:57
Yes sir, I am passing in one of the procedures that I have created. — LearningHero, Dec 20 '18 at 19:10

score 1 · Accepted Answer · answered Dec 22 '18 at 02:40

The procedure "SYS"."GET_PROCEDURE_OBJECTS" has not been documented for general use.

But I managed to successfully call it from a SQL Script context:

Have a procedure definition:

create procedure pr_test (in i int)
as
begin
declare a, b int;

    select :i into a from dummy;
    select count(*) into b from objects;

    select :a, :b, :a*:b from dummy;
end;


call pr_test(10);

Assign this source code to a CLOB variable and call the system procedure with this variable:

do begin
declare proc_source clob;

proc_source :='create procedure pr_test (in i int)
                as
                begin
                declare a, b int;

                    select :i into a from dummy;
                    select count(*) into b from objects;

                    select :a, :b, :a*:b from dummy;
                end;';

 CALL SYS.GET_PROCEDURE_OBJECTS(:proc_source);

end;

Get the procecure result from the system view PROCEDURE_OBJECTS:

select * from procedure_objects;


SCHEMA_NAME PROCEDURE_NAME  OBJECT_SCHEMA   OBJECT_NAME OBJECT_TYPE_ID  OBJECT_TYPE START_POSITION  END_POSITION
SYSTEM      PR_TEST         PUBLIC          DUMMY       128             SYNONYM     100             105         
SYSTEM      PR_TEST         SYS             DUMMY       32              TABLE       100             105         
SYSTEM      PR_TEST         PUBLIC          OBJECTS     128             SYNONYM     141             148         
SYSTEM      PR_TEST         SYS             OBJECTS     31              VIEW        141             148         
SYSTEM      PR_TEST         PUBLIC          DUMMY       128             SYNONYM     189             194         
SYSTEM      PR_TEST         SYS             DUMMY       32              TABLE       189             194

Hello Lars, I got following error message "SAP DBTech JDBC: [258]: insufficient privilege: Not authorized" when I execute CALL statement within do-begin-end. But when I query "select * from procedure_objects;" alone I got no rows as output without error message — Eralper, Dec 24 '18 at 07:35
The table will only contain the results of the procedure execution in the current session. So if there’s a missing privilege that prevents the procedure from running, there won’t be any results. I don’t know which privileges or roles cover this procedure - I just ran it via the SYSTEM user as it owns the procedure. — Lars Br., Dec 24 '18 at 12:01
Lars thank you so much. One more questions, how do I call the GET_PROCEDURE_OBJECTS if the procedure I am inserting has an apostrophe within it? I ran your procedure and it worked beautifully using your example, however, when I run it using the procedure that I have which is pasted above I get incorrect syntax near "EST". Your support is appreciated in advance. — LearningHero, Dec 24 '18 at 13:41
Haven’t tried it, but my bet is that you just need to escape the single quote as you usually do in SQLSCRIPT (repeating single quotes `'''` to escape `'`. — Lars Br., Dec 24 '18 at 22:21

Pass CLOB to system procedure SAP HANA

1 Answers1