-2
create or replace procedure buyunits is

mnav number;
mpno varchar2(100);
mtransactioncharge number;
mservicecharge number;
maccno number;
cursor ecursor is
select accno from sipholder where EXTRACT(day from 

TO_DATE(ECS,'DD-MON-RR'))=5 order by ECS;




begin

OPEN ecursor;

LOOP
    FETCH ecursor into maccno;
    EXIT WHEN ecursor%NOTFOUND;
    select portfoliono into mpno from sipholder where   

accno=rec.accno;

    select nav into mnav from sip_price where   

portfoliono=mpno;

    select servicecharge into mservicecharge from   

sipparam;

    select transactioncharge into mtransactioncharge    

from sipparam;

    update savingaccperm set balance=balance-   

balance-permonthinvestment;

    mtotalamountleft=permonthinvestment-    

(permonthinvestment*mtransactioncharge)/100 -   

(permonthinvestment*mservicecharge)/100

    update sipholder set    

totalunitsbought=totalunitsbought   

+(mtotalamountleft)/mnav;
end loop;

close ecursor;

end;

ERROR at line 41: PLS-00103: Encountered the symbol "END"

  1. mnav number;
  2. mpno varchar2(100);
  3. mtransactioncharge number;
  4. mservicecharge number;
  5. maccno number;

CREATE TABLE  "SAVINGACCPERM" 
   (    "ACCNO" NUMBER, 
    "PASSWORD" VARCHAR2(4000), 
    "APPNO" NUMBER, 
    "BALANCE" NUMBER(38,0) NOT NULL ENABLE, 
     CONSTRAINT "SAVINGACCPERM_PK" PRIMARY KEY ("ACCNO") ENABLE
   )

CREATE TABLE  "SIPHOLDER" 
   (    "ACCNO" NUMBER, 
    "PORTFOLIONO" NUMBER, 
    "PORTFOLIONAME" VARCHAR2(4000), 
    "NAV" NUMBER, 
    "PERMONTHINVESTMENT" NUMBER, 
    "TOTALUNITSBOUGHT" NUMBER, 
    "STARTDATE" DATE, 
    "SELLDATE" DATE, 
    "FINALAMOUNT" NUMBER, 
    "RETURN" NUMBER, 
    "ECS" DATE, 
     CONSTRAINT "SIPHOLDER_CON" FOREIGN KEY ("ACCNO")
      REFERENCES  "SAVINGACCPERM" ("ACCNO") ENABLE
   )

CREATE TABLE  "SIPPARAM" 
   (    "EXITLOAD1" NUMBER NOT NULL ENABLE, 
    "SERVICETAX" NUMBER(3,0), 
    "TRANSACTIONTAX" NUMBER(3,0), 
    "EXITLOAD2" NUMBER(3,0), 
    "CAPITALGAIN" NUMBER(3,0)
   )

CREATE TABLE  "SIP_PRICE" 
   (    "PORTFOLIONO" NUMBER, 
    "PORTFOLIONAME" VARCHAR2(4000), 
    "DATEOF" DATE, 
    "NAV" NUMBER, 
     CONSTRAINT "SIP_PRICE_PK" PRIMARY KEY ("PORTFOLIONO", "DATEOF") ENABLE
   )
0

1 Answer 1

1

This will work, but you have all sorts of problems in your db application. Mainly it seems to me that you haven't structured the tables in a good manner, nor have you normalized the design. You're also quoting column names incorrectly, improperly using selects into variables, etc. If you can, please ask a senior DBA to review your code and db design.

create or replace procedure buyunits is
    mnav number; 
    mpno varchar2(100); 
    mtransactiontax number; 
    mservicetax number; 
    maccno number; 
    mtotalamountleft number;
    mpermonthinvestment number;

    cursor ecursor 
    is select accno 
    from sipholder 
    where EXTRACT(day from TO_DATE(ECS,'DD-MON-RR'))=5;

begin

    FOR e IN ecursor LOOP
    select portfoliono, permonthinvestment
    into mpno, mpermonthinvestment
    from sipholder 
    where accno= e.accno;

    select nav 
    into mnav 
    from sip_price 
    where portfoliono = mpno;

    select servicetax, transactiontax
    into mservicetax, mtransactiontax
    from  sipparam
    where rownum = 1; -- You were select all rows, would throw an error insert into a variable

    update savingaccperm 
    set balance = balance-balance-mpermonthinvestment
    where accno = e.accno; -- You were trying update all rows in this table, fixed that

    mtotalamountleft := (mpermonthinvestment - (mpermonthinvestment*mtransactiontax)/100 - (mpermonthinvestment*mservicetax)/100);

    update sipholder 
    set totalunitsbought= totalunitsbought +(mtotalamountleft)/mnav
    where accno= e.accno; 

    end loop;

end;

This is according to the following table structure (which you posted in the comments):

CREATE TABLE savingaccperm
    (accno                          NUMBER ,
    password                       VARCHAR2(4000),
    appno                          NUMBER,
    balance                        NUMBER(38,0) NOT NULL,
    CONSTRAINT savingaccperm_pk PRIMARY KEY (accno)
    );

CREATE TABLE sip_price
    (portfoliono                    NUMBER ,
    portfolioname                  VARCHAR2(4000),
    dateof                         DATE ,
    nav                            NUMBER,
    CONSTRAINT sip_price_pk PRIMARY KEY (portfoliono, dateof)
    );

CREATE TABLE sipholder
    (accno                          NUMBER,
    portfoliono                    NUMBER,
    portfolioname                  VARCHAR2(4000),
    nav                            NUMBER,
    permonthinvestment             NUMBER,
    totalunitsbought               NUMBER,
    startdate                      DATE,
    selldate                       DATE,
    finalamount                    NUMBER,
    return                         NUMBER,
    ecs                            DATE,
    CONSTRAINT sipholder_con FOREIGN KEY (accno) REFERENCES savingaccperm (accno)
    );

CREATE TABLE sipparam
    (exitload1                      NUMBER NOT NULL,
    servicetax                     NUMBER(3,0),
    transactiontax                 NUMBER(3,0),
    exitload2                      NUMBER(3,0),
    capitalgain                    NUMBER(3,0));
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.