Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2009
    Posts
    6

    Unanswered: update on remote database

    hello!
    i am trying to update my table on remote database (sql server) and i have error:

    UPDATE billing.pack_sub@billing SET sub_id = (select sub_id from subscriber)
    *
    ERROR at line 1:
    ORA-00904: "SUB_ID": invalid identifier

    i have this table:
    SQL> desc billing.pack_sub@billing;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    sub_id NOT NULL NUMBER(10)
    package_cost NUMBER(19,4)
    phone_cost NUMBER(19,4)
    sms_cost NUMBER(19,4)
    multi_cost NUMBER(19,4)


    is someone know what is the problem?
    ty

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    is someone know what is the problem?
    What does DESC subscriber show you?

  3. #3
    Join Date
    Dec 2009
    Posts
    6
    SQL> desc subscriber
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    SUB_ID NOT NULL NUMBER(10)
    CUST_ID NUMBER(5)
    SUB_NAME VARCHAR2(25)
    SUB_LAST_NAME VARCHAR2(25)
    SUB_EMAIL VARCHAR2(2)
    FEATURE_ID NUMBER(5)
    PACKAGE_ID NUMBER(5)
    ADRESS_ID NUMBER(5)

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Try
    Code:
    UPDATE billing.pack_sub@billing SET "sub_id" = (select sub_id from subscriber)
    Note the double quotes around the first sub_id.

    It seems that table was created using quotes, because it has lower case column names
    (My first thought was, that subscriber does not have a column called sub_id)

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    I suspect that your SQL Server is expecting case sensitive table and column names. Oracle will convert all of your column references to upper case by default. A clue to this might be the "desc billing.pack_sub@billing" which reported all of the columns in lower case. I would therefore think that the sub_id column should be used like:
    UPDATE billing.pack_sub@billing SET "sub_id" = (select sub_id from subscriber)

    The presence of the quote signs forces Oracle to maintain case sensitivity in column names. Try that out and see if it works.

  6. #6
    Join Date
    Dec 2009
    Posts
    6
    ty for replay... this reconize the sub_id but i have steel error:
    UPDATE billing.pack_sub@billing SET "sub_id" = (select sub_id from subscriber)
    *
    ERROR at line 1:
    ORA-02070: database BILLING does not support subqueries in this context

    why cant i update on remote database from local table?

  7. #7
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    try:
    Code:
    declare
       l_sub_id subscriber.sub_id%type;
    begin
       select sub_id into l_sub_id from subscriber;
       UPDATE billing.pack_sub@billing SET "sub_id" = l_sub_id;
    end;
    /

  8. #8
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    try:
    Code:
    declare
       l_sub_id subscriber.sub_id%type;
    begin
       select sub_id into l_sub_id from subscriber;
       UPDATE billing.pack_sub@billing SET "sub_id" = l_sub_id;
    end;
    /

Posting Permissions

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