Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509

    Unanswered: DB link in cursor generates ORA-06550

    I am having something weird happening and I can't figure out what is going on. The Client and database server is 8.1.7.4.0 . If I run a script that I have written:


    Code:
    DECLARE
     
    CURSOR checkemps IS
    SELECT me.EMP_NR,
           me.FNAME,
           me.MI,
           me.LNAME,
           me.STATUS,
           me.STORE_CD,
           me.TITLE,
           me.TERMDATE,
           me.HIREDATE,
           me.EMP_CD,
           me.EMP_CD_TENT,
           mec.EMP_NR X_EMP_NR,
           mec.FNAME X_FNAME,
           mec.MI X_MI,
           mec.LNAME X_LNAME,
           mec.STATUS X_STATUS,
           mec.STORE_CD X_STORE_CD,
           mec.TITLE X_TITLE,
           mec.TERMDATE X_TERMDATE,
           mec.HIREDATE X_HIREDATE,
           mec.EMP_CD X_EMP_CD,
           mec.EMP_CD_TENT X_EMP_CD_TENT
    FROM MASTER_EMPLOYEE@DB_LINK_XXXXX me, MASTER_EMPLOYEE_CHANGES@DB_LINK_XXXXX mec
    WHERE (me.EMP_NR=mec.EMP_NR(+))
    ORDER BY me.lname,me.fname,me.mi;
       
       l_output    utl_file.file_type;
     
    BEGIN
       -- Open up the message body file
       l_output := utl_file.fopen( '/gers/test/adhoc','mailbody.txt', 'w');
       cnt := 0;
       FOR PNT IN CHECKEMPS LOOP
          -- Is it a new employee?
          IF PNT.x_emp_nr IS NULL THEN
             utl_file.put_line(l_output, '<<New Employee>>');
             utl_file.put_line(l_output, '  EMP #'||to_char(pnt.emp_nr));
             utl_file.put_line(l_output, '  Name :'||rtrim(pnt.fname)||' '||rtrim(pnt.mi)||' '||rtrim(pnt.lname));
             utl_file.put_line(l_output, '  Status :'||pnt.status);
             utl_file.put_line(l_output, '  Store Code :'||pnt.store_cd);
             utl_file.put_line(l_output, '  Title :'||pnt.title);
             utl_file.put_line(l_output, '  Hire date :'||to_char(pnt.hiredate,'MM/DD/YYYY'));
             utl_file.put_line(l_output, '  Emp_Cd :'||pnt.EMP_CD);
             utl_file.put_line(l_output, '  Emp_Cd_Tent :'||pnt.EMP_CD_TENT);
             INSERT INTO master_employee_changes@DB_LINK_XXXXX (EMP_NR,FNAME,MI,LNAME,STATUS,STORE_CD,
                                                  TITLE,TERMDATE,HIREDATE,EMP_CD,EMP_CD_TENT)
                                          VALUES (PNT.EMP_NR,PNT.FNAME,PNT.MI,PNT.LNAME,PNT.STATUS,PNT.STORE_CD,
                                                  PNT.TITLE,PNT.TERMDATE,PNT.HIREDATE,PNT.EMP_CD,PNT.EMP_CD_TENT);
          -- Is it a terminate employee
          ELSIF (pnt.status = 'A' AND pnt.x_status = 'T') OR
                (PNT.TERMDATE IS NULL AND pnt.x_termdate IS NOT NULL)  THEN
                   utl_file.put_line(l_output, '<<Termed Employee>>');
                   utl_file.put_line(l_output, '  EMP #'||to_char(pnt.emp_nr));
                   utl_file.put_line(l_output, '  Name :'||rtrim(pnt.fname)||' '||rtrim(pnt.mi)||' '||rtrim(pnt.lname));
                   utl_file.put_line(l_output, '  Store Code :'||pnt.store_cd);
                   utl_file.put_line(l_output, '  Title :'||pnt.title);
                   utl_file.put_line(l_output, '  Hire date :'||to_char(pnt.hiredate,'MM/DD/YYYY'));
                   utl_file.put_line(l_output, '  Emp_Cd :'||pnt.EMP_CD);
                   utl_file.put_line(l_output, '  Emp_Cd_Tent :'||pnt.EMP_CD_TENT);
                 UPDATE master_employee_changes@DB_LINK_RAYORA
                    SET FNAME       = pnt.fname,
                        MI          = pnt.MI,
                        LNAME       = pnt.LNAME,
                        STATUS      = PNT.STATUS,
                        STORE_CD    = PNT.STORE_CD,
                        TITLE       = PNT.TITLE,
                        TERMDATE    = PNT.TERMDATE,
                        HIREDATE    = PNT.HIREDATE,
                        EMP_CD      = PNT.EMP_CD,
                        EMP_CD_TENT = PNT.EMP_CD_TENT
                    WHERE EMP_NR = PNT.EMP_NR;
          -- Is it simply a new modification>
          ELSIF (NVL(PNT.FNAME,'@@@') <> NVL(PNT.X_FNAME,'@@@')) OR
                (NVL(PNT.MI,'@') <> NVL(PNT.X_MI,'@')) OR
                (NVL(PNT.LNAME,'@@@') <> NVL(PNT.X_LNAME,'@@@')) OR
                (NVL(PNT.STATUS,'@') <> NVL(PNT.X_STATUS,'@')) OR
                (NVL(PNT.STORE_CD,'@@') <> NVL(PNT.X_STORE_CD,'@@')) OR
                (NVL(PNT.TITLE,'@@@') <> NVL(PNT.X_TITLE,'@@@')) OR
                (NVL(PNT.TERMDATE,TO_DATE('31-DEC-4712','DD-MON-YYYY')) <> NVL(PNT.X_TERMDATE,TO_DATE('31-DEC-4712','DD-MON-YYYY'))) OR
                (NVL(PNT.HIREDATE,TO_DATE('31-DEC-4712','DD-MON-YYYY')) <> NVL(PNT.X_HIREDATTE('31-DEC-4712','DD-MON-YYYY'))) OR
                (NVL(PNT.EMP_CD,'@E,TO_DA@@@@') <> NVL(PNT.X_EMP_CD,'@@@@@')) OR
                (NVL(PNT.EMP_CD_TENT,'@@@@@') <> NVL(PNT.X_EMP_CD_TENT,'@@@@@')) THEN
             utl_file.put_line(l_output, '<<Modified Employee>>');
             IF (NVL(PNT.FNAME,'@@@') <> NVL(PNT.X_FNAME,'@@@')) OR
                (NVL(PNT.MI,'@') <> NVL(PNT.X_MI,'@')) OR
                (NVL(PNT.LNAME,'@@@') <> NVL(PNT.X_LNAME,'@@@')) THEN
                utl_file.put_line(l_output, '  Name Changed From :'||rtrim(pnt.fname)||' '||rtrim(pnt.mi)||' '||rtrim(pnt.lname));
                utl_file.put_line(l_output, '  Name Changed To   :'||rtrim(pnt.x_fname)||' '||rtrim(pnt.x_mi)||' '||rtrim(pnt.x_lname));
             ELSE
                utl_file.put_line(l_output, '  Name :'||rtrim(pnt.fname)||' '||rtrim(pnt.mi)||' '||rtrim(pnt.lname));
             END IF;
             IF (NVL(PNT.STATUS,'@') <> NVL(PNT.X_STATUS,'@')) THEN
                utl_file.put_line(l_output, '  Status Change From "'||rtrim(pnt.status)||'" to "'||rtrim(pnt.x_status)||'"');
             END IF;
             IF (NVL(PNT.STORE_CD,'@@') <> NVL(PNT.X_STORE_CD,'@@')) THEN
                utl_file.put_line(l_output, '  Store Code Change From "'||rtrim(pnt.store_cd)||'" to "'||rtrim(pnt.x_store_cd)||'"');
             END IF;
             IF (NVL(PNT.TITLE,'@@@') <> NVL(PNT.X_TITLE,'@@@')) THEN
                utl_file.put_line(l_output, '  Title Change From "'||rtrim(pnt.title)||'" to "'||rtrim(pnt.x_title)||'"');
             END IF;
             IF (NVL(PNT.TITLE,'@@@') <> NVL(PNT.X_TITLE,'@@@')) THEN
                utl_file.put_line(l_output, '  Title Change From "'||rtrim(pnt.title)||'" to "'||rtrim(pnt.x_title)||'"');
             END IF;
             IF (NVL(PNT.TERMDATE,TO_DATE('31-DEC-4712','DD-MON-YYYY')) <> NVL(PNT.X_TERMDATE,TO_DATE('31-DEC-4712','DD-MON-YYYY'))) THEN
                utl_file.put_line(l_output, '  Termination Date From "'||to_char(pnt.termdate,'MM/DD/YYYY')||'" to "'||to_char(pnt.X_termdate,'MM/DD/YYYY')||'"');
             END IF;
             IF (NVL(PNT.HIREDATE,TO_DATE('31-DEC-4712','DD-MON-YYYY')) <> NVL(PNT.X_HIREDATE,TO_DATE('31-DEC-4712','DD-MON-YYYY'))) THEN
                utl_file.put_line(l_output, '  Hire Date From "'||to_char(pnt.hiredate,'MM/DD/YYYY')||'" to "'||to_char(pnt.X_hiredate,'MM/DD/YYYY')||'"');
             END IF;
             IF (NVL(PNT.EMP_CD,'@@@') <> NVL(PNT.X_EMP_CD,'@@@')) THEN
                utl_file.put_line(l_output, '  Emp_Cd Change From "'||rtrim(pnt.emp_cd)||'" to "'||rtrim(pnt.x_emp_cd)||'"');
             END IF;
             IF (NVL(PNT.EMP_CD_TENT,'@@@') <> NVL(PNT.X_EMP_CD_TENT,'@@@')) THEN
                utl_file.put_line(l_output, '  Emp_Cd_Tent Change From "'||rtrim(pnt.emp_cd_tent)||'" to "'||rtrim(pnt.x_emp_cd_tent)||'"');
             END IF;
             UPDATE master_employee_changes@DB_LINK_XXXXX
             SET FNAME       = pnt.fname,
                 MI          = pnt.MI,
                 LNAME       = pnt.LNAME,
                 STATUS      = PNT.STATUS,
                 STORE_CD    = PNT.STORE_CD,
                 TITLE       = PNT.TITLE,
                 TERMDATE    = PNT.TERMDATE,
                 HIREDATE    = PNT.HIREDATE,
                 EMP_CD      = PNT.EMP_CD,
                 EMP_CD_TENT = PNT.EMP_CD_TENT
             WHERE EMP_NR = PNT.EMP_NR;
          END IF;
       END LOOP;
       utl_file.fclose( l_output );
       COMMIT;
    END;
    I am getting the following errors

    Code:
    SQL> @c:\sql\newem.sql
    FROM MASTER_EMPLOYEE@DB_LINK_XXXXX me, MASTER_EMPLOYEE_CHANGES@DB_LINK_XXXXX mec
         *
    ERROR at line 26:
    ORA-06550: line 26, column 6:
    PLS-00201: identifier 'MASTER_EMPLOYEE@DB_LINK_XXXXX' must be declared
    ORA-06550: line 4, column 1:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 35, column 4:
    PLS-00201: identifier 'CNT' must be declared
    ORA-06550: line 35, column 4:
    PL/SQL: Statement ignored
    ORA-06550: line 38, column 10:
    PLS-00364: loop index variable 'PNT' use is invalid
    ORA-06550: line 38, column 7:
    PL/SQL: Statement ignored
    If I run the select that the cursor is based on as a standalone select, it works correctly. Any ideas?


    Code:
    SELECT me.EMP_NR,
           me.FNAME,
           me.MI,
           me.LNAME,
           me.STATUS,
           me.STORE_CD,
           me.TITLE,
           me.TERMDATE,
           me.HIREDATE,
           me.EMP_CD,
           me.EMP_CD_TENT,
           mec.EMP_NR X_EMP_NR,
           mec.FNAME X_FNAME,
           mec.MI X_MI,
           mec.LNAME X_LNAME,
           mec.STATUS X_STATUS,
           mec.STORE_CD X_STORE_CD,
           mec.TITLE X_TITLE,
           mec.TERMDATE X_TERMDATE,
           mec.HIREDATE X_HIREDATE,
           mec.EMP_CD X_EMP_CD,
           mec.EMP_CD_TENT X_EMP_CD_TENT
    FROM MASTER_EMPLOYEE@DB_LINK_XXXXX me, MASTER_EMPLOYEE_CHANGES@DB_LINK_XXXXX mec
    WHERE (me.EMP_NR=mec.EMP_NR(+))
    ORDER BY me.lname,me.fname,me.mi;
    Last edited by beilstwh; 12-27-04 at 14:15.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Variable 'cnt' isn't declared at all.

    Declare it in the DECLARE section and try to execute your PL/SQL procedure again. If it doesn't work, post another error message. Then we'll see what to do next.

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Sounds like the user you are using to connect to the linked database doesn't have SELECT privs on the target table. What does your "CREATE DATABASE LINK" statement look like?
    JoeB
    save disk space, use smaller fonts

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by Littlefoot
    Variable 'cnt' isn't declared at all.

    Declare it in the DECLARE section and try to execute your PL/SQL procedure again. If it doesn't work, post another error message. Then we'll see what to do next.
    I already removed the CNT line, the error is the same.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by joebednarz
    Sounds like the user you are using to connect to the linked database doesn't have SELECT privs on the target table. What does your "CREATE DATABASE LINK" statement look like?
    The user in the DB link is fine. If I issue the select that is attached to the cursor checkemps, it works correctly and returns all the rows. It is only when it's included in the cursor statement that it fails. If this is a pl/sql error, has anyone seen it before?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK. What happens when you simplify (but really simplify) your procedure? For example:
    DECLARE
    cursor cur is select one_field from db_link_table;
    BEGIN
    FOR cur_r IN cur LOOP
    null;
    END LOOP;
    END;

    Does it show the same message?

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The error is the same. The sql@plus client on my workstation is 8.1.7.0.0, I am going to try iupgrading my client to the terminal release to see if this is a bug.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I have applied the terminal patches for Oracle 8i, and it made no difference. If the select is inside an sql ananymous block, it fails. If it is run outside a block, it works. I have also tried to see if it is a rights problem. I ran the script as SYSTEM and it still failed.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Privs acquired via roles, which "work" in SQL*Plus, do NOT exist inside PL/SQL procedures.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Really? How odd ... didn't know that. Could you tell - in a few words - why, Anacedent?

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    When all else fails RTFM
    http://download-west.oracle.com/docs...privs.htm#4770
    The short answer is because that is how Oracle implemented their DB & PL/SQL.
    ================================================== ===
    Named Blocks with Definer Rights

    All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer rights. Roles are not used for privilege checking and you cannot set roles within a definer-rights procedure.

    The SESSION_ROLES view shows all roles that are currently enabled. If a named PL/SQL block that executes with definer rights queries SESSION_ROLES, the query does not return any rows.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Thats all true, but I am not running a stored procedure. I was running a sql script stored as a flat file on my workstation that contains a "Declare;begin;end" block. This is really frustrating! I also tried the following

    SET ROLE NONE;
    Issued the bare select, it still worked.
    issued the @c:\sql\newem.sql - Still failed with the above error.


    Oh, if I didn't mention it before, the database link is setup as a public link and the user defined in the link owns the tables that I need to query,
    Last edited by beilstwh; 12-28-04 at 14:55.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  13. #13
    Join Date
    Oct 2004
    Posts
    145
    Silly question.

    Why are you joining two remote tables in a select statement?
    Do you not have the ability to create a view that joins two tables on the other side?

    Also noticed you are update the table you are querying did you try executing the pl/sql block without the update statement? If you have not done this, can you try the script (pl/sql block) without the update statement to see if it give you an error.

    I am wondering with the error line numbers if it is true the select statement that is causing you grief.

  14. #14
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by JimYoo
    Silly question.

    Why are you joining two remote tables in a select statement?
    Do you not have the ability to create a view that joins two tables on the other side?

    Also noticed you are update the table you are querying did you try executing the pl/sql block without the update statement? If you have not done this, can you try the script (pl/sql block) without the update statement to see if it give you an error.

    I am wondering with the error line numbers if it is true the select statement that is causing you grief.
    1)What I am doing is to query the employee tables on a remote database, detect any changes since the last query, track the changes in the remote change table, and create a flat file that will be inserted into a mail system on the local server to be sent to the help desk so that they are notified of new, terminated, and modified users.

    2) yes I tried to remove the updates, as a matter of fact I removed everything except the cursor statement and begin;null;end; , it still errored.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  15. #15
    Join Date
    Oct 2004
    Posts
    145
    Have you tried breaking down the SQL statement as well? ie. selecting from single table without the where and order by clause? It seems like you isolated the problem to the cursor and the where

    Only time, I have seem cursor problems (when sqlplus works) is database links through sybase gateway but error is not similar to what you have encountered. I be curious to see if client upgrade would solve this problem. I would have assumed it would not be dependent on client version as database links should be handled from DB to DB.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •