none
DataTable Adapters and Oracle Provider RRS feed

  • Question

  • Hello,

     

    The following old post describes my problem:

    ---------------------------------------------------------------------------------------------------------------------

    "Using the GUI tools to have a Table Adapter's Fill& GetData proc point to a
    SPROC in the database (or a function) works well if the proc is a Function
    that has no parameters, OR if it is a proc and you are using SQL Server.

    In my case, I want to use functions inside Oracle, and want to pass
    parameters.  First I tried using SPROC. Note I'm not trying to return a
    cursor here - my goal is simply to get the SPROC to run.


    CREATE OR REPLACE PROCEDURE getScreenByApparatus (parmapparatusID in varchar)
    IS
    myCursor doe_pkg.TPI_Cursor;

    BEGIN

    open myCursor for
    Select scr.screenID, scr.screenType,scr.Description, scr.userid
    from screen scr, screenprocessstep sps
    where sps.apparatusID = 1 -- parmApparatusID
    and sps.screenID = scr.screenID;
    end getScreenByApparatus;


      --- End SQL ---


    In the DataSet designer, I set the SCREENTableAdapter (attached to the
    SCREEN object I dragged over) to use these values:

    Command Text:  getScreenbyApparatus
    CommandType: StoredPrcocedure
    ExecuteMode: Reader
    Parameters:
       Parameter1:
                  DbType:  AnsiString
                  Direction:  Input
                  ProviderType:  VarChar

    I Right click Preview Data, select the table and procedure, and fill in the
    Parameter (either Hi There, "Hi There" or "hiThere")

    I then get back a dialog:
    Could not be previewed:
    ORA-6550: line 1 column 7:  PLS-00306. wrong number or types of arguments in
    call to 'GETSCREENBYAPPARATUS'
    ORA-6550:line 1, column 7
    PL/SQL:  Statement ignored."

    ---------------------------------------------------------------------------------------------------------------------

    The person who submitted this resolved the problem by removing the "in" qualifier in the proc declaration, however, I still get the error.  He was also using Oracle 10g, whereas I am using Oracle 9i.  Anyone run across this recently?

    Wednesday, October 24, 2007 7:30 PM

All replies