problem with parameter of stored procedure....

HI,

I've this stored procedure:

CREATE OR REPLACE procedure ST_DAT (NAME_IN VARCHAR2) as


BEGIN

insert into REP_NAME
select COD_ID,DESCR_ID,AREA, NAME_IN, sysdate
from REP_TAB;

COMMIT;

END ST_DAT;
/

this procedure is called from an external program

It run correctly, but I have any problem when I tried to insert into tab REP_NAME one value of NAME_IN
with apostrophe.

for example if I write (from external program) Mary run correctly, but If I write Mary's Bar I get
this error:

ORA-01756: quoted string not properly terminated.

I tried with REPLACE:

insert into REP_NAME
select COD_ID,DESCR_ID,AREA, REPLACE(NAME_IN,'',''''), sysdate
from REP_TAB;

BUT I GET SAME ERROR:

How can I call this procedure with apostrophe?

Thanks in advance!

Use NULL in place of ''.

Tamil

"Mary's Bar" has to be "Mary''s Bar" (two single quotes) if you're going to insert it as a plain string.

The better way to do it would be using bind variables in your host program.

The procedure above wouldn't have any problem.
The problem almost certainly exists in the external routine.
If the external routine is running something like
BEGIN ST_DAT('Mary's bar'); END;
Then Oracle interprests the apostophe in the name as the end of the string, and the single quote at the end of bar as the start of another string.

As stated above, the external routine should be amended to use a bind variable. There is nothing you can do to ST_DAT to cater for this problem as the problem stops it getting that far.

ST_DAT (NAME_IN VARCHAR2)