| return a subset rows from sp [message #374905] |
Tue, 10 July 2001 17:01  |
George
Messages: 68
Registered: April 2001
|
Member |
|
|
I need to retun a subset of rows from the sp, which use a ref cursor to query tables, to the calling program (JAVA). I tried to use a host variable passing in the sp, but I got the whole result sets instead of what I need. Does anybody know how to do this? any help will be greatly appreciated. below is my code:
CREATE OR REPLACE PACKAGE BODY emppkg AS
procedure querysubrows (
table_name VARCHAR2,
startrowno IN NUMBER,
stoprowno IN NUMBER,
orderbystring IN VARCHAR2,
totalrows OUT NUMBER,
sum_cv IN OUT sumCur) AS
sql_statement VARCHAR2(1000);
BEGIN
sql_statement :='SELECT * FROM '||table_name||' ORDER BY :str';
OPEN sum_cv FOR sql_statement USING orderbystring;
END;
END;
I run the sp like:
SET AUTOPRINT ON
VARIABLE num NUMBER
VARIABLE cv REFCURSOR
EXECUTE emppkg.querysubrows ('emp', 5, 8, 'EMPNO', :num, :cv);
|
|
|
|
| Re: return a subset rows from sp [message #374962 is a reply to message #374905] |
Fri, 13 July 2001 16:39  |
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
|
Member |
|
|
I guess, you can use OUT parameter in the parameter list for the procedure with the datatype VARCHAR and get the output desired. Try it out.
It would be like
create or replace procedure my_test (emp_no_in IN number, data_out OUT varchar) as
begin
select emp_no||last_name||first_name into dataout from my_table where emp_no = emp_no_in;
end;
/
Try it out and please let me know whether it worked.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
