PLSQL during update by moving from ODAC

PLSQL during update by moving from ODAC

Postby 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
hannes_a
 
Posts: 77
Joined: Sun 22 Jan 2006 20:44
Location: munic

Postby 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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby 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
hannes_a
 
Posts: 77
Joined: Sun 22 Jan 2006 20:44
Location: munic

Postby 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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby 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 ?
hannes_a
 
Posts: 77
Joined: Sun 22 Jan 2006 20:44
Location: munic

Postby 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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby 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

Postby 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

Postby 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
hannes_a
 
Posts: 77
Joined: Sun 22 Jan 2006 20:44
Location: munic


Return to dotConnect for Oracle