Oracle PL/SQL Query

Hi All, im new in Uipath and i was about to add an existing oracle sql query. just curious how to declare the sql script as a query command?

i am able to run simple sql query enclose e.g " sql command " somehow i am not sure how will i execute the below sql command as i undertand it should be enclose in double quotes.

CREATE GLOBAL TEMPORARY TABLE tmptbl_policy_list ON COMMIT PRESERVE ROWS

AS

SELECT

--A.SEQUENCE_NUMBER,

CASE a.portfolio_type

    WHEN '1' THEN

        'TREATY'

    ELSE

        'FACULTATIVE'

END AS "PORTFOLIO TYPE",

a.underwriting_year       AS uy,

to_number(a.contract_number) AS "CONTRACT NUMBER",

a.insured_name            AS "DESCRIPTION",

TO_CHAR(a.inception_date, 'DD-MON-YY') AS "INCEPTION DATE",

TO_CHAR(a.expiry_date, 'DD-MON-YY') AS "EXPIRY DATE",

e.lob_description         AS line,

h.branch_name             AS branch,

i.sub_branch_name         AS "SUB BRANCH",

CASE a.type_of_contract

    WHEN '1' THEN

        'PROP'

    ELSE

        'NON-PROP'

END AS ptcr,

f.premium_currency        AS currency,

to_number(a.share_signed) AS "SHARE SIGNED %",

to_number(f.epi_whole) AS "EPI WHOLE",

to_number(f.original_gwp) AS premium,

to_number(j.max_liability) AS "MAX LIABILITY",

to_number(j.max_liability *(a.share_signed / 100)) AS "MAX LIABILITY - OUR SHARE",

upper(b.description) AS status,

a.cedant_code             AS "CEDANT CODE",

c.broker_cedant_name      AS "CEDANT NAME",

nvl(a.broker_code, '-') AS "BROKER CODE",

nvl(g.broker_cedant_name, '-') AS "BROKER NAME",

a.domicile_country_code   AS "DOMICILE COUNTRY CODE",

d.country_description     AS "DOMICILE COUNTRY",

CASE a.retrocession_ind

    WHEN 'E'   THEN

        'YES'

    WHEN 'R'   THEN

        'OLD POLICY'

    ELSE

        'NO'

END AS "IS RETROCEEDED?"

FROM

inwards_policy             a

INNER JOIN contract_status            b ON a.contract_status = b.contract_status

INNER JOIN broker_cedant              c ON a.cedant_code = c.broker_cedant_code

INNER JOIN country                    d ON a.domicile_country_code = d.country_code

INNER JOIN lob                        e ON a.lob_code = e.lob_code

LEFT JOIN inwards_policy_premium     f ON a.sequence_number = f.inwards_policy_sequence_no

LEFT JOIN broker_cedant              g ON a.broker_code = g.broker_cedant_code

LEFT JOIN branch                     h ON a.branch_group_code = h.branch_group_code

                      AND a.branch_code = h.branch_code

LEFT JOIN sub_branch                 i ON a.branch_group_code = i.branch_group_code

                          AND a.branch_code = i.branch_code

                          AND a.sub_branch_code = i.sub_branch_code

LEFT JOIN inwards_policy_liability   j ON a.sequence_number = j.inwards_policy_sequence_no

–WHERE

– A.CONTRACT_NUMBER IN (2299570, 2352623,2336516)

ORDER BY

a.portfolio_type,

a.contract_number,

a.underwriting_year;

BEGIN

–Insert Line to create temporary table

–Rename table name with what was created above

as_xlsx.query2sheet('SELECT * FROM tmptbl_Policy_List');

–Rename output file name with reportname timespamp-.xls

as_xlsx.save('REPORT_DIR', TO_CHAR(SYSDATE, 'DD-MM-YYYY-HH24-MI-SS')

                           || '-Policy_List'

                           || '.xlsx');

–Rename table name with what was created above

EXECUTE IMMEDIATE ( 'TRUNCATE TABLE tmptbl_Policy_List' );

EXECUTE IMMEDIATE ( 'COMMIT' );

EXECUTE IMMEDIATE ( 'Drop TABLE tmptbl_Policy_List' );

EXECUTE IMMEDIATE ( 'COMMIT' );

END;

CREATE OR REPLACE PROCEDURE POLICY_LIST;

BEGIN

SELECT FUNCTION_ID FROM RMS_FUNCTION_TEXT

WHERE FUNCTION_NAME ='Receipt Enquiry;

END POLICY_LIST;

Hi,

Sharing my thought. Store your query in one text file and use read text file activity to read the whole query into one string and pass that string to your execute Query activity. please try and let me know if it works.

Regards,
Kirankumar.

Hi @kirankumar.mahanthi1, appreciate your prompt reply. I put the query in the text file and stored in a string variable as you suggest. unfortunately it didn’t work . it throws this error:

Hi,

ok. in that case could you please try insert this query in the config excel as value and use this config value in the execute query activity.

in one of my process i have done the same way. it is working fine for me.

if it is stored procedure could you please select command type as stored procedure in the execute query activity and insert if you have any arguments to pass to that procedure. if it is a normal query please try the option i have suggested above.

Put the script in a config file but thing. the scripts works for me with normal query 'Select, Insert ’ etc in uipath but the script above throws error “invalid character” whereas it works fine when i run it in oracle sql developer. any idea what could be wrong?

Hi,

ORA00911 error usually we will get if we have any invalid character in the query. i sense some extra semicolon causing the error. before we had one issue with the power bi. the query usually work fine in the SSMS but it will not work in powerbi. in that time we have removed extra semicolon in the end of the query to resolve the issue. could you please try remove semicolon at the end if you have any or some invalid character to resolve the issue.

if it is possible to mention your full query one more time to analyze the issue. thanks.

Regards,
Kirankumar.

Thanks @kirankumar.mahanthi1 , i really appreciate your help.

i removed all the semi colons in every end of the line. btw here’s the actual query:

CREATE GLOBAL TEMPORARY TABLE tmptbl_policy_list ON COMMIT PRESERVE ROWS
AS
SELECT
–A.SEQUENCE_NUMBER,
CASE a.portfolio_type
WHEN ‘1’ THEN
‘TREATY’
ELSE
‘FACULTATIVE’
END AS “PORTFOLIO TYPE”,
a.underwriting_year AS uy,
to_number(a.contract_number) AS “CONTRACT NUMBER”,
a.insured_name AS “DESCRIPTION”,
TO_CHAR(a.inception_date, ‘DD-MON-YY’) AS “INCEPTION DATE”,
TO_CHAR(a.expiry_date, ‘DD-MON-YY’) AS “EXPIRY DATE”,
e.lob_description AS line,
h.branch_name AS branch,
i.sub_branch_name AS “SUB BRANCH”,
CASE a.type_of_contract
WHEN ‘1’ THEN
‘PROP’
ELSE
‘NON-PROP’
END AS ptcr,
f.premium_currency AS currency,
to_number(a.share_signed) AS “SHARE SIGNED %”,
to_number(f.epi_whole) AS “EPI WHOLE”,
to_number(f.original_gwp) AS premium,
to_number(j.max_liability) AS “MAX LIABILITY”,
to_number(j.max_liability *(a.share_signed / 100)) AS “MAX LIABILITY - OUR SHARE”,
upper(b.description) AS status,
a.cedant_code AS “CEDANT CODE”,
c.broker_cedant_name AS “CEDANT NAME”,
nvl(a.broker_code, ‘-’) AS “BROKER CODE”,
nvl(g.broker_cedant_name, ‘-’) AS “BROKER NAME”,
a.domicile_country_code AS “DOMICILE COUNTRY CODE”,
d.country_description AS “DOMICILE COUNTRY”,
CASE a.retrocession_ind
WHEN ‘E’ THEN
‘YES’
WHEN ‘R’ THEN
‘OLD POLICY’
ELSE
‘NO’
END AS “IS RETROCEEDED?”
FROM
inwards_policy a
INNER JOIN contract_status b ON a.contract_status = b.contract_status
INNER JOIN broker_cedant c ON a.cedant_code = c.broker_cedant_code
INNER JOIN country d ON a.domicile_country_code = d.country_code
INNER JOIN lob e ON a.lob_code = e.lob_code
LEFT JOIN inwards_policy_premium f ON a.sequence_number = f.inwards_policy_sequence_no
LEFT JOIN broker_cedant g ON a.broker_code = g.broker_cedant_code
LEFT JOIN branch h ON a.branch_group_code = h.branch_group_code
AND a.branch_code = h.branch_code
LEFT JOIN sub_branch i ON a.branch_group_code = i.branch_group_code
AND a.branch_code = i.branch_code
AND a.sub_branch_code = i.sub_branch_code
LEFT JOIN inwards_policy_liability j ON a.sequence_number = j.inwards_policy_sequence_no
–WHERE
– A.CONTRACT_NUMBER IN (2299570, 2352623,2336516)
ORDER BY
a.portfolio_type,
a.contract_number,
a.underwriting_year;

BEGIN
–Insert Line to create temporary table

–Rename table name with what was created above
as_xlsx.query2sheet(‘SELECT * FROM tmptbl_Policy_List’);

–Rename output file name with reportname timespamp-.xls
as_xlsx.save(‘REPORT_DIR’, TO_CHAR(SYSDATE, ‘DD-MM-YYYY-HH24-MI-SS’)
|| ‘-Policy_List’
|| ‘.xlsx’);

–Rename table name with what was created above

EXECUTE IMMEDIATE ( 'TRUNCATE TABLE tmptbl_Policy_List' );
EXECUTE IMMEDIATE ( 'COMMIT' );
EXECUTE IMMEDIATE ( 'Drop TABLE tmptbl_Policy_List' );
EXECUTE IMMEDIATE ( 'COMMIT' );

END;

Hi,

After removing the semicolons did you try executing the activity in studio. Are you getting the same error or different error.

can i suggest one thing please remove the semicolon at the end of the query alone and try executing this activity and let me know. thanks.

Regards,
Kirankumar.

Yes, I removed all the semi colons in each lines


Processing: SICSNAT_NEWUAT-20211117T032151Z-001.zip…
Processing: SICS_LF_485-20211117T032149Z-001.zip…
and getting different error ’ ORA-00933 SQL Command not properly ended’

Hi @VicP ,

Ok. Please don’t remove all the colons only remove colon from the END statement and try and let me know. Thanks.

Regards,
Kirankumar.

if you dont mind checking the oracle script i am using, you may download from here
https://drive.google.com/drive/folders/1VxfW6Qb7sXObNVtU7dWUYLFwRTUBfWDQ?usp=sharing

Ok thanks but I cannot try in my office laptop. Let me look into the link you provided. Did you try my latest message. Sorry I am not helping you much in this issue.

Regards,
Kirankumar.

I appreciate your time on this. yes i did and same error ORA-00933 SQL Command not properly ended

Ok thanks. Hi @rahulsharma any idea on this. Thanks.

Regards,
Kirankumar.

1 Like

Thanks for the tag mate @kirankumar.mahanthi1, appreciate it!

Welcome to the UiPath Community @VicP!

I think the issue is with the reserved characters in the query → This means there are some characters that are reserved in VB as a part of syntax and functions for eg.: " " double quote is reserved to let the complier to know that whatever is inside this is a string.

So I suggest you to replace the reserved characters with the required tag
for double quote simply add one more double quote, that will keep the double quote as it is.
For eg: ““ExtractR”” → this will be “ExtractR” in the complied query

You can give a try to this and also one link mentioned below can help.

Replace Slash with non-reserved character | PLSQL

What is suggested to go about the procedure by breaking to see what all this are causing the issue. Execute a smaller part to gain more clear idea.

Hope this helps!

your most welcome @rahulsharma buddy . thanks for looking into this.

Regards,
Kirankumar.

@rahulsharma @kirankumar.mahanthi1 thanks guys for helping me out !

i shorten my script and each trace line.

i added extra “” to strings that has double quotes e.g “IS RETROCEEDED?” to ““IS RETROCEEDED?””

tested with and without semi colon at the end of the query and same error

CREATE GLOBAL TEMPORARY TABLE tmptbl_policy_list ON COMMIT PRESERVE ROWS
AS
SELECT
CASE a.portfolio_type
WHEN ‘1’ THEN
‘TREATY’
ELSE
‘FACULTATIVE’
END AS ““PORTFOLIO TYPE””,
a.underwriting_year AS uy,
CASE a.retrocession_ind
WHEN ‘E’ THEN
‘YES’
WHEN ‘R’ THEN
‘OLD POLICY’
ELSE
‘NO’
END AS ““IS RETROCEEDED?””
FROM
inwards_policy a
ORDER BY
a.portfolio_type,
a.underwriting_year;
BEGIN
as_xlsx.query2sheet(‘SELECT * FROM tmptbl_Policy_List’);
as_xlsx.save(‘REPORT_DIR’, TO_CHAR(SYSDATE, ‘DD-MM-YYYY-HH24-MI-SS’)||’-Policy_List’||’.xlsx’);
EXECUTE IMMEDIATE ( ‘TRUNCATE TABLE tmptbl_Policy_List’ );
EXECUTE IMMEDIATE ( ‘COMMIT’ );
EXECUTE IMMEDIATE ( ‘Drop TABLE tmptbl_Policy_List’ );
EXECUTE IMMEDIATE ( ‘COMMIT’ );
END;