CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqloracle11godp.net
SoftwareSavant
SoftwareSavant

Reputation: 9767

Watching .net-Oracle stored procedures calls to see parameter list

I am wondering if there is some way to look at the parameter list of stored procedure called from a .net method. I keep getting an 

PLS-00306: wrong number or types of arguments in call to 'SPPUT_FOCUSEDREADMIT'

ORA-06550: line 1, column 7

exception whenever I call my stored procedure. I look at the object that I set up to make the call and it seems like I am setting it up correctly. I know there are going to be howls for me to show the code to prove it, but I loop through an XML Document to fill the cmd.Parameters object of ODP.net... So I'll just show you the array that I use to XPath to the right nodes and the parameter listing of the stored proc... No need to show all the code...

string[] paths = new string[31];
        paths[0] = "EncounterId";
        paths[1] = "PopulationPatientID";
        paths[2] = "EMPIID";
        paths[3] = "Active";
        paths[4] = "FirstName";
        paths[5] = "LastName";
        paths[6] = "DateOfBirth";
        paths[7] = "Phone";
        paths[8] = "HospitalFinNumber";
        paths[9] = "AdmitDate";
        paths[10] = "MRNType";
        paths[11] = "MRN";
        paths[12] = "PatientRoomPhone";
        paths[13] = "DischargeDateTime";
        paths[14] = "DischargeDisposition";
        paths[15] = "DischargeTo";
        paths[16] = "DischargeAdvocateCall";
        paths[17] = "Payor";
        paths[18] = "HomeHealthCareAccepted";
        paths[19] = "SafeLandingAccepted";
        paths[20] = "PCPName";
        paths[21] = "PCPPhone";
        paths[22] = "SpecialistName";
        paths[23] = "SpecialistPhone";
        paths[24] = "PCPAppointmentDateTime";
        paths[25] = "PCPAppointmentLocation";
        paths[26] = "SpecialistAppointmentDateTime";
        paths[27] = "SpecialistAppointmentLocation";
        paths[28] = "CompletedPathway";
        paths[29] = "CompletedPathwayReason";
        paths[30] = "Comment";

        string[] colName = new string[31];
        colName[0] = "FOCUSED_READMISSIONS_IDIn";
        colName[1] = "POPULATION_PATIENT_IDIn";
        colName[2] = "EMPIin";
        colName[3] = "ACTIVEIn";
        colName[4] = "FIRST_NAMEin";
        colName[5] = "LAST_NAMEin";
        colName[6] = "DOBin";
        colName[7] = "PHONEin";
        colName[8] = "HOSPITAL_FIN_NUMBERin";
        colName[9] = "ADMIT_DATEin";
        colName[10] = "MRN_TYPEin";
        colName[11] = "MRNin";
        colName[12] = "PATIENT_ROOM_PHONEin";
        colName[13] = "DISCHARGE_DATEin";
        colName[14] = "DISCHARGE_DISPOSITIONin";
        colName[15] = "DISCHARGE_TOin";
        colName[16] = "DISCHARGE_ADVOCATE_CALLin";
        colName[17] = "PAYORin";
        colName[18] = "HOME_HEALTHCARE_ACCEPTEDin";
        colName[19] = "SAFE_LANDING_ACCEPTEDin";
        colName[20] = "PCP_NAMEin";
        colName[21] = "PCP_PHONEin";
        colName[22] = "SPECIALIST_NAMEin";
        colName[23] = "SPECIALIST_PHONEin";
        colName[24] = "PCP_APPOINTMENT_DATETIMEin";
        colName[25] = "PCP_APPOINTMENT_LOCATIONin";
        colName[26] = "SPECIALIST_APPT_DATETIMEin";
        colName[27] = "SPECIALIST_APPT_LOCATIONin";
        colName[28] = "COMPLETED_PATHWAYin";
        colName[29] = "COMPLETED_PATHWAY_REASONin";
        colName[30] = "COMMENTSin";

Here is the Parameter list of the stored Proc...

FOCUSED_READMISSIONS_IDIn   NUMBER,
POPULATION_PATIENT_IDIn     NUMBER,
EMPIin                      VARCHAR2,
FIRST_NAMEin                VARCHAR2,
LAST_NAMEin                 VARCHAR2,
DOBin                       VARCHAR2,
PHONEin                     VARCHAR2,
HOSPITAL_FIN_NUMBERin       VARCHAR2,
ADMIT_DATEin                VARCHAR2,
MRN_TYPEin                  VARCHAR2,
MRNin                       VARCHAR2,
PATIENT_ROOM_PHONEin        VARCHAR2,
DISCHARGE_DATEin            VARCHAR2,
DISCHARGE_DISPOSITIONin     VARCHAR2,
DISCHARGE_TOin              VARCHAR2,
DISCHARGE_ADVOCATE_CALLin   VARCHAR2,
PAYORin                     VARCHAR2,
HOME_HEALTHCARE_ACCEPTEDin  VARCHAR2,
SAFE_LANDING_ACCEPTEDin     VARCHAR2,
PCP_NAMEin                  VARCHAR2,
PCP_PHONEin                 VARCHAR2,
SPECIALIST_NAMEin           VARCHAR2,
SPECIALIST_PHONEin          VARCHAR2,
PCP_APPOINTMENT_DATETIMEin  VARCHAR2,
PCP_APPOINTMENT_LOCATIONin  VARCHAR2,
SPECIALIST_APPT_DATETIMEin  VARCHAR2,
SPECIALIST_APPT_LOCATIONin  VARCHAR2,
COMPLETED_PATHWAYin         VARCHAR2,
COMPLETED_PATHWAY_REASONin  VARCHAR2,
COMMENTSIn                  VARCHAR2,
ACTIVEIn                    VARCHAR2,
ACTIVE_DATEIn               VARCHAR2

But that code is beside the point. How can I view what is being sent to the stored proc when I call it from a .net function. Because when I call it from sql Developer, it seems to work just fine.

Upvotes: 0

Views: 487

Answers (1)

Ben
Ben

Reputation: 52923

Actually, I count 32 parameters in the Oracle parameter list and you are only calling it with 31. Which doesn't really answer the question but is the reason for your error.

There are plenty of ways of finding out the list of parameters from Oracle. You can use describe or the stored procedures dbms_metadata, dbms_describe; then there's the system tables and views, all_procedures, user_arguments etc.

user_arguments is probably the one most likely to be useful to you.

Upvotes: 1

Related Questions