Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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; 
share|improve this question
    
And the error is? –  a_horse_with_no_name Apr 23 '13 at 12:30
    
Please provide the tables being worked on –  druzin Apr 23 '13 at 12:36
    
ERROR at line 41: PLS-00103: Encountered the symbol "END" 2. mnav number; 3. mpno varchar2(100); 4. mtransactioncharge number; 5. mservicecharge number; 6. maccno number; –  HImadri Apr 23 '13 at 12:59
    
1)CREATE TABLE "SAVINGACCPERM" ( "ACCNO" NUMBER, "PASSWORD" VARCHAR2(4000), "APPNO" NUMBER, "BALANCE" NUMBER(38,0) NOT NULL ENABLE, CONSTRAINT "SAVINGACCPERM_PK" PRIMARY KEY ("ACCNO") ENABLE ) –  HImadri Apr 23 '13 at 13:00
1  
Please edit your question. Posting unreadable code in the comments makes it even worse. –  a_horse_with_no_name Apr 23 '13 at 13:41
show 3 more comments

closed as too localized by a_horse_with_no_name, Mat, Jon Seigel, dezso, StanleyJohns Apr 23 '13 at 13:52

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

up vote 0 down vote accepted

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));
share|improve this answer
    
Thank u so much.. –  HImadri Apr 23 '13 at 14:24
add comment

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