1 2 Previous Next 21 Replies Latest reply on May 15, 2013 4:14 AM by sb92075

    PL/SQL: ORA-00984: column not allowed here

    894936
      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;
        • 1. Re: PL/SQL: ORA-00984: column not allowed here
          Your code is unreadable.

          Please edit your thread and add \
           on the line before and on the line after the code to preserve formatting.
          
          You also need to tell us which lines of code those line numbers belong to.
          {quote}
          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
          {quote}
          That last error is because in PL/SQL you can't just do a SELECT you have to provide somewhere for Oracle to put the data.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
          • 2. Re: PL/SQL: ORA-00984: column not allowed here
            894936
            Hi i copied whole error
            "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"

            line 83 " ON_HOLD_FLAG);
            ---End loop;"

            line 55 "insert into test_temp("

            line 89 "select xmlelement("tendermanagement","

            description is :
            CREATE GLOBAL TEMPORARY TABLE RIMS.TEST_TEMP ( BU_CODE VARCHAR2(5 CHAR), BU_TYPE VARCHAR2(3 CHAR), CUST_NO VARCHAR2(7 BYTE), CUR_CODE VARCHAR2(3 BYTE), SALES_DATE DATE, RECEIPT_NO VARCHAR2(10 BYTE), TILL_NO VARCHAR2(5 BYTE), CARD_NO VARCHAR2(2 BYTE), INVOICE_TOTAL NUMBER(11,2), AMOUNT_OF_GOODS NUMBER(11,2), AMOUNT_NON_GOODS NUMBER(11,2), AMOUNT_ADVANCE_PAY NUMBER(11,2), AMOUNT_DISCOUNTS NUMBER(11,2), ON_HOLD_FLAG VARCHAR2(3 BYTE) ) ON COMMIT PRESERVE ROWS NOCACHE;
            Edited by: 891933 on May 6, 2013 9:27 PM
            • 3. Re: PL/SQL: ORA-00984: column not allowed here
              Sarah_7
              Also, post table desc for test_temp
              • 4. Re: PL/SQL: ORA-00984: column not allowed here
                894936
                CREATE GLOBAL TEMPORARY TABLE RIMS.TEST_TEMP ( BU_CODE VARCHAR2(5 CHAR), BU_TYPE VARCHAR2(3 CHAR), CUST_NO VARCHAR2(7 BYTE), CUR_CODE VARCHAR2(3 BYTE), SALES_DATE DATE, RECEIPT_NO VARCHAR2(10 BYTE), TILL_NO VARCHAR2(5 BYTE), CARD_NO VARCHAR2(2 BYTE), INVOICE_TOTAL NUMBER(11,2), AMOUNT_OF_GOODS NUMBER(11,2), AMOUNT_NON_GOODS NUMBER(11,2), AMOUNT_ADVANCE_PAY NUMBER(11,2), AMOUNT_DISCOUNTS NUMBER(11,2), ON_HOLD_FLAG VARCHAR2(3 BYTE) ) ON COMMIT PRESERVE ROWS NOCACHE;
                • 5. Re: PL/SQL: ORA-00984: column not allowed here
                  Tubby
                  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);
                  You're listing things based on your cursor like rec_st.bu_code ... but where do you think values for things like ON_HOLD_FLAG are coming from ? You haven't declared a variable with that name anywhere in your plsql block and you aren't getting it from the cursor or else it would have a prefix of rec_st.

                  Cheers,
                  • 6. Re: PL/SQL: ORA-00984: column not allowed here
                    Sarah_7
                    Hi,

                    In your insert statement, you are inserting the column names as it is after the 'Values' clause.
                    Instead, use rec_st.<alias_you_gave_in_cursor>.
                    Hope this helps

                    Regards
                    Sarah
                    • 7. Re: PL/SQL: ORA-00984: column not allowed here
                      894936
                      Hi ,

                      the value for ON_HOLD_FLAG should be allways 'N'

                      in cursor select we can see "CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_DISCOUNTS,
                      'N' AS ON_HOLD_FLAG"

                      so, where i need to make changes.
                      • 8. Re: PL/SQL: ORA-00984: column not allowed here
                        Sarah_7
                        For all the names that you have listed after the values clause, use rec_st.<alias_name>
                        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,
                        Rec_st.BU_TYPE,
                        Rec_st.CUST_NO,
                        Rec_stCUR_CODE,
                        .....
                        ...
                        ..
                        so on);
                        Regards
                        Sarah
                        • 9. Re: PL/SQL: ORA-00984: column not allowed here
                          Tubby
                          891933 wrote:
                          Hi ,

                          the value for ON_HOLD_FLAG should be allways 'N'

                          in cursor select we can see "CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_DISCOUNTS,
                          'N' AS ON_HOLD_FLAG"

                          so, where i need to make changes.
                          If the value should always be 'N' then put that in your insert statement. You'd have to do that with all the columns in the VALUES clause that aren't currently referencing the cursor.

                          Anywhere you have constant values there's no point selecting them in the cursor.

                          Cheers,
                          • 10. Re: PL/SQL: ORA-00984: column not allowed here
                            894936
                            ORA-06550: line 89, column 1:
                            PLS-00428: an INTO clause is expected in this SELECT statement
                            in my select statment i am using this
                            " CAST (NULL AS NUMBER (11, 2)) AS AMOUNT_DISCOUNTS,
                            'N' AS ON_HOLD_FLAG"

                            columns names were not mentioned properli in insert into test_temp.
                            so i corrected them.
                            now i am getting the above error.
                            can u suggest me on this.
                            • 11. Re: PL/SQL: ORA-00984: column not allowed here
                              Sarah_7
                              No SQL in pl/sql without catching the values. You got to store those selected values in some variables or so.
                              Rectify.


                              Regards
                              Sarah

                              Edited by: Sarah_7 on May 7, 2013 11:37 AM
                              • 12. Re: PL/SQL: ORA-00984: column not allowed here
                                894936
                                Hi,

                                i made necessary changes to my plsql block.
                                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.
                                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;
                                • 13. Re: PL/SQL: ORA-00984: column not allowed here
                                  Sarah_7
                                  Well, i have never worked with XML types.
                                  Guess you need to call some built-in packaged functions/procs.
                                  You can explore [http://www.akadia.com/services/ora_gen_xml.html]


                                  Hope this helps.

                                  Regards
                                  Sarah
                                  • 14. Re: PL/SQL: ORA-00984: column not allowed here
                                    894936
                                    Hi Guys,
                                    plese do help me.
                                    Thanks,
                                    MR

                                    Edited by: 891933 on 07-May-2013 07:44
                                    1 2 Previous Next