1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle connection thrown exception

Discussion in 'SQL PL/SQL' started by Muni, Aug 11, 2016.

  1. Muni

    Muni Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    usa
    Hi All,

    I am using oracle connection string, when I tried to call store procedure getting bellow error

    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "MAXMCP.Datace_Getby_Id", line 36
    ORA-06512: at line 1

    please help me

    conn.ConnectionString = "User Id=raja;Password=yellow;Data Source=radusfour";

    conn.Open();
    Submit2WorkflowCB.Enabled = false;

    cmd = new OracleCommand("MAXMCP.Datace_Getby_Id", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    OracleParameter ID = new OracleParameter();
    ID.OracleDbType = OracleDbType.Varchar2;
    ReffID.Direction = ParameterDirection.Input;
    ReffID.Size = 255;
    ReffID.Value = "8766664443";
    cmd.Parameters.Add(ReffID);

    OracleParameter P_Place_service = new OracleParameter();
    P_Place_service.OracleDbType = OracleDbType.Varchar2;
    P_Place_service.Direction = ParameterDirection.Output;
    P_Place_service.Size = 255;
    cmd.Parameters.Add(P_Place_service);

    OracleParameter P_service = new OracleParameter();
    P_service.OracleDbType = OracleDbType.Varchar2;
    P_service.Direction = ParameterDirection.Output;
    P_service.Size = 255;
    cmd.Parameters.Add(P_service);

    //cmd = new OracleCommand(sql, conn);
    //cmd.CommandType = CommandType.Text;

    //conn.Open();

    da = new OracleDataAdapter(cmd);
    cb = new OracleCommandBuilder(da);
    ds = new DataSet();

    da.Fill(ds);

    AuthSummaryDG.DataSource = ds.Tables[0];



    Store procedure:
    ------------------
    CREATE OR REPLACE PROCEDURE MAXMCP.Datace_Getby_Id(ReffID IN varchar2,
    P_Place_service OUT varchar2,
    P_service OUT varchar2
    )
    IS


    Begin


    SELECT POS.pos_code||' - '||POS.pos_desc place_of_service,
    SEV.sev_code||' - '||SEV.sev_desc service
    Into P_Place_service,P_service
    from MAXMCP.tau_treatment_authorization TAU
    LEFT OUTER JOIN MAXMCP.tcm_tau_component TCM ON TCM.TCM_TAU_UID=TAU.tau_uid
    LEFT OUTER JOIN MAXMCP.TID_TCM_DAYS_DETAILS TID ON TID.TID_TCM_UID = TCM.TCM_UID

    WHERE TAU.TAU_REFERENCE_NUMBER=ReffID;

    -- DBMS_OUTPUT.PUT_LINE('time before procedure ' || P_Place_service||P_service);


    End;
    /
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What you report:

    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "MAXMCP.Datace_Getby_Id", line 36
    ORA-06512: at line 1

    it NOT a connection error; you're connected to the database successfully. That is a PL/SQL error stating that you have a coding problem at line 36 of your procedure. The problem is how you are calling that procedure; you have provided NO input values and, as such, Oracle is throwing the error.

    Fix the call to the stored procedure to include the required input values.