hanasql-scriptshana-sql-script

Pass CLOB to system procedure SAP HANA


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;

Solution

  • 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