"DA Morgan" <damorgan_at_psoug.org> schreef in bericht
news:1160152896.121354_at_bubbleator.drizzle.com...
> thtsang_yh_at_yahoo.com.hk wrote:
>> Robbert van der Hoorn ??:
>>
>>> 1) Do you have the ADVISOR role?
>>> 2) MV's on DUAL are impossible, for DUAL has no primary key.
>>> 3) Don't assign anything to name. It's returned.
>>> 4) Try this one (works for me):
>>>
>>> log in as system
>>> set serveroutput on
>>>
>>> run this:
>>>
>>> declare
>>> name varchar2(30);
>>> mv_sql varchar2(1000);
>>> begin
>>> mv_sql := 'create materialized view mvtest as select seq, topic, info
>>> from
>>> help where seq=1';
>>> DBMS_ADVISOR.TUNE_MVIEW(NAME, mv_sql);
>>> dbms_output.put_line(name);
>>> END;
>>> /
>>>
>>> Robbert van der Hoorn
>>
>> 1) Do you have the ADVISOR role?
>> Not sure. I have DBA role. I also granted ADVISOR role to myself with
>> SYS successfully. But I cannot see ADVISOR role in SESSION_ROLES and
>> USER_ROLE_PRIVS.
>> I also tried the samples with sys and system, the same thing happens.
>>
>> 2) MV's on DUAL are impossible, for DUAL has no primary key.
>> It is possible to create a primary key on DUAL.
>> BTW, my CREATE MATERIALIZED VIEW statement do work even without PK.
>> As I've said, the problem happens on other table. I am just using a
>> table that exists on every Oracle DB.
>>
>> 3) Don't assign anything to name. It's returned.
>> Why can't I assign anything to INOUT parameter?
>> Anyway, the same problem occur when I set nothing to it.
>>
>> 4) Try this one (works for me)
>> Same error.
>>
>> I also get the same error with Morgan's sample. I have neither SH
>> schema nor the HELP table. But I've created the tables and made sure
>> the CREATE MV statements do work.
>>
>> According to DBMS_ADVISOR docs, there are 4 possible values for
>> EXECUTION_TYPE. But the error message listed two only, missing out the
>> ones related to MV. Can this be the cause / symptom of the problem?
>
> SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 6 09:40:58 2006
>
> Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
>
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
> With the Partitioning, OLAP and Data Mining options
>
> SQL> set serveroutput on
> SQL> DECLARE
> 2 vname VARCHAR2(30); -- 'name' is a reserved word
> 3 mv_sql VARCHAR2(1000);
> 4 BEGIN
> 5 mv_sql := 'create materialized view mvtest as select srvr_id,
> network_id, status from servers
> where srvr_id=1';
> 6 DBMS_ADVISOR.TUNE_MVIEW(vname, mv_sql);
> 7 dbms_output.put_line(vname);
> 8 END;
> 9 /
> TASK_3747
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
> It is generally a bad idea to name variables after reserved words.
> --
> Daniel Morgan
> University of Washington
> Puget Sound Oracle Users Group
May be, but this is probably not the cause of the problem, because my
example works with Name.
You're right about reserved words in general, though.
Robbert van der Hoorn
OSA it BV
The Netherlands
Received on Fri Oct 06 2006 - 12:16:08 CDT
