problem with parameter of stored procedure....
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: problem with parameter of stored procedure....

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    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!

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Use NULL in place of ''.

    Tamil

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    "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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Feb 2005
    Posts
    158
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width