1 2 Previous Next 21 Replies Latest reply: May 8, 2013 9:43 AM by Jason_(A_Non) Go to original post RSS
      • 15. Re: XML in plsql
        Jason_(A_Non)
        In the sample INSERT statements you provided, there are two BU_TYPE codes (221 and 421). You only happen to have one BU_CODE. In the SELECT statement I started for you and you expanded, it returns one tendermanagement node, aka the root node, and one tendermovement node per each grouping that it found. The error you are getting is because Oracle found multiple values for BU_TYPE code, yet has no clue how to map multiple rows into a single node. You could add a MAX() around those two attribute values, but I'm pretty sure that is not what you want.

        In order to get good answers from the forums, you need to provide good data. In this case, you need to provide input, a sample that represents the data in the table, and output, what you want the XML to look like. SQL is the means to transform input to output and without knowing what both sides look like, it is hard to figure out how to provide the correct SQL to achieve what you need, especially since we only know what you tell us.
        • 16. Re: XML in plsql
          894936
          Hi
          Thank you for the reply.
          i have already provided sample table structure and table data.
          here is table structure .

          1>>Table :
          CREATE TABLE RIMS.I_0470002_LOG_T ( REG_DATE VARCHAR2(6 BYTE), STO_NO VARCHAR2(3 BYTE), SALES_DATE VARCHAR2(6 BYTE), CASH_NO NUMBER(5), RECEIPT_NO NUMBER(10), ORDER_NO NUMBER(10), TOT_CUST_NO VARCHAR2(13 BYTE), CONTROL_NO NUMBER(10), ART_NO VARCHAR2(8 BYTE), SOLD_QTY NUMBER(10), SOLD_AMOUNT NUMBER(10), INV_ADM_COST_FR VARCHAR2(1 BYTE), VAT_OBLIGED VARCHAR2(1 BYTE), CUR_CODE VARCHAR2(3 BYTE), VAT_RATE VARCHAR2(6 BYTE), VAT_CODE VARCHAR2(10 BYTE), BU_CODE VARCHAR2(15 BYTE), BU_TYPE VARCHAR2(9 BYTE), TIMESTAMP DATE, INS_USER VARCHAR2(30 BYTE), MACHINE VARCHAR2(64 BYTE) )

          2>> sample data
          SET DEFINE OFF
          Insert into RIMS.I_0470002_LOG_T
          (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
          Values
          ('081208', '221', '081208', 8, 92,
          0, ' 1753136033', 196, '40089477', 4,
          24000, '0', '1', 'EUR', '001900',
          'STO', '0', '221', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS',
          'hummer');
          Insert into RIMS.I_0470002_LOG_T
          (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
          Values
          ('081208', '421', '081208', 7, 147,
          0, ' 2064491042', 0, '50122339', 2,
          5998, '0', '1', 'EUR', '001900',
          'STO', '0', '421', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS',
          'hummer');
          Insert into RIMS.I_0470002_LOG_T
          (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
          Values
          ('081208', '421', '081208', 7, 147,
          0, ' 2064491042', 0, '50131386', 2,
          5998, '0', '1', 'EUR', '001900',
          'STO', '0', '421', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS',
          'hummer');
          Insert into RIMS.I_0470002_LOG_T
          (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
          Values
          ('081208', '221', '081208', 8, 92,
          0, ' 1753136033', 196, '40125550', 5,
          7950, '0', '1', 'EUR', '001900',
          'STO', '0', '221', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS',
          'hummer');
          Insert into RIMS.I_0470002_LOG_T
          (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
          Values
          ('081208', '421', '081208', 7, 147,
          0, ' 2064491042', 0, '60064211', 1,
          259, '0', '1', 'EUR', '001900',
          'STO', '0', '421', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS',
          'hummer');
          Insert into RIMS.I_0470002_LOG_T
          (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
          Values
          ('081208', '221', '081208', 8, 92,
          0, ' 1753136033', 196, '40129666', 5,
          2495, '0', '1', 'EUR', '001900',
          'STO', '0', '221', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS',
          'hummer');
          Insert into RIMS.I_0470002_LOG_T
          (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
          Values
          ('081208', '421', '081208', 7, 147,
          0, ' 2064491042', 0, '60140762', 1,
          5500, '0', '1', 'EUR', '001900',
          'STO', '0', '421', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS',
          'hummer');
          Insert into RIMS.I_0470002_LOG_T
          (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
          Values
          ('081208', '221', '081208', 8, 92,
          0, ' 1753136033', 196, '40130170', 6,
          1794, '0', '1', 'EUR', '001900',
          'STO', '0', '221', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS',
          'hummer');
          Insert into RIMS.I_0470002_LOG_T
          (REG_DATE, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, TOT_CUST_NO, CONTROL_NO, ART_NO, SOLD_QTY, SOLD_AMOUNT, INV_ADM_COST_FR, VAT_OBLIGED, CUR_CODE, VAT_RATE, VAT_CODE, BU_CODE, BU_TYPE, TIMESTAMP, INS_USER, MACHINE)
          Values
          ('081208', '421', '081208', 7, 147,
          0, ' 2064491042', 0, '70000087', 1,
          0, '0', '1', 'EUR', '001900',
          'STO', '0', '421', TO_DATE('12/09/2008 09:18:38', 'MM/DD/YYYY HH24:MI:SS'), 'IDRS',
          'hummer');
          COMMIT;

          3>> plsql block
          I wrote one plsql block in which i want to generate xml file.
          declare 
           CURSOR Cur_st
             IS
          SELECT DISTINCT
                      CAST (A.STO_NO AS VARCHAR2 (5 CHAR)) AS BU_CODE,
                      CAST ('STO' AS VARCHAR2 (3 CHAR)) AS BU_TYPE,
                      CAST (NULL AS VARCHAR2 (7 BYTE)) AS CUST_NO,
                      CAST (A.CUR_CODE AS VARCHAR2 (3 BYTE)) AS CUR_CODE,
                      TO_DATE (A.SALES_DATE, 'YYMMDD') AS SALES_DATE,
                      CAST (A.RECEIPT_NO AS VARCHAR2 (10 BYTE)) AS RECEIPT_N,
                      CAST (A.CASH_NO AS VARCHAR2 (5 BYTE)) AS TILL_NO,
                      CAST (NULL AS VARCHAR2 (2 BYTE)) AS CARD_NO,
                      sum(sold_amount) AS INVOICE_TOTAL,
                      CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_ADVANCE_PAY,
                      CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_OF_GOODS,
                      CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_NON_GOODS,
                      CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_DISCOUNTS,
                      'N' AS ON_HOLD_FLAG
                 FROM I_0470002_log_t A
             WHERE A.SOLD_AMOUNT != 0
             and rownum < 10
             GROUP BY A.STO_NO,
                      A.CUR_CODE,
                      A.RECEIPT_NO,
                      A.CASH_NO,
                      A.SALES_DATE;
             v_comp_code   VARCHAR2 (10);
             v_sum         NUMBER;
             v_factor      NUMBER;
             v_sto_no      NUMBER;
             v_large       LONG;
          BEGIN
             FOR Rec_st IN Cur_st
             LOOP
          
                batch_01_pck.setcompfromstore_prc (pi_str_stono => Rec_st.BU_CODE);
                /*dbms_output.put_line('Rec_st.BU_CODE-->'||Rec_st.BU_CODE);*/
                /*dbms_output.put_line('Rec_st.INVOICE_TOTAL-->'||Rec_st.INVOICE_TOTAL);*/
          
                v_comp_code := glob_01_pck.getcompcode_fct;
                /*dbms_output.put_line('v_comp_code-->'||v_comp_code);*/
          
                v_factor := curr_02_pck.corrfact_fct (v_comp_code, 'I0470001');
                /*dbms_output.put_line('v_factor-->'||v_factor); */
          
                v_sum := rec_st.INVOICE_TOTAL* v_factor;
                dbms_output.put_line('v_sum------>'||v_sum);
          
               v_large :=
          'select sum(sold_amount)* '
          || v_factor
          || ' as INVOICE_TOTAL ,sto_no as bu_code,''STO'' as bu_type,null as cust_no,cur_code,RECEIPT_NO,TO_DATE (SALES_DATE,''YYMMDD'')as sales_date,
          cash_no as till_no,null as card_no,null as amount_advance_pay,null as amount_of_goods,null as amount_non_goods, null as amount_discounts,''N''as on_hold_flag from I_0470002 where BU_CODE ='
          || rec_st.BU_CODE
          || ' group by sto_no,cur_code,receipt_no,cash_no,sales_date,sold_amount';
          
          dbms_output.put_line('v_LARGE----->'||v_large);
             END LOOP;
          END P1;
          4>>Present i am getting the below output:
          step2-->sto_no = '227'
          step7-->sto_no = '227'
          Start
          Fetched factors: 1 and .01
          v_sum------>477
          v_LARGE----->select sum(sold_amount)* .01 as INVOICE_TOTAL ,sto_no as bu_code,'STO' as bu_type,null as cust_no,cur_code,RECEIPT_NO,TO_DATE (SALES_DATE,'YYMMDD')as sales_date,
          cash_no as till_no,null as card_no,null as amount_advance_pay,null as amount_of_goods,null as amount_non_goods, null as amount_discounts,'N'as on_hold_flag from I_0470002 where BU_CODE =227 group by sto_no,cur_code,receipt_no,cash_no,sales_date,sold_amount
          step2-->sto_no = '301'
          step7-->sto_no = '301'
          Start
          Fetched factors: 1 and .01
          v_sum------>2.7
          v_LARGE----->select sum(sold_amount)* .01 as INVOICE_TOTAL ,sto_no as bu_code,'STO' as bu_type,null as cust_no,cur_code,RECEIPT_NO,TO_DATE (SALES_DATE,'YYMMDD')as sales_date,
          cash_no as till_no,null as card_no,null as amount_advance_pay,null as amount_of_goods,null as amount_non_goods, null as amount_discounts,'N'as on_hold_flag from I_0470002 where BU_CODE =301 group by sto_no,cur_code,receipt_no,cash_no,sales_date,sold_amount
          step2-->sto_no = '227'

          5>> required output

          I want the ourput like below:

          <?xml version="1.0" encoding="UTF-8" ?>
          - <tendermanagement xmlns="http://www.ikea.com/sarec/declaredfunds" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.ikea.com/sarec/declaredfunds declaredFunds.xsd" buType="STO" buCode="312" sourceSystem="RIMS">
          - <!-- RIMS
          -->
          <tendermovement date="2012-11-17T01:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="381.72" tillNo="9" transactionNo="104" />
          <tendermovement date="2012-11-17T01:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="147.52" tillNo="5" transactionNo="199" />
          <tendermovement date="2012-11-17T01:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="119.84" tillNo="52" transactionNo="23" />
          <tendermovement date="2012-11-17T01:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="67.77" tillNo="6" transactionNo="63" />
          </tendermanagement>

          6>> for this i generated one sample xml tagging.

          select xmlelement("tendermanagement",
          xmlattributes(
          'http://www.ikea.com/sarec/declaredfunds' as "xmlns"
          , 'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi"
          , 'http://www.prod.com/S_Dest/declaredfunds declaredFunds.xsd' as "xsi:schemaLocation"
          , bu_type as "buType"
          , bu_code as "buCode"
          , 'RIMS' as "sourceSystem"
          )
          , xmlagg(
          xmlelement("tendermovement",
          xmlattributes(
          to_char(to_date(sales_date,'YYMMDD'), 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS "date"
          , 'DROP' as "bookingType"
          , 'B2B Invoice' as "tenderType"
          , 'STORE' as "salesAreaGroup"
          --, SUM(SOLD_AMOUNT) as "valueTendered" ------------------i am getting error.
          , CASH_no as "tillNo"
          , receipt_no as "transactionNo"
          )
          )
          )
          ).extract('/*').getclobval() as CUSTOMER
          from I_0470002_log_t WHERE ROWNUM <10
          group by bu_code, bu_type


          so can you please tell me how i need to generate my xml file as per the 3point (plsqlblock).........
          please do suggest me on this.
          Thanks for your support in advance.

          Edited by: 891933 on 06-May-2013 03:54
          • 17. Re: XML in plsql
            Jason_(A_Non)
            Alright, let me see if I can ask this question in a different way.

            In the sample INSERT statements you provided, there are two BU_CODE values. One is 221 and the other is 421.

            You are mapping BU_CODE to the attribute buCode on the root node of tendermanagement Within an XML document, there is only one root node and you only have one attribute called buCode.

            CURSOR Cur_st returns two rows as well.

            Your sample XML shows
            buCode="312"
            Oracle cannot put two different values (221 and 421) into the one spot for buCode that you specified. That is the root cause of your error.

            Ignoring the fact we have no clue where 312 comes from are you wanting to return
            a) one tendermanagement node regardless of the number of rows returned by CURSOR Cur_st. If so, how do you map the many possible values for bu_code and bu_type into a single attribute for each
            b) one tendermanagement node for each row returned by CURSOR Cur_st

            Also note that your INSERT statements have a sales_date in 2008, yet your expected output has sales dates in 2012. Your sample rows need to be able to generate your expected output, so we do not have to guess about how to get from input to output.
            • 18. Re: XML in plsql
              894936
              Hi ,

              Just i have provided my sample outside to be in the form of 6th step.
              But the output which i got in step 5 should be written in the form of step6.
              coming towards bu_code... its a kind of company code which will have different values.
              so, for each and every bu_code the sales_Date will be difernet.

              In the step6 i provided just a sample xml file.
              • 19. Re: XML in plsql
                Jason_(A_Non)
                In your steps 1 and 2, you create a table and insert 9 rows into it. The relevant data from that table looks like
                SQL> select bu_type, bu_code, sales_date, cash_no, receipt_no, sold_amount
                  2  from  I_0470002_log_t
                  3  order by bu_type;
                 
                BU_TYPE   BU_CODE         SALES_DATE CASH_NO  RECEIPT_NO SOLD_AMOUNT
                --------- --------------- ---------- ------- ----------- -----------
                221       0               081208           8          92        1794
                221       0               081208           8          92        7950
                221       0               081208           8          92        2495
                221       0               081208           8          92       24000
                421       0               081208           7         147        5500
                421       0               081208           7         147           0
                421       0               081208           7         147        5998
                421       0               081208           7         147        5998
                421       0               081208           7         147         259
                 
                9 rows selected
                From step 5, your XML looks like
                <?xml version="1.0" encoding="UTF-8"?>
                <tendermanagement xmlns="http://www.ikea.com/sarec/declaredfunds" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.ikea.com/sarec/declaredfunds declaredFunds.xsd" buType="STO" buCode="312" sourceSystem="RIMS">
                - <!-- RIMS
                -->
                     <tendermovement date="2012-11-17T01:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="381.72" tillNo="9" transactionNo="104"/>
                     <tendermovement date="2012-11-17T01:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="147.52" tillNo="5" transactionNo="199"/>
                     <tendermovement date="2012-11-17T01:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="119.84" tillNo="52" transactionNo="23"/>
                     <tendermovement date="2012-11-17T01:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="67.77" tillNo="6" transactionNo="63"/>
                </tendermanagement>
                It is impossible for those 9 rows in the table to produce the entries in that XML. At this point I am not sure what you are really looking for given you still have not answered this question.

                Are you wanting the XML in step 5 to return
                a) one tendermanagement node regardless of the number of rows in the sample table I_0470002_log_t. If so, how do you map the many possible values for bu_code and bu_type into a single attribute for each
                b) one tendermanagement node for each distinct combination (grouping) of bu_code/bu_type

                Until you provide the requested answers or a sample set of data that flows from input to output, I cannot help you.
                • 20. Re: XML in plsql
                  894936
                  Hi,

                  i made necessary changes to my plsql block. i did some other logic
                  i am able to insert the records into my temporary table that is test_temp.
                  Now i have concern regarding this xml select statment.
                  where i need to add this select statement in my plsql block.
                  from test_temp table i need to prepare the below select xml statment.

                  Here when you observe my plsql block its successfully inserting the records into my temporaty table test_temp.
                  so, now i want to add this select xmlelement in my plsql block to get the xml output.because once this xml file is generated based on the data available in test_temp table.
                  i need to trasnfer this xml file to other application through ftp.
                  before that i want to know how to add this select xml in my plsql code.......just tell me the syntax how to add this.
                  select xmlelement("tendermanagement",
                  xmlattributes(
                  'http://www.ikea.com/sarec/declaredfunds' as "xmlns"
                  , 'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi"
                  , 'http://www.prod.com/S_Dest/declaredfunds declaredFunds.xsd' as "xsi:schemaLocation"
                  , bu_type as "buType"
                  , bu_code as "buCode"
                  , 'RIMS' as "sourceSystem"
                  )
                  , xmlagg(
                  xmlelement("tendermovement",
                  xmlattributes(
                  to_char(to_date(sales_date,'YYMMDD'), 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS "date"
                  , 'DROP' as "bookingType"
                  , 'B2B Invoice' as "tenderType"
                  , 'STORE' as "salesAreaGroup"
                  ,invoice_total as "valueTendered"
                  , till_no as "tillNo"
                  , receipt_no as "transactionNo"
                  )
                  )
                  )
                  ).extract('/*').getclobval() as cutomer_info
                  from test_temp WHERE ROWNUM <10
                  group by bu_code, bu_type,cur_code,receipt_no,till_no;
                  DECLARE
                  CURSOR Cur_st
                  IS
                  SELECT DISTINCT
                  CAST (A.STO_NO AS VARCHAR2 (5 CHAR)) AS BU_CODE,
                  CAST ('STO' AS VARCHAR2 (3 CHAR)) AS BU_TYPE,
                  CAST (NULL AS VARCHAR2 (7 BYTE)) AS CUST_NO,
                  CAST (A.CUR_CODE AS VARCHAR2 (3 BYTE)) AS CUR_CODE,
                  TO_DATE (A.SALES_DATE, 'YYMMDD') AS SALES_DATE,
                  CAST (A.RECEIPT_NO AS VARCHAR2 (10 BYTE)) AS RECEIPT_NO,
                  CAST (A.CASH_NO AS VARCHAR2 (5 BYTE)) AS TILL_NO,
                  CAST (NULL AS VARCHAR2 (2 BYTE)) AS CARD_NO,
                  sold_amount AS INVOICE_TOTAL,
                  CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_ADVANCE_PAY,
                  CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_OF_GOODS,
                  CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_NON_GOODS,
                  CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_DISCOUNTS,
                  'N' AS ON_HOLD_FLAG
                  FROM I_0470002_log_t A
                  WHERE A.SOLD_AMOUNT != 0
                  and rownum < 12
                  GROUP BY A.STO_NO,
                  A.CUR_CODE,
                  A.RECEIPT_NO,
                  A.CASH_NO,
                  A.SALES_DATE,
                  A.SOLD_AMOUNT;
                  v_comp_code VARCHAR2 (10);
                  v_sum NUMBER;
                  v_factor NUMBER;
                  v_sto_no NUMBER;
                  context DBMS_XMLGEN.ctxtype;
                  v_large LONG;
                  l_clob CLOB;
                  BEGIN
                  execute immediate 'truncate table test_temp';
                  FOR Rec_st IN Cur_st
                  LOOP
                  batch_01_pck.setcompfromstore_prc (pi_str_stono => Rec_st.BU_CODE);
                  /*dbms_output.put_line('Rec_st.BU_CODE-->'||Rec_st.BU_CODE);*/
                  /*dbms_output.put_line('Rec_st.INVOICE_TOTAL-->'||Rec_st.INVOICE_TOTAL);*/

                  v_comp_code := glob_01_pck.getcompcode_fct;
                  /*dbms_output.put_line('v_comp_code-->'||v_comp_code);*/

                  v_factor := curr_02_pck.corrfact_fct (v_comp_code, 'I0470001');
                  /*dbms_output.put_line('v_factor-->'||v_factor); */

                  v_sum := rec_st.INVOICE_TOTAL* v_factor;
                  dbms_output.put_line('v_sum------>'||v_sum);
                  v_large :=
                  'select sum(sold_amount)* '
                  || v_factor
                  || ' as INVOICE_TOTAL ,sto_no as bu_code,''STO'' as bu_type,null as cust_no,cur_code,RECEIPT_NO,TO_DATE (SALES_DATE,''YYMMDD'')as sales_date,
                  cash_no as till_no,null as card_no,null as amount_advance_pay,null as amount_of_goods,null as amount_non_goods, null as amount_discounts,''N''as on_hold_flag from I_0470002_log_t where BU_CODE ='
                  || rec_st.BU_CODE
                  || ' group by sto_no,cur_code,receipt_no,cash_no,sales_date';--,sold_amount';
                  insert into test_temp(
                  BU_CODE,
                  BU_TYPE,
                  CUST_NO,
                  CUR_CODE,
                  SALES_DATE,
                  RECEIPT_NO,
                  TILL_NO,
                  CARD_NO,
                  INVOICE_TOTAL,
                  AMOUNT_OF_GOODS,
                  AMOUNT_NON_GOODS,
                  AMOUNT_ADVANCE_PAY,
                  AMOUNT_DISCOUNTS,
                  ON_HOLD_FLAG)
                  Values(Rec_st.BU_CODE,
                  Rec_st.BU_TYPE,
                  Rec_st.CUST_NO,
                  Rec_st.CUR_CODE,
                  Rec_st.SALES_DATE,
                  Rec_st.RECEIPT_NO,
                  Rec_st.TILL_NO,
                  Rec_st.CARD_NO,
                  -- rec_st.INVOICE_TOTAL,
                  rec_st.INVOICE_TOTAL* v_factor,
                  Rec_st.AMOUNT_OF_GOODS,
                  Rec_st.AMOUNT_NON_GOODS,
                  Rec_st.AMOUNT_ADVANCE_PAY,
                  Rec_st.AMOUNT_DISCOUNTS,
                  Rec_st.ON_HOLD_FLAG);
                  ---End loop;
                  END LOOP;
                  END;
                  • 21. Re: XML in plsql
                    Jason_(A_Non)
                    before that i want to know how to add this select xml in my plsql code.......just tell me the syntax how to add this.
                    I showed you how to have a SELECT statement within PL/SQL code over in
                    {message:id=11004918}
                    A SELECT statement is a SELECT statement. The fact that one returns a number and the other returns a CLOB makes no difference to the compiler.
                    1 2 Previous Next