Q
Problem solve Get help with specific problems with your technologies, process and projects.

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.

Dig Deeper on Using Oracle PL-SQL

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.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close