I am trying to create a script in oracle to search through a number of tables and insert into a temp table i am creating but i am getting a few errors when trying to do anything other than a number.
Declare Variables
m_polCount NUMBER:= 0;
m_product NUMBER:= 0;
m_version NUMBER:= 0;
m_plan NUMBER:= 0;
m_policy NUMBER:= 0;
m_pol_comm_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
m_pol_end_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
m_pol_status NUMBER:= 0;
m_next_pre_renew_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
m_next_renew_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
m_last_renew_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
m_cover_no NUMBER:= 0;
m_cover_name VARCHAR(240):= 'Test';
m_cover_start_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
m_cover_end_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
m_sum_assured NUMBER:= 0;
m_cover_layer NUMBER:= 0;
m_cover_prem_layer NUMBER:= 0;
m_premium NUMBER:= 0;
m_loading_type NUMBER:= 0;
m_loading_name VARCHAR(200):= 'Test';
m_basic_prem_loading SMALLINT:= 0;
m_loading_start_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
m_loading_end_date DATE:= TO_DATE('01-01-3000','DD-MM-YYYY');
m_loading_perc NUMBER:= 0;
m_loading_rate NUMBER:= 0;
m_loading_prem NUMBER:= 0;
m_calc_desc VARCHAR(240):= 'Test';
m_inflation_rate NUMBER:= 0;
m_agent_no NUMBER:= 0;
Table Created
EXECUTE IMMEDIATE '
CREATE TABLE I233_ACTIVE_P4L_POLICY_DATA
(
PRODUCT NUMBER,
VERSION NUMBER,
PLAN_NUMBER NUMBER,
POLICY_NUMBER NUMBER,
POLICY_COMM_DATE DATE,
POLICY_END_DATE DATE,
POLICY_STATUS NUMBER,
NEXT_PRE_RENEWAL_DATE DATE,
NEXT_RENEWAL_DATE DATE,
LAST_RENEWAL_DATE DATE,
COVER_NUMBER NUMBER,
COVER_NAME VARCHAR(240),
COVER_START_DATE DATE,
COVER_END_DATE DATE,
SUM_ASSURED NUMBER,
COVER_LAYER NUMBER,
COVER_PREMIUM_LAYER NUMBER,
ANNUAL_PREMIUM NUMBER,
LOADING_TYPE NUMBER,
LOADING_NAME VARCHAR(200),
BASIC_PREM_LOADING SMALLINT,
LOADING_START_DATE DATE,
LOADING_END_DATE DATE,
LOADING_PERC NUMBER,
LOADING_RATE NUMBER,
LOADING_PREM NUMBER,
PREMIUM_CALC_DESC VARCHAR(240),
INFLATION_RATE NUMBER,
AGENT_NUMBER NUMBER
)';
Insert Statement
EXECUTE IMMEDIATE '
INSERT INTO I233_ACTIVE_P4L_POLICY_DATA
VALUES(
'||m_product||',
'||m_version||',
'||m_plan||',
'||policy_rec.policy_no||',
'||m_pol_comm_date||', <----This field
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
'||m_pol_status||',
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
'||m_cover_no||',
'''',
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
'||m_sum_assured||',
'||m_cover_layer||',
'||m_cover_prem_layer||',
'||m_premium||',
'||m_loading_type||',
''TEST3'',
'||m_basic_prem_loading||',
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
'||m_loading_perc||',
'||m_loading_rate||',
'||m_loading_prem||',
''TEST3'',
'||m_inflation_rate||',
'||m_agent_no||')';
Error
ORA-00917: missing comma
If i try to do the date in the same way i get a difference error
Insert Statement
EXECUTE IMMEDIATE '
INSERT INTO I233_ACTIVE_P4L_POLICY_DATA
VALUES(
'||m_product||',
'||m_version||',
'||m_plan||',
'||policy_rec.policy_no||',
m_pol_comm_date, <----This field
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
'||m_pol_status||',
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
'||m_cover_no||',
'''',
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
'||m_sum_assured||',
'||m_cover_layer||',
'||m_cover_prem_layer||',
'||m_premium||',
'||m_loading_type||',
''TEST3'',
'||m_basic_prem_loading||',
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
'||m_loading_perc||',
'||m_loading_rate||',
'||m_loading_prem||',
''TEST3'',
'||m_inflation_rate||',
'||m_agent_no||')';
Gives me the following error
ORA-00984: column not allowed here
I also come into the same problems when trying to insert any of the VARCHAR fields using the same methods.
Is there any way i can insert date / varchar fields using an execute immediate into a temp table created above ?
All the above statements are contained within a BEGIN , END chunk
Thanks
Assuming you can't avoid the dynamic sql, use bind variables. That way, you don't have to worry about datatype conversions, etc.
Something like:
execute immediate '
INSERT INTO I233_ACTIVE_P4L_POLICY_DATA
VALUES(:m_product,
:m_version,
:m_plan,
:policy_rec.policy_no,
:m_pol_comm_date, <----This field
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
:m_pol_status,
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
:m_cover_no,
'''',
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
:m_sum_assured,
:m_cover_layer,
:m_cover_prem_layer,
:m_premium,
:m_loading_type,
''TEST3'',
:m_basic_prem_loading,
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
TO_DATE(''01-01-3000'',''DD-MM-YYYY''),
:m_loading_perc,
:m_loading_rate,
:m_loading_prem,
''TEST3'',
:m_inflation_rate,
:m_agent_no)'
USING :m_product,
:m_version,
:m_plan,
:policy_rec.policy_no,
:m_pol_comm_date,
:m_pol_status,
:m_cover_no,
:m_sum_assured,
:m_cover_layer,
:m_cover_prem_layer,
:m_premium,
:m_loading_type,
:m_basic_prem_loading,
:m_loading_perc,
:m_loading_rate,
:m_loading_prem,
:m_inflation_rate,
:m_agent_no;