PLSQL during update by moving from ODAC

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
hannes_a
Posts: 77
Joined: Sun 22 Jan 2006 20:44
Location: munic

PLSQL during update by moving from ODAC

Post by hannes_a » Sat 07 Nov 2009 17:12

I move from ODAC to dotConnect ASP.NET and during update with PL/SQL of a query I get an error
ORA-01036 illegal variable name/number
with ODAC it works properly.

Code: Select all

declare 
  compcount integer;
begin
  select count(*) into compcount from competitor where name = :NAME;

  if (compcount > 0) then
    select id into compcount from competitor where name = :NAME;
  else
    INSERT INTO competitor
      (ID, NAME) 
    VALUES
      (SEQ_COMPETITOR.NEXTVAL, :NAME);
    select SEQ_COMPETITOR.CURRVAL into compcount from dual;  
  end if;

  insert into compet 
   (quo_id, competitor_id, offerprice)
  values
   (:Original_quo_id, compcount, :offerprice);
end;
what is wrong there ?

I sent you an example

Shalex
Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 10 Nov 2009 14:59

The OracleCommand.CommandText property in dotConnect for Oracle can contain only one SQL statement. We recommend you to create stored procedure at the database side and call it via our components.

hannes_a
Posts: 77
Joined: Sun 22 Jan 2006 20:44
Location: munic

Post by hannes_a » Tue 10 Nov 2009 19:22

thank you for the respose

do you have a plan to implement the feature of update PL/SQL block during insert/update/delete ? otherwise I've to make a lot of storedproc

Shalex
Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 11 Nov 2009 17:06

It should work for PL/SQL blocks, because several statements inside one PL/SQL block are considered as one command. Probably, you didn't create one of the used (NAME, Original_quo_id or offerprice) parameters. There is a known bug with the Direct mode when the parameter (NAME) is used several times.

hannes_a
Posts: 77
Joined: Sun 22 Jan 2006 20:44
Location: munic

Post by hannes_a » Thu 12 Nov 2009 20:50

yes, I use direct mode, and I simplified it, as you recommended, but without success

Code: Select all

declare 
  compcount integer;
  nam varchar2(35);
begin
  nam := :name;
  select count(*) into compcount from competitor where name = nam;
  if (compcount > 0) then
    select id into compcount from competitor where name = nam;
  else
    INSERT INTO competitor
      (ID, NAME) 
    VALUES
      (SEQ_COMPETITOR.NEXTVAL, nam);
    select SEQ_COMPETITOR.CURRVAL into compcount from dual;  
  end if;
  --insert into compet 
  -- (quo_id, competitor_id, offerprice)
  --values
  -- (:Original_quo_id, compcount, :offerprice);
end;
when do you solve the known issue in direct mode ?

Shalex
Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 16 Nov 2009 13:48

Could you please try the latest 5.35.52 version of dotConnect for Oracle? If the problem persists with the latest version, please send us a small test project with the DDL/DML script of your database objects you work with using the above CommandText. We will try to reproduce the problem.

hannes_a
Posts: 77
Joined: Sun 22 Jan 2006 20:44
Location: munic

Post by hannes_a » Fri 08 Jan 2010 12:47

The problem still exists,I sent you a test app

hannes_a
Posts: 77
Joined: Sun 22 Jan 2006 20:44
Location: munic

Post by hannes_a » Tue 19 Jan 2010 20:14

with 5.35.79 it's not solved, are there any news ?

hannes_a
Posts: 77
Joined: Sun 22 Jan 2006 20:44
Location: munic

Post by hannes_a » Sun 24 Jan 2010 20:52

maybe someone runs in the same issue: this helps http://www.devart.com/forums/viewtopic.php?p=52832

Post Reply