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;
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