Re: Problem with Export/Import of Views with function(s) in the GROUP BY Section

I've seen some problems importing stuff with "forward references" i.e. DB objects that refer to something that hasn't been imported yet or hasn't been granted access yet. In your example, I would guess that the view definition is being imported before the function definition so the view is invalid when imported.

When the invalid view is first accessed, Oracle should automatically recompile it. I seem to recall that this doesn't always work.

If possible, try importing twice, the first time with ROWS=Y and a second time with ROWS=N. The second import pass addresses objects with forward references, ROWS=N prevents table data from getting doubled up.

Finally, here's a script to recompile all invalid objects in a schema:

--
-- Recompile all invalid objects in this schema
--
-- usage: exec RECOMPILE_ALL
--

CREATE OR REPLACE PROCEDURE
   RECOMPILE (object_name VARCHAR2, object_type VARCHAR2) AS

   CURSOR OBJECT_ERROR_TEXT IS
   Select SUBSTR(TEXT, 1, 80)
   From User_ERRORS
   Where NAME = object_name;

   cid INTEGER;
   errors Boolean;
   error_text varchar2(80);

BEGIN
   DBMS_OUTPUT.PUT_LINE('=================================================');
   DBMS_OUTPUT.PUT_LINE('Compiling '||object_type||' '||object_name);

   /* Open new cursor and return cursor ID. */
   cid := DBMS_SQL.OPEN_CURSOR;

   /* Conditionally parse and execute DDL */
   if object_type = 'FUNCTION' then
      DBMS_SQL.PARSE(cid, 'ALTER FUNCTION "' ||object_name||'" COMPILE',
dbms_sql.v7);
   elsif object_type = 'PACKAGE' then
      DBMS_SQL.PARSE(cid, 'ALTER PACKAGE "' ||object_name||'" COMPILE PACKAGE',
dbms_sql.v7);
   elsif object_type = 'PACKAGE BODY' then
      DBMS_SQL.PARSE(cid, 'ALTER PACKAGE "' ||object_name||'" COMPILE BODY',
dbms_sql.v7);
   elsif object_type = 'PROCEDURE' then
      DBMS_SQL.PARSE(cid, 'ALTER PROCEDURE "' ||object_name||'" COMPILE',
dbms_sql.v7);
   elsif object_type = 'TRIGGER' then
      DBMS_SQL.PARSE(cid, 'ALTER TRIGGER "' ||object_name||'" COMPILE',
dbms_sql.v7);
   elsif object_type = 'VIEW' then
      DBMS_SQL.PARSE(cid, 'ALTER VIEW "' ||object_name||'" COMPILE',
dbms_sql.v7);
   end if;

   /* Close cursor. */
   DBMS_SQL.CLOSE_CURSOR(cid);

   errors := FALSE;

   OPEN Object_Error_Text;

   LOOP
      FETCH Object_Error_Text into Error_text;
      if Object_Error_Text%Found Then
  errors := TRUE;
  if error_text <> 'PL/SQL: Statement ignored' then
     DBMS_OUTPUT.PUT_LINE('>   '||error_text);
  End If;
      End If;
      Exit When Object_Error_Text%NotFound;
   END LOOP;

   CLOSE Object_Error_Text;

   If Errors then
      DBMS_OUTPUT.PUT_LINE(object_type||' '||object_name||' Compiled With
Errors');
   Else
      DBMS_OUTPUT.PUT_LINE(object_type||' '||object_name||' Compiled OK');
   End if;

EXCEPTION

   /* If an exception is raised, close cursor before exiting. */

   WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(cid);
      CLOSE Object_Error_Text;

END RECOMPILE;
/

CREATE OR REPLACE PROCEDURE RECOMPILE_ALL AS

   CURSOR INVALID_OBJECTS IS
   Select Object_name, Object_type
   From User_Objects
   Where Status = 'INVALID'
   ORDER by DECODE(Object_type,
      'PROCEDURE', 1,
      'FUNCTION', 2,
      'PACKAGE', 3,
      'PACKAGE BODY', 4,
      'TRIGGER', 5,
      'VIEW', 6,
 7);
Begin

   DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'MM/DD/YY HH24:MI:SS')||' ReComp V2.O
12/12/97');

   For REC in INVALID_OBJECTS LOOP

      RECOMPILE(REC.Object_name, REC.Object_type);

   End Loop;

END RECOMPILE_ALL;
/






Adam Tadjkarimi wrote:



> We are having problem with importing of exported views with functions in the

> GROUP BY section.  They get transferred but not compiled, and we have to

> manually correct it.  We are running V8.03 and V8.04 and problem occurs in

> either of these versions.  Here is the example:

>

> CREATE OR REPLACE VIEW a_view

>   ( a_col,  b_col,  c_col, d_col, a_sum) AS

>   SELECT a_col, b_col, a_fn (x_col) c_col,  b_fn (x_col,y_col) d_col,

>         SUM(amount) a_sum FROM a_table

> GROUP BY a_col, b_col, a_fn (x_col), b_fn (x_col,y_col) ;

>

> Any hints?  Thanks in advance.

>

> Adam Tadj

> adamt_at_hsltd.com

>

> Opinions expressed or questions asked are mine, not of my employer.