Toad World® Forums

Code


#1

declare

task_mv varchar2(30);

create_mv_ddl varchar2(4000);

begin

:task_mv :****= ‘mscitemsrc_mv’;

:create_mv_ddl :****= ‘create materialized view lawson.mscitemsrc refresh fast enable query rewrite as SELECT COMPANY, LOCATION, ITEM, REPL_FROM_CO, REPL_FROM_LOC, REPL_FROM_BIN FROM lawson.ITEMSRC ISRC WHERE ISRC.REPLENISH_PRI = (SELECT MIN(ISRC2.REPLENISH_PRI) FROM lawson.ITEMSRC ISRC2 WHERE ISRC2.COMPANY = ISRC.COMPANY AND ISRC2.LOCATION = ISRC.LOCATION AND ISRC2.ITEM = ISRC.ITEM)’ ;

dbms_advisor**.tune_mview(:task_mv,:create_mv_ddl);**

dbms_advisor**.create_file (dbms_advisor.get_task_script(:task_mv),‘TUNEME’,‘mv_create.sql’);**

end;

I am new to coding in SQLNav, and am having trouble understanding what I can do and can’t do in SQLEditor. I know I am missing something simple, can someone please help me get started. This gives me the below error.

10:21:12 *** SCRIPT START : Session:LAWSON@DLAW8.HEALTHALL.COM(1) 8-May-2007 10:21:12 ***
10:21:12 Start SQL Editor Execution …
10:21:12 Processing …
10:21:12 declare
10:21:12 *
10:21:12 ORA-06502: PL/SQL: numeric or value error: character string buffer too small
10:21:12 ORA-06512: at line 5
10:21:12 *** Script stopped due to error ***
10:21:12 *** SCRIPT END : Session:LAWSON@DLAW8.HEALTHALL.COM(1) 8-May-2007 10:21:12 ***
10:21:12 End SQL Editor Execution

Message was edited by: molonede


#2

I figured it out, sorry about the post.

declare
task_mv varchar2(30);
create_mv_ddl varchar2(4000);
begin
task_mv := ‘mscitemsrc_mv’;
dbms_advisor.delete_task(‘mscitemsrc_mv’);
create_mv_ddl := ‘create materialized view lawson.mscitemsrc refresh fast enable query rewrite as SELECT COMPANY, LOCATION, ITEM, REPL_FROM_CO, REPL_FROM_LOC, REPL_FROM_BIN FROM lawson.ITEMSRC ISRC WHERE ISRC.REPLENISH_PRI = (SELECT MIN(ISRC2.REPLENISH_PRI) FROM lawson.ITEMSRC ISRC2 WHERE ISRC2.COMPANY = ISRC.COMPANY AND ISRC2.LOCATION = ISRC.LOCATION AND ISRC2.ITEM = ISRC.ITEM)’ ;
dbms_advisor.tune_mview(task_mv,create_mv_ddl);
dbms_advisor.create_file (dbms_advisor.get_task_script(task_mv),‘TUNEME’,‘mv_create.sql’);
end;


#3

Hi
This script works for me. Of course i chanched materialized view and directory, and it works.
What do u have in 5’th line of your script? Or attach your script as text file, not paste into message.

Regards.


#4

Thanks for looking, but I did post a response that indicated I figured out what I did wrong. Have a good day!