Click here to Skip to main content
12,290,514 members (30,223 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# VB Oracle VB.NET
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
Posted 16-Dec-12 23:54pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

try to pass the date as a string

cmd.Parameters.Add(New OracleParameter("p_effective_date", OracleType.DateTime)).Value = "10/12/2012"
  Permalink  
Comments
PradeepMogha 17-Dec-12 8:36am
   
Thank for you reply, I tried the same, but not help out
Nilsomudra 19-Sep-15 0:35am
   
How you solved it ?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

U can parse line by line and check ur solution and also comment the line in which u r getting the issue
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160518.1 | Last Updated 24 May 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100