We help IT Professionals succeed at work.

ORACLE STORED PROCEDURE CALLING PROBLEM IN JSP?

onlyamir007
onlyamir007 asked
on
Medium Priority
304 Views
Last Modified: 2010-03-31
I wrote a stored procedure in oracle 9i  I don’t know its right or wrong  but its compiling error free ..  I want pass TID for example 189  it should return record set it could be 1 record or more then 1..
well This is my PROCEDURE:


-----------------XXX----------------XXX-------------------------XXX------------------
CREATE OR REPLACE PROCEDURE GETMSGRECSET
(TH_ID   IN ESSE.TID%TYPE)
        
        
IS
         cursor ET_cursor IS
      select EM.MSGID,EMT.PID,EM.SUT,EM.CDATE
                     FROM ESSE EM, ESST EMT
                     WHERE
                     EM.TID=TH_ID AND
                     EMT.CID=EM.MSGID AND
                     EMT.APPROVED=1 ;
begin
      
      COMMIT;
      
      
end GETMSGRECSET;
/

-----------------XXX----------------XXX-------------------------XXX------------------

I need 2 no how I can get all filed  in jsp ?
and is there any thing wrong in my s-procedure so let me know ..??
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:

You need to return the cursor from the SP. Why is there a commit in a select?

>>I need 2 no how I can get all filed  in jsp ?

Probably just cast the single row in the ResultSet returned from the SP  (the cursor) to a ResultSet (your n rows)

Author

Commented:
Can you give me example ??
b/caz, I’m new I mean I’m writing SP first time :$
CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
thx 4 your help
CERTIFIED EXPERT
Top Expert 2016

Commented:
8-)

Author

Commented:
HI  CEHJ
I need little more help :$
-----------------------------xxxxxxxxxxxxxxxxxxxxxxxx-------------------------xxxxxxxxxxxxxxxxxxxxxx------------------
CREATE OR REPLACE FUNCTION sp_get_M
(
FM_ID          IN             NUMBER,
TUSER        OUT             EM.USERID%TYPE,
TRPS         OUT            EM.MID%TYPE
)
    RETURN types.ref_MGcursor
AS
    F_cursor types.ref_MGcursor;
BEGIN
    OPEN F_cursor FOR

                                SELECT EM.USERID
            FROM EF, ET, EM , EUR
            WHERE
            EF.FID        =    FM_ID ;


            SELECT COUNT(USERID) AS TUSER
            INTO TUSER
            FROM EM
            WHERE
            EM.USERID   =   <<<<<<<  I WANT USE HERE  FIRST QUERY USERID OUTPUT ???????? >>>>>        
            
    RETURN F_cursor;
END;
/
CERTIFIED EXPERT
Top Expert 2016

Commented:
This is not really the TA for these questions onlyamir007. Please post in the database TA.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.