Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2009
    Location
    Prattville, AL
    Posts
    5

    Question Unanswered: Strange ORA-01747 error

    SET RECEP_PERSONNEL_SUPP = (SELECT RECEP_PERSONNEL_SUPP FROM base.airfield_site_text where site_id=2000670),
    *

    ERROR at line 4:
    ORA-01747: invalid user.table.column, table.column, or column specification

    The table names and column names are correct, The field in question is a CLOB. Is that causing the problem?

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

    Talking Tsql?

    Oracle does not understand TSQL.

    Or is this part of an UPDATE statement?

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

  3. #3
    Join Date
    Jan 2009
    Location
    Prattville, AL
    Posts
    5
    What is TSQL and where did you see that in my post?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by PatsRick
    SET RECEP_PERSONNEL_SUPP = (SELECT RECEP_PERSONNEL_SUPP FROM base.airfield_site_text where site_id=2000670),
    *

    ERROR at line 4:
    ORA-01747: invalid user.table.column, table.column, or column specification
    The error message says "Line 4" but there is only one line in your posting.
    So you are not showing us the full SQL.
    How should we able to find the problem?

    The single line you posted, looks very much like the SQL dialect used by Microsoft SQL Server (TSQL), that's why LKBrwn_DBA mentioned it

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

    Talking Incomplete

    Because you are posting an incomplete statement, it looks like T-SQL (WinDoze SQL c.r.a.p.)

    It may be due to RECEP_PERSONNEL_SUPP if it is a CLOB, you may need to review the fine Oracle® Database Application Developer's Guide on Large Objects.

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

  6. #6
    Join Date
    Jan 2009
    Location
    Prattville, AL
    Posts
    5
    Here is the complete code:
    SET LONG 65000
    UPDATE base.airfield_site_text
    SET RECEP_BASE_CONOPS = (SELECT RECEP_BASE_CONOPS FROM base.airfield_site_text where site_id=2000670),
    SET RECEP_INDIV_PROCESS = (SELECT RECEP_INDIV_PROCESS FROM base.airfield_site_text where site_id=2000670),
    SET RECEP_PERSONNEL_SUPP = (SELECT RECEP_PERSONNEL_SUPP FROM base.airfield_site_text where site_id=2000670),
    SET RECEP_FINANCE_SUPP = (SELECT RECEP_FINANCE_SUPP FROM base.airfield_site_text where site_id=2000670),
    SET RECEP_CHAPLAIN_SUPP = (SELECT RECEP_CHAPLAIN_SUPP FROM base.airfield_site_text where site_id=2000670),
    SET RECEP_LEGAL_SUPP = (SELECT RECEP_LEGAL_SUPP FROM base.airfield_site_text where site_id=2000670),
    SET RECEP_MEDICAL_SUPP = (SELECT RECEP_MEDICAL_SUPP FROM base.airfield_site_text where site_id=2000670),
    SET RECEP_FOOD_SVC_SUPP = (SELECT RECEP_FOOD_SVC_SUPP FROM base.airfield_site_text where site_id=2000670)
    WHERE site_id=2000654;

  7. #7
    Join Date
    Jan 2009
    Location
    Prattville, AL
    Posts
    5
    Here is what it should have been:
    SET LONG 65000
    UPDATE base.airfield_site_text
    SET RECEP_BASE_CONOPS = (SELECT RECEP_BASE_CONOPS FROM base.airfield_site_text where site_id=2000670)
    , RECEP_INDIV_PROCESS = (SELECT RECEP_INDIV_PROCESS FROM base.airfield_site_text where site_id=2000670)
    , RECEP_PERSONNEL_SUPP = (SELECT RECEP_PERSONNEL_SUPP FROM base.airfield_site_text where site_id=2000670)
    , RECEP_FINANCE_SUPP = (SELECT RECEP_FINANCE_SUPP FROM base.airfield_site_text where site_id=2000670)
    , RECEP_CHAPLAIN_SUPP = (SELECT RECEP_CHAPLAIN_SUPP FROM base.airfield_site_text where site_id=2000670)
    , RECEP_LEGAL_SUPP = (SELECT RECEP_LEGAL_SUPP FROM base.airfield_site_text where site_id=2000670)
    , RECEP_MEDICAL_SUPP = (SELECT RECEP_MEDICAL_SUPP FROM base.airfield_site_text where site_id=2000670)
    , RECEP_FOOD_SVC_SUPP = (SELECT RECEP_FOOD_SVC_SUPP FROM base.airfield_site_text where site_id=2000670)
    WHERE site_id=2000654;

    The problem was the repeated "SET", not the CLOB.

    Thanks for your comments.
    Last edited by PatsRick; 01-14-09 at 16:34.

  8. #8
    Join Date
    Jan 2009
    Location
    Prattville, AL
    Posts
    5
    In my original code, the lines between "SET" and "WHERE" are indented. They were indented when I pasted them into the Messge block. They are not indented in the final message. How do I fix that?

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by PatsRick
    The problem was the repeated "SET", not the CLOB.
    See how posting the full SQL helps

    Btw: you can write that statement a lot simpler and avoid the multiple nested selects:
    Code:
    UPDATE base.airfield_site_text
     SET ( RECEP_BASE_CONOPS, 
           RECEP_INDIV_PROCESS, 
           RECEP_PERSONNEL_SUPP, 
           RECEP_FINANCE_SUPP, 
           RECEP_CHAPLAIN_SUPP,
           RECEP_LEGAL_SUPP,
           RECEP_MEDICAL_SUPP,
           RECEP_FOOD_SVC_SUPP
          )  
          =  
          ( SELECT RECEP_BASE_CONOPS, 
                RECEP_INDIV_PROCESS, 
                RECEP_PERSONNEL_SUPP, 
                RECEP_FINANCE_SUPP, 
                RECEP_CHAPLAIN_SUPP,
                RECEP_LEGAL_SUPP,
                RECEP_MEDICAL_SUPP,
                RECEP_FOOD_SVC_SUPP
            WHERE site_id=2000670
          )
    WHERE site_id = 2000654;
    This might have some types though...

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by PatsRick
    In my original code, the lines between "SET" and "WHERE" are indented. They were indented when I pasted them into the Messge block. They are not indented in the final message. How do I fix that?
    By using [code] tags.

    If you don't know how to do that, next time don't post a Quick Reply but Go Advanced. In there you'll see a toolbar; hash (#) sign stands for the [code] tags. Test it!

Posting Permissions

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