Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PLS-00526

Posted on 2004-09-27
2
Medium Priority
?
542 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 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

715 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