Solved

PLS-00526

Posted on 2004-09-27
2
511 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 22

Assisted Solution

by:Helena Marková
Helena Marková earned 100 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 400 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 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