Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2007
    Location
    Tampa, FL
    Posts
    3

    Unanswered: Pro*C dynamic sql, ORA-22806 error on PREPARE

    In Pro*C I want to vary the name of the sequence from which nextval is taken. The following works:

    strcpy(dyn_sql, "SELECT ");
    strcat(dyn_sql, acct_seq);
    strcat(dyn_sql, ".nextval INTO :refno_03 FROM dual");
    printf ("dyn_sql = %s\n", dyn_sql);
    EXEC SQL at supdb EXECUTE IMMEDIATE :dyn_sql;

    However, since this will be done many times, I want to prepare the statement and then supply the sequence name. I tried this:

    strcpy(dyn_sql, "SELECT :seq_name");
    strcat(dyn_sql, ".nextval INTO :refno_03 FROM dual");
    printf("dyn_sql=%s\n", dyn_sql);
    EXEC SQL at supdb PREPARE S FROM :dyn_sql;
    EXEC SQL at supdb EXECUTE S USING :acct_seq;

    but got this error on the prepare:
    ORA-22806: An attempt was made to extract an attribute from an item that is neither an object nor a REF

    Please advise how I can prepare the statement.

    Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    You cannot supply object names as bind variables.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Feb 2007
    Location
    Tampa, FL
    Posts
    3
    No wonder I couldn't get it to work! Thank you for the CLEAR explanation. People gave me all kinds of long-winded speeches related to my issue but until now no person or manual said what you did, which is all I needed to know!

Posting Permissions

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