Oracle script exection- PL/SQL procedure

Help IT Automation
1

Hi Team,

I need to execute the below script in uipath, connecting using connection string.
Not able to get the output as expected:
[11:11] Sait, Girish

SET SERVEROUTPUT ON

DECLARE

CURSOR cur_wtn

IS

SELECT ref_id_value

FROM rcr_acct_mapping

WHERE ref_id_type = ‘BBTN’ AND ref_id_value IN (‘5415488482’); --input pattern here

v_wtn VARCHAR (10);

v_cid rcr_acct_mapping.cust_acct_id%TYPE;

v_list VARCHAR (75);

BEGIN

DBMS_OUTPUT.ENABLE (1000000);

FOR i IN cur_wtn

LOOP

v_wtn := i.ref_id_value;

SELECT NVL (cust_acct_id, 0)

INTO v_cid

FROM rcr_acct_mapping

WHERE ref_id_value = v_wtn AND ref_id_type = ‘BBTN’;

IF (v_cid = 0)

THEN

SELECT ‘QRP’

|| LPAD (MAPPING.rcr_prod_acct_id, 20, 0)

|| ’ ’

|| SUBSTR (temp.btn, 1, 3)

|| ’ ’

|| SUBSTR (temp.btn, 4, 3)

|| ’ ’

|| SUBSTR (temp.btn, 7, 4)

|| ’ ’

|| temp.cust_code

|| ’ ’

|| MAPPING.ref_id_value

|| ’ ’

|| ‘QRL’

|| LPAD (MAPPING.rcr_acct_mapping_id, 20, 0)

INTO v_list

FROM rcr_acct_mapping MAPPING, rcr_acct_prof_temp temp

WHERE MAPPING.ref_id_type = ‘BBTN’

AND temp.rcr_prod_acct_id = MAPPING.rcr_prod_acct_id

AND MAPPING.ref_id_value = v_wtn;

ELSE

SELECT ‘QRP’

|| LPAD (MAPPING.rcr_prod_acct_id, 20, 0)

|| ’ ’

|| SUBSTR (acct.btn, 1, 3)

|| ’ ’

|| SUBSTR (acct.btn, 4, 3)

|| ’ ’

|| SUBSTR (acct.btn, 7, 4)

|| ’ ’

|| acct.cust_cd

|| ’ ’

|| MAPPING.ref_id_value

|| ’ ’

|| ‘QRL’

|| LPAD (MAPPING.rcr_acct_mapping_id, 20, 0)

INTO v_list

FROM rcr_acct_mapping MAPPING, qw_acct_activity acct

WHERE MAPPING.ref_id_type = ‘BBTN’

AND acct.cust_acct_id = MAPPING.cust_acct_id

AND acct.create_dt = (SELECT MAX (create_dt)

FROM qw_acct_activity

WHERE cust_acct_id = MAPPING.cust_acct_id)

AND ref_id_value = v_wtn;

END IF;

DBMS_OUTPUT.put_line (v_list);

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

DBMS_OUTPUT.put_line ('wtn ’ || v_wtn);

END;

Expected results:
[image]
Find the error:
[image]

2

I tried with stored procedure but there is no stored procedure name present