This discussion is archived
6 Replies Latest reply: Dec 11, 2012 3:48 AM by 961624 RSS

ODP.NET gives ORA-22806 on update with fully defined names

25033 Newbie
Currently Being Moderated
Just thought I'd post my experience since I found nothing on this issue when I had the problem. I figured it out, so hopefully it will help someone in the future.

I was updating some records using array binding. For example:

UPDATE MySchema.MyTable SET MySchema.MyTable.MyField = :varValue
WHERE MySchema.MyTable.MyIDField = :varIDValue;

and I was getting

ORA-22806: not an object or REF
Array Bind Error: ORA-22806: not an object or REF

After a few painful hours, I finally figured out that it did not like the fully defined naming. When I changed my query to:

UPDATE MyTable SET MyField = :varValue WHERE MyIDField = :varIDValue;

It worked perfectly. Also note, that INSERT statements seem to work fine with fully defined column and table names - I only had a problem with update statements.

This was tested and confirmed in Oracle 9i and 10g R1 and R2 database instances.
  • 1. Re: ODP.NET gives ORA-22806 on update with fully defined names
    24208 Oracle ACE
    Currently Being Moderated
    Why would it work? It is invalid syntax, so it is not surprising you received errors.

    The syntax for the update statement can be found here:

    http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#i2112182

    - Mark
  • 2. Re: ODP.NET gives ORA-22806 on update with fully defined names
    gdarling - oracle Expert
    Currently Being Moderated
    Hi,

    I might be blind here (and thats quite possibly the case :)) but I dont see what's wrong with the syntax.
    What version of ODP are you using? I tested the following (with 10.2.0.2 odp beta, 10.2.0.2 client and database) and it worked fine for me.

    Cheers,
    Greg


    using System;
    using System.Data;
    using Oracle.DataAccess.Client;

    namespace ODPSample
    {
    class ArrayBind
    {
    static void Main(string[] args)
    {
    string connectStr = "User Id=scott;Password=tiger;Data Source=orcl";
    string strsql = "update scott.emp set scott.emp.job = :1 where scott.emp.empno=:2";

    int[] myArrayofNums = new int[2] { 7900,7934 };
    String[] myArrayofV2s = { "coder", "mgr" };

    OracleConnection con = new OracleConnection(connectStr);
    OracleCommand cmd = new OracleCommand(strsql, con);
    cmd.CommandType = CommandType.Text;
    cmd.ArrayBindCount = 2;

    OracleParameter v2param = new OracleParameter("param1", OracleDbType.Varchar2);
    v2param.Direction = ParameterDirection.Input;
    v2param.Value = myArrayofV2s;
    cmd.Parameters.Add(v2param);

    OracleParameter numParam = new OracleParameter("param2", OracleDbType.Int32);
    numParam.Direction = ParameterDirection.Input;
    numParam.Value = myArrayofNums;
    cmd.Parameters.Add(numParam);

    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
    cmd.Dispose();
    con.Dispose();
    }
    }
    }
  • 3. Re: ODP.NET gives ORA-22806 on update with fully defined names
    24208 Oracle ACE
    Currently Being Moderated
    Weird. On my 10.2.0.2.0 system:
    SQL> update scott.emp set scott.emp.ename = 'scott' where scott.emp.empno = 7788;
    update scott.emp set scott.emp.ename = 'scott' where scott.emp.empno = 7788
                                                         *
    ERROR at line 1:
    ORA-00904: "SCOTT"."EMP"."EMPNO": invalid identifier
    All the same, the "schema.table.column" syntax is not documented as I read it; but, that aside, I wonder why I am seeing different behavior.

    - Mark
  • 4. Re: ODP.NET gives ORA-22806 on update with fully defined names
    gdarling - oracle Expert
    Currently Being Moderated
    Hi Mark,

    Wierd indeed.. just for a sanity check, on my 10.2.0.2 system:

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
    With the Partitioning, OLAP and Data Mining options

    SQL> update scott.emp set scott.emp.ename = 'scott' where scott.emp.empno = 7788;

    1 row updated.


    scratches head

    some days you're the bug, some days you're the windshield :)
    Greg
  • 5. Re: ODP.NET gives ORA-22806 on update with fully defined names
    24208 Oracle ACE
    Currently Being Moderated
    Greg,

    Hmm... I can't reproduce on my system at home (the other was at work)... But I still can't think of a good reason one would want to fully qualify a column in an update statement.

    In any case, would it be possible for you to send me an email (my address is in my profile). I have a question for you that is not really related to the forum stuff (it is related to a user group thing).

    Thanks,

    Mark
  • 6. Warning: Procedure created with compilation errors.
    961624 Newbie
    Currently Being Moderated
    create or replace procedure load_prod_dim
    is

    cursor c2 is select p.productid,p.productname,p.unitprice,c.categoryname,s.companyname from products p

    join categories c on p.categoryid = c.categoryid

    join suppliers s on p.supplierid = s.supplierid;

    type c2_prod is table of c2%rowtype index by pls_integer;

    type typ_prod_dim is table of product_dim%rowtype index by pls_integer;

    p_dim typ_prod_dim;

    p_dim_up typ_prod_dim;

    c1_var     typ_prod_dim;

    k number := 1;

    m number := 1;

    flag number := 1;

    y number := 1;

    j number := 1;

    v_sdate date := to_date('1-Jan-1995','DD-MON-YYYY');

    v_edate date := to_date('31-Dec-2050','DD-MON-YYYY');

    begin

         select * bulk collect into p_dim from

                             (select * from product_dim order by eff_start_dt desc);

         if p_dim.count = 0 then

         dbms_output.put_line('Insert all rows in the table...');

         for i in c2

         loop

                   select Product_Seq.nextval into c1_var(k).seq_id from dual;

                   c1_var(k).product_id := i.productid;

                   c1_var(k).product_name := i.productname;

                   c1_var(k).list_price := i.unitprice;

                   c1_var(k).category := i.categoryname;

                   c1_var(k).supplier_name := i.companyname;

                   c1_var(k).eff_start_dt := v_sdate;

                   c1_var(k).eff_end_dt := v_edate;

                   k := k + 1;

         end loop;

         forall i in c1_var.first..c1_var.last insert into product_dim values c1_var(i);

    else

    dbms_output.put_line('Inside the else part..');

         for i in c2

         loop

              for j in p_dim.first..p_dim.last

              loop

              if p_dim(j).product_id = i.productid

                        and p_dim(j).product_name = i.productname

                        and p_dim(j).list_price = i.unitprice

                        and p_dim(j).category = i.categoryname

                        and p_dim(j).supplier_name = i.companyname

              then

                   flag := 1;

                   exit;

              else

                   if p_dim(j).product_id = i.productid then

                        p_dim_up(y).eff_end_dt := sysdate - 1;

                        p_dim_up(y).seq_id := p_dim(j).seq_id;

                        flag := flag + 1;

                        y := y + 1;

                        exit;

                   end if;

              end if;

              end loop;



              if flag != 1 then



              select Product_Seq.nextval into c1_var(m).seq_id from dual;

                   --c1_var(m).seq_id := Product_Seq.nextval;

                   c1_var(m).product_id := i.productid;

                   c1_var(m).product_name := i.productname;

                   c1_var(m).list_price := i.unitprice;

                   c1_var(m).category := i.categoryname;

                   c1_var(m).supplier_name := i.companyname;

                   c1_var(m).eff_start_dt := sysdate;

                   c1_var(m).eff_end_dt := v_edate;

                   m := m + 1;

              end if;



         end loop;


         forall i in p_dim_up.first..p_dim_up.last
              update product_dim set eff_end_dt = p_dim_up(i).eff_end_dt where seq_id = p_dim_up(i).seq_id;



         forall i in c1_var.first..c1_var.last insert into product_dim values c1_var(i);

    --     forall i in p_dim_up.first..p_dim_up.last update product_dim set eff_end_dt = p_dim_up(i).eff_end_dt where seq_id = p_dim_up(i).seq_id;

    end if;
    end;
    /
    show err

    Error in procedure are:

    @load_prod_dim.sql

    Warning: Procedure created with compilation errors.

    Errors for PROCEDURE LOAD_PROD_DIM:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    98/3     PL/SQL: SQL Statement ignored
    98/39     PL/SQL: ORA-22806: not an object or REF
    98/39     PLS-00382: expression is of wrong type
    98/39     PLS-00436: implementation restriction: cannot reference fields of
         BULK In-BIND table of records

    98/77     PLS-00382: expression is of wrong type
    98/77     PLS-00436: implementation restriction: cannot reference fields of
         BULK In-BIND table of records

    Edited by: 958621 on Dec 11, 2012 3:31 AM

    Edited by: 958621 on Dec 11, 2012 3:48 AM