3 Replies Latest reply: Mar 4, 2013 3:53 AM by BluShadow RSS

    DBMS_SQL.PARSE

    Augustine Vinish
      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);
        • 1. Re: DBMS_SQL.PARSE
          rp0428
          >
          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 ?
          >
          Run the SELECT part of the query manually.

          Remove all of the unnecessary columns from the SELECT so you can focus on the part of the query giving the problem.

          Why do you have an ORDER BY clause on a SELECT query that is being used for an INSERT?
          • 2. Re: DBMS_SQL.PARSE
            damorgan
            I will not even begin to try to help you ... here's why.

            1. You did not read the FAQ and learn the proper way to post to the forums using
             tags for listings.
            
            2. I have on idea what your version number is.
            
            3. I'm not going to read a kilometer of code to try to find an issue ... create a simple test case that recreates the issue.
            
            4. You didn't think the error important enough to post so I've no idea what is actually going on.
            
            Please fix this thread and likely I or someone else will be able to help you.
            
            But why are you using DBMS_SQL and not native dynamic SQL? That is really my first question.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
            • 3. Re: DBMS_SQL.PARSE
              BluShadow
              Augustine Vinish wrote:
              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 ?
              There is nothing in the code you've posted that would directly cause that error. Therefore, it would suggest that the issue is in one of the functions that is being called in the query, but as we don't have your code, we cannot help.

              You've been a member here long enough that you should know how to ask a question by now.

              Read this: {message:id=9360002}