SQL Type Oracle Procedure

unknown

I have SQL Query node with type run SQL procedure

CALL PROC LAWSON.UPDATE_GTINS
RECORDPROCESSED DECIMAL = 'empty' 22 INOUT

I don't know how to assign procedure out parameter "recordprocessed" to a variable. Can someone provide a pointer?

Find more posts tagged with

Process Automation (IPA) - General Discussions

Accepted answers

unknown

Hi dchen, I am not an Oracle person but I am familiar with cursor processing and stored procedures in SQL Server. Here is an alternate way to do your updates and get the record count you want, without using a stored procedure (this works with t-sql, I apologize in advance if Oracle does not work in the same way): Put your select statement into an SQLQuery node. Inside the SQLquery node, put an Assign node to store the RECORD_COUNT returned by the SQLquery node (this is the total record count I believe you are trying to get out of your procedure), and an SQLTxn node that performs an update on the record that the SQLquery node returns. If you cannot do it this way with Oracle, you can preselect the records you plan to update in a separate query, and save the RECORD_COUNT from the select in an Assign node inside the SQLQuery node.

All comments

russel-cornelson

What does your stored procedure do?

Example:

CREATE PROCEDURE getTasksAndFilters @User varchar(30)

AS

SELECT PFIUSERPROFILE, PTTASKNAME, PFIFILTERKEY, PFIUSERCATEGORY

FROM l10cur.PFIUSERCATEGORY

WHERE PFIUSERPROFILE = @User

This stored procedure will return the fields from the select statement; when the result is output to a process flow it will create variables SQLNODE_PFIUSERPROFILE, SQLNODE_PTTASKNAME, etc

I recommend:

1. create stored procedure

2. execute stored procedure in test system and review workunit log to see what variables are created at run time

3. modify flow to use returned variables

unknown

Thanks Russel. I have a stored procedure like this:

create or replace PROCEDURE UPDATE_GTINS (recordProcessed OUT NUMBER)

AS

CURSOR C1 IS

SELECT itm.item, itm.description, gtin.full_desc

FROM itemmast itm, icitemgtin gtin

WHERE itm.item = gtin.item and trim(itm.DESCRIPTION) != trim(gtin.full_desc);

item itemmast.item%type;

item_desc itemmast.description%type;

gtin_full_desc lawson.icitemgtin.full_desc%type;

BEGIN

recordProcessed := 0;

OPEN C1;

LOOP

FETCH C1 INTO item, item_desc, gtin_full_desc;

EXIT WHEN C1%NOTFOUND;

recordProcessed := recordProcessed + 1;

END LOOP;

CLOSE C1;

END UPDATE_GTINS;

I am trying to get the value out of recordProcessed parameter. Any suggestions on how to get this value?

unknown

Thank you for a different perspective. I will try that. I wanted to use a procedure to do batch update and be done.

dEPM-634 - Demonstrating_Whats_New_WB_12_0_8_vid.mp4

Copyright © 2025 Infor. All rights reserved.