Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009

    Unanswered: Using dynamic sql to alter a table -- "missing keyword"

    Hello all...

    I am trying to issue the following statement (which works in sqlplus) in pl/sql. "table_object" is an oracle external table which already exists and is being successfully accessed from inside pl/sql. The problem is the physical file to which the table object is attached changes daily and I want to be able to pass the new name into pl/sql and execute the alter table statement inside pl/sql based on that parameter... Passing IN the parameter works fine. I've used dbms_output.put_line to validate that the file name is coming in correctly...

    In sqlplus the statement is:

    alter table table_object location ('new_name_for_file');

    This works fine...

    In pl/sql the file name comes in as v_new_source...

    v_string := 'alter table table_object location ('||v_new_source||')';
    execute immediate v_string;

    No matter how I try to change v_string I get a "missing keyword" error (ora-00905). I've tried all kinds of things to either embed a literal single quote around the v_new_source, or to use a bind variable like:

    v_string := 'alter table table_object location(:1)';
    execute immediate v_string using v_new_source;

    and more and more... I've been batting my head against this for a day now and it is very frustrating... Is there anyone who might know where my syntax is going wrong? I've even tried things like:

    v_string := 'alter table table_object location ('||'''||v_new_source||'''||')'; to embed a literal quote inside the parentheses, but nothing seems to work...

    Any suggestions?
    thanks much
    matthew rapaport (

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    The secret to debugging EXECUTE IMMEDIATE print/display v_string
    as in the following:

    v_string := 'alter table table_object location ('||v_new_source||')';
    --execute immediate v_string;

    Now CUT the results & PASTE into sqlplus to see what works & what does not

    it appears you have a problem with the quote marks.
    CHR(39) is the same as a single quote mark
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2009
    Sheesh! How many years have I done this? But it's been a while and I completely forgot about chr().

    Anyway, that worked fine, thank you... The syntax was:

    v_statement := 'alter table object_name location('||chr(39)||v_file_name||chr(39)||')';
    execute immediate v_statement;

    Worked like a charm! Thank you!!!

    matthew rapaport

  4. #4
    Join Date
    Jun 2004
    Liverpool, NY USA
    or what would be less typing

    v_string := 'alter table table_object location ('''||v_new_source||''')';

    two single quotes within a string is treated as an embedded quote.
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Aug 2009
    Hi... Thanks, but I tried that approach and it didn't seem to work... Don't mind typing chr(39) a couple of times :-)

Posting Permissions

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