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 last published in August 2006

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close