Cant add two number in stored procedure. Did I miss something?

84 views Asked by At

Im working on a stored procedure in SQL Developer from Oracle. The stored procedure compiles, both for loops work as exepted. My Problem: If I read out for example a single variable like pos."AssetValueCHF" it makes sense and gives me the right value. But if I want to add them to a total value its NULL.

Here is the stored procedure:

   create or replace 
PROCEDURE AVGCLIENTRISKPORFOLIOS (
  customer IN NUMBER,
  avgRisk OUT NUMBER
)AS 
  totalAsset  NUMBER ;
  riskRankXAsset NUMBER ;

BEGIN

      DBMS_OUTPUT.PUT_LINE('Starting...');

  FOR portf IN (SELECT "PortfolioId" FROM "Portfolios" WHERE "CustomerId"=customer) LOOP
    DBMS_OUTPUT.PUT_LINE('Portfolio gefunden');
    FOR pos IN (SELECT "AssetValueCHF","RiskRank" FROM "Positions" WHERE "Portfolio_PortfolioId"=portf."PortfolioId") LOOP

      /*these two lines here are the problem*/
      totalAsset:=totalAsset+pos."AssetValueCHF"; 
      riskRankXAsset:=riskRankXAsset + pos."AssetValueCHF" *pos."RiskRank" ; 

      DBMS_OUTPUT.PUT_LINE('x');   
      DBMS_OUTPUT.PUT_LINE(totalAsset);     

    END LOOP;
  END LOOP;

  avgRisk:=riskRankXAsset/totalAsset;

END AVGCLIENTRISKPORFOLIOS;

Output here is: Starting... Portfolio gefunden x x x x x x x x x x Prozess beendet.

Can you help me please?

Im using Oracle 12.1 and Oracle Developer 3.2.20.10

1

There are 1 answers

0
DonQQ On

Ok, omg...

I worked too long ... it works now:

 totalAsset  NUMBER :=0;
 riskRankXAsset NUMBER :=0;

Thats was my mistake.