Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    95

    Unanswered: SELECT doesn't return anything inside FUNCTION/PROCEDURE

    Hi all,

    I have a strange situation, which I can't get around.

    So here goes. I've got a procedure, in which I make a SELECT INTO statment to get some values for later use.
    The problem is, when I run the procedure I get an error of "No Data Found", but if I run the same SELECT I've got no problems, and obtaining the correct values.

    I've tried to put the SELECT INTO, inside a function, to return the value, and make a SELECT function INTO FROM DUAL on the procedure, with the same result "No Data Found", but if I run the function on it's own, no problem there.

    Has anyone have a clue, because I can't guess it.

    Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    privilege acquired via ROLE do NOT apply within named PL/SQL procedure

    direct GRANT against table is required.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2004
    Posts
    95
    Don't think this is a priviledge problem, once the all structure (tables, views, procedures, packages and functions) is created with in only one USER/SCHEMA.

    I've done the same process in another packages, and all work.

    Thanks.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You have a mystery & we have no clues.

    how can we reproduce what you report?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    it would help if we had the code to see your problem.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    May 2004
    Posts
    95
    here goes:
    the function
    Code:
    CREATE OR REPLACE 
    FUNCTION get_hierarchy
      (w_person_id  IN  person.person_id%TYPE)
        RETURN  VARCHAR2 IS
        
        v_person_id person.person_id%TYPE := w_person_id;
        v_return    VARCHAR2(4000);
    BEGIN 
        SELECT SUBSTR(sys_connect_by_path(per.person_id,','),2,LENGTH(sys_connect_by_path(per.person_id,','))) tree
        INTO v_return
        FROM person per
            ,(SELECT DISTINCT pen.person_id, pen.superior_id
            FROM per_ent pen
            ) xxx
        WHERE per.person_id = xxx.person_id AND per.person_id = v_person_id
        CONNECT BY xxx.superior_id = PRIOR per.person_id
        START WITH xxx.superior_id = 0;
        
        RETURN v_return ;
    END;
    /
    now the procedure:
    Code:
    CREATE OR REPLACE 
    PACKAGE insert_pkg 
      IS
    
    /*===============================================================*/
        PROCEDURE INSERT_MOVEMENT
    /*===============================================================*/
        ( w_person_id_creation      IN  NUMBER);
    
    END; -- Package spec
    /
    
    CREATE OR REPLACE 
    PACKAGE BODY insert_pkg
    IS
    
    /*===============================================================*/
        PROCEDURE INSERT_MOVEMENT
    /*===============================================================*/
        ( w_person_id_creation      IN  NUMBER)
        IS
    
        v_person_id_creation        NUMBER := w_pessoa_id_criacao;
        v_movement_id				NUMBER;
        v_tree                      VARCHAR2(4000);
    	v_id						NUMBER;
    
        BEGIN
            INSERT INTO movement
    			(person_id, date)
            VALUES
                (v_person_id, SYSDATE);
                        
    		SELECT mov_seq.CURRVAL INTO v_movement_id FROM dual;
            
    		/*here is where the error occurs*/
            SELECT get_hierarchy(v_person_id)
            INTO v_tree
            FROM DUAL;
            
            v_tree := v_tree||',';
    		
    		WHILE LENGTH(v_tree) > 0 LOOP
    			v_id := TO_NUMBER(SUBSTR(v_tree,1,INSTR(v_tree,',',1,1)-1));
    			
    			insert into validation
    				(id, movement_id)
    			values
    				(v_id, v_movement_id);
    			
    			v_tree := SUBSTR(v_tree,INSTR(v_tree,',',1,1)+1,LENGTH(v_tree));
    		END LOOP;
    		
            <<end>>
            COMMIT;
        END;
    
    END;
    /

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    does below behave differently/better?

    Code:
    CREATE OR REPLACE 
    FUNCTION get_hierarchy
      (w_person_id  IN  person.person_id%TYPE)
        RETURN  VARCHAR2 IS
        
        v_person_id person.person_id%TYPE;
        v_return    VARCHAR2(4000);
    BEGIN 
       v_person_id := w_person_id;
     
        SELECT SUBSTR(sys_connect_by_path(per.person_id,','),2,LENGTH(sys_connect_by_path(per.person_id,','))) tree
        INTO v_return
        FROM person per
            ,(SELECT DISTINCT pen.person_id, pen.superior_id
            FROM per_ent pen
            ) xxx
        WHERE per.person_id = xxx.person_id AND per.person_id = v_person_id
        CONNECT BY xxx.superior_id = PRIOR per.person_id
        START WITH xxx.superior_id = 0;
        
        RETURN v_return ;
    END;
    /
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    May 2004
    Posts
    95
    is the same...

    ORA-06503: PL/SQL: Function returned without value

Posting Permissions

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