Home » Non-English Forums » German » PLS-00364: loop index variable 'REC' use is invalid
PLS-00364: loop index variable 'REC' use is invalid [message #666868] Wed, 29 November 2017 14:04 Go to next message
Shylina
Messages: 1
Registered: November 2017
Junior Member
hi, ich brauche Hilfe um PLS-00364 umzugehen.

CREATE OR REPLACE FUNCTION CMP_RATE (P_START IN DATE DEFAULT TRUNC(SYSDATE-1), P_END IN DATE DEFAULT TRUNC(SYSDATE-1))
 
  RETURN cmp_rate_ct PIPELINED IS
 ---return type cmp_rate_ct as table of cmp_rate_t? ---
 ----aenderung  result_rec cmp_rate_ct := cmp_rate_t--war falsch!--

--declare rec?---
 --- declare rec;
  result_rec cmp_rate_t := cmp_rate_t (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
   
  BEGIN


                                  FOR rec IN (WITH masterdata AS (SELECT DISTINCT c.facility,
                                                             c.work_center,
                                                             c.operation,
                                                             m.route,
                                                             m.sps_number,
                                                             m.sps_id,
                                                             m.sps_sequence_number,
                                                             CASE WHEN m.sps_sequence_number = '3' THEN 'PROD'
                                                                                      ELSE 'REWORK'
                                                                                 END AS PROD_CATEGORY
                                                                                 ---Fehler view does not exist--auf dwh_admin.* geändert!--
                                                      FROM dwh_admin.dwh_plan_workcenter_oper c JOIN dwh_admin.dwh_route_operation_sps m ON c.facility = m.facility
                                                                                                                       AND c.operation = m.operation
                                                      WHERE c.datasource = 'Workbench200'
                                                      AND c.work_center IN ('CMP012','CMP064_HIT', 'CMP064_POLY', 'CMP013','CMP064_PSG/', 'CMP017', 'CMPP64_BPSG', 'CMPP64_POLY', 'CMPP64_W')
                                                      AND m.sps_sequence_number IN ('3', '7')),
                                       moves AS (SELECT t.facility,
                                                        t.route,
                                                        t.basic_type,
                                                        t.process_group,
                                                        t.operation,
                                                        t.work_center,
                                                        t.equipment,
                                                        t.lot,
                                                        t.sps_number,
                                                        CASE WHEN m.PROD_CATEGORY = 'PROD' THEN COALESCE(t.qty_out_1,0) ELSE 0 END AS qty_out_prod,
                                                        CASE WHEN m.PROD_CATEGORY = 'PROD' THEN COALESCE(t.quantity_exec, t.qty_out_1) ELSE 0 END AS qty_exec_prod,
                                                        CASE WHEN m.PROD_CATEGORY = 'REWORK' THEN COALESCE(t.qty_out_1,0) ELSE 0 END AS qty_out_rework,
                                                        CASE WHEN m.PROD_CATEGORY = 'REWORK' THEN COALESCE(t.quantity_exec, t.qty_out_1) ELSE 0 END AS qty_exec_rework,
                                                        m.PROD_CATEGORY,
                                                        m.sps_sequence_number,
                                                        t.TIME_STAMP
                                                   FROM GLB_RBL.DWH_WIP_DATA_ALL_TRANSACTIONS t JOIN masterdata m ON t.facility = m.facility
                                                                                                                 AND t.operation = m.operation
                                                                                                                 AND t.sps_sid = m.sps_id
                                                                                                                 AND t.work_center = m.work_center
                                                 WHERE t.date_stamp >= P_START
                                                     AND t.date_stamp < P_END
                                                     
                                                    AND t.transcode IN ('MVNS', 'MVOU', 'RVLT', 'RWLT', 'SHLT')
                                                    AND t.owner NOT IN ('ANKO'))
                                  SELECT trunc(time_stamp) AS date_stamp,
                                         work_center,
                                         route,
                                         basic_type,
                                         process_group,
                                         facility,
                                         sps_number,
                                         sum(qty_exec_rework) AS qty_exec_rework,
                                         sum(qty_exec_prod) AS qty_exec_prod
                                  FROM moves
                                  GROUP BY trunc(time_stamp),
                                         work_center,
                                         route,
                                         basic_type,
                                         process_group,
                                         facility,
                                         sps_number)
               
    LOOP
---Fehler korrigiert! result_rec.
       result_rec.TIME_STAMP   := rec.TIME_STAMP;
       ---result_rec.  := rec. fuer alle? nein!---
       -- result_rec.work_center   := rec.work_center;
        --result_rec.route   := rec.route;
        --result_rec.basic_type   := rec.basic_type;
        --result_rec.process_group   := rec.process_group;
        --result_rec.facility   := rec.facility;
        --result_rec.sps_number   := rec.sps_number;
        
        
        pipe row(result_rec);
     --pipe row (cmp_rate_t);

    END LOOP;

    RETURN;
  END CMP_RATE;

[mod-edit: code tags added by bb]

[Updated on: Thu, 30 November 2017 01:04] by Moderator

Report message to a moderator

Re: PLS-00364: loop index variable 'REC' use is invalid [message #666870 is a reply to message #666868] Wed, 29 November 2017 14:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: PLS-00364: loop index variable 'REC' use is invalid [message #666879 is a reply to message #666868] Thu, 30 November 2017 03:03 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Wenn eine tabelle nicht existiert oder sie keine privilegien haben, erhalten sie ORA-00942 und PLS-00364.

In prozeduren dürfen solche berechtigungen nicht über eine rolle zugewiesen werden. In prozeduren müssen solche berechtigungen direkt zugewiesen werden.

SCOTT@orcl_12.1.0.2.0> begin
  2    for rec in (select deptno from foo) loop
  3  	 dbms_output.put_line (rec.deptno);
  4    end loop;
  5  end;
  6  /
  for rec in (select deptno from foo) loop
*
ERROR at line 2:
ORA-06550: line 2, column 34:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, column 15:
PL/SQL: SQL Statement ignored
ORA-06550: line 3, column 27:
PLS-00364: loop index variable 'REC' use is invalid
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored

SCOTT@orcl_12.1.0.2.0> begin
  2    for rec in (select deptno from dept) loop
  3  	 dbms_output.put_line (rec.deptno);
  4    end loop;
  5  end;
  6  /
10
20
30
40

PL/SQL procedure successfully completed.

[Updated on: Thu, 30 November 2017 03:11]

Report message to a moderator

Previous Topic: Umlaute ersetzen
Next Topic: String hinten abschneiden - vorne einfügen
Goto Forum:
  


Current Time: Thu Mar 28 02:55:00 CDT 2024