Can you "SET DEFINE OFF" in a procedure?

[spook007]

I've got the following procedure:

(i_ast in number,
i_ah in number,
i_be in number,
i_ed in number,
i_gir in number,
i_gov in number,
i_hp in number,
i_news in number,
i_sci in number,
i_sbs in number,
i_fulltext in number,
i_guide in varchar2,
i_description in varchar2,
i_id in number)
AS
BEGIN
SET DEFINE OFF;
UPDATE ARTICLE SET
ARTICLE_DESCRIPTION=i_description,
ARTICLE_FULLTEXT=i_fulltext,
ARTICLE_GUIDE=i_guide,
ARTICLE_AST=i_ast,
ARTICLE_AH=i_ah,
ARTICLE_BE=i_be,
ARTICLE_ED=i_ed,
ARTICLE_GIR=i_gir,
ARTICLE_GOV=i_gov,
ARTICLE_HP=i_hp,
ARTICLE_NEW=i_news,
ARTICLE_SCI=i_sci,
ARTICLE_SBS=i_sbs
WHERE ARTICLE_ID=i_id;
SET DEFINE ON;
END;

In my i_definition variable I have '&' signs that I would like Oracle to 'overlook' when processing. Is there anyway I can do this by simply switching "SET DEFINE OFF" on my procedure. I've tried running the above procedure and I get:

Line # = 18 Column # = 7 Error Text = PL/SQL: ORA-00922: missing or invalid option
Line # = 18 Column # = 3 Error Text = PL/SQL: SQL Statement ignored
Line # = 34 Column # = 7 Error Text = PL/SQL: ORA-00922: missing or invalid option
Line # = 34 Column # = 3 Error Text = PL/SQL: SQL Statement ignored

Any suggestion?? Thanks in advance.

 

[stefanhei]

Hi.
SET DEFINE is a SQL*Plus feature, while you are using PL/SQL. You don't have to worry about the & as part of the value of your variable, it won't be interpreted as a user variable.

Stefan

 

[spook007]

Stefan;

When I run the statement with the '&' included it does not "crash", but it does not simply ignore the '&' either. Instead it replaces the '&' with the following text "OEM_sqlplus_input_finished". So if I input the following "Smith&Edwards" it would come out like "SmithOEM_sqlplus_input_finished".

How can I correct this?

 

[stefanhei]

How do call the procedure?
If you call from SQL*Plus and pass the parameter manually set define off before calling.

Stefan