Oracle stored procedure to return multiple rows
I want a stored procedure in Oracle, which will return multiple rows. What should be the code? I also want to call this stored procedure from VB.NET and display this record in this platform. In the DBMS_SQL package, what does NATIVE stand for?
- I want a stored procedure in Oracle, which will return multiple rows. What should be the code?
- I also want to call this stored procedure from VB.NET and display this record in this platform.
- In the DBMS_SQL package, what does NATIVE stand for?
- There are many types of procedures that you can use. Here is an example of a REF CURSOR:
create or replace procedure employee_sel( cv_results in out sys_refcursor) is begin open cv_results for select first_name, last_name from employee; end;
where sys_refcursor is a type of REF CURSOR type
- Try calling this procedure in a select statement in VB.NET. Since VB.NET is not my strong suit, this is all of the help I can give you on VB.NET.
- Dbms_sql.native is a mandatory parameter for DBMS_SQL.PARSE.
Oracle8i introduced native dynamic SQL, an alternative to DBMS_SQL. Using native dynamic SQL, you can place dynamic SQL statements directly into PL/SQL blocks. In most situations, native dynamic SQL can replace DBMS_SQL. Native dynamic SQL is easier to use. For more info on dynamic SQL statements see Note:198306.1 at metalink.oracle.com.