PLSQL during update by moving from ODAC

hannes_a · 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.

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 · 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 · 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 · 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 · 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 · 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 · Post by **hannes_a** » Fri 08 Jan 2010 12:47

The problem still exists,I sent you a test app

hannes_a · Post by **hannes_a** » Tue 19 Jan 2010 20:14

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

hannes_a · 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