Need help in executing Database query

Hi Team,

I wanted to execute a sql query which is inserting and finally giving some Id’s as output.

I am using Execute query activity. which is giving error.

But if i am manually executing the query in editor it is giving output.

please find the below Error screenshot.

image

Hi
May I know what type of statement is that
@Shaik.Yezdani

can i see the query which you are using??

“INSERT INTO TableName(‘NAME’,‘AGE’)VALUES (‘BOT’,‘BNG’)”
try this
@Shaik.Yezdani
cheers

Hi @Shaik.Yezdani
use this query in insert activity

Thanks
Ashwin S

If it’s inserting datatable then we can directly use INSERT activity instead of EXECUTE QUERY activity

Or try with EXECUTE NON QUERY activity instead of EXECUTE QUERY activity, as it is the one meant for insert statement and we can Choose the type
Of statement we want in SQL property panel https://docs.uipath.com/activities/docs/execute-non-query

Cheers @Shaik.Yezdani

@Pradeep_Shiv @Palaniyappan Query is having multiple statements.

It is having 2 statements 1. select and insert

Please find the below query

"SET SERVEROUTPUT ON;
DECLARE
TYPE slsorder_tab IS TABLE OF VARCHAR2 (2000);
slsorder slsorder_tab := slsorder_tab ();
CNT VARCHAR2(10):= 0;

BEGIN
SELECT esd.EXTERNAL_SYSTEM_ID
BULK COLLECT INTO slsorder
FROM external_system_data esd
LEFT OUTER JOIN proxy_instance_status pis
ON esd.EXTERNAL_SYSTEM_ID = TO_CHAR (pis.owner_id)
WHERE esd.EXTERNAL_SYSTEM_ID IN (SELECT TO_CHAR (ORDER_ID)
FROM ord_view@VOMWFPL
WHERE ORDER_TYPE_CD = ‘SLS’
AND ORDER_STATUS_CD = ‘OPN’)
AND esd.OWNER_TYPE = ‘ORDER’
AND esd.MAPPING_CLOSE_DATE IS NULL
AND pis.owner_id IS NULL
AND esd.EXTERNAL_SYSTEM_NAME = ‘OES’;

FOR i IN 1 … slsorder.COUNT

LOOP
IF slsorder.EXISTS (i)
THEN
DBMS_OUTPUT.put_line (slsorder (i));

     INSERT INTO PROXY_INSTANCE_STATUS (PROXY_INSTANCE_STATUS_ID,
                                        PROXY_ID,
                                        PROXY_METHOD_NAME,
                                        OWNER_ID,
                                        OWNER_TYPE,
                                        PROCESS_START_TIME,
                                        STATUS_CD,
                                        STATUS_MESSAGE,
                                        EXT_SYS_RESPONSE_CODE)
          VALUES (PROXY_INSTANCE_STATUS_ID_SEQ.NEXTVAL,
                  22,
                  'EOCC',
                  slsorder (i),
                  'SALESORDER',
                  SYSDATE,
                  4,
                  'Success',
                  '0');
  END IF;
  CNT := CNT + 1;

END LOOP;

DBMS_OUTPUT.put_line (CNT||’ Rows have been inserted’);
END;"

@Pradeep_Shiv

"SET SERVEROUTPUT ON;
DECLARE
TYPE slsorder_tab IS TABLE OF VARCHAR2 (2000);
slsorder slsorder_tab := slsorder_tab ();
CNT VARCHAR2(10):= 0;

BEGIN
SELECT esd.EXTERNAL_SYSTEM_ID
BULK COLLECT INTO slsorder
FROM external_system_data esd
LEFT OUTER JOIN proxy_instance_status pis
ON esd.EXTERNAL_SYSTEM_ID = TO_CHAR (pis.owner_id)
WHERE esd.EXTERNAL_SYSTEM_ID IN (SELECT TO_CHAR (ORDER_ID)
FROM ord_view@VOMWFPL
WHERE ORDER_TYPE_CD = ‘SLS’
AND ORDER_STATUS_CD = ‘OPN’)
AND esd.OWNER_TYPE = ‘ORDER’
AND esd.MAPPING_CLOSE_DATE IS NULL
AND pis.owner_id IS NULL
AND esd.EXTERNAL_SYSTEM_NAME = ‘OES’;

FOR i IN 1 … slsorder.COUNT

LOOP
IF slsorder.EXISTS (i)
THEN
DBMS_OUTPUT.put_line (slsorder (i));

     INSERT INTO PROXY_INSTANCE_STATUS (PROXY_INSTANCE_STATUS_ID,
                                        PROXY_ID,
                                        PROXY_METHOD_NAME,
                                        OWNER_ID,
                                        OWNER_TYPE,
                                        PROCESS_START_TIME,
                                        STATUS_CD,
                                        STATUS_MESSAGE,
                                        EXT_SYS_RESPONSE_CODE)
          VALUES (PROXY_INSTANCE_STATUS_ID_SEQ.NEXTVAL,
                  22,
                  'EOCC',
                  slsorder (i),
                  'SALESORDER',
                  SYSDATE,
                  4,
                  'Success',
                  '0');
  END IF;
  CNT := CNT + 1;

END LOOP;

DBMS_OUTPUT.put_line (CNT||’ Rows have been inserted’);
END;"

@Palaniyappan @Pradeep_Shiv if i remove “SET SERVEROUTPUT ON” this from first line it is executing the query but giving blank output.

2 Likes

Then try like keeping these in a PROCEDURE and just mention the procedure name within double quotes in EXECUTE NON QUERY ACTIVITY so that this activity will call the procedure And execute all those statements

That would work either
Cheers @Shaik.Yezdani

if i remove “SET SERVEROUTPUT ON” this from first line it is executing the query

This is the key. I missed it in my previous searches. It’s not only that Studio / Run command activity ignores it, but trying to run whatever piece of PL/SQL starting with this will fail.

Been struggling for weeks.

Thanks a lot, @Shaik.Yezdani.