Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PLS-00526

Posted on 2004-09-27
2
Medium Priority
?
554 Views
Last Modified: 2008-01-09
Hi All,

         I am receiving the below error when I try to compare collections. How to fix this one. The error is at compilation time.


v_col_AB_Info(1) := Ab_info(1,null,'ABC',1);
    v_col_AB_Info(2) := Ab_info(2,1,'DEF',2);
    v_col_AB_Info(3) := Ab_info(3,2,'GHIJ',3);
   
    v_col_AB_Info_1(1) := Ab_info(1,null,'ABC',1);
    v_col_AB_Info_1(2) := Ab_info(2,1,'DEF',2);
    v_col_AB_Info_1(3) := Ab_info(3,2,'GHIJ',3);

IF v_col_AB_Info(1) = v_col_AB_Info_1(1) THEN
   
        DBMS_OUTPUT.put_line('COLLECTIONS MATCH');
    ELSE
                DBMS_OUTPUT.put_line('COLLECTIONS DONOT MATCH');
    END IF;


PLS-00526 A MAP or ORDER function is required for comparing objects in PL/SQL.

Cause: Within stand alone PL/SQL, an attempt was made to compare objects without a map or order function.

Action: Provide a map or order function and retry the operation.

0
Comment
Question by:cavaturu
2 Comments
 
LVL 22

Assisted Solution

by:Helena Marková
Helena Marková earned 400 total points
ID: 12167014
The collection cannot be compared directly - here is a documentation (the registration is free):
http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/04_colls.htm#26859
0
 
LVL 13

Accepted Solution

by:
bochgoch earned 1600 total points
ID: 12169453
You need to compare each individual element, which isn't nice O'Reilly provide the following code to help:

CREATE OR REPLACE PACKAGE gen_record_comparison
IS
    -- Author  : SPENCER
    -- Created : 12/27/01 8:38:20 AM
    -- Purpose : create functions to test records for equality
   -- Public function and procedure declarations
   PROCEDURE make_package_compare (name_in IN VARCHAR2 := '%');

   
-- this function will require CREATE PROCEDURE priviledge
   PROCEDURE make_function_compare (
      name_in   all_objects.object_name%TYPE
   );
END gen_record_comparison;
/
CREATE OR REPLACE PACKAGE BODY gen_record_comparison
IS
   bdy_lines   DBMS_SQL.varchar2s;

   -- Private function and procedure implementations

-- generate the FUNCTION definition header string
--   common to package body and header and standalone function  
   FUNCTION gen_func_call (
      name_in        all_objects.object_name%TYPE,
      func_name_in   all_objects.object_name%TYPE
   )
      RETURN VARCHAR2
   IS
      v_str   VARCHAR2 (255);
   BEGIN
      RETURN    'FUNCTION '
             || func_name_in
             || '( A '
             || name_in
             || '%ROWTYPE , '
             || ' B '
             || name_in
             || '%ROWTYPE ) '
             || 'RETURN BOOLEAN ';
   END gen_func_call;

   
-- generate the FUNCTION definition body lines
--   common to package body and standalone function  
   PROCEDURE gen_func_body (
      name_in       all_objects.object_name%TYPE,
      rec_name_in   all_objects.object_name%TYPE
   )
   IS
      v_rec_name   all_objects.object_name%TYPE
                    := NVL (rec_name_in, name_in);
      v_line       PLS_INTEGER;
   BEGIN
      bdy_lines (  bdy_lines.LAST
                 + 1) := ' IS BEGIN RETURN (';

      FOR k IN  (SELECT   *
                     FROM user_tab_columns
                    WHERE table_name =
                                  UPPER (name_in)
                 ORDER BY column_id)
      LOOP
         v_line :=   bdy_lines.LAST
                   + 1;

         IF k.column_id > 1
         THEN
            bdy_lines (  v_line
                       - 1) :=
                  bdy_lines (  v_line
                             - 1)
               || ' AND ';
         END IF;

         bdy_lines (v_line) :=    '(( A.'
                               || k.column_name
                               || ' IS NULL AND B.'
                               || k.column_name
                               || ' IS NULL ) OR ';

         IF k.data_type LIKE '%LOB'
         THEN
            bdy_lines (  v_line
                       + 1) :=
                  'DBMS_LOB.COMPARE( A.'
               || k.column_name
               || ', B.'
               || k.column_name
               || ' ) = 0 )';
         ELSE
            bdy_lines (  v_line
                       + 1) :=    ' A.'
                               || k.column_name
                               || '= B.'
                               || k.column_name
                               || '  )';
         END IF;
      END LOOP;

      bdy_lines (  bdy_lines.LAST
                 + 1) := ') ;';
      bdy_lines (  bdy_lines.LAST
                 + 1) :=
                          'END '
                       || v_rec_name
                       || ';';
   END gen_func_body;

   
-- compile the generated PL/SQL  
   PROCEDURE COMPILE (lines DBMS_SQL.varchar2s)
   IS
      v_cur   PLS_INTEGER := DBMS_SQL.open_cursor;
   BEGIN
      DBMS_SQL.parse (
         v_cur,
         lines,
         lines.FIRST,
         lines.LAST,
         TRUE,
         DBMS_SQL.native
      );
      DBMS_SQL.close_cursor (v_cur);
   END COMPILE;

   
  -- Public function and procedure implementations
-- creates a package of overloaded COMPARE_RECORD.EQ functions
--   for each table and view in the current schema  
   PROCEDURE make_package_compare (name_in IN VARCHAR2 := '%')
   IS
      hdr_lines   DBMS_SQL.varchar2s;
      func_line   VARCHAR2 (255);
   BEGIN
      hdr_lines.DELETE;
      bdy_lines.DELETE;
      hdr_lines (1) :=
         'CREATE OR REPLACE PACKAGE COMPARE_RECORD IS ';
      bdy_lines (1) :=
         'CREATE OR REPLACE PACKAGE BODY COMPARE_RECORD IS ';

      FOR j IN  (SELECT object_name
                   FROM user_objects
                  WHERE object_name like UPPER (name_in)
                    AND object_type IN
                                ('VIEW', 'TABLE'))
      LOOP
         func_line :=
               gen_func_call (j.object_name, 'EQ');
         hdr_lines (  hdr_lines.LAST
                    + 1) :=    func_line
                            || ';';
         bdy_lines (  bdy_lines.LAST
                    + 1) := func_line;
         gen_func_body (j.object_name, 'EQ');
      END LOOP;

      hdr_lines (  hdr_lines.LAST
                 + 1) := 'END COMPARE_RECORD;';
      bdy_lines (  bdy_lines.LAST
                 + 1) := 'END COMPARE_RECORD;';
      COMPILE (hdr_lines);
      COMPILE (bdy_lines);
   END make_package_compare;

   
--Creates a standalone function in the current schema for one
--   table or view
   PROCEDURE make_function_compare (
      name_in   all_objects.object_name%TYPE
   )
   IS
      v_name   VARCHAR2 (30)
              :=    'EQ_'
                 || SUBSTR (name_in, 1, 27);
   BEGIN
      bdy_lines.DELETE;
      bdy_lines (1) :=    'CREATE OR REPLACE '
                       || gen_func_call (
                             name_in,
                             v_name
                          );
      gen_func_body (name_in, v_name);

      FOR k IN 1 .. bdy_lines.LAST
      LOOP
         DBMS_OUTPUT.put_line (bdy_lines (k));
      END LOOP;

      COMPILE (bdy_lines);
   END make_function_compare;
END gen_record_comparison;
/



/*======================================================================
| Supplement to the third edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2002 O'Reilly &
| Associates, Inc. To submit corrections or find more code samples visit
| http://www.oreilly.com/catalog/oraclep3/
*/
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question