SQL/Golden Query Error - "ORA-00900: invalid SQL statement"

Help
1

Hello!

I have the following Golden Query that I am trying to and it is giving me this error message. When query is ran in Golden, it gives no issue. Only when put into the Execute Query activity:

Error message is :
Source: Execute Query

Message: ERROR [42000] [Microsoft] [ODBC driver for Oracle][Oracle]ORA-00900: invalid SQL statement

Exception Type: System.Data.Odbc.OdbcException

Query being ran is this:
"DEFINE starttime = sysdate - 1

SELECT CUST_CD,FRM_SHPG_LOC_CD,TO_SHPG_LOC_CD, SHPM_NUM, SHPM_DESC, CRTD_DTT, PLAN_ID

FROM SHPM_t

WHERE
CRTD_DTT > &&starttime
AND SHPM_NUM not like ‘SH-%’
AND CUST_CD = ‘FLNA’

order by CRTD_DTT desc"

Any help with this?

1 Like
2

dont you need something to define line endings?

1 Like
3

Hi
Welcome to UiPath community

Kindly mention like this when using variable
CRTD_DTT > @starttime

That is use a assign activity like this
And mention as
Str_starttime = Now.AddHours(-1)

Then in EXECUTE QUERY
And pass the input sql statement in a single line as
“SELECT CUST_CD,FRM_SHPG_LOC_CD,TO_SHPG_LOC_CD, SHPM_NUM, SHPM_DESC, CRTD_DTT, PLAN_ID
FROM SHPM_t
WHERE
CRTD_DTT > @str_starttime
AND SHPM_NUM not like ‘SH-%’
AND CUST_CD = ‘FLNA’
order by CRTD_DTT desc”

Cheers @meggasaurus

1 Like
4

this is not wrong, he is defining the variable in the oracle query… and it is && not @.

5

maybe you are missing the end of lines like @bcorrea said. check that or put entire query in one line to better work

6

I think you need to put semi columns after each command. So one for the define and one for the select. @meggasaurus

7

Maybe like this right?
SELECT CUST_CD,FRM_SHPG_LOC_CD,TO_SHPG_LOC_CD, SHPM_NUM, SHPM_DESC, CRTD_DTT, PLAN_ID FROM SHPM_t WHERE CRTD_DTT > && (sysdate - 1) AND SHPM_NUM not like 'SH-%' AND CUST_CD = 'FLNA' order by CRTD_DTT desc

1 Like
8

Hi there - I copied and pasted and am getting a different error on this -

Message: ERROR [42000] [Microsoft] [ODBC driver for Oracle][Oracle]ORA-00936: missing expression

Exception Type: System.Data.Odbc.OdbcException

9

Hi there - I used this query and it’s giving me a different error this time -

Source: Execute Query

MEssage: ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-01745: invalid host/bind variable name

Exception Type: System.Data.Odbc.OdbcException

10

sorry one char was missplaced:
SELECT CUST_CD,FRM_SHPG_LOC_CD,TO_SHPG_LOC_CD, SHPM_NUM, SHPM_DESC, CRTD_DTT, PLAN_ID FROM SHPM_t WHERE CRTD_DTT > (&&sysdate - 1) AND SHPM_NUM not like 'SH-%' AND CUST_CD = 'FLNA' order by CRTD_DTT desc

11

Hi @bcorrea - still giving me that same error - the invalid host/bind variable name.

12

oh im sorry, we should remove those && and not put them near sysdate…

SELECT CUST_CD,FRM_SHPG_LOC_CD,TO_SHPG_LOC_CD, SHPM_NUM, SHPM_DESC, CRTD_DTT, PLAN_ID FROM SHPM_t WHERE CRTD_DTT > (sysdate - 1) AND SHPM_NUM not like 'SH-%' AND CUST_CD = 'FLNA' order by CRTD_DTT desc

13

This worked! Thanks so much!

1 Like