Hi Guys,
I am using oracle 10g.
I wrote one cursor ,
from cursor i want to retrieve the specified column values and want to insert into my temporray table that is test_temp.
After inserting into test_temp , i want to generate xml file.
i am getting syntatical errors.
can you please suggest where i made mistake.
ORA-06550: line 83, column 3:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 55, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 89, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement
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,
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,
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_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';
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_OF_NON_GOODS,
AMOUNTS_OF_ADVANCE_PAY,
AMOUNT_OF_DISCOUNTS,
ON_HOLD_FLAG)
Values(Rec_st.BU_CODE,
BU_TYPE,
CUST_NO,
CUR_CODE,
SALES_DATE,
RECEIPT_NO,
TILL_NO,
CARD_NO,
Rec_st.INVOICE_TOTAL,
AMOUNT_OF_GOODS,
AMOUNT_OF_NON_GOODS,
AMOUNTS_OF_ADVANCE_PAY,
AMOUNT_OF_DISCOUNTS,
ON_HOLD_FLAG);
---End loop;
dbms_output.put_line('v_LARGE----->'||v_large);
/*dbms_output.put_line('v_factor->'||v_factor);*/
/*dbms_output.put_line('rec_st.sto_no->'||rec_st.sto_no);*/
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;
END LOOP;
END;