Materialized View

[ExecMan]

Hi,

Can a materialized view be created based on an object table like
this. I have a PL/SQL procedure which creates this object table and
populates it, then I want to create a materialized view from it:

EXECUTE IMMEDIATE 'CREATE MATERIALIZED VIEW inst_holdings_mv AS
SELECT * FROM TABLE(CAST(v_inst_hold_rec AS
inst_holdings_table))';

I get an error:

ERROR at line 1:
ORA-00904: "V_INST_HOLD_REC": invalid identifier
ORA-06512: at line 44

[ddf]

v_inst_hold_rec is out of scope for the execute immediate command.


David Fitzjarrell

[ExecMan]

David,

Not sure what you mean by "out of scope". Is my syntax wrong?

[Mladen Gogala]

On Wed, 16 May 2012 19:11:50 -0700, ExecMan wrote:

> David,
>
> Not sure what you mean by "out of scope". Is my syntax wrong?

Apparently it is, when an error is thrown.



--
http://mgogala.byethost5.com

[ExecMan]

You are so very helpful. Fortunately for me it is the same syntax I
use when I am dealing with normal tables, so it is something that may
be related to Materialized Views.

Let someone else who has something to contribute supply a suggestion.

[Mladen Gogala]

On Thu, 17 May 2012 08:24:42 -0700, ExecMan wrote:


> Let someone else who has something to contribute supply a suggestion.

Good luck!



--
http://mgogala.freehostia.com

[dombrooks]

Can you show the same approach working with a normal table?

1. I don't see why anyone should ever want to do this whether creating tables or MVs.

2. If this was going to work you'd need to pass a bind variable, surely. The SQL Engine knows nothing about v_inst_hold_rec.

3. You can't pass a bind variable because you'd get ORA-01027: bind variables not allowed for data definition operations.


So, I'm very interested to see what you're doing when you say it works with normal tables.

[ExecMan]

This is all done in a PL/SQL procedure. Sorry if I did not indicate
that before........

[Mladen Gogala]

On Thu, 17 May 2012 09:35:42 -0700, ExecMan wrote:


> This is all done in a PL/SQL procedure. Sorry if I did not indicate
> that before........

You're creating a materialized view in a PL/SQL procedure?



--
http://mgogala.byethost5.com

[ddf]

Using EXECUTE IMMEDATE. The PL/SQL variable you are trying to pass in
the execute immediate text does not exist in the SQL context you
create with the execute immediate call -- thus it's out of scope for
SQL as it has no idea what you are referring to. It's basically the
same talking on the telephone to someone across town and saying 'Did
you see that?' while looking at an event outside of your window; the
person across town doesn't have your viewpoint and can't relate to the
question or the subject of it.


David Fitzjarrell

[Mladen Gogala]

This looks like one of those "object factory" philosophical things. That
reminds me why I do not adopt programming paradigms in place of
religions. There is but one God and Tom Kyte is his prophet.



--
http://mgogala.byethost5.com

[dombrooks]

>
> This is all done in a PL/SQL procedure. Sorry if I did not indicate
> that before........

Well, that's obvious from the presence of EXECUTE IMMEDIATE.

As I said, prove how you're doing it with a normal table.

The points I made above still apply.

You can't pass a plsql variable to the sql engine without using a bind. You can't pass a bind to dynamic DDL, you'll get the error I mentioned.

[Gerard H. Pille]

You're wasting your precious breath, David, if the man doesn't realize he's switching from
PL/SQL to SQL when calling "execute immediate" ...

[joel garry]

It really isn't obvious what is PL and what is SQL, everyone has to be
edakated about it. The grammar isn't always explicit about what is
procedural and what is not. For example, AVG is a PL/SQL keyword, can
be used as an aggregate, and can be used in analytics. But not
always, like in ADRCI. There's something new I learned today.

jg
--
@home.com is bogus.
When all is going to fail, blame your predecessor:
http://www.utsandiego.com/news/2012/may/17/reports-hp-poised-to-eliminate-up-to-30000-jobs/

[John Hurley]

Joel:

#  For example, AVG is a PL/SQL keyword, can be used as an aggregate,

and can be used in analytics.  But not always, like in ADRCI.  There's
something new I learned today.

Give us a specific full example or it did not happen ...

[joel garry]

Nothing happened, you can find it in the Utilities manual.

jg
--
@home.com is bogus.

http://news.cnet.com/8301-1001_3-57437371-92/oracle-google-another-juror-falls-as-impasse-looms-again/

[John Hurley]

Joel:

# Nothing happened, you can find it in the Utilities manual.

Don't use adrcl much except for some automated purging of stuff that I
don't want to keep around anymore. Can be done in multiple ways like
finding create/mod times of a file or thru adrcl.

Sorry you lost me ... when do you want to use AVG invoking stuff thru
adrcl?

[Mladen Gogala]

On Fri, 18 May 2012 15:31:12 -0700, John Hurley wrote:

> Don't use adrcl much except for some automated purging of stuff that I
> don't want to keep around anymore.

I must say that I like it. Typing is a drag.

tail -f $ORACLE_BASE/diag/rdbms/O11/O11/trace/alert_O11.log

Also, listing tracefiles is very convenient:

[oracle@medo ~]$ adrci

ADRCI: Release 11.2.0.3.0 - Production on Sat May 19 13:51:02 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights
reserved.

ADR base = "/oracle"
adrci> show tracefile %lgwr%
diag/rdbms/o11/O11/trace/O11_lgwr_31810.trc
diag/rdbms/o11/O11/trace/O11_lgwr_6001.trc
diag/rdbms/o11/O11/trace/O11_lgwr_11502.trc

Of course, purging is also convenient.
--

http://mgogala.byethost5.com

[joel garry]

I dunno, I was just coming up with an example of how it is not always
obvious whether something is PL or SQL and ran across a third case
with the same keyword that I hadn't seen mentioned anywhere. I
suppose whoever wrote adrci thought being able to come up with
averages in reports from log files might be something someone would
want to do.

jg
--
@home.com is bogus.

http://www.crn.com/news/data-center/240000620/oracle-hp-release-documents-that-paint-ugly-pictures-of-each-other.htm