ORA-01036: illegal variable name/number

See more:

C#

VB

Oracle

I have below sp wrttin in Oracle, I am new to Oracle.

PROCEDURE PRC_SetAdjustment
(p_auth_entity_id_no IN NUMBER,
p_adjustment_id_no IN NUMBER,
p_adj_type IN CHAR,-- Can take A-Agency/P-Policy Values only
p_Agency_Id_No IN NUMBER,
p_Policy_id_no IN VARCHAR,-- signifies the Policy Id/Agency Id, depending on p_adj_type
p_entity_id_no IN NUMBER,
p_effective_date IN DATE,
p_mp_life_prem IN NUMBER,--For Annuity Adjustments/Policy Level
p_rep_life_prem IN NUMBER,--For Annuity Adjustments/Policy Level
p_mp_ann_prem IN NUMBER,--For Annuity Adjustments/Policy Level
p_rep_ann_prem IN NUMBER,--For Annuity Adjustments/Policy Level
p_fully_charge IN NUMBER,--For Vogas Adjustment/Agency Level
p_manual_exp IN NUMBER,--For Vogas Adjustment/Agency Level
p_other_amt IN NUMBER,--For Vogas Adjustment/Agency Level
p_adjustment_comments IN VARCHAR2,
p_return_value OUT NUMBER,
p_error_code OUT VARCHAR2,
p_error_msg OUT VARCHAR2) IS
v_process_code NUMBER := 80000; --*******Need to Confirm the Process Code for this*******
-- v_return_code NUMBER;
--v_error_code VARCHAR2(250);
--v_error_msg VARCHAR2(250);
v_mp_life_prem NUMBER(20,2):=NULL;
v_rep_life_prem NUMBER(20,2):=NULL;
v_mp_ann_prem NUMBER(20,2):=NULL;
v_rep_ann_prem NUMBER(20,2):=NULL;
v_fully_charge NUMBER(20,2):=NULL;
v_manual_exp NUMBER(20,2):=NULL;
v_other_amt NUMBER(20,2):=NULL;
v_policy_id_no NUMBER:=NULL;
v_agy_id_no NUMBER:=NULL;
p_adjust_id_no NUMBER;
BEGIN
p_return_value := 0;
-- If the Adjustment Type is Policy, then Values related to BGLP and Annuities will be passed from Online Screen
IF (ltrim(rtrim(p_adj_type))='P') THEN
v_mp_life_prem:=p_mp_life_prem;
v_rep_life_prem:=p_REP_life_prem;
v_mp_ann_prem:=p_mp_ann_prem;
v_rep_ann_prem:=p_rep_ann_prem;
v_policy_id_no:=p_Policy_id_no;
-- If the Adjustment Type is Firm, then Values related to VOGAS will be passed from Online Screen
ELSIF (ltrim(rtrim(p_adj_type))='A') THEN
v_fully_charge:=p_fully_charge;
v_manual_exp:=p_manual_exp;
v_other_amt:=p_other_amt;
v_agy_id_no:=p_Agency_id_no;
END IF;
IF (ltrim(rtrim(p_adj_type))='P' OR ltrim(rtrim(p_adj_type))='A' ) THEN
-- Check to see if this adjustment has been specified
IF p_adjustment_id_no IS NULL OR p_adjustment_id_no < 0 THEN
-- Create a new adjustment
SELECT SEQ_4228_ADJ.NEXTVAL INTO p_adjust_id_no FROM DUAL;
INSERT INTO SMRT_DW.TNY4228_ADJUSTMENTS
(PK_T4228_ADJUSTMENT ,
ENTITY_ID_NO ,
AGENCY_ID_NO ,
POLICY_NUMBER ,
ADJUSTMENT_TYPE ,
EFFECTIVE_DATE ,
MP_LIFE_PREMIUM ,
REP_LIFE_PREMIUM ,
ANNUITY_MP_PREMIUM ,
ANNUITY_REP_PREMIUM ,
VOGAS_FULLY_CHARGABLE_EXPENSES,
VOGAS_MANUAL_EXPENSES ,
VOGAS_OTHER_EXPENSES ,
ADJUSTMENT_COMMENT ,
CREAT_BY_NM ,
CREAT_DTM ,
CREAT_BY_PRCS_CD ,
LAST_UPD_DTM ,
LAST_UPD_BY_NM ,
LAST_UPD_PRCS_CD )
VALUES
(p_adjust_id_no,
p_entity_id_no,
v_agy_id_no,
v_policy_id_no,
p_adj_type,
p_effective_date,
v_mp_life_prem,
v_rep_life_prem,
v_mp_ann_prem,
v_rep_ann_prem,
v_fully_charge,
v_manual_exp,
v_other_amt,
p_adjustment_comments,
to_char(p_auth_entity_id_no),
SYSDATE,
v_process_code,
SYSDATE,
to_char(p_auth_entity_id_no),
v_process_code);
COMMIT;
END IF;
END IF;
--To Check Whether Adjustment will be Processed by Informatica Process/Stored Procedure Process
--Check for the Effective Date. If the Effective Date is after the 300 Date, then this will be picked up by the Informatica Process. Else SP will be Called
--v_300_dt := FN_Calc_300_Date(p_effective_date, 'C', 'M');
--IF NOT(v_300_dt) IS NULL THEN
--v_year := to_number(to_char(v_300_DT, 'YYYY'), 9999);
--v_month := to_number(to_char(v_300_DT, 'MM'));
--ELECT trunc(v_300_dt,'MM')-1 into v_prev_300_dt
--FROM DUAL;
--END IF;
--IF ltrim(rtrim(p_adj_type))='P' THEN
--IF p_effective_date < v_prev_300_dt-- and p_effective_date < v_300_dt
--prc_vogas_adj_upd(p_auth_entity_id_no,p_id_no,p_effective_date,p_amount1,p_amount2,p_amount3,p_error_code,p_error_msg);
--END IF;
--ELSE -- For Agency Related Data
--IF p_effective_date < v_prev_300_dt-- SP will be processing only the Adjustments which are with Effective Date less than
--prc_ann_adj_upd(p_auth_entity_id_no,p_id_no,p_effective_date,p_amount1,p_amount2,p_amount3,p_amount4,p_error_code,p_error_msg);
--END IF;
-- EXCEPTION
-- WHEN OTHERS THEN
p_return_value := 1;
p_error_code := to_char(SQLCODE);
p_error_msg := SQLERRM;
END PRC_SetAdjustment;



When I calling same SP from Vb.Net I am getting ORA-01036: illegal variable name/number Error

Dim returnCode As Int32
Dim cn As New OracleConnection(GetConnectionString)
conn = New OracleConnection(oradb)
Dim cmd As OracleCommand = New OracleCommand("SMRT_DW.Pkg_4228_ADJUSTMENTS.PRC_SetAdjustment", conn)
cmd.Parameters.Clear()
cmd.Parameters.Add(New OracleParameter("p_auth_entity_id_no", OracleType.Number)).Value = 0
cmd.Parameters.Add(New OracleParameter("p_adjustment_id_no", OracleType.Number)).Value = DBNull.Value
cmd.Parameters.Add(New OracleParameter("p_adj_type", OracleType.VarChar, 10)).Value = "P"
cmd.Parameters.Add(New OracleParameter(":p_Agency_Id_No", OracleType.Number)).Value = DBNull.Value
cmd.Parameters.Add(New OracleParameter(":p_Policy_id_no", OracleType.VarChar, 50)).Value = "43345"
cmd.Parameters.Add(New OracleParameter("p_entity_id_no", OracleType.Number)).Value = 8
cmd.Parameters.Add(New OracleParameter("p_effective_date", OracleType.DateTime)).Value = #10/12/2012#
cmd.Parameters.Add(New OracleParameter(":p_mp_life_prem", OracleType.Number)).Value = 878
cmd.Parameters.Add(New OracleParameter(":p_rep_life_prem", OracleType.Number)).Value = 878
cmd.Parameters.Add(New OracleParameter(":p_mp_ann_prem", OracleType.Number)).Value = 878
cmd.Parameters.Add(New OracleParameter(":p_rep_ann_prem", OracleType.Number)).Value = 878
cmd.Parameters.Add(New OracleParameter(":p_fully_charge", OracleType.Number)).Value = DBNull.Value
cmd.Parameters.Add(New OracleParameter(":p_manual_exp", OracleType.Number)).Value = DBNull.Value
cmd.Parameters.Add(New OracleParameter(":p_other_amt", OracleType.Number)).Value = DBNull.Value
cmd.Parameters.Add(New OracleParameter("p_adjustment_comments", OracleType.VarChar, 500)).Value = "test"
cmd.Parameters.Add(New OracleParameter("p_return_value", OracleType.Number)).Direction = ParameterDirection.Output
cmd.Parameters.Add(New OracleParameter("p_error_code", OracleType.VarChar, 50)).Direction = ParameterDirection.Output
cmd.Parameters.Add(New OracleParameter("p_error_msg ", OracleType.VarChar, 50)).Direction = ParameterDirection.Output

cmd.CommandType = CommandType.StoredProcedure
Try
conn.Open()
cmd.ExecuteNonQuery()
'Dim dr As OracleDataReader = cmd.ExecuteReader
'returnCode = CType(cmd.Parameters.Item("p_return_value").Value, Int32)
'Return returnCode

Catch ex As Exception
Throw ex
Finally
conn.Close()
End Try


Need help to fix this issue