Q

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?

  1. I want a stored procedure in Oracle, which will return multiple rows. What should be the code?
  2. I also want to call this stored procedure from VB.NET and display this record in this platform.
  3. In the DBMS_SQL package, what does NATIVE stand for?
  1. 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

  2. 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.

  3. 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.

This was first published in August 2006

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close