Home » SQL & PL/SQL » SQL & PL/SQL » Error while executing the plsql block for mview refresh (oracle 11g)
Error while executing the plsql block for mview refresh [message #610435] Thu, 20 March 2014 01:41 Go to next message
narendra.treddi
Messages: 6
Registered: August 2007
Location: mumbai
Junior Member
Hi,

Here is the code iam using to refresh mviews in my schema.


declare
cursor mv_cur is select mview_name from user_mviews where rownum<=5;
mview_list dbms_utility.uncl_array;
begin
open mv_cur ;
fetch mv_cur
bulk collect into mview_list ;
close mv_cur ;
dbms_mview.refresh(mview_list, 'F' ) ;
end ;
/

iam getting the following error

Error report -
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at line 9
01403. 00000 - "no data found"
*Cause:
*Action:

even though it implemented the loop and used exit when nodata found still im getting the same error. Please help me what is the real cause for this .

Regards,
Narendra

Report message to a moderator

Re: Error while executing the plsql block for mview refresh [message #610438 is a reply to message #610435] Thu, 20 March 2014 01:58 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
You code does not include a loop: you open a cursor, and then close it.

Also, please read How to use [code] tags and make your code easier to read

[Updated on: Thu, 20 March 2014 02:05]

Report message to a moderator

Re: Error while executing the plsql block for mview refresh [message #610439 is a reply to message #610435] Thu, 20 March 2014 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the result of:
select mview_name from user_mviews where rownum<=5;
select owner, object_type from dba_objects where object_name='DUAL';

Report message to a moderator

Re: Error while executing the plsql block for mview refresh [message #610440 is a reply to message #610438] Thu, 20 March 2014 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
You code does not include a loop: you open a cursor, and then close it.


No need there is a bulk collect and dbms_mview.refresh can take an array as first parameter.

[Updated on: Thu, 20 March 2014 02:06]

Report message to a moderator

Re: Error while executing the plsql block for mview refresh [message #610441 is a reply to message #610440] Thu, 20 March 2014 02:06 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Thank you. Silly of me.

Report message to a moderator

Re: Error while executing the plsql block for mview refresh [message #610463 is a reply to message #610439] Thu, 20 March 2014 05:49 Go to previous messageGo to next message
narendra.treddi
Messages: 6
Registered: August 2007
Location: mumbai
Junior Member
result is there i got the blw

DSL
SVC_ORDER_STATUS
REGIONAL_LOCAL_SVC
EBONDING_SOLUTION
COMPANY

Report message to a moderator

Re: Error while executing the plsql block for mview refresh [message #610464 is a reply to message #610463] Thu, 20 March 2014 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And the second query?

Report message to a moderator

Re: Error while executing the plsql block for mview refresh [message #610467 is a reply to message #610464] Thu, 20 March 2014 07:13 Go to previous messageGo to next message
narendra.treddi
Messages: 6
Registered: August 2007
Location: mumbai
Junior Member
result of second query

select owner, object_type from dba_objects where object_name='DUAL';

SYS TABLE
PUBLIC SYNONYM

Report message to a moderator

Re: Error while executing the plsql block for mview refresh [message #610476 is a reply to message #610467] Thu, 20 March 2014 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Between the following 2 lines:
close mv_cur ;
dbms_mview.refresh(mview_list, 'F' ) ;

insert 
dbms_output.put_line(mview_list.count);


Use SQL*Plus and copy and paste the execution.
Before, Please read How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: Error while executing the plsql block for mview refresh [message #610778 is a reply to message #610476] Mon, 24 March 2014 05:01 Go to previous messageGo to next message
narendra.treddi
Messages: 6
Registered: August 2007
Location: mumbai
Junior Member
Iam getting the count results.

declare
cursor mv_cur is select mview_name from user_mviews where rownum<=5;
mview_list dbms_utility.uncl_array;
begin
open mv_cur ;
fetch mv_cur
bulk collect into mview_list ;
close mv_cur ;
dbms_output.put_line('no of mviews '||mview_list.count);
dbms_mview.refresh(mview_list, 'F' ) ;
end ;
/

Error starting at line : 3 in command -
declare
cursor mv_cur is select mview_name from user_mviews where rownum<=5;
mview_list dbms_utility.uncl_array;
begin
open mv_cur ;
fetch mv_cur
bulk collect into mview_list ;
close mv_cur ;
dbms_output.put_line('no of mviews '||mview_list.count);
dbms_mview.refresh(mview_list, 'F' ) ;
end ;
Error report -
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at line 10
01403. 00000 - "no data found"
*Cause:
*Action:
no of mviews 5

Report message to a moderator

Re: Error while executing the plsql block for mview refresh [message #610794 is a reply to message #610778] Mon, 24 March 2014 06:46 Go to previous message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What are your NLS settings?
Post the result of:
col parameter format a30
col value format a40
select * from v$nls_parameters;



Michel Cadot wrote on Thu, 20 March 2014 14:12
Between the following 2 lines:

Use SQL*Plus and copy and paste the execution.
Before, Please read How to use [code] tags and make your code easier to read.



FORMAT your post, otherwise no more help.

Report message to a moderator

Previous Topic: Please help for ORA-01416: two tables cannot be outer-joined to each other
Next Topic: How? - Create Check constraint - field must contain @ symbol
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 01:20:38 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.