Re: Problem with a PL/SQL script. FOR....LOOP
Date: Wed, 19 Apr 2000 14:13:52 GMT
Message-ID: <8dkeug$i9e$1_at_nnrp1.deja.com>
Henrik,
I think you need to give a bit more information. In the code fragment, you try and multiply ChildTypeNr... so it is declared in the table as a number? That implies to me that ParentTypeNr is a number too? So the problem you are having is that you are trying to assign the character string 'F...' to a numeric type variable?
The following code works fine for me. Notice how I declared CompNr as the same type as the field you are comparing it to in the cursor:
SET SERVER OUTPUT ON -- (type at SQL*Plus prompt)
DECLARE
CURSOR SparePart_Cur(Curvar CHAR) IS
SELECT ChildTypeNr, Quantity
FROM SparePartComb
WHERE ParentTypeNr = Curvar;
CompNr SparePartComb.ParentTypeNr%TYPE;
MachinePrice Number := 0;
BEGIN
CompNr := 2;
FOR Spare_rec IN SparePart_Cur(CompNr) LOOP
MachinePrice := (Spare_rec.ChildTypeNr * Spare_rec.Quantity);
END LOOP;
DBMS_OUTPUT.put_line( to_char(MachinePrice) );
END;
/
Make sure ALL variables are EXACTLY the same type as whetever you are assigning them to, or comparing them with, and DON'T mix types!
HTH
Mick
In article <8dk5dc$98k$1_at_news.inet.tele.dk>,
"Henrik Meldgaard Frisk" <hfr_at_bang-olufsen.dk> wrote:
>
>
> Hi all.
>
> I have a problem that is driving me crazy. The problem is in the
following
> PL/SQL script, and more exact in the linies were I have put the *
marks.
>
> OK. The problem is, that I want to transfer the value of the variabel
> 'CompNr' to the CURSOR through the FOR LOOP. But somehow that is just
not
> possible.
>
> The value of 'CompNr' is not vissible from the FOR LOOP.
> If I type a number instead of 'CompNr' in the FOR LOOP there is no
problem
> handle it, but as soon as I want to use the variable in the FOR LOOP
the
> problem beginnings. How can I access the value of CompNr from the FOR
LOOP
> and send it to the CURSOR.
>
> I really hope someone can help me.
> If someone prefer to mail then do it to this address:
> henrikfrisk_at_hotmail.com
>
> /*********************************************************************
> *************/
>
> CREATE OR REPLACE FUNCTION SQLTEST(FaaNr IN CHAR) RETURN NUMBER IS
> MachinePrice NUMBER(7,2);
>
> CompNr CHAR(25);
>
> CURSOR SparePart_Cur(Curvar CHAR) IS
> SELECT ChildTypeNr, Quantity
> FROM SparePartComb
> WHERE ParentTypeNr = Curvar;
>
> BEGIN
> MachinePrice := 0;
> * CompNr := 'F5920003';
>
> * FOR Spare_rec IN SparePart_Cur(CompNr) LOOP
> MachinePrice := (Spare_rec.ChildTypeNr *
Spare_rec.Quantity);
> END LOOP;
>
> RETURN MachinePrice;
>
> END SQLTEST;
>
> /
> SHOW ERRORS;
>
> /*********************************************************************
> ****************/
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Wed Apr 19 2000 - 16:13:52 CEST