Oracle Stored procedure with table of varchar2 as outparameter

Posted on 2012-09-17
Last Modified: 2012-10-22
I am executing a oracle stored procedure which is having output parameter of type table of varchar2. This procedure is defined inside a package.

Iam using ORAOLEDB.ORACLE provider to connect with oracle DB.

Below is the stored procedure description,

Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 V_COMMODITYTYPECD              VARCHAR2                IN
 V_PARTORIGIN                   VARCHAR2                IN
 V_VEHLINE                      VARCHAR2                IN
 V_EFFIN                        VARCHAR2                IN
 V_EFFOUT                       VARCHAR2                IN

I am using the below line as command text to execute the SP,

cmdMY.CommandText = "{call SGTI_PKGETSECTIONMODELYEAR.PRGETSECTIONMODELYEAR('T','WERS','U7','31-JAN-2000','NIL',{resultset 2,O_strModelYear})}"

While executing this I am getting the error stating "O_strModelYear identifier undefined"

Could any one please help me?
    LVL 16

    Expert Comment

    by:Swadhin Ray
    >> O_STRMODELYEAR                 TABLE OF VARCHAR2(15)   OUT

    Change it to O_STRMODELYEAR                 TABLE OF VARCHAR2 OUT

    Author Comment

    I cant change in DB. This works fine with the MicrosoftODBC provider.

    Accepted Solution

    OraOLEDB.Oracle provider does not support table of varchar type. Work around should be done.

    Author Closing Comment

    Didnt get any other exact answer.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now