Update command in sql having issue

Question

I have below details in the table

GEMS@TEST1>select BUILTIN_ARGUMENTS from FND_FORM_CUSTOM_ACTIONS WHERE (RULE_ID = 2243);

BUILTIN_ARGUMENTS
--------------------------------------------------------------------------------
='http://prod.client.com:3001/ords/f?p=1:2:::NO::P_ORDER_HEADER_ID,P_SESSION
_ID:'||${item.ORDER.HEADER_ID.VALUE}||','||${ps.db_session_id.value}

For a need ,I have to update this "prod.client.com:3001" as

"test1-scan.client.com"

When I am executing below getting error

GEMS@TEST1>update FND_FORM_CUSTOM_ACTIONS set = '='http://test1-scan.client.com/ords/f?p=1:2:::NO::P_ORDER_HEADER_ID,P_SESSION
_ID:'||${item.ORDER.HEADER_ID.VALUE}||','||${ps.db_session_id.value}' WHERE (RULE_ID = 2243);
SP2-0552: Bind variable "NO" not declared.    

GEMS@TEST1>

I know I might have to use escape character or declare the variable but not getting clue as I am not very good in coding .

You got to format the question, its hard to understand what you are trying to do. — Sai
– Sai, Commented Oct 11, 2017 at 4:04

Kaushik Nayak · Accepted Answer · 2017-10-11 04:06:52Z

1

Using REPLACE in this case is better.

UPDATE fnd_form_custom_actions
SET    builtin_arguments = REPLACE (builtin_arguments, 'prod.client.com:3001',
                                  'test1-scan.client.com')
WHERE   rule_id = 2243 ;

answered Oct 11, 2017 at 4:06

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2500742 Over a year ago

Thanks a lot , it solved the purpose . Thanks once again for prompt response.

user5683823 · Accepted Answer · 2017-10-11 04:21:29Z

0

You have obvious typos in your statement:

set = '='http://test1-scan.client.com/ords/f?p=1:2:::NO  [.......]

set what = .....?

Then: what is with the second equal sign, in single quotes? (OR... I see - did you mean single quotes within the assigned string? You must enter TWO single quotes to represent one single quote in a string!)

Then: since the second equal sign consumes the single quotes, what follows AFTER it is not quoted. So :NO is seen as a bind variable. Correct the syntax and Oracle won't ask you about any bind variables.

With that fixed, look at Kaushik's answer for a better approach altogether.

answered Oct 11, 2017 at 4:21

user5683823

Collectives™ on Stack Overflow

Update command in sql having issue

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related