SQL & PL/SQL :: Create View In Another View?

Dec 1, 2011

Can we create view in another view

View 10 Replies


ADVERTISEMENT

SQL & PL/SQL :: Create Primary Key On View And Use This View For Creating Foreign Key?

Oct 8, 2010

is it possible to create primary key on view and use this view for creating foreign key .

View 3 Replies View Related

Server Administration :: How To Change Existing Materialized View To Normal View

Jan 17, 2013

can we change the existing materialized view to normal view? if yes how?

View 2 Replies View Related

Creating Materialized View On Remote Simple View

Dec 11, 2012

I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.

A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.

Is there another work around for this situation without having to work inside the data sources' environment?

View 5 Replies View Related

SQL & PL/SQL :: Change View Definition When Select Is Running On That View

Apr 25, 2012

We wrote one data load process to load GZ files into Database.during process we will change client facing view definitions to backup table so that we can work on base tables.

This view definition changes are related to FROM and WHERE clause (not columns/type). during load process, client/user may connect to current server and accessing these views. My question is what will be the reflection of changing view definition while user is accessing view?

I created a scenario-
STEP1: Created a view-
create or replace view view_01 as
select object_name from dba_objects union all
select object_name from dba_objects union all
select object_name from dba_objects union all
[code]....

View definition is replaced by new definition while select is executing on that view. select returned number of records as per view definition one.

View 6 Replies View Related

SQL & PL/SQL :: How To Create View

Feb 25, 2011

I want to create view as follows.

Example:

Create or replace v_name
select job,sal, <funcation>
from emp;

Note:- Funcation returning 4 values.

Requirement:- I want to create view 6 columns.

how to create view.

View 3 Replies View Related

SQL & PL/SQL :: How To Create A View Using Five Tables

Oct 30, 2012

I am trying to create view ..Using totally Five tables....

The main driven table almost 10 GB Data

When i am creating a view..

End of communication channel Error raise...

At the same time I am running query ..Running Fine...

View 5 Replies View Related

SQL & PL/SQL :: Create View With Functions

Jan 17, 2011

I have created a package with some functions in it.I want to create a view with from 2 tables but also i wanto to use the functions from some fields of the view. (e.g. if the wview has 10 fields the 5 of them will be from the 2 tables and the other from package functions).

View 2 Replies View Related

PL/SQL :: Create Or Replace View

Sep 11, 2012

I am about to pass 1Z0-051 and have been cramming. Ran across this scenario: Create or replace view. Correct answer: allows insert from view in multitable insert statement. Can I actually insert data from a view? My understanding is no data actually exists in a view. This has created a cavernous feeling of inadequacy.

View 3 Replies View Related

SQL & PL/SQL :: Oracle View Have Dynamic View Function?

Jul 28, 2010

Are oracle view have Dynamic view function?

View 8 Replies View Related

SQL & PL/SQL :: Materialized View - Table Or View Does Not Exist

May 2, 2012

I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.

EXEC DBMS_MVIEW.REFRESH('pro_mview','C');

But I am getting the below error.

*
Error at line 1:
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 1

I am able to fetch the data from that materialized view(pro_mview).

View 3 Replies View Related

Create View Off Split String

May 22, 2012

How do i split a string based on a space. And then create a view with three columns of that split string.

For example if i have a table with Full name as a column how do i split that string and create a view with firstname, middlename, lastname as column names with the split string as data.

If you need anything clarifying write back.

View 1 Replies View Related

How To Create Index On Materialized View

Aug 6, 2010

How to create index on materialized view which is refreshed daily basis.

View 3 Replies View Related

SQL & PL/SQL :: Create A Table Through Inline View?

Oct 1, 2010

there is a diff. problem for me.when i create table through inline view then it shows 2246 records but if i check these records only in select statement then it shows 124 records. i cant understand how table shows 2246 records even then atual records in inline view shows only 124 records.

following is a query

create table sam as
select * from
((
select distinct stck.item_code
from (
select item_code,bal

[code]...

View 4 Replies View Related

SQL & PL/SQL :: Create View Without Base Table?

Jun 25, 2012

How to create a view without base table . some example?

View 8 Replies View Related

SQL & PL/SQL :: Currency Conversion In SQL To Create A View

Apr 27, 2012

I need to implement the Currency Conversion logic in SQL to create a view.

Sample data

Acount Table

Accno Amt Crncy_id
--------------------
101 1000 1
102 500 2
103 500 2
104 500 3
107 500 4
.
.
.
Currency Table

Crncy_id Crncy_nm
-------------------------
1 INR
2 USD
3 GBP
4 EUR

My local currency is INR, I need to write a query to convert the foreign currency to loacl currency INR.In my case i need to write aSQL for a view.It should not implement through PL/SQL

View 6 Replies View Related

SQL & PL/SQL :: How To Create A View Having All Rows From Tables

Apr 21, 2010

I have a table as shown below,

SQL> select * from Main_Table;

AA
--------------------
SUB1
SUB2

Here the SUB1 and SUB2 are "tables" and are similar in their structure. The "Main_Table" will be update dynamically and the no of rows in this table will vary.

Now my question , i need to create a view which will have all the rows from these tables ,in the current case it is something like

create or replace view sample
as
select * from SUB1 union all
select * from SUB2

How can this be achived. I tried as shown below:

spool file_to_exe.sql
select 'select * from ' || AA ||' union all ' from Main_table;
spool off

i will end up in a union all "extra" , if i do like this.

how can achieve this ?..

View 5 Replies View Related

How To Create Unique Index On A View

Aug 24, 2012

11.2.0.1...How do I create an index on a view or any workaround that my view won't get duplicates?

SQL> create unique index indx01 on db_backup_details_vw(id);
create unique index indx01 on db_backup_details_vw(id)
*
ERROR at line 1:
ORA-01702: a view is not appropriate here

View 7 Replies View Related

SQL & PL/SQL :: How To Create Materialized View On Partition Table

May 16, 2012

how to create materialized view on partition table?

View 1 Replies View Related

Replication :: Oracle 10g - Create Materialized View

Jun 16, 2008

We are using Oracle 10g rel 2. The replication is setup on 1 server which is in City A, and the snapshot server is in City B.

City A .
Create materialized view log on table-a
with primary key
including new values.

City B database.
Create user test_rep identified by test
grant connect, resource, create any materialized view , table, view , procedure to test_rep.

Create materialized view city-A_db_MV
refresh fast
select * from cityA.Tablea@city-Adb

When i select from city-A_db_MV, it showed the complete table-A of city-A database.

Now if we make any changes to City-A table at the Master site, will it be propagated automatically to the MV site.

I guess we need to create jobs to push / or refresh fast .. isnt it. But exactly how to do it is a question.

Secondly if we make a replication group at Master site at city-A db, how do we refresh that Group and how to monitor whether it is refreshing on time or not? do we need to see the jobs every now and then.

but still a lot of questions unanswered, even though i had read the documents earlier.

1-The MView was created without identifying that after what interval it will be fast refreshed.
2- How to Manually refresh it. Does it support On Commit, I think it is not.
3- Where should be we make a group and then add the table to that group and refresh that group.

Should this group belongs to the Master Site or to MV site?

View 1 Replies View Related

SQL & PL/SQL :: Cannot Create Materialized View - Insufficient Privileges

Aug 3, 2010

i am trying to create materialized view but it give me insufficient privilege i give it all grant and i run it from sys it give the same.

View 3 Replies View Related

Replication :: Unable To Create Materialized View

Mar 13, 2008

I have to create a materialized view for a table which does not have index on any field.

While creating a Mview i am getting an error "TABLE DOES NOT HAVE THE PRIMARY KEY CONSTRAINT".

application developers do not want to create an index on the base table onto which MView is to be created.

is there any way to create a materialised view for the table without index, or is it necessary to have the index on the base table before creating MView on it.

View 1 Replies View Related

SQL & PL/SQL :: Trigger To Execute Immediate Create Or Replace View?

Jan 30, 2013

i try to make trigger to execute immediate create or replace view

(
CREATE OR REPLACE TRIGGER xxxx
BEFORE INSERT ON table1
REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW
declare
l number ;
v_ddl varchar2(4000);
v_job number;
[code]....

it give me error for insuffition privilage in execute immediate and after i make GRANT CREATE ANY TRIGGER TO user give me error can't commit in trigger.

View 8 Replies View Related

Replication :: Create Materialized View Without Having Any Data In It

Nov 5, 2008

How I can create a Materialized View without having any data in it.

For e.g.

I create a Materialialized View based on a View.

CREATE MATERIALIZED VIEW test_mv
REFRESH FORCE ON DEMAND
AS
SELECT * FROM test_view

In the above case the data fetched by the view test_view gets stored in the Materialized View test_mv. Suppose I want materialized view test_mv to get created with all the columns of test_view but not the data. I will refresh the materialized view test_mv later for data as and when required.

What shall I do for immidiate formation of materialized view test_mv without data.

View 4 Replies View Related

Server Administration :: Create View With Parameters?

Oct 26, 2011

I created a view with parameters l_id and l_name, how can i find them in oracle view?

Create Table tb_test
(
Id Number,
Name Varchar2(64)
);

Create Or Replace View vw_tb_test(l_id,l_name)
As
Select Id,Name From tb_test;

View 3 Replies View Related

SQL & PL/SQL :: Create A View To Show Counter For Occurrences?

Jul 24, 2011

I am trying to create view that selects a number of rows from a table and has a calculated field which is a counter form 1 to n next to each row.

In the below example, s_name is repeated many times each with a differnet serial, I want to cteate a view that shows a counter for the occurences of s_name next to the serial.

CREATE TABLE XYZ
(
SERIAL NUMBER PRIMARY KEY,
S_NAME VARCHAR2(30)
);
INSERT ALL
INTO XYZ VALUES (1, 'ABC')
INTO XYZ VALUES (11, 'ABC')

[code].....

View 3 Replies View Related

SQL & PL/SQL :: Create Materialized View For Accessing Data

Feb 29, 2012

1.As we can create materialized view for accessing data from other schema but same database. will it be effective or it will act as a normal view.

2.Will materialized views can be created in Fast mode for the above scenario?

View 4 Replies View Related

SQL & PL/SQL :: Create Directory - Table Or View Does Not Exist

Oct 2, 2010

I am trying to do the following-

CREATE DIRECTORY my_dir AS 'd:oraclefiles';
GRANT READ,WRITE ON my_dir TO PUBLIC;

The above throws up an error-

ORA-00942: Table or view does not exist

View 9 Replies View Related

PL/SQL :: Create View And Procedures - Not Getting Desired Results?

Jul 16, 2012

Im doing some create view and create procedures for my work.In creating view, its just done perfectly.with create procedures work, I got some problem with the result.

So here is the coding:

create or replace
PROCEDURE "USP_EDW_CASH_MARGIN"
(
result_cursor          OUT     TYPES.cursor_type
)

[code]....

After i run this coding.
I got these errors :
1)Error(46,5): PL/SQL: SQL Statement ignored.
2)Error(46,5): PLS-00394: wrong number of values in the INTO list of a FETCH statement
It says that the error is with this code "FETCH v_cursor INTO v_row;"

View 6 Replies View Related

SQL & PL/SQL :: Display Time Format In Oracle By Create View

Nov 16, 2011

I am not able to find any information regarding inserting data with timestamp format without putting the date.

This is what i want to do:

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH24:MI TZR';

I create the column data type as timestamp with time zone. This is value i have inserted:

Insert Into TEST Values('05-OCT-2013 01:00 +08:00','05-OCT-2013 23:00 +08:00','SCIENCE);

And now i wish to display it in 'HH24:MI TZR' only after i deduct both of the time. This is the view i have created:

Create View TESTRECRDS As Select (STARTTIME- ENDTIME)*24 As DURATION, Science;

But when i select it display as weird timing because it would default in 'DD-MON-RR HH24:MI TZR' format.

BUT IF i create the view and display as 'HH24:MI TZR', it would gives me error:

Create View TESTRECRDS As Select To_Char(STARTTIME- ENDTIME, 'HH24:MI TZR')*24 As DURATION, Science;

Error would be invalid number. How can i display only 'HH24:MI TZR' after subtracting?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved