Home » SQL & PL/SQL » SQL & PL/SQL » Error in cursor
Error in cursor [message #596666] Wed, 25 September 2013 10:03 Go to next message
ersad
Messages: 9
Registered: September 2013
Location: US
Junior Member
hi I am getting the error while executing the below procedure.


subprogram or cursor reference is out of scope


CREATE OR REPLACE PROCEDURE PRO_UPD_CI_CC_CUST_COMM
AS

V_CUSTOMER_KY CHAR(10 BYTE);
V_TAKEN_DTE DATE;
V_CUST_COMMENT_SEQ NUMBER(6,0);
V_CMNT_ID CHAR (2 BYTE);
V_CMNT_TXT VARCHAR2(4000);
V_INFO_SRC VARCHAR2(30);
V_COUNT NUMBER :=0;
V_INC_COUNTER NUMBER :=0;




V_DESCLONG VARCHAR2(4000);

CURSOR CR_CUST_COMMENT
IS SELECT CUSTOMER_KY,TAKEN_DTE,CUST_COMMENT_SEQ,
CMNT_ID,INFO_SRC,CMNT_TXT
FROM (SELECT A.CUSTOMER_KY,A.TAKEN_DTE,A.CUST_COMMENT_SEQ,B.CUST_CMNT_TXT_SEQ as CMNT_SEQ ,
'A' AS CMNT_ID,A.INFO_SRC,B.CUST_CMNT_TXT AS CMNT_TXT
from CUST_COMMENT A join
CUST_COMMENT_TXT B
on A.CUSTOMER_KY=B.CUSTOMER_KY
and A.TAKEN_DTE =B.TAKEN_DTE
and A.CUST_COMMENT_SEQ=B.CUST_COMMENT_SEQ
Union
SELECT A.CUSTOMER_KY,A.TAKEN_DTE,A.CUST_COMMENT_SEQ,B.CMNT_RES_TXT_SEQ as CMNT_SEQ ,
'B' as CMNT_ID,A.INFO_SRC,B.RESOLVED_DESC AS CMNT_TXT
from CUST_COMMENT A join
CUST_CMNT_RES_TXT B
on A.CUSTOMER_KY=B.CUSTOMER_KY
and A.TAKEN_DTE =B.TAKEN_DTE
and A.CUST_COMMENT_SEQ=B.CUST_COMMENT_SEQ)X
Order By X.CUSTOMER_KY,X.TAKEN_DTE,X.CUST_COMMENT_SEQ,X.CMNT_ID,X.CMNT_SEQ ;

BEGIN
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE dataconv.tmp_cust_comment(
CUSTOMER_KY CHAR (10),
TAKEN_DTE DATE,
CUST_COMMENT_SEQ NUMBER(6,0),
DESCLONG VARCHAR2(4000) ) ON COMMIT DELETE ROWS' ;

OPEN CR_CUST_COMMENT ;
LOOP
FETCH CR_CUST_COMMENT INTO
V_CUSTOMER_KY,V_TAKEN_DTE,V_CUST_COMMENT_SEQ,V_CMNT_ID,V_INFO_SRC,V_CMNT_TXT ;
IF CR_CUST_COMMENT%FOUND
THEN
IF(V_CUSTOMER_KY=CR_CUST_COMMENT.CUSTOMER_KY and V_TAKEN_DTE=CR_CUST_COMMENT.TAKEN_DTE and V_UST_COMMENT_SEQ=CR_CUST_COMMENT.CUST_COMMENT_SEQ and CR_CUST_COMMENT.CMNT_ID='A')
THEN
V_DESCLONG := V_DESCLONG || CR_CUST_COMMENT.V_CMNT_TXT ;
V_INC_COUNTER := V_INC_COUNTER +1 ;
ELSE IF
(V_COUNT =0 and CR_CUST_COMMENT.CMNT_ID ='B' )

THEN V_DESCLONG :=V_DESCLONG || 'Resolution:'|| CR_CUST_COMMENT.Cmnt_txt;
V_INC_COUNTER := V_INC_COUNTER +1 ;
ELSE
V_COUNT :=V_COUNT+1;
V_DESCLONG :=V_DESCLONG || CR_CUST_COMMENT.CMNT_TXT ;
V_INC_COUNTER := V_INC_COUNTER +1 ;
END IF ;
END IF ;
ELSE IF
( V_INC_COUNTER <> 0)
THEN
insert into DATACONV.tmp_cust_comment values(V_CUSTOMER_KY,V_TAKEN_DTE,V_CUST_COMMENT_SEQ,V_DESCLONG);
ELSE
V_CUSTOMER_KY :=CR_CUST_COMMENT.CUSTOMER_KY ;
V_TAKEN_DTE :=CR_CUST_COMMENT.TAKEN_DTE ;
V_CUST_COMMENT_SEQ :=CR_CUST_COMMENT.CUST_COMMENT_SEQ ;

IF ( CR_CUST_COMMENT.V_CMNT_ID='A')
THEN
V_DESCLONG :='Cust_commene taken '||CR_CUST_COMMENT.TAKEN_DTE ||'received from the following source: '|| CR_CUST_COMMENT.Info_src || 'Comment Txt:' || CR_CUST_COMMENT.Cmnt_Txt;

ELSE
V_DESCLONG :='Cust_commene taken '||CR_CUST_COMMENT.TAKEN_DTE ||'received from the following source: '|| CR_CUST_COMMENT.Info_src || 'Comment Txt:' || CR_CUST_COMMENT.Cmnt_Txt ||'Resolution:'||CR_CUST_COMMENT.Cmnt_txt;

V_COUNT :=0;
END IF;
END IF ;


END IF ;
END LOOP;
CLOSE CR_CUST_COMMENT ;
update CI_CC@LINK2CCBCNV.WORLD CC SET CC.DESCRLONG=(select DESCLONG from DATACONV.tmp_cust_comment A join CCB_STAGE_DEV.XREF_CI_CC_ID B
on A.CUSTOMER_KY =B.CUSTOMER_KY
and A.TAKEN_DTE=B.EFFECITVE_DATE
and A.CUST_COMMENT_SEQ=B.SEQ
and B.SOURCE_NM='CUSTCOMM'
where B.CC_ID=A.CC_ID);

exit when CR_CUST_COMMENT%NOTFOUND;


execute immediate 'drop table tmp_cust_comment';


END PRO_UPD_CI_CC_CUST_COMM ;

Re: Error in cursor [message #596667 is a reply to message #596666] Wed, 25 September 2013 10:07 Go to previous messageGo to next message
BlackSwan
Messages: 22500
Registered: January 2009
Senior Member
ERROR? What Error?
I don't see any error.

Since we don't have your tables or data, we can not compile, run or test posted code.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

CREATE OR replace PROCEDURE Pro_upd_ci_cc_cust_comm 
AS 
  v_customer_ky      CHAR(10 byte); 
  v_taken_dte        DATE; 
  v_cust_comment_seq NUMBER(6, 0); 
  v_cmnt_id          CHAR (2 byte); 
  v_cmnt_txt         VARCHAR2(4000); 
  v_info_src         VARCHAR2(30); 
  v_count            NUMBER := 0; 
  v_inc_counter      NUMBER := 0; 
  v_desclong         VARCHAR2(4000); 
  CURSOR cr_cust_comment IS 
    SELECT customer_ky, 
           taken_dte, 
           cust_comment_seq, 
           cmnt_id, 
           info_src, 
           cmnt_txt 
    FROM   (SELECT A.customer_ky, 
                   A.taken_dte, 
                   A.cust_comment_seq, 
                   B.cust_cmnt_txt_seq AS CMNT_SEQ, 
                   'A'                 AS CMNT_ID, 
                   A.info_src, 
                   B.cust_cmnt_txt     AS CMNT_TXT 
            FROM   cust_comment A 
                   join cust_comment_txt B 
                     ON A.customer_ky = B.customer_ky 
                        AND A.taken_dte = B.taken_dte 
                        AND A.cust_comment_seq = B.cust_comment_seq 
            UNION 
            SELECT A.customer_ky, 
                   A.taken_dte, 
                   A.cust_comment_seq, 
                   B.cmnt_res_txt_seq AS CMNT_SEQ, 
                   'B'                AS CMNT_ID, 
                   A.info_src, 
                   B.resolved_desc    AS CMNT_TXT 
            FROM   cust_comment A 
                   join cust_cmnt_res_txt B 
                     ON A.customer_ky = B.customer_ky 
                        AND A.taken_dte = B.taken_dte 
                        AND A.cust_comment_seq = B.cust_comment_seq)X 
    ORDER  BY X.customer_ky, 
              X.taken_dte, 
              X.cust_comment_seq, 
              X.cmnt_id, 
              X.cmnt_seq; 
BEGIN 
    EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE dataconv.tmp_cust_comment( CUSTOMER_KY CHAR (10), TAKEN_DTE DATE, CUST_COMMENT_SEQ NUMBER(6,0), DESCLONG VARCHAR2(4000) ) ON COMMIT DELETE ROWS'; 

    OPEN cr_cust_comment; 

    LOOP 
        FETCH cr_cust_comment INTO v_customer_ky, v_taken_dte, 
        v_cust_comment_seq, 
        v_cmnt_id, v_info_src, v_cmnt_txt; 

        IF cr_cust_comment%FOUND THEN 
          IF( v_customer_ky = cr_cust_comment.customer_ky 
              AND v_taken_dte = cr_cust_comment.taken_dte 
              AND v_ust_comment_seq = cr_cust_comment.cust_comment_seq 
              AND cr_cust_comment.cmnt_id = 'A' ) THEN 
            v_desclong := v_desclong 
                          || cr_cust_comment.v_cmnt_txt; 

            v_inc_counter := v_inc_counter + 1; 
          ELSE 
            IF ( v_count = 0 
                 AND cr_cust_comment.cmnt_id = 'B' ) THEN 
              v_desclong := v_desclong 
                            || 'Resolution:' 
                            || cr_cust_comment.cmnt_txt; 

              v_inc_counter := v_inc_counter + 1; 
            ELSE 
              v_count := v_count + 1; 

              v_desclong := v_desclong 
                            || cr_cust_comment.cmnt_txt; 

              v_inc_counter := v_inc_counter + 1; 
            END IF; 
          END IF; 
        ELSE 
          IF ( v_inc_counter <> 0 ) THEN 
            INSERT INTO dataconv.tmp_cust_comment 
            VALUES     (v_customer_ky, 
                        v_taken_dte, 
                        v_cust_comment_seq, 
                        v_desclong); 
          ELSE 
            v_customer_ky := cr_cust_comment.customer_ky; 

            v_taken_dte := cr_cust_comment.taken_dte; 

            v_cust_comment_seq := cr_cust_comment.cust_comment_seq; 

            IF ( cr_cust_comment.v_cmnt_id = 'A' ) THEN 
              v_desclong := 'Cust_commene taken ' 
                            ||cr_cust_comment.taken_dte 
                            ||'received from the following source: ' 
                            || cr_cust_comment.info_src 
                            || 'Comment Txt:' 
                            || cr_cust_comment.cmnt_txt; 
            ELSE 
              v_desclong := 'Cust_commene taken ' 
                            ||cr_cust_comment.taken_dte 
                            ||'received from the following source: ' 
                            || cr_cust_comment.info_src 
                            || 'Comment Txt:' 
                            || cr_cust_comment.cmnt_txt 
                            ||'Resolution:' 
                            ||cr_cust_comment.cmnt_txt; 

              v_count := 0; 
            END IF; 
          END IF; 
        END IF; 
    END LOOP; 

    CLOSE cr_cust_comment; 

    UPDATE ci_cc@link2ccbcnv.world CC 
    SET    CC.descrlong = (SELECT desclong 
                           FROM   dataconv.tmp_cust_comment A 
                                  join ccb_stage_dev.xref_ci_cc_id B 
                                    ON A.customer_ky = B.customer_ky 
                                       AND A.taken_dte = B.effecitve_date 
                                       AND A.cust_comment_seq = B.seq 
                                       AND B.source_nm = 'CUSTCOMM' 
                           WHERE  B.cc_id = A.cc_id); 

    exit WHEN cr_cust_comment%NOTFOUND; 

    EXECUTE IMMEDIATE 'drop table tmp_cust_comment'; 
END pro_upd_ci_cc_cust_comm; 

[Updated on: Wed, 25 September 2013 10:10]

Report message to a moderator

Re: Error in cursor [message #596671 is a reply to message #596667] Wed, 25 September 2013 10:43 Go to previous messageGo to next message
ersad
Messages: 9
Registered: September 2013
Location: US
Junior Member

Hi Swan,
Please find the ddl.

create table CUST_COMMENT(CUSTOMER_KY CHAR(10 BYTE),TAKEN_DATE DATE,CUST_COMMENT_SEQ NUMBER(6,0),

CUST_ACCT_KY CHAR(10 BYTE),COMMENT_TYP CHAR(1 BYTE),RESOLVED_DTE DATE,RESOLVED_BY VARCHAR2(8 BYTE),INFO_SRC VARCHAR2(30),OPER VARCHAR2(8 BYTE),
PRIMARY KEY(CUSTOMER_KY,TAKEN_DATE,CUST_COMMENT_SEQ)
)


CREATE TABLE CUST_COMMENT_TXT(CUSTOMER_KY CHAR(10 BYTE),TAKEN_DTE DATE,CUST_COMMENT_SEQ NUMBER(6,0),CUST_CMNT_TXT_SEQ NUMBER(6,0),CUST_CMNT_TXT VARCHAR2(40 BYTE),
PRIMARY KEY(CUSTOMER_KY,TAKEN_DTE,CUST_COMMENT_SEQ,CUST_CMNT_TXT_SEQ)

CREATE TABLE CUST_CMNT_RES_TXT(CUSTOMER_KY CHAR(10 BYTE),TAKEN_DTE DATE,CUST_COMMENT_SEQ NUMBER(6,0),CMNT_RES_SEQ NUMBER(6,0),RESOLVED_DESC VARCHAR2(40 BYTE),
PRIMARY KEY(CUSTOMER_KY,TAKEN_DTE,CUST_COMMENT_SEQ,CMNT_RES_TXT_SEQ))

icon13.gif  Re: Error in cursor [message #596673 is a reply to message #596671] Wed, 25 September 2013 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 58567
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Even with these DDL we cannot execute your code as we have not ALL objects (like the db link).
Use SQL*Plus and copy and paste the session that shows the error.

Re: Error in cursor [message #596675 is a reply to message #596673] Wed, 25 September 2013 10:56 Go to previous messageGo to next message
BlackSwan
Messages: 22500
Registered: January 2009
Senior Member
SQL> CREATE TABLE cust_cmnt_res_txt
  2    (
  3       customer_ky      CHAR(10 byte),
  4       taken_dte        DATE,
  5       cust_comment_seq NUMBER(6, 0),
  6       cmnt_res_seq     NUMBER(6, 0),
  7       resolved_desc    VARCHAR2(40 byte),
  8       PRIMARY KEY(customer_ky, taken_dte, cust_comment_seq, cmnt_res_txt_seq)
  9    )
 10  /
     PRIMARY KEY(customer_ky, taken_dte, cust_comment_seq, cmnt_res_txt_seq)
                                                           *
ERROR at line 8:
ORA-00904: "CMNT_RES_TXT_SEQ": invalid identifier



other table also have syntax error!
You should actually TEST before posting any SQL!
Re: Error in cursor [message #596685 is a reply to message #596675] Wed, 25 September 2013 12:08 Go to previous messageGo to next message
ersad
Messages: 9
Registered: September 2013
Location: US
Junior Member
CREATE TABLE cust_cmnt_res_txt
2 (
3 customer_ky CHAR(10 byte),
4 taken_dte DATE,
5 cust_comment_seq NUMBER(6, 0),
6 cmnt_res_txt_seq NUMBER(6, 0),
7 resolved_desc VARCHAR2(40 byte),
8 PRIMARY KEY(customer_ky, taken_dte, cust_comment_seq, cmnt_res_txt_seq)
9 )
updated ddl
Re: Error in cursor [message #596686 is a reply to message #596685] Wed, 25 September 2013 12:18 Go to previous messageGo to next message
ersad
Messages: 9
Registered: September 2013
Location: US
Junior Member
Hi BlackSwan,
Can you please reply me asap.
Re: Error in cursor [message #596687 is a reply to message #596685] Wed, 25 September 2013 12:20 Go to previous messageGo to next message
BlackSwan
Messages: 22500
Registered: January 2009
Senior Member

  1  CREATE OR replace PROCEDURE Pro_upd_ci_cc_cust_comm
  2  AS
  3    v_customer_ky      CHAR(10 byte);
  4    v_taken_dte        DATE;
  5    v_cust_comment_seq NUMBER(6, 0);
  6    v_cmnt_id          CHAR (2 byte);
  7    v_cmnt_txt         VARCHAR2(4000);
  8    v_info_src         VARCHAR2(30);
  9    v_count            NUMBER := 0;
 10    v_inc_counter      NUMBER := 0;
 11    v_desclong         VARCHAR2(4000);
 12    CURSOR cr_cust_comment IS
 13      SELECT customer_ky,
 14             taken_dte,
 15             cust_comment_seq,
 16             cmnt_id,
 17             info_src,
 18             cmnt_txt
 19      FROM   (SELECT A.customer_ky,
 20                     A.taken_dte,
 21                     A.cust_comment_seq,
 22                     B.cust_cmnt_txt_seq AS CMNT_SEQ,
 23                     'A'                 AS CMNT_ID,
 24                     A.info_src,
 25                     B.cust_cmnt_txt     AS CMNT_TXT
 26              FROM   cust_comment A
 27                     join cust_comment_txt B
 28                       ON A.customer_ky = B.customer_ky
 29                          AND A.taken_dte = B.taken_dte
 30                          AND A.cust_comment_seq = B.cust_comment_seq
 31              UNION
 32              SELECT A.customer_ky,
 33                     A.taken_dte,
 34                     A.cust_comment_seq,
 35                     B.cmnt_res_txt_seq AS CMNT_SEQ,
 36                     'B'                AS CMNT_ID,
 37                     A.info_src,
 38                     B.resolved_desc    AS CMNT_TXT
 39              FROM   cust_comment A
 40                     join cust_cmnt_res_txt B
 41                       ON A.customer_ky = B.customer_ky
 42                          AND A.taken_dte = B.taken_dte
 43                          AND A.cust_comment_seq = B.cust_comment_seq)X
 44      ORDER  BY X.customer_ky,
 45                X.taken_dte,
 46                X.cust_comment_seq,
 47                X.cmnt_id,
 48                X.cmnt_seq;
 49  BEGIN
 50      EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE dataconv.tmp_cust_comment( CUSTOMER_KY CHAR (10), TAKEN_DTE DATE, CUST_COMMENT_SEQ NUMBER(6,0), DESCLONG VARCHAR2(4000) ) ON COMMIT DELETE ROWS';
 51      OPEN cr_cust_comment;
 52      LOOP
 53          FETCH cr_cust_comment INTO v_customer_ky, v_taken_dte,
 54          v_cust_comment_seq,
 55          v_cmnt_id, v_info_src, v_cmnt_txt;
 56          IF cr_cust_comment%FOUND THEN
 57            IF( v_customer_ky = cr_cust_comment.customer_ky
 58                AND v_taken_dte = cr_cust_comment.taken_dte
 59                AND v_ust_comment_seq = cr_cust_comment.cust_comment_seq
 60                AND cr_cust_comment.cmnt_id = 'A' ) THEN
 61              v_desclong := v_desclong
 62                            || cr_cust_comment.v_cmnt_txt;
 63              v_inc_counter := v_inc_counter + 1;
 64            ELSE
 65              IF ( v_count = 0
 66                   AND cr_cust_comment.cmnt_id = 'B' ) THEN
 67                v_desclong := v_desclong
 68                              || 'Resolution:'
 69                              || cr_cust_comment.cmnt_txt;
 70                v_inc_counter := v_inc_counter + 1;
 71              ELSE
 72                v_count := v_count + 1;
 73                v_desclong := v_desclong
 74                              || cr_cust_comment.cmnt_txt;
 75                v_inc_counter := v_inc_counter + 1;
 76              END IF;
 77            END IF;
 78          ELSE
 79            IF ( v_inc_counter <> 0 ) THEN
 80              INSERT INTO dataconv.tmp_cust_comment
 81              VALUES     (v_customer_ky,
 82                          v_taken_dte,
 83                          v_cust_comment_seq,
 84                          v_desclong);
 85            ELSE
 86              v_customer_ky := cr_cust_comment.customer_ky;
 87              v_taken_dte := cr_cust_comment.taken_dte;
 88              v_cust_comment_seq := cr_cust_comment.cust_comment_seq;
 89              IF ( cr_cust_comment.v_cmnt_id = 'A' ) THEN
 90                v_desclong := 'Cust_commene taken '
 91                              ||cr_cust_comment.taken_dte
 92                              ||'received from the following source: '
 93                              || cr_cust_comment.info_src
 94                              || 'Comment Txt:'
 95                              || cr_cust_comment.cmnt_txt;
 96              ELSE
 97                v_desclong := 'Cust_commene taken '
 98                              ||cr_cust_comment.taken_dte
 99                              ||'received from the following source: '
100                              || cr_cust_comment.info_src
101                              || 'Comment Txt:'
102                              || cr_cust_comment.cmnt_txt
103                              ||'Resolution:'
104                              ||cr_cust_comment.cmnt_txt;
105                v_count := 0;
106              END IF;
107            END IF;
108          END IF;
109      END LOOP;
110      CLOSE cr_cust_comment;
111      UPDATE ci_cc@link2ccbcnv.world CC
112      SET    CC.descrlong = (SELECT desclong
113                             FROM   dataconv.tmp_cust_comment A
114                                    join ccb_stage_dev.xref_ci_cc_id B
115                                      ON A.customer_ky = B.customer_ky
116                                         AND A.taken_dte = B.effecitve_date
117                                         AND A.cust_comment_seq = B.seq
118                                         AND B.source_nm = 'CUSTCOMM'
119                             WHERE  B.cc_id = A.cc_id);
120      exit WHEN cr_cust_comment%NOTFOUND;
121      EXECUTE IMMEDIATE 'drop table tmp_cust_comment';
122* END pro_upd_ci_cc_cust_comm;
123  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE PRO_UPD_CI_CC_CUST_COMM:

LINE/COL ERROR
-------- -----------------------------------------------------------------
13/5     PL/SQL: SQL Statement ignored
29/29    PL/SQL: ORA-00904: "A"."TAKEN_DTE": invalid identifier
57/11    PL/SQL: Statement ignored
57/47    PLS-00225: subprogram or cursor 'CR_CUST_COMMENT' reference is
         out of scope

80/13    PL/SQL: SQL Statement ignored
80/34    PL/SQL: ORA-00942: table or view does not exist
86/13    PL/SQL: Statement ignored
86/46    PLS-00225: subprogram or cursor 'CR_CUST_COMMENT' reference is
         out of scope

LINE/COL ERROR
-------- -----------------------------------------------------------------

87/13    PL/SQL: Statement ignored
87/44    PLS-00225: subprogram or cursor 'CR_CUST_COMMENT' reference is
         out of scope

88/13    PL/SQL: Statement ignored
88/51    PLS-00225: subprogram or cursor 'CR_CUST_COMMENT' reference is
         out of scope

89/13    PL/SQL: Statement ignored
89/34    PLS-00225: subprogram or cursor 'CR_CUST_COMMENT' reference is

LINE/COL ERROR
-------- -----------------------------------------------------------------
         out of scope

111/5    PL/SQL: ORA-00942: table or view does not exist
111/5    PL/SQL: SQL Statement ignored
120/5    PL/SQL: Statement ignored
120/5    PLS-00376: illegal EXIT/CONTINUE statement; it must appear inside
         a loop

Re: Error in cursor [message #596688 is a reply to message #596687] Wed, 25 September 2013 12:28 Go to previous messageGo to next message
ersad
Messages: 9
Registered: September 2013
Location: US
Junior Member
Yes i got the same error can you please suggest how to rectify the subprogram or cursor 'CR_CUST_COMMENT' reference is out of scope error.
Thanks a lot in adv.
Re: Error in cursor [message #596689 is a reply to message #596688] Wed, 25 September 2013 12:32 Go to previous messageGo to next message
BlackSwan
Messages: 22500
Registered: January 2009
Senior Member
First you need to fix the errors that are reported on lines #13 & #29
Re: Error in cursor [message #596690 is a reply to message #596689] Wed, 25 September 2013 12:34 Go to previous messageGo to next message
ersad
Messages: 9
Registered: September 2013
Location: US
Junior Member
yes u can now run it.
CREATE OR REPLACE PROCEDURE PRO_UPD_CI_CC_CUST_COMM
AS

V_CUSTOMER_KY CHAR(10 BYTE);
V_TAKEN_DTE DATE;
V_CUST_COMMENT_SEQ NUMBER(6,0);
V_CMNT_ID CHAR (2 BYTE);
V_CMNT_TXT VARCHAR2(4000);
V_INFO_SRC VARCHAR2(30);
V_COUNT NUMBER :=0;
V_INC_COUNTER NUMBER :=0;




V_DESCLONG VARCHAR2(4000);

CURSOR CR_CUST_COMMENT
IS SELECT CUSTOMER_KY,TAKEN_DTE,CUST_COMMENT_SEQ,
CMNT_ID,INFO_SRC,CMNT_TXT
FROM (SELECT A.CUSTOMER_KY,A.TAKEN_DATE,A.CUST_COMMENT_SEQ,B.CUST_CMNT_TXT_SEQ as CMNT_SEQ ,
'A' AS CMNT_ID,A.INFO_SRC,B.CUST_CMNT_TXT AS CMNT_TXT
from CUST_COMMENT A join
CUST_COMMENT_TXT B
on A.CUSTOMER_KY=B.CUSTOMER_KY
and A.TAKEN_DATE =B.TAKEN_DTE
and A.CUST_COMMENT_SEQ=B.CUST_COMMENT_SEQ
Union
SELECT A.CUSTOMER_KY,A.TAKEN_DATE,A.CUST_COMMENT_SEQ,B.CMNT_RES_TXT_SEQ as CMNT_SEQ ,
'B' as CMNT_ID,A.INFO_SRC,B.RESOLVED_DESC AS CMNT_TXT
from CUST_COMMENT A join
CUST_CMNT_RES_TXT B
on A.CUSTOMER_KY=B.CUSTOMER_KY
and A.TAKEN_DATE =B.TAKEN_DTE
and A.CUST_COMMENT_SEQ=B.CUST_COMMENT_SEQ)X
Order By X.CUSTOMER_KY,X.TAKEN_DTE,X.CUST_COMMENT_SEQ,X.CMNT_ID,X.CMNT_SEQ ;

BEGIN
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE dataconv.tmp_cust_comment(
CUSTOMER_KY CHAR (10),
TAKEN_DTE DATE,
CUST_COMMENT_SEQ NUMBER(6,0),
DESCLONG VARCHAR2(4000) ) ON COMMIT DELETE ROWS' ;

OPEN CR_CUST_COMMENT ;
LOOP
FETCH CR_CUST_COMMENT INTO
V_CUSTOMER_KY,V_TAKEN_DTE,V_CUST_COMMENT_SEQ,V_CMNT_ID,V_INFO_SRC,V_CMNT_TXT ;
IF CR_CUST_COMMENT%FOUND
THEN
IF(V_CUSTOMER_KY=CR_CUST_COMMENT.CUSTOMER_KY and V_TAKEN_DTE=CR_CUST_COMMENT.TAKEN_DTE and V_UST_COMMENT_SEQ=CR_CUST_COMMENT.CUST_COMMENT_SEQ and CR_CUST_COMMENT.CMNT_ID='A')
THEN
V_DESCLONG := V_DESCLONG || CR_CUST_COMMENT.V_CMNT_TXT ;
V_INC_COUNTER := V_INC_COUNTER +1 ;
ELSE IF
(V_COUNT =0 and CR_CUST_COMMENT.CMNT_ID ='B' )

THEN V_DESCLONG :=V_DESCLONG || 'Resolution:'|| CR_CUST_COMMENT.Cmnt_txt;
V_INC_COUNTER := V_INC_COUNTER +1 ;
ELSE
V_COUNT :=V_COUNT+1;
V_DESCLONG :=V_DESCLONG || CR_CUST_COMMENT.CMNT_TXT ;
V_INC_COUNTER := V_INC_COUNTER +1 ;
END IF ;
END IF ;
ELSE IF
( V_INC_COUNTER <> 0)
THEN
insert into DATACONV.tmp_cust_comment values(V_CUSTOMER_KY,V_TAKEN_DTE,V_CUST_COMMENT_SEQ,V_DESCLONG);
ELSE
V_CUSTOMER_KY :=CR_CUST_COMMENT.CUSTOMER_KY ;
V_TAKEN_DTE :=CR_CUST_COMMENT.TAKEN_DTE ;
V_CUST_COMMENT_SEQ :=CR_CUST_COMMENT.CUST_COMMENT_SEQ ;

IF ( CR_CUST_COMMENT.V_CMNT_ID='A')
THEN
V_DESCLONG :='Cust_commene taken '||CR_CUST_COMMENT.TAKEN_DTE ||'received from the following source: '|| CR_CUST_COMMENT.Info_src || 'Comment Txt:' || CR_CUST_COMMENT.Cmnt_Txt;

ELSE
V_DESCLONG :='Cust_commene taken '||CR_CUST_COMMENT.TAKEN_DTE ||'received from the following source: '|| CR_CUST_COMMENT.Info_src || 'Comment Txt:' || CR_CUST_COMMENT.Cmnt_Txt ||'Resolution:'||CR_CUST_COMMENT.Cmnt_txt;

V_COUNT :=0;
END IF;
END IF ;


END IF ;
END LOOP;
CLOSE CR_CUST_COMMENT ;
update CI_CC@LINK2CCBCNV.WORLD CC SET CC.DESCRLONG=(select DESCLONG from DATACONV.tmp_cust_comment A join CCB_STAGE_DEV.XREF_CI_CC_ID B
on A.CUSTOMER_KY =B.CUSTOMER_KY
and A.TAKEN_DTE=B.EFFECITVE_DATE
and A.CUST_COMMENT_SEQ=B.SEQ
and B.SOURCE_NM='CUSTCOMM'
where B.CC_ID=A.CC_ID);

exit when CR_CUST_COMMENT%NOTFOUND;


execute immediate 'drop table tmp_cust_comment';


END PRO_UPD_CI_CC_CUST_COMM ;

Re: Error in cursor [message #596692 is a reply to message #596690] Wed, 25 September 2013 12:35 Go to previous messageGo to next message
ersad
Messages: 9
Registered: September 2013
Location: US
Junior Member
actually i am getting the below error in my system

57/11 PL/SQL: Statement ignored
57/47 PLS-00225: subprogram or cursor 'CR_CUST_COMMENT' reference is
out of scope

80/13 PL/SQL: SQL Statement ignored
80/34 PL/SQL: ORA-00942: table or view does not exist
86/13 PL/SQL: Statement ignored
86/46 PLS-00225: subprogram or cursor 'CR_CUST_COMMENT' reference is
out of scope

LINE/COL ERROR
-------- -----------------------------------------------------------------

87/13 PL/SQL: Statement ignored
87/44 PLS-00225: subprogram or cursor 'CR_CUST_COMMENT' reference is
out of scope

88/13 PL/SQL: Statement ignored
88/51 PLS-00225: subprogram or cursor 'CR_CUST_COMMENT' reference is
out of scope

89/13 PL/SQL: Statement ignored
89/34 PLS-00225: subprogram or cursor 'CR_CUST_COMMENT' reference is

LINE/COL ERROR
-------- -----------------------------------------------------------------
out of scope

111/5 PL/SQL: ORA-00942: table or view does not exist
111/5 PL/SQL: SQL Statement ignored
120/5 PL/SQL: Statement ignored
120/5 PLS-00376: illegal EXIT/CONTINUE statement; it must appear inside
a loop

Re: Error in cursor [message #596693 is a reply to message #596692] Wed, 25 September 2013 12:42 Go to previous messageGo to next message
BlackSwan
Messages: 22500
Registered: January 2009
Senior Member
>yes u can now run it.
Oracle disagrees with you.

First you need to fix the errors that are reported on lines #13 & #45

  1  CREATE OR replace PROCEDURE Pro_upd_ci_cc_cust_comm
  2  AS
  3    v_customer_ky      CHAR(10 byte);
  4    v_taken_dte        DATE;
  5    v_cust_comment_seq NUMBER(6, 0);
  6    v_cmnt_id          CHAR (2 byte);
  7    v_cmnt_txt         VARCHAR2(4000);
  8    v_info_src         VARCHAR2(30);
  9    v_count            NUMBER := 0;
 10    v_inc_counter      NUMBER := 0;
 11    v_desclong         VARCHAR2(4000);
 12    CURSOR cr_cust_comment IS
 13      SELECT customer_ky,
 14             taken_dte,
 15             cust_comment_seq,
 16             cmnt_id,
 17             info_src,
 18             cmnt_txt
 19      FROM   (SELECT A.customer_ky,
 20                     A.taken_date,
 21                     A.cust_comment_seq,
 22                     B.cust_cmnt_txt_seq AS CMNT_SEQ,
 23                     'A'                 AS CMNT_ID,
 24                     A.info_src,
 25                     B.cust_cmnt_txt     AS CMNT_TXT
 26              FROM   cust_comment A
 27                     join cust_comment_txt B
 28                       ON A.customer_ky = B.customer_ky
 29                          AND A.taken_date = B.taken_dte
 30                          AND A.cust_comment_seq = B.cust_comment_seq
 31              UNION
 32              SELECT A.customer_ky,
 33                     A.taken_date,
 34                     A.cust_comment_seq,
 35                     B.cmnt_res_txt_seq AS CMNT_SEQ,
 36                     'B'                AS CMNT_ID,
 37                     A.info_src,
 38                     B.resolved_desc    AS CMNT_TXT
 39              FROM   cust_comment A
 40                     join cust_cmnt_res_txt B
 41                       ON A.customer_ky = B.customer_ky
 42                          AND A.taken_date = B.taken_dte
 43                          AND A.cust_comment_seq = B.cust_comment_seq)X
 44      ORDER  BY X.customer_ky,
 45                X.taken_dte,
 46                X.cust_comment_seq,
 47                X.cmnt_id,
 48                X.cmnt_seq;
 49  BEGIN
 50      EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE dataconv.tmp_cust_comment( CUSTOMER_KY CHAR (10), TAKEN_DTE DATE, CUST_COMMENT_SEQ NUMBER(6,0), DESCLONG VARCHAR2(4000) ) ON COMMIT DELETE ROWS';
 51      OPEN cr_cust_comment;
 52      LOOP
 53          FETCH cr_cust_comment INTO v_customer_ky, v_taken_dte,
 54          v_cust_comment_seq,
 55          v_cmnt_id, v_info_src, v_cmnt_txt;
 56          IF cr_cust_comment%FOUND THEN
 57            IF( v_customer_ky = cr_cust_comment.customer_ky
 58                AND v_taken_dte = cr_cust_comment.taken_dte
 59                AND v_ust_comment_seq = cr_cust_comment.cust_comment_seq
 60                AND cr_cust_comment.cmnt_id = 'A' ) THEN
 61              v_desclong := v_desclong
 62                            || cr_cust_comment.v_cmnt_txt;
 63              v_inc_counter := v_inc_counter + 1;
 64            ELSE
 65              IF ( v_count = 0
 66                   AND cr_cust_comment.cmnt_id = 'B' ) THEN
 67                v_desclong := v_desclong
 68                              || 'Resolution:'
 69                              || cr_cust_comment.cmnt_txt;
 70                v_inc_counter := v_inc_counter + 1;
 71              ELSE
 72                v_count := v_count + 1;
 73                v_desclong := v_desclong
 74                              || cr_cust_comment.cmnt_txt;
 75                v_inc_counter := v_inc_counter + 1;
 76              END IF;
 77            END IF;
 78          ELSE
 79            IF ( v_inc_counter <> 0 ) THEN
 80              INSERT INTO dataconv.tmp_cust_comment
 81              VALUES     (v_customer_ky,
 82                          v_taken_dte,
 83                          v_cust_comment_seq,
 84                          v_desclong);
 85            ELSE
 86              v_customer_ky := cr_cust_comment.customer_ky;
 87              v_taken_dte := cr_cust_comment.taken_dte;
 88              v_cust_comment_seq := cr_cust_comment.cust_comment_seq;
 89              IF ( cr_cust_comment.v_cmnt_id = 'A' ) THEN
 90                v_desclong := 'Cust_commene taken '
 91                              ||cr_cust_comment.taken_dte
 92                              ||'received from the following source: '
 93                              || cr_cust_comment.info_src
 94                              || 'Comment Txt:'
 95                              || cr_cust_comment.cmnt_txt;
 96              ELSE
 97                v_desclong := 'Cust_commene taken '
 98                              ||cr_cust_comment.taken_dte
 99                              ||'received from the following source: '
100                              || cr_cust_comment.info_src
101                              || 'Comment Txt:'
102                              || cr_cust_comment.cmnt_txt
103                              ||'Resolution:'
104                              ||cr_cust_comment.cmnt_txt;
105                v_count := 0;
106              END IF;
107            END IF;
108          END IF;
109      END LOOP;
110      CLOSE cr_cust_comment;
111      UPDATE ci_cc@link2ccbcnv.world CC
112      SET    CC.descrlong = (SELECT desclong
113                             FROM   dataconv.tmp_cust_comment A
114                                    join ccb_stage_dev.xref_ci_cc_id B
115                                      ON A.customer_ky = B.customer_ky
116                                         AND A.taken_dte = B.effecitve_date
117                                         AND A.cust_comment_seq = B.seq
118                                         AND B.source_nm = 'CUSTCOMM'
119                             WHERE  B.cc_id = A.cc_id);
120      exit WHEN cr_cust_comment%NOTFOUND;
121      EXECUTE IMMEDIATE 'drop table tmp_cust_comment';
122* END pro_upd_ci_cc_cust_comm;
123  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE PRO_UPD_CI_CC_CUST_COMM:

LINE/COL ERROR
-------- -----------------------------------------------------------------
13/5     PL/SQL: SQL Statement ignored
45/15    PL/SQL: ORA-00904: "X"."TAKEN_DTE": invalid identifier
57/11    PL/SQL: Statement ignored
57/47    PLS-00225: subprogram or cursor 'CR_CUST_COMMENT' reference is
         out of scope

80/13    PL/SQL: SQL Statement ignored
80/34    PL/SQL: ORA-00942: table or view does not exist
86/13    PL/SQL: Statement ignored
86/46    PLS-00225: subprogram or cursor 'CR_CUST_COMMENT' reference is
         out of scope

LINE/COL ERROR
-------- -----------------------------------------------------------------

87/13    PL/SQL: Statement ignored
87/44    PLS-00225: subprogram or cursor 'CR_CUST_COMMENT' reference is
         out of scope

88/13    PL/SQL: Statement ignored
88/51    PLS-00225: subprogram or cursor 'CR_CUST_COMMENT' reference is
         out of scope

89/13    PL/SQL: Statement ignored
89/34    PLS-00225: subprogram or cursor 'CR_CUST_COMMENT' reference is

LINE/COL ERROR
-------- -----------------------------------------------------------------
         out of scope

111/5    PL/SQL: ORA-00942: table or view does not exist
111/5    PL/SQL: SQL Statement ignored
120/5    PL/SQL: Statement ignored
120/5    PLS-00376: illegal EXIT/CONTINUE statement; it must appear inside
         a loop

SQL> 



Re: Error in cursor [message #596694 is a reply to message #596693] Wed, 25 September 2013 12:45 Go to previous messageGo to next message
ersad
Messages: 9
Registered: September 2013
Location: US
Junior Member
1 CREATE OR replace PROCEDURE Pro_upd_ci_cc_cust_comm
2 AS
3 v_customer_ky CHAR(10 byte);
4 v_taken_dte DATE;
5 v_cust_comment_seq NUMBER(6, 0);
6 v_cmnt_id CHAR (2 byte);
7 v_cmnt_txt VARCHAR2(4000);
8 v_info_src VARCHAR2(30);
9 v_count NUMBER := 0;
10 v_inc_counter NUMBER := 0;
11 v_desclong VARCHAR2(4000);
12 CURSOR cr_cust_comment IS
13 SELECT customer_ky,
14 taken_date,
15 cust_comment_seq,
16 cmnt_id,
17 info_src,
18 cmnt_txt
19 FROM (SELECT A.customer_ky,
20 A.taken_date,
21 A.cust_comment_seq,
22 B.cust_cmnt_txt_seq AS CMNT_SEQ,
23 'A' AS CMNT_ID,
24 A.info_src,
25 B.cust_cmnt_txt AS CMNT_TXT
26 FROM cust_comment A
27 join cust_comment_txt B
28 ON A.customer_ky = B.customer_ky
29 AND A.taken_date = B.taken_dte
30 AND A.cust_comment_seq = B.cust_comment_seq
31 UNION
32 SELECT A.customer_ky,
33 A.taken_date,
34 A.cust_comment_seq,
35 B.cmnt_res_txt_seq AS CMNT_SEQ,
36 'B' AS CMNT_ID,
37 A.info_src,
38 B.resolved_desc AS CMNT_TXT
39 FROM cust_comment A
40 join cust_cmnt_res_txt B
41 ON A.customer_ky = B.customer_ky
42 AND A.taken_date = B.taken_dte
43 AND A.cust_comment_seq = B.cust_comment_seq)X
44 ORDER BY X.customer_ky,
45 X.taken_date,
46 X.cust_comment_seq,
47 X.cmnt_id,
48 X.cmnt_seq;
49 BEGIN
50 EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE dataconv.tmp_cust_comment( CUSTOMER_KY CHAR (10), TAKEN_DTE DATE, CUST_COMMENT_SEQ NUMBER(6,0), DESCLONG VARCHAR2(4000) ) ON COMMIT DELETE ROWS';
51 OPEN cr_cust_comment;
52 LOOP
53 FETCH cr_cust_comment INTO v_customer_ky, v_taken_dte,
54 v_cust_comment_seq,
55 v_cmnt_id, v_info_src, v_cmnt_txt;
56 IF cr_cust_comment%FOUND THEN
57 IF( v_customer_ky = cr_cust_comment.customer_ky
58 AND v_taken_dte = cr_cust_comment.taken_dte
59 AND v_ust_comment_seq = cr_cust_comment.cust_comment_seq
60 AND cr_cust_comment.cmnt_id = 'A' ) THEN
61 v_desclong := v_desclong
62 || cr_cust_comment.v_cmnt_txt;
63 v_inc_counter := v_inc_counter + 1;
64 ELSE
65 IF ( v_count = 0
66 AND cr_cust_comment.cmnt_id = 'B' ) THEN
67 v_desclong := v_desclong
68 || 'Resolution:'
69 || cr_cust_comment.cmnt_txt;
70 v_inc_counter := v_inc_counter + 1;
71 ELSE
72 v_count := v_count + 1;
73 v_desclong := v_desclong
74 || cr_cust_comment.cmnt_txt;
75 v_inc_counter := v_inc_counter + 1;
76 END IF;
77 END IF;
78 ELSE
79 IF ( v_inc_counter <> 0 ) THEN
80 INSERT INTO dataconv.tmp_cust_comment
81 VALUES (v_customer_ky,
82 v_taken_dte,
83 v_cust_comment_seq,
84 v_desclong);
85 ELSE
86 v_customer_ky := cr_cust_comment.customer_ky;
87 v_taken_dte := cr_cust_comment.taken_dte;
88 v_cust_comment_seq := cr_cust_comment.cust_comment_seq;
89 IF ( cr_cust_comment.v_cmnt_id = 'A' ) THEN
90 v_desclong := 'Cust_commene taken '
91 ||cr_cust_comment.taken_dte
92 ||'received from the following source: '
93 || cr_cust_comment.info_src
94 || 'Comment Txt:'
95 || cr_cust_comment.cmnt_txt;
96 ELSE
97 v_desclong := 'Cust_commene taken '
98 ||cr_cust_comment.taken_dte
99 ||'received from the following source: '
100 || cr_cust_comment.info_src
101 || 'Comment Txt:'
102 || cr_cust_comment.cmnt_txt
103 ||'Resolution:'
104 ||cr_cust_comment.cmnt_txt;
105 v_count := 0;
106 END IF;
107 END IF;
108 END IF;
109 END LOOP;
110 CLOSE cr_cust_comment;
111 UPDATE ci_cc@link2ccbcnv.world CC
112 SET CC.descrlong = (SELECT desclong
113 FROM dataconv.tmp_cust_comment A
114 join ccb_stage_dev.xref_ci_cc_id B
115 ON A.customer_ky = B.customer_ky
116 AND A.taken_dte = B.effecitve_date
117 AND A.cust_comment_seq = B.seq
118 AND B.source_nm = 'CUSTCOMM'
119 WHERE B.cc_id = A.cc_id);
120 exit WHEN cr_cust_comment%NOTFOUND;
121 EXECUTE IMMEDIATE 'drop table tmp_cust_comment';
122* END pro_upd_ci_cc_cust_comm;
123 /



Now u can check it..thanks a lot .
Re: Error in cursor [message #596696 is a reply to message #596694] Wed, 25 September 2013 12:52 Go to previous messageGo to next message
BlackSwan
Messages: 22500
Registered: January 2009
Senior Member
since you are incapable or unwilling to actually test before posting, I decline to continue trying to assist.
You're On Your Own (YOYO)!
SQL>       SELECT customer_ky,
  2               taken_dte,
  3               cust_comment_seq,
  4               cmnt_id,
  5               info_src,
  6               cmnt_txt
  7        FROM   (SELECT A.customer_ky,
  8                       A.taken_date,
  9                       A.cust_comment_seq,
 10                       B.cust_cmnt_txt_seq AS CMNT_SEQ,
 11                       'A'                 AS CMNT_ID,
 12                       A.info_src,
 13                       B.cust_cmnt_txt     AS CMNT_TXT
 14                FROM   cust_comment A
 15                       join cust_comment_txt B
 16                         ON A.customer_ky = B.customer_ky
 17                            AND A.taken_date = B.taken_dte
 18                            AND A.cust_comment_seq = B.cust_comment_seq
 19                UNION
 20                SELECT A.customer_ky,
 21                       A.taken_date,
 22                       A.cust_comment_seq,
 23                       B.cmnt_res_txt_seq AS CMNT_SEQ,
 24                       'B'                AS CMNT_ID,
 25                       A.info_src,
 26                       B.resolved_desc    AS CMNT_TXT
 27                FROM   cust_comment A
 28                       join cust_cmnt_res_txt B
 29                         ON A.customer_ky = B.customer_ky
 30                            AND A.taken_date = B.taken_dte
 31                            AND A.cust_comment_seq = B.cust_comment_seq)X
 32        ORDER  BY X.customer_ky,
 33                  X.taken_dte,
 34                  X.cust_comment_seq,
 35                  X.cmnt_id,
 36                  X.cmnt_seq;
                X.taken_dte,
                *
ERROR at line 33:
ORA-00904: "X"."TAKEN_DTE": invalid identifier


Re: Error in cursor [message #596700 is a reply to message #596696] Wed, 25 September 2013 13:08 Go to previous messageGo to next message
ersad
Messages: 9
Registered: September 2013
Location: US
Junior Member
actually the pl/sql which i provide u 1st is right.
but 2nd time the ddl for creating tables it was wrong taken_date in stead of taken_dte.
I have attached you all the correct ddl and pl/sql procedure pls have look into it.

CREATE OR REPLACE PROCEDURE PRO_UPD_CI_CC_CUST_COMM
AS

V_CUSTOMER_KY CHAR(10 BYTE);
V_TAKEN_DTE DATE;
V_CUST_COMMENT_SEQ NUMBER(6,0);
V_CMNT_ID CHAR (2 BYTE);
V_CMNT_TXT VARCHAR2(4000);
V_INFO_SRC VARCHAR2(30);
V_COUNT NUMBER :=0;
V_INC_COUNTER NUMBER :=0;




V_DESCLONG VARCHAR2(4000);

CURSOR CR_CUST_COMMENT
IS SELECT CUSTOMER_KY,TAKEN_DTE,CUST_COMMENT_SEQ,
CMNT_ID,INFO_SRC,CMNT_TXT
FROM (SELECT A.CUSTOMER_KY,A.TAKEN_DTE,A.CUST_COMMENT_SEQ,B.CUST_CMNT_TXT_SEQ as CMNT_SEQ ,
'A' AS CMNT_ID,A.INFO_SRC,B.CUST_CMNT_TXT AS CMNT_TXT
from CUST_COMMENT A join
CUST_COMMENT_TXT B
on A.CUSTOMER_KY=B.CUSTOMER_KY
and A.TAKEN_DTE =B.TAKEN_DTE
and A.CUST_COMMENT_SEQ=B.CUST_COMMENT_SEQ
Union
SELECT A.CUSTOMER_KY,A.TAKEN_DTE,A.CUST_COMMENT_SEQ,B.CMNT_RES_TXT_SEQ as CMNT_SEQ ,
'B' as CMNT_ID,A.INFO_SRC,B.RESOLVED_DESC AS CMNT_TXT
from CUST_COMMENT A join
CUST_CMNT_RES_TXT B
on A.CUSTOMER_KY=B.CUSTOMER_KY
and A.TAKEN_DTE =B.TAKEN_DTE
and A.CUST_COMMENT_SEQ=B.CUST_COMMENT_SEQ)X
Order By X.CUSTOMER_KY,X.TAKEN_DTE,X.CUST_COMMENT_SEQ,X.CMNT_ID,X.CMNT_SEQ ;

BEGIN
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE dataconv.tmp_cust_comment(
CUSTOMER_KY CHAR (10),
TAKEN_DTE DATE,
CUST_COMMENT_SEQ NUMBER(6,0),
DESCLONG VARCHAR2(4000) ) ON COMMIT DELETE ROWS' ;

OPEN CR_CUST_COMMENT ;
LOOP
FETCH CR_CUST_COMMENT INTO
V_CUSTOMER_KY,V_TAKEN_DTE,V_CUST_COMMENT_SEQ,V_CMNT_ID,V_INFO_SRC,V_CMNT_TXT ;
IF CR_CUST_COMMENT%FOUND
THEN
IF(V_CUSTOMER_KY=CR_CUST_COMMENT.CUSTOMER_KY and V_TAKEN_DTE=CR_CUST_COMMENT.TAKEN_DTE and V_UST_COMMENT_SEQ=CR_CUST_COMMENT.CUST_COMMENT_SEQ and CR_CUST_COMMENT.CMNT_ID='A')
THEN
V_DESCLONG := V_DESCLONG || CR_CUST_COMMENT.V_CMNT_TXT ;
V_INC_COUNTER := V_INC_COUNTER +1 ;
ELSE IF
(V_COUNT =0 and CR_CUST_COMMENT.CMNT_ID ='B' )

THEN V_DESCLONG :=V_DESCLONG || 'Resolution:'|| CR_CUST_COMMENT.Cmnt_txt;
V_INC_COUNTER := V_INC_COUNTER +1 ;
ELSE
V_COUNT :=V_COUNT+1;
V_DESCLONG :=V_DESCLONG || CR_CUST_COMMENT.CMNT_TXT ;
V_INC_COUNTER := V_INC_COUNTER +1 ;
END IF ;
END IF ;
ELSE IF
( V_INC_COUNTER <> 0)
THEN
insert into DATACONV.tmp_cust_comment values(V_CUSTOMER_KY,V_TAKEN_DTE,V_CUST_COMMENT_SEQ,V_DESCLONG);
ELSE
V_CUSTOMER_KY :=CR_CUST_COMMENT.CUSTOMER_KY ;
V_TAKEN_DTE :=CR_CUST_COMMENT.TAKEN_DTE ;
V_CUST_COMMENT_SEQ :=CR_CUST_COMMENT.CUST_COMMENT_SEQ ;

IF ( CR_CUST_COMMENT.V_CMNT_ID='A')
THEN
V_DESCLONG :='Cust_commene taken '||CR_CUST_COMMENT.TAKEN_DTE ||'received from the following source: '|| CR_CUST_COMMENT.Info_src || 'Comment Txt:' || CR_CUST_COMMENT.Cmnt_Txt;

ELSE
V_DESCLONG :='Cust_commene taken '||CR_CUST_COMMENT.TAKEN_DTE ||'received from the following source: '|| CR_CUST_COMMENT.Info_src || 'Comment Txt:' || CR_CUST_COMMENT.Cmnt_Txt ||'Resolution:'||CR_CUST_COMMENT.Cmnt_txt;

V_COUNT :=0;
END IF;
END IF ;


END IF ;
END LOOP;
CLOSE CR_CUST_COMMENT ;

exit when CR_CUST_COMMENT%NOTFOUND;


execute immediate 'drop table tmp_cust_comment';


END PRO_UPD_CI_CC_CUST_COMM ;


DDLs:
create table CUST_COMMENT(CUSTOMER_KY CHAR(10 BYTE),TAKEN_DTE DATE,CUST_COMMENT_SEQ NUMBER(6,0),

CUST_ACCT_KY CHAR(10 BYTE),COMMENT_TYP CHAR(1 BYTE),RESOLVED_DTE DATE,RESOLVED_BY VARCHAR2(8 BYTE),INFO_SRC VARCHAR2(30),OPER VARCHAR2(8 BYTE),
PRIMARY KEY(CUSTOMER_KY,TAKEN_DTE,CUST_COMMENT_SEQ)
)


CREATE TABLE CUST_COMMENT_TXT(CUSTOMER_KY CHAR(10 BYTE),TAKEN_DTE DATE,CUST_COMMENT_SEQ NUMBER(6,0),CUST_CMNT_TXT_SEQ NUMBER(6,0),CUST_CMNT_TXT VARCHAR2(40 BYTE),
PRIMARY KEY(CUSTOMER_KY,TAKEN_DTE,CUST_COMMENT_SEQ,CUST_CMNT_TXT_SEQ))

CREATE TABLE CUST_CMNT_RES_TXT(CUSTOMER_KY CHAR(10 BYTE),TAKEN_DTE DATE,CUST_COMMENT_SEQ NUMBER(6,0),CMNT_RES_TXT_SEQ NUMBER(6,0),RESOLVED_DESC VARCHAR2(40 BYTE),
PRIMARY KEY(CUSTOMER_KY,TAKEN_DTE,CUST_COMMENT_SEQ,CMNT_RES_TXT_SEQ))


Thanks a lot in adv.
Re: Error in cursor [message #596706 is a reply to message #596700] Wed, 25 September 2013 13:44 Go to previous message
BlackSwan
Messages: 22500
Registered: January 2009
Senior Member
unless & until you produce valid syntax you will continue to be stuck where you are now


  1  SELECT CUSTOMER_KY,TAKEN_DTE,CUST_COMMENT_SEQ,
  2  CMNT_ID,INFO_SRC,CMNT_TXT
  3  FROM (SELECT A.CUSTOMER_KY,A.TAKEN_DTE,A.CUST_COMMENT_SEQ,B.CUST_CMNT_TXT_SEQ as CMNT_SEQ ,
  4  'A' AS CMNT_ID,A.INFO_SRC,B.CUST_CMNT_TXT AS CMNT_TXT
  5  from CUST_COMMENT A join
  6  CUST_COMMENT_TXT B
  7  on A.CUSTOMER_KY=B.CUSTOMER_KY
  8  and A.TAKEN_DTE =B.TAKEN_DTE
  9  and A.CUST_COMMENT_SEQ=B.CUST_COMMENT_SEQ
 10  Union
 11  SELECT A.CUSTOMER_KY,A.TAKEN_DTE,A.CUST_COMMENT_SEQ,B.CMNT_RES_TXT_SEQ as CMNT_SEQ ,
 12  'B' as CMNT_ID,A.INFO_SRC,B.RESOLVED_DESC AS CMNT_TXT
 13  from CUST_COMMENT A join
 14  CUST_CMNT_RES_TXT B
 15  on A.CUSTOMER_KY=B.CUSTOMER_KY
 16  and A.TAKEN_DTE =B.TAKEN_DTE
 17  and A.CUST_COMMENT_SEQ=B.CUST_COMMENT_SEQ)X
 18* Order By X.CUSTOMER_KY,X.TAKEN_DTE,X.CUST_COMMENT_SEQ,X.CMNT_ID,X.CMNT_SEQ
SQL> /
and A.TAKEN_DTE =B.TAKEN_DTE
    *
ERROR at line 8:
ORA-00904: "A"."TAKEN_DTE": invalid identifier


SQL> 


Previous Topic: Please correct the update statement
Next Topic: How to check procedure compiled warnings & errors
Goto Forum:
  


Current Time: Fri Jul 25 13:30:50 CDT 2014

Total time taken to generate the page: 0.12526 seconds