Ask the Expert

Oracle stored procedure to return multiple rows

  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?

    Requires Free Membership to View

  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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: