Home » SQL & PL/SQL » SQL & PL/SQL » How to pass a value from one procedure to another block by calling it (Oracle 9.2.0.3)
How to pass a value from one procedure to another block by calling it [message #405278] Wed, 27 May 2009 05:17 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I want to get the cnt value and pass it to a different procedure.

BuT I am getting null for cnt value
CREATE or replace PROCEDURE DUMMY270509 ( in_application_id IN number,

RC1 OUT globalPkg.RCT1)
AS
a number;
cnt  number,
BEGIN

OPEN RC1 FOR
SELECT application_id into a FROM APPLICATION WHERE APPLICATION_ID=in_application_id;
CNT:=SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(CNT);
END;




I want to use the cnt value got from the above procedure in another procedure .How to do this?
thanks
Re: How to pass a value from one procedure to another block by calling it [message #405285 is a reply to message #405278] Wed, 27 May 2009 05:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You only opened a cursor. SQL%ROWCOUNT returns the number of records that were fetched (or deleted or updated) during the last SQL statement.
In this case, SQL%ROWCOUNT is not applicable.

If you want to know the number of records in your resultset, add "count(*) over ()" as (pseudo)column in your query
Re: How to pass a value from one procedure to another block by calling it [message #405299 is a reply to message #405285] Wed, 27 May 2009 05:55 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
call procedure dummy270509
INSERT INTO temp_tbl1 
SELECT * FROM dept_tbl  WHERE deptid in (        
                             SELECT deptid
                             FROM (        
                                     SELECT deptid FROM dept_tbl 
                                     HAVING MAX(sectionnbr)= [ cnt of rows got from RC1 of DUMMY270509 procedure]
                                     GROUP BY deptid
                                     ORDER BY DBMS_RANDOM.value) 
                              WHERE ROWNUM = 1)
                              

                           
    loop
    fetch rc1 into  vdeptnbr, v_sectionnbr, var3...;
    insert into temp_tbl2
    select  vdeptnbr,v_sectionnbr , LOCATION_ID (location_id is a column from dept_tbl) from dept_tbl where sectionnbr=v_section_nbr;
    end loop;                        
    


after this I want to insert the fetch values into another temp table with some values from dept_tbl
and some values from the fetch statement where sectionnbr= fetched section nbr variable (it would be in loop)
here requirement is to pick any dept_id that have max nuber of sections equal to last fetched value of resultset.




Re: How to pass a value from one procedure to another block by calling it [message #405316 is a reply to message #405278] Wed, 27 May 2009 06:20 Go to previous message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Can we use FETCH rc1 into twice in a block one with loop and another without loop. That way I can get the last value for sectionnbr (without loop)
Previous Topic: Can't qrouping or sorting in monthwise (merged 2)
Next Topic: procedure not working properly when run through sys
Goto Forum:
  


Current Time: Wed Dec 07 14:47:10 CST 2016

Total time taken to generate the page: 0.13344 seconds