[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

ORACLE STORED PROCEDURE CALLING PROBLEM IN JSP?

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 ..??
0
onlyamir007
Asked:
onlyamir007
  • 4
  • 3
1 Solution
 
CEHJCommented:

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)
0
 
onlyamir007Author Commented:
Can you give me example ??
b/caz, I’m new I mean I’m writing SP first time :$
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
onlyamir007Author Commented:
thx 4 your help
0
 
CEHJCommented:
8-)
0
 
onlyamir007Author 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;
/
0
 
CEHJCommented:
This is not really the TA for these questions onlyamir007. Please post in the database TA.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now