question

sruty avatar image
sruty asked

Convert Stored procedure statements to script in SQL*Plus

i am new to this SQl, I have no idea in doing this conversion tpe.. just I need these below stored procedures statements to be converted into SQl plus scripts here is the Procedure: PROCEDURE SP_PROCESS_RATE_PLAN IS var_cursor INTEGER; prv_rp_update_sql VARCHAR2(4000); prv_rp_insert_sql VARCHAR2(4000); v_Dummy INTEGER; SQL_STATEMENT VARCHAR2(400); /* Massage the data into the Rate_Plan table */ BEGIN /*Trigger is dropped and recreated for table PRICEVIEW_RATE_PLAN_PROC */ EXECUTE IMMEDIATE 'DROP TRIGGER RATE_PLAN_ID_TRIGGER'; EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER RATE_PLAN_ID_TRIGGER before insert on priceview_rate_plan_proc for each row begin select RATE_PLAN_ID_SEQ.nextval into :new.rate_plan_id from dual; end;'; /* Update the Rate_Period to the same value for all rate_periods*/ prv_rp_update_sql:= 'UPDATE RATE_USAGE_OVERRIDES a SET a.rate_period = ''0'' WHERE a.seqnum NOT IN (SELECT /*+ use_hash(rate_usage_overrides) */ a.seqnum FROM rate_usage_overrides b WHERE a.type_id_usg = b.type_id_usg AND a.element_id = b.element_id AND a.jurisdiction = b.jurisdiction AND a.provider_class = b.provider_class AND a.corridor_plan_id = b.corridor_plan_id AND ( a.FIXED_CHARGE_AMT <> b.FIXED_CHARGE_AMT OR a.ADD_FIXED_AMT <> b.ADD_FIXED_AMT OR a.ADD_UNIT_RATE <> b.ADD_UNIT_RATE ) AND DECODE(a.INACTIVE_DT,NULL,''00/00/0000'',a.INACTIVE_DT) = DECODE(b.INACTIVE_DT,NULL,''00/00/0000'',b.INACTIVE_DT) AND a.ACTIVE_DT = b.ACTIVE_DT ) AND (SELECT count(*) from RATE_USAGE_OVERRIDES c WHERE a.type_id_usg = c.type_id_usg AND a.element_id = c.element_id AND a.jurisdiction = c.jurisdiction AND a.provider_class = c.provider_class AND a.corridor_plan_id = c.corridor_plan_id AND a.FIXED_CHARGE_AMT = c.FIXED_CHARGE_AMT AND a.ADD_FIXED_AMT = c.ADD_FIXED_AMT AND DECODE(a.INACTIVE_DT,NULL,''00/00/0000'',a.INACTIVE_DT) = DECODE(c.INACTIVE_DT,NULL,''00/00/0000'',c.INACTIVE_DT) AND a.ACTIVE_DT = c.ACTIVE_DT AND a.ADD_UNIT_RATE = c.ADD_UNIT_RATE) > 1'; var_cursor := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(var_cursor,prv_rp_update_sql,DBMS_SQL.V7); v_Dummy:= DBMS_SQL.EXECUTE(var_cursor); DBMS_SQL.CLOSE_CURSOR(var_cursor); COMMIT; LOCK TABLE PRICEVIEW_RATE_PLAN_PROC IN EXCLUSIVE MODE; /* Insert the Rate_Plan data */ prv_rp_insert_sql := 'insert into PRICEVIEW_RATE_PLAN_PROC (SSR_CODE, CORRIDOR_PLAN_ID, CORRIDOR_PLAN_DESCRIPTION, USAGE_TYPE, PRODUCT, JURISDICTION, PROVIDER, RATE_PERIOD, FLAGFALL, RATE, RATEBAND, NUMSECS, BAND_RATE, ACTIVE_DT, INACTIVE_DT) select /*+ use_hash(rate_usage_overrides,corridor_plan_id_values,product_elements,descriptions,jurisdictions,rate_usage_bands_overrides) */ distinct decode(a.corridor_plan_id,0, '''', (select c.short_display from corridor_plan_id_values c where a.corridor_plan_id = c.corridor_plan_id)) AS SSR_CODE, a.corridor_plan_id AS CORRIDOR_PLAN_ID, decode(a.corridor_plan_id,0, '''', (select d.display_value from corridor_plan_id_values d where a.corridor_plan_id = d.corridor_plan_id)) AS CORRIDOR_PLAN_DESCRIPTION, decode(a.type_id_usg,0, '''', (select f.description_text from usage_types e, descriptions f where a.type_id_usg = e.type_id_usg and e.description_code = f.description_code)) AS USAGE_TYPE, decode(a.element_id,0, '''', (select h.description_text from product_elements g,descriptions h where a.element_id = g.element_id and g.description_code = h.description_code)) AS PRODUCT, decode(a.jurisdiction,0, '''', (select j.description_text from jurisdictions i,descriptions j where a.jurisdiction = i.jurisdiction and j.description_code = i.description_code)) AS JURISDICTION, decode(a.provider_class,0, '''', (select k.display_value from provider_class_values k where a.provider_class = k.provider_class)) AS PROVIDER, decode(a.rate_period,''0'', '''',(select l.display_value from rate_period_values l where a.rate_period = l.rate_period)) AS RATE_PERIOD, (a.FIXED_CHARGE_AMT/100) + (a.ADD_FIXED_AMT/10000000) AS FLAGFALL, (a.ADD_UNIT_RATE/10000000) * 60 AS RATE, b.RATEBAND AS RATEBAND, b.NUM_UNITS AS NUMSECS, (b.UNIT_RATE/10000000) * 60 AS BAND_RATE, a.ACTIVE_DT AS ACTIVE_DT, a.INACTIVE_DT AS INACTIVE_DT from rate_usage_overrides a, rate_usage_bands_overrides b where a.seqnum = b.seqnum(+)'; var_cursor := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(var_cursor,prv_rp_insert_sql,DBMS_SQL.V7); v_Dummy:= DBMS_SQL.EXECUTE(var_cursor); DBMS_SQL.CLOSE_CURSOR(var_cursor); COMMIT; /* Create the indexes */ EXECUTE IMMEDIATE 'DROP INDEX RRP_RATE_PLAN_ID'; EXECUTE IMMEDIATE 'DROP INDEX RRP_USAGE_TYPE'; EXECUTE IMMEDIATE 'DROP INDEX RRP_CORRIDOR_PLAN_ID'; EXECUTE IMMEDIATE 'DROP INDEX RRP_SSR_CODE'; EXECUTE IMMEDIATE 'DROP INDEX RRP_UT_PROD'; EXECUTE IMMEDIATE 'DROP INDEX RRP_UT_JURIS'; EXECUTE IMMEDIATE 'DROP INDEX RRP_UT_JURIS_PROV'; EXECUTE IMMEDIATE 'DROP INDEX RRP_UT_PD_JR_PV'; EXECUTE IMMEDIATE 'DROP INDEX RRP_UT_PROD_JURIS'; EXECUTE IMMEDIATE 'DROP INDEX RRP_UT_PROD_PROV'; EXECUTE IMMEDIATE 'DROP INDEX RRP_UT_PROV'; EXECUTE IMMEDIATE 'CREATE INDEX RRP_CORRIDOR_PLAN_ID ON PRICEVIEW_RATE_PLAN_PROC(CORRIDOR_PLAN_ID, USAGE_TYPE) TABLESPACE PRV_Index STORAGE( PCTINCREASE 0 )'; EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX RRP_RATE_PLAN_ID ON PRICEVIEW_RATE_PLAN_PROC(RATE_PLAN_ID) TABLESPACE PRV_Index STORAGE( PCTINCREASE 0 )'; EXECUTE IMMEDIATE 'CREATE INDEX RRP_SSR_CODE ON PRICEVIEW_RATE_PLAN_PROC(SSR_CODE) TABLESPACE PRV_Index STORAGE( PCTINCREASE 0 )'; EXECUTE IMMEDIATE 'CREATE INDEX RRP_USAGE_TYPE ON PRICEVIEW_RATE_PLAN_PROC(USAGE_TYPE) TABLESPACE PRV_Index STORAGE( PCTINCREASE 0 )'; EXECUTE IMMEDIATE 'CREATE INDEX RRP_UT_PROD ON PRICEVIEW_RATE_PLAN_PROC(USAGE_TYPE, PRODUCT) TABLESPACE PRV_Index STORAGE( PCTINCREASE 0 )'; EXECUTE IMMEDIATE 'CREATE INDEX RRP_UT_JURIS ON PRICEVIEW_RATE_PLAN_PROC(USAGE_TYPE, JURISDICTION) TABLESPACE PRV_Index STORAGE( PCTINCREASE 0 )'; EXECUTE IMMEDIATE 'CREATE INDEX RRP_UT_JURIS_PROV ON PRICEVIEW_RATE_PLAN_PROC(USAGE_TYPE, JURISDICTION, PROVIDER) TABLESPACE PRV_Index STORAGE( PCTINCREASE 0 )'; EXECUTE IMMEDIATE 'CREATE INDEX RRP_UT_PD_JR_PV ON PRICEVIEW_RATE_PLAN_PROC(USAGE_TYPE, PRODUCT, JURISDICTION, PROVIDER) TABLESPACE PRV_Index STORAGE( PCTINCREASE 0 )'; EXECUTE IMMEDIATE 'CREATE INDEX RRP_UT_PROD_JURIS ON PRICEVIEW_RATE_PLAN_PROC(USAGE_TYPE, PRODUCT, JURISDICTION) TABLESPACE PRV_Index STORAGE( PCTINCREASE 0 )'; EXECUTE IMMEDIATE 'CREATE INDEX RRP_UT_PROD_PROV ON PRICEVIEW_RATE_PLAN_PROC(USAGE_TYPE, PRODUCT, PROVIDER) TABLESPACE PRV_Index STORAGE( PCTINCREASE 0 )'; EXECUTE IMMEDIATE 'CREATE INDEX RRP_UT_PROV ON PRICEVIEW_RATE_PLAN_PROC(USAGE_TYPE, PROVIDER) TABLESPACE PRV_Index STORAGE( PCTINCREASE 0 )'; /* Cut over to the new tables */ LOCK TABLE PRICEVIEW_RATE_PLAN IN EXCLUSIVE MODE ; EXECUTE IMMEDIATE 'ALTER TABLE PRICEVIEW_RATE_PLAN RENAME TO PRICEVIEW_RATE_PLAN_X'; EXECUTE IMMEDIATE 'ALTER TABLE PRICEVIEW_RATE_PLAN_PROC RENAME TO PRICEVIEW_RATE_PLAN'; EXECUTE IMMEDIATE 'ALTER TABLE PRICEVIEW_RATE_PLAN_X RENAME TO PRICEVIEW_RATE_PLAN_PROC'; /* Remove the old data */ EXECUTE IMMEDIATE 'TRUNCATE TABLE PRICEVIEW_RATE_PLAN_PROC'; END SP_PROCESS_RATE_PLAN; END PRICEVIEW_PROCESS_EXTRACT; /
plsqloracle-sql-developerstored procedures
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.