6 Replies Latest reply: Dec 11, 2012 5:48 AM by 961624 RSS

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

    25033
      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
          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
            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
              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
                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
                  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
                    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