1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query pl sql

Discussion in 'SQL PL/SQL' started by kamilia, Aug 27, 2016.

  1. kamilia

    kamilia Active Member

    Messages:
    21
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    algiers
    hi, everyone,
    I'm trying to write a query in order to save a result of select into a variable using pl sql like this:


    set serveroutput on;
    DECLARE
    v_nb_benev number(4);
    v_id_act number(4);
    begin
    select b.id_act, count(b.id_benev) into v_id_act, v_nb_benev from act_benev b, activite a, benevole v where a.id_act=b.id_act and v.id_benev=b.id_benev group by b.id_act;
    DBMS_OUTPUT.put_line(v_nb_benev);
    end;
    /
    but i have this error :
    DECLARE
    *
    ERREUR Ó la ligne 1 :
    ORA-01422: l'extraction exacte ramÞne plus que le nombre de lignes demandÚ
    ORA-06512: Ó ligne 5

    Thanks for any help.

    Kamilia
     
    raima likes this.
  2. Nilesh Pujari

    Nilesh Pujari Newly Initiated

    Messages:
    1
    Likes Received:
    1
    Trophy Points:
    30
    Location:
    Hyderabad
    why do you need b.id in the select query? remove it as you are not saving it to the variable.your select query must be returning two records hence ORA-01422 error.
    Run the select query outside the block, it should return only one record, ie b.id_act and the count(b.id_benev).
    these are the custom views so i couldnot test this.
     
    raima likes this.
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Very rare error.
    You need to check dimensionality the description of fields in tables and the pl/sql variables of blok.
     
    raima likes this.
  4. kamilia

    kamilia Active Member

    Messages:
    21
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    algiers
    I have checked on, i think that the problem is that i should use a loop boucle to read all lines.
     
    raima likes this.
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    At you several records with b.id_act turn out.
    It leads to TOO_MANY_ROWS error.
    Add predicates in WHERE or use HAVING.
     
  6. kamilia

    kamilia Active Member

    Messages:
    21
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    algiers
    we use having when we have a condition on agregat functions, but it's not my case, idont have any condition on function count.
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    How many rows your query
    Code (SQL):
    SELECT b.id_act, COUNT(b.id_benev)
    FROM act_benev b, activite a, benevole v WHERE a.id_act=b.id_act AND v.id_benev=b.id_benev
    GROUP BY b.id_act;
    returns ?

    for example:
    Code (SQL):
    CREATE TABLE t1  (id INT);
    CREATE TABLE t2  (id INT);
    INSERT INTO t1 SELECT level FROM dual CONNECT BY level <=7;
    INSERT INTO t2 SELECT MOD(level,3)+1 FROM dual CONNECT BY level <=7;
    commit;

    DECLARE
        l_id    INT;
        l_cnt   INT;      
    BEGIN
        dbms_output.put('Pass 1: ');
        BEGIN
            SELECT
                t1.id,
                COUNT(*)
            INTO
                l_id,
                l_cnt      
            FROM
                t1 ,
                t2
            WHERE
                t1.id = t2.id  
            GROUP BY
                t1.id;
            dbms_output.put_line(l_cnt);          
        exception
            WHEN too_many_rows THEN
                dbms_output.put_line('Error!More than one value in result!');          
        END;  
        dbms_output.put('Pass 2: ');  
        BEGIN
            SELECT
                t1.id,
                COUNT(*)
            INTO
                l_id,
                l_cnt      
            FROM
                t1 ,
                t2
            WHERE
                t1.id = t2.id  
                AND
                t1.id = 1
            GROUP BY
                t1.id;          
            dbms_output.put_line('Rows='||l_cnt);          
        exception
            WHEN too_many_rows THEN
                dbms_output.put_line('Error!More than one row  in result!');          
        END;  
     
    END;  
    /
    DROP TABLE t1;
    DROP TABLE t2;

     
     
  8. kamilia

    kamilia Active Member

    Messages:
    21
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    algiers
    thanks for your intervention, the problem was resolved, i have forgotten to make a boucle to display each rows, because i have many rows.