Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DBMS_SQL.PARSE

Augustine VinishMar 1 2013 — edited Mar 4 2013
Hi Team ,

The below inesrt query returns an error message " exact fetch returns more than one row " . How can I found the insert query statement on the below query in the DBMS_SQL.PARSE ?

Code :

v_query(1) := 'insert into ' || gv_table_name || ' ' ;
v_query(2) :=' select ';
v_query(3) := v_counter || '+ rownum , ';
v_query(4) :='''020'' || ';
v_query(5) :='lpad(p.fixed_cont_code,11,''0'') || ';
v_query(6) :='rpad(NVL(ref.bridge_code_1,'' ''),15,'' '') || ';
v_query(7) :='rpad(NVL(ref.bridge_code_2,'' ''),15,'' '') || ';
v_query(8) :='rpad(NVL(ref.bridge_code_3,'' ''),15,'' '') || ';
v_query(9) :='LPAD(NVL(ref.bridge_code_4,'' ''),15,'' '') || ';
v_query(10) :='LPAD(NVL(ref.bridge_code_5,'' ''),15,'' '') || ';
v_query(11) :='LPAD(NVL(ref.bridge_code_6,'' ''),15,'' '') || ';
v_query(12) :='LPAD(NVL(ref.bridge_code_7,'' ''),15,'' '') || ';
v_query(13) :='LPAD(NVL(ref.bridge_code_8,'' ''),15,'' '') || ';
v_query(14) :='LPAD(NVL(ref.bridge_code_9,'' ''),15,'' '') || ';
v_query(15) :='LPAD(NVL(ref.bridge_code_10,'' ''),15,'' '') || ';
v_query(16) :='rpad(prn.prod_short_name,80,'' '') || ';
v_query(17) :='rpad(nvl(p.pack_description,'' ''),80,'' '') || ';
v_query(18) :='lpad(NVL(get_prod_org_code(p.market_abbr,p.prod_code,''' || GV_PERIOD_START_DATE ||'''),''0''),5,''00'') || ';
--v_query(19) :='lpad(''0'',5,''0'') || ';
v_query(19) :='lpad(NVL(get_prod_org_dist_code(p.market_abbr,p.prod_code,''' || GV_PERIOD_START_DATE ||'''),''0''),5,''00'') || ';
v_query(20) :='RPAD(NVL(trim(ref.class_code_1),''Z98A2''),12,'' '') || ';
v_query(21) :='RPAD(NVL(trim(ref.class_code_2),''98A2Z''),12,'' '') || ';
v_query(22) :='RPAD(NVL(ref.class_code_3,'' ''),12,'' '') || ';
v_query(23) :='RPAD(NVL(ref.class_code_4,'' ''),12,'' '') || ';
v_query(24) :='RPAD(NVL(ref.class_code_5,'' ''),12,'' '') || ';
v_query(25) :='RPAD(NVL(ref.class_code_6,'' ''),12,'' '') || ';
v_query(26) :='RPAD(NVL(ref.class_code_7,'' ''),12,'' '') || ';
v_query(27) :='RPAD(NVL(ref.class_code_8,'' ''),12,'' '') || ';
v_query(28) :='RPAD(NVL(ref.class_code_9,'' ''),12,'' '') || ';
v_query(29) :='RPAD(NVL(ref.class_code_10,'' ''),12,'' '') || ';
v_query(30) :='RPAD(NVL(ref.class_code_11,'' ''),12,'' '') || ';
v_query(31) :='RPAD(NVL(ref.class_code_12,'' ''),12,'' '') || ';
v_query(32) :='RPAD(NVL(ref.class_code_13,'' ''),12,'' '') || ';
v_query(33) :='RPAD(NVL(ref.class_code_14,'' ''),12,'' '') || ';
v_query(34) :='RPAD(NVL(ref.class_code_15,'' ''),12,'' '') || ';
v_query(35) :='RPAD(NVL(ref.prod_flag_1,'' ''),12,'' '') || ';
v_query(36) :='RPAD(NVL(ref.prod_flag_2,'' ''),12,'' '') || ';
v_query(37) :='RPAD(NVL(ref.prod_flag_3,'' ''),12,'' '') || ';
v_query(38) :='RPAD(NVL(ref.prod_flag_4,'' ''),12,'' '') || ';
v_query(39) :='RPAD(NVL(ref.prod_flag_5,'' ''),12,'' '') || ';
v_query(40) :='RPAD(NVL(ref.prod_flag_6,'' ''),12,'' '') || ';
v_query(41) :='RPAD(NVL(ref.prod_flag_7,'' ''),12,'' '') || ';
v_query(42) :='RPAD(NVL(ref.prod_flag_8,'' ''),12,'' '') || ';
v_query(43) :='RPAD(NVL(ref.prod_flag_9,'' ''),12,'' '') || ';
v_query(44) :='RPAD(NVL(ref.prod_flag_10,'' ''),12,'' '') || ';
v_query(45) :='RPAD(NVL(ref.prod_flag_11,'' ''),12,'' '') || ';
v_query(46) :='RPAD(NVL(ref.prod_flag_12,'' ''),12,'' '') || ';
v_query(47) :='RPAD(NVL(ref.prod_flag_13,'' ''),12,'' '') || ';
v_query(48) :='RPAD(NVL(ref.prod_flag_14,'' ''),12,'' '') || ';
v_query(49) :='RPAD(NVL(ref.prod_flag_15,'' ''),12,'' '') || ';
v_query(50) :='RPAD(NVL(ref.prod_flag_16,'' ''),12,'' '') || ';
v_query(51) :='RPAD(NVL(ref.prod_flag_17,'' ''),12,'' '') || ';
v_query(52) :='RPAD(NVL(ref.prod_flag_18,'' ''),12,'' '') || ';
v_query(53) :='RPAD(NVL(ref.prod_flag_19,'' ''),12,'' '') || ';
v_query(54) :='RPAD(NVL(ref.prod_flag_20,'' ''),12,'' '') || ';
v_query(55) :='RPAD(NVL(ref.prod_flag_21,'' ''),12,'' '') || ';
v_query(56) :='RPAD(NVL(ref.prod_flag_22,'' ''),12,'' '') || ';
v_query(57) :='RPAD(NVL(ref.prod_flag_23,'' ''),12,'' '') || ';
v_query(58) :='RPAD(NVL(ref.prod_flag_24,'' ''),12,'' '') || ';
v_query(59) :='RPAD(NVL(ref.prod_flag_25,'' ''),12,'' '') || ';
v_query(60) :='RPAD(NVL(ref.pack_flag_1,'' ''),12,'' '') || ';
v_query(61) :='RPAD(NVL(ref.pack_flag_2,'' ''),12,'' '') || ';
v_query(62) :='RPAD(NVL(ref.pack_flag_3,'' ''),12,'' '') || ';
v_query(63) :='RPAD(NVL(ref.pack_flag_4,'' ''),12,'' '') || ';
v_query(64) :='RPAD(NVL(ref.pack_flag_5,'' ''),12,'' '') || ';
v_query(65) :='RPAD(NVL(ref.pack_flag_6,'' ''),12,'' '') || ';
v_query(66) :='RPAD(NVL(ref.pack_flag_7,'' ''),12,'' '') || ';
v_query(67) :='RPAD(NVL(ref.pack_flag_8,'' ''),12,'' '') || ';
v_query(68) :='RPAD(NVL(ref.pack_flag_9,'' ''),12,'' '') || ';
v_query(69) :='RPAD(NVL(ref.pack_flag_10,'' ''),12,'' '') || ';
v_query(70) :='RPAD(NVL(ref.pack_flag_11,'' ''),12,'' '') || ';
v_query(71) :='RPAD(NVL(ref.pack_flag_12,'' ''),12,'' '') || ';
v_query(72) :='RPAD(NVL(ref.pack_flag_13,'' ''),12,'' '') || ';
v_query(73) :='RPAD(NVL(ref.pack_flag_14,'' ''),12,'' '') || ';
v_query(74) :='RPAD(NVL(ref.pack_flag_15,'' ''),12,'' '') || ';
v_query(75) :='RPAD(NVL(ref.pack_flag_16,'' ''),12,'' '') || ';
v_query(76) :='RPAD(NVL(ref.pack_flag_17,'' ''),12,'' '') || ';
v_query(77) :='RPAD(NVL(ref.pack_flag_18,'' ''),12,'' '') || ';
v_query(78) :='RPAD(NVL(ref.pack_flag_19,'' ''),12,'' '') || ';
v_query(79) :='RPAD(NVL(ref.pack_flag_20,'' ''),12,'' '') || ';
v_query(80) :='RPAD(NVL(ref.pack_flag_21,'' ''),12,'' '') || ';
v_query(81) :='RPAD(NVL(ref.pack_flag_22,'' ''),12,'' '') || ';
v_query(82) :='RPAD(NVL(ref.pack_flag_23,'' ''),12,'' '') || ';
v_query(83) :='RPAD(NVL(ref.pack_flag_24,'' ''),12,'' '') || ';
v_query(84) :='RPAD(NVL(ref.pack_flag_25,'' ''),12,'' '') || ';
v_query(85) :='nvl(lpad(NVL(ref.pack_price_1,''0''),13,''0''),''0000000000000'') || ';
v_query(86) :='nvl(ref.effective_date_1,''00000000'') || ';
v_query(87) :='nvl(lpad(NVL(ref.pack_price_2,''0''),13,''0''),''0000000000000'') || ';
v_query(88) :='nvl(ref.effective_date_2,''00000000'') || ';
v_query(89) :='nvl(lpad(NVL(ref.pack_price_3,''0''),13,''0''),''0000000000000'') || ';
v_query(90) :='nvl(ref.effective_date_3,''00000000'') || ';
v_query(91) :='nvl(lpad(NVL(ref.pack_price_4,''0''),13,''0''),''0000000000000'') || ';
v_query(92) :='nvl(ref.effective_date_4,''00000000'') || ';
v_query(93) :='nvl(lpad(NVL(ref.pack_price_5,''0''),13,''0''),''0000000000000'') || ';
v_query(94) :='nvl(ref.effective_date_5,''00000000'') || ';
v_query(95) :='nvl(lpad(NVL(ref.pack_price_6,''0''),13,''0''),''0000000000000'') || ';
v_query(96) :='nvl(ref.effective_date_6,''00000000'') || ';
v_query(97) :='nvl(lpad(NVL(ref.pack_price_7,''0''),13,''0''),''0000000000000'') || ';
v_query(98) :='nvl(ref.effective_date_7,''00000000'') || ';
v_query(99) :='nvl(lpad(NVL(ref.pack_price_8,''0''),13,''0''),''0000000000000'') || ';
v_query(100) :='nvl(ref.effective_date_8,''00000000'') || ';
v_query(101) :='nvl(lpad(NVL(ref.pack_price_9,''0''),13,''0''),''0000000000000'') || ';
v_query(102) :='nvl(ref.effective_date_9,''00000000'') || ';
v_query(103) :='nvl(lpad(NVL(ref.pack_price_10,''0''),13,''0''),''0000000000000'') || ';
v_query(104) :='nvl(ref.effective_date_10,''00000000'') || ';
v_query(105) :='lpad(nvl(NDF_PAV(p.market_abbr,p.fixed_cont_code,''PEF'',''' || GV_PERIOD_START_DATE ||'''),''0''),15,''0'') || ';
v_query(106) :='RPAD(''0'',15,''0'') || ';
v_query(107) :='lpad(nvl(NDF_PAV(p.market_abbr,p.fixed_cont_code,''QLIM'',''' || GV_PERIOD_START_DATE ||'''),''0''),15,''0'') || ';
v_query(108) :='NVL(TO_CHAR(PRD.PROD_LAUNCH_DATE,''YYYYMMDD''),''00000000'') || ';
v_query(109) :='NVL(TO_CHAR(PCKD.PACK_LAUNCH_DATE,''YYYYMMDD''),''00000000'') || ';
v_query(110) :='NVL(TO_CHAR(PACK_OUT_OF_TRADE_DATE,''YYYYMMDD''),''00000000'') || ';
v_query(111) :='nvl(ref.study_connection_1,'' '') || ';
v_query(112) :='nvl(ref.study_connection_2,'' '') || ';
v_query(113) :='nvl(ref.study_connection_3,'' '') || ';
v_query(114) :='nvl(ref.study_connection_4,'' '') || ';
v_query(115) :='nvl(ref.study_connection_5,'' '') || ';
v_query(116) :='nvl(ref.study_connection_6,'' '') || ';
v_query(117) :='nvl(ref.study_connection_7,'' '') || ';
v_query(118) :='nvl(ref.study_connection_8,'' '') || ';
v_query(119) :='nvl(ref.study_connection_9,'' '') || ';
v_query(120) :='nvl(ref.study_connection_10,'' '') || ';
v_query(121) :='nvl(ref.study_connection_11,'' '') || ';
v_query(122) :='nvl(ref.study_connection_12,'' '') || ';
v_query(123) :='nvl(ref.study_connection_13,'' '') || ';
v_query(124) :='nvl(ref.study_connection_14,'' '') || ';
v_query(125) :='nvl(ref.study_connection_15,'' '') || ';
v_query(126) :='nvl(ref.study_connection_16,'' '') || ';
v_query(127) :='nvl(ref.study_connection_17,'' '') || ';
v_query(128) :='nvl(ref.study_connection_18,'' '') || ';
v_query(129) :='nvl(ref.study_connection_19,'' '') || ';
v_query(130) :='nvl(ref.study_connection_20,'' '') || ';
v_query(131) :='nvl(rpad(ndf_pmcc(p.market_abbr,p.fixed_cont_code,''' || v_market_abbr ||'FD'',''' || GV_PERIOD_START_DATE ||'''),20,'' ''),lpad('' '',20,'' '')) || ';
v_query(132) :='LPAD(NVL(PACK_SIZE,''0''),8,''0'') || ';
v_query(133) :='''00000'' || ';
v_query(134) :='rpad(NVL(decode(rpad(nvl(p.weight_unit_absolute_strength,''0''),2,''0''),''00'',''0000'',lpad(nvl(to_char(p.absolute_strength_measure),''0''),4,''0'')),''0000''),4,'' '') || ';
v_query(135) :=' '' '' || rpad(nvl(p.weight_unit_absolute_strength,'' ''),2,'' '') || ';
v_query(136) :='decode(rpad(nvl(p.weight_unit_absolute_strength,''0''),2,''0''),''00'',''00000000'',lpad(nvl(to_char(p.absolute_strength_measure*1000),''0''),8,''0'')) || ';
v_query(137) :='RPad(nvl(p.weight_unit_absolute_strength,'' ''),5,'' '') || ';
v_query(138) :='decode(rpad(nvl(p.weight_unit_relative_strength,''0''),2,''0''),''00'',''00000000'',lpad(nvl(to_char(p.relative_strength_measure*1000),''0''),8,''0'')) || ';
v_query(139) :='RPad(nvl(p.weight_unit_relative_strength,'' ''),5,'' '') || ';
v_query(140) :='''00000000'' || ';
v_query(141) :=''' '' || ';
v_query(142) :='''00000000'' || ';
v_query(143) :=''' '' || ';
v_query(144) :='decode(pack_weight_measure,null,''00000000'',lpad(pack_weight_measure*1000,8,''0'')) || ';
v_query(145) :='rpad(nvl(p.weight_unit_pack,'' ''),5,'' '') || ';
v_query(146) := 'rpad(nvl( ' ;
v_query(147) := 'ndf_pmcc(p.market_abbr, ' ;
v_query(148) := ' p.fixed_cont_code, ' ;
v_query(149) := '''ADDST'', ''' ;
v_query(150) := GV_PERIOD_START_DATE ||'''), '' '')' ;
v_query(151) := ' ,12,'' '' ) || ' ;
v_query(152) :='rpad(nvl(p.ndf_pack_add_info,'' ''),8,'' '') || ';
v_query(153) :='nvl(decode(p.pin_elh_status,''ACTIVE'','' '',''Z'' ),'' '') || lpad('' '',100,'' '') ';
v_query(154) :='from pack p , PROD_NAME prn, PROD_DATE PRD, PACK_DATE PCKD, prod,';
v_query(155) :=' lpin_cpi_20_ref_data ref ';
v_query(156) :=' where ';
v_query(157) :=' p.market_abbr = ''' || v_market_abbr ||''' and ';
v_query(158) :=' p.market_abbr = ref.market_abbr and';
v_query(159) :=' p.fixed_cont_code = ref.fixed_cont_code and';
v_query(160) :=' p.PIN_ELH_STATUS <> ''RESEARCH'' and';
v_query(161) :=' prod.prod_code = p.prod_code and';
v_query(162) :=' prod.market_abbr = p.market_abbr and';
v_query(163) :=' prod.PIN_ELH_STATUS <> ''RESEARCH'' and';
v_query(164) :=' NDF_confmkt2 (p.market_abbr,p.fixed_cont_code,''' || GV_PERIOD_START_DATE ||''')>0 and ';
v_query(165) :=' p.market_abbr = prn.market_abbr and';
v_query(166) :=' p.prod_code = prn.prod_code and';
v_query(167) :=' p.market_abbr = PRD.market_abbr and';
v_query(168) :=' p.prod_code = PRD.prod_code AND';
v_query(169) :=' p.market_abbr = PCKD.market_abbr and';
v_query(170) :=' p.FIXED_CONT_CODE = PCKD.FIXED_CONT_CODE';
v_query(171) :=' AND row_current_check (prd.valid_from_date, prd.valid_to_date, ''' || GV_PERIOD_START_DATE ||''') = 0';
v_query(172) :=' AND row_current_check (prn.valid_from_date, prn.valid_to_date, ''' || GV_PERIOD_START_DATE ||''') = 0';
v_query(173) :=' AND row_current_check (pckd.valid_from_date, pckd.valid_to_date, ''' || GV_PERIOD_START_DATE ||''') = 0';
v_query(174) :=' order by p.fixed_cont_code ';

DBMS_SQL.PARSE (v_cursor_handle, v_query,1,174,TRUE,dbms_sql.native);
gv_total_packs :=dbms_sql.execute(v_cursor_handle);


DBMS_SQL.CLOSE_CURSOR (v_cursor_handle);
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 1 2013
Added on Mar 1 2013
#pl-sql, #sql
3 comments
151 views