CODEWITHSUNDEEP
javasqloracle-databaseoracle10g
user3738332
user3738332

Reputation: 41

How to return multiple rows from the stored procedure?

 "select tableshow from alltable where branch =      
 '"+session.getAttribute("Branch")+"' and sem = 
 '"+session.getAttribute("Sem")+"'";

through this query i am fetching the name of the table for example let the table name be 'fifthsem' which is stored in exe variable

now i need to apply query on this variable exe as a table name. i need to fetch the whole data from procedure of this table and show in jsp page.

using oracle database

Upvotes: 1

Views: 1420

Answers (3)

Maulik Shah
Maulik Shah

Reputation: 402

You can use Oracle Pipelined table functions.

For details on how to create a Pipelined table function in oracle, refer this link

Upvotes: 0

Bacteria
Bacteria

Reputation: 8596

How to return multiple rows from the stored procedure?

You can use REF CURSORs to return multiple rows from the stored procedure to a client application.

Using REF CURSORs is one of the most powerful, flexible, and scalable ways to return query results from an Oracle Database to a client application.

A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. In essence, a REF CURSOR is a pointer or a handle to a result set on the database. REF CURSORs are represented through the OracleRefCursor ODP.NET class.

View this example : Retrieving an Oracle cursor in Java

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

You can assign it to a variable and get data using dynamic sql

Refer this Dynamic SQL LOOP

Upvotes: 0

Related Questions