Solved

PLS-00526

Posted on 2004-09-27
2
484 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 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
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…

746 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now