Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    833

    Unanswered: cursor is having no rows but should show at least 2

    hi,

    habe build a package and a function to concat rows to a single column.

    at least, if I run the select statement nativly I retrieve zwo rows
    but calling it within a select statement
    select denorm_pkg.GetNetz (id_plan, id_knoten) netzd from knoten where id_plan= 0 and id_knoten = 122
    the column netzd is showing no values

    does anybody have a hint what might cause to problem

    (select conut(*) from knoten k , netzwerkdienst n , knoten_netzwerkdienste kn where k.id_knoten = kn.id_knoten and
    n.id_netzwerkdienst = kn.id_netzwerkdienst
    and k.id_plan = kn.ip_plan is showing 2 rows)

    table netzwerkdienst (id_netzwerkdienst number pk, name varchar(32))
    intersection table knoten_netzwerkdienst (id_plan, id_knoten, id_netzwerkdienst) all number
    table knoten (id_plan number , id_knoten number, name varchar(32) ...)

    thanks



    create or replace PACKAGE denorm_PKG IS

    /*
    Denormatlisierung der Knoten fuer itmasterplan

    */
    FUNCTION GetNetz (ix_knoten IN knoten.id_knoten%TYPE,
    ix_plan IN knoten.id_plan%TYPE) RETURN Varchar2 ;

    END denorm_PKG;
    /


    create or replace PACKAGE BODY DENORM_PKG AS

    /*

    */
    FUNCTION GetNetz (ix_knoten IN knoten.id_knoten%TYPE,
    ix_plan IN knoten.id_plan%TYPE) RETURN VARCHAR2

    IS

    -- Cursor für die Datenbankabfrage
    CURSOR netzcrs (ix_knoten knoten.id_knoten%TYPE,
    ix_plan knoten.id_plan%TYPE) IS
    select n.netzwerkdienst
    from netzwerkdienst n, knoten_netzwerkdienste kn

    where n.id_netzwerkdienst = kn.id_netzwerkdienst

    and kn.id_plan = ix_plan
    and kn.id_knoten = ix_knoten;



    tmpVar varchar2(255);

    netz_rec netzcrs%ROWTYPE;
    BEGIN
    tmpVar := NULL;



    IF NOT netzcrs%ISOPEN
    THEN
    OPEN netzcrs( ix_knoten, ix_plan);
    END IF;

    FETCH netzcrs INTO netz_rec;
    -- Schleife über alle Resultdatensätze, konkateniert alle
    netzwerkdienste
    WHILE (netzcrs%FOUND)
    LOOP
    IF tmpVar IS NULL
    THEN
    tmpVar := netz_rec.netzwerkdienst;
    ELSE
    tmpVar := tmpVar ||','|| netz_rec.netzwerkdienst;

    END IF;
    FETCH netzcrs INTO netz_rec;
    END LOOP;

    CLOSE netzcrs;

    RETURN tmpVar;
    END GetNetz;

    END DENORM_PKG;
    /

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    sorry folks for the disruption

    have got it my fault were the direction of the parameters

    first id_knoten next id_plan instead if id_plan, id_knoten

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: cursor is having no rows but should show at least 2

    I can't see what your problem is. What do you get for:

    select n.netzwerkdienst
    from netzwerkdienst n, knoten_netzwerkdienste kn
    where n.id_netzwerkdienst = kn.id_netzwerkdienst
    and kn.id_plan = 0
    and kn.id_knoten = 122;

    ?

    While looking at it I simplified your function code to:

    Code:
      FUNCTION GetNetz (ix_knoten IN knoten.id_knoten%TYPE, 
      ix_plan IN knoten.id_plan%TYPE) RETURN VARCHAR2 
    
      IS 
    
        -- Cursor für die Datenbankabfrage 
        CURSOR netzcrs (ix_knoten knoten.id_knoten%TYPE, 
          ix_plan knoten.id_plan%TYPE) IS 
          select n.netzwerkdienst 
          from netzwerkdienst n, knoten_netzwerkdienste kn 
          where n.id_netzwerkdienst = kn.id_netzwerkdienst 
          and kn.id_plan = ix_plan 
          and kn.id_knoten = ix_knoten; 
    
        tmpVar varchar2(255); 
    
      BEGIN 
    
        -- Schleife über alle Resultdatensätze, konkateniert alle netzwerkdienste 
        FOR netz_rec IN netzcrs( ix_knoten, ix_plan)
        LOOP 
          IF tmpVar IS NULL 
          THEN 
            tmpVar := netz_rec.netzwerkdienst; 
          ELSE 
            tmpVar := tmpVar ||','|| netz_rec.netzwerkdienst; 
          END IF; 
        END LOOP; 
    
        RETURN tmpVar; 
      END GetNetz;
    Or to take it further:

    Code:
      FUNCTION GetNetz (ix_knoten IN knoten.id_knoten%TYPE, 
      ix_plan IN knoten.id_plan%TYPE) RETURN VARCHAR2 
    
      IS 
    
        tmpVar varchar2(255); 
    
      BEGIN 
    
        -- Schleife über alle Resultdatensätze, konkateniert alle netzwerkdienste 
        FOR netz_rec IN
        ( select n.netzwerkdienst 
          from netzwerkdienst n, knoten_netzwerkdienste kn 
          where n.id_netzwerkdienst = kn.id_netzwerkdienst 
          and kn.id_plan = ix_plan 
          and kn.id_knoten = ix_knoten
        )
        LOOP 
          IF tmpVar IS NULL 
          THEN 
            tmpVar := netz_rec.netzwerkdienst; 
          ELSE 
            tmpVar := tmpVar ||','|| netz_rec.netzwerkdienst; 
          END IF; 
        END LOOP; 
    
        RETURN tmpVar; 
      END GetNetz;

  4. #4
    Join Date
    Nov 2002
    Posts
    833

    Re: cursor is having no rows but should show at least 2

    only for a better understanding and another appologize

    I' ve defined the function getnetz ((ix_knoten IN knoten.id_knoten%TYPE, ix_plan IN knoten.id_plan%TYPE)

    but called it getnetz (ip_plan, ip_knoten) ...

    sorry but it realy was my fault




    Originally posted by andrewst
    I can't see what your problem is. What do you get for:

    select n.netzwerkdienst
    from netzwerkdienst n, knoten_netzwerkdienste kn
    where n.id_netzwerkdienst = kn.id_netzwerkdienst
    and kn.id_plan = 0
    and kn.id_knoten = 122;

    ?

    While looking at it I simplified your function code to:

    Code:
      FUNCTION GetNetz (ix_knoten IN knoten.id_knoten%TYPE, 
      ix_plan IN knoten.id_plan%TYPE) RETURN VARCHAR2 
    
      IS 
    
        -- Cursor für die Datenbankabfrage 
        CURSOR netzcrs (ix_knoten knoten.id_knoten%TYPE, 
          ix_plan knoten.id_plan%TYPE) IS 
          select n.netzwerkdienst 
          from netzwerkdienst n, knoten_netzwerkdienste kn 
          where n.id_netzwerkdienst = kn.id_netzwerkdienst 
          and kn.id_plan = ix_plan 
          and kn.id_knoten = ix_knoten; 
    
        tmpVar varchar2(255); 
    
      BEGIN 
    
        -- Schleife über alle Resultdatensätze, konkateniert alle netzwerkdienste 
        FOR netz_rec IN netzcrs( ix_knoten, ix_plan)
        LOOP 
          IF tmpVar IS NULL 
          THEN 
            tmpVar := netz_rec.netzwerkdienst; 
          ELSE 
            tmpVar := tmpVar ||','|| netz_rec.netzwerkdienst; 
          END IF; 
        END LOOP; 
    
        RETURN tmpVar; 
      END GetNetz;
    Or to take it further:

    Code:
      FUNCTION GetNetz (ix_knoten IN knoten.id_knoten%TYPE, 
      ix_plan IN knoten.id_plan%TYPE) RETURN VARCHAR2 
    
      IS 
    
        tmpVar varchar2(255); 
    
      BEGIN 
    
        -- Schleife über alle Resultdatensätze, konkateniert alle netzwerkdienste 
        FOR netz_rec IN
        ( select n.netzwerkdienst 
          from netzwerkdienst n, knoten_netzwerkdienste kn 
          where n.id_netzwerkdienst = kn.id_netzwerkdienst 
          and kn.id_plan = ix_plan 
          and kn.id_knoten = ix_knoten
        )
        LOOP 
          IF tmpVar IS NULL 
          THEN 
            tmpVar := netz_rec.netzwerkdienst; 
          ELSE 
            tmpVar := tmpVar ||','|| netz_rec.netzwerkdienst; 
          END IF; 
        END LOOP; 
    
        RETURN tmpVar; 
      END GetNetz;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •