Error Creating View With Union

Jan 14, 2011

i have a union all query (two quiries i suppose) that runs fine and returns the results i am after.... when i try to use this query to create a view I get

SQL Command: CREATE OR REPLACE FORCE Failed: Warning: execution completed with warning

CREATE OR REPLACE FORCE VIEW "schema1"."name_of_view" ("column1", "column2", "column3") AS
SELECT b.column1, a.column2, c.column3

[Code].....

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Error In Creating Materialized View

Oct 1, 2012

Am trying to create a materialized view as below

SQL> CREATE MATERIALIZED VIEW emp_view_73 BUILD IMMEDIATE REFRESH COMPLETE ON COMMIT
2 AS
3 SELECT em.employee_id,
4 em.employee_fname
5 ||' '
6 ||em.employee_lname employee_name,
[code]....

ERROR at line 12: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

View 37 Replies View Related

PL/SQL :: Error While Creating View From One Instance To Another?

Aug 22, 2012

H want to Create a view which reside in one instance for example xxx Instance . I want to create the same view which reside in xxx instance to another instance for example YYY Instance How to acheive this ?I tried desc the View take the view script from XXX instance and copied the view script to YYY instance when I compiled Iam getting an Error

For Example My view in XXX Instance
CREATE OR REPLACE FORCE VIEW xxx.sample
(
First_name,
last_name
)
as
     select "F_NAME",
             "L_NAME"
     FROM  EMP;

When try to copy and compile the same view in my YYY Instance i am getting error

View 7 Replies View Related

Error In Creating Materialized View With DB Link?

Aug 7, 2013

we have the following requirement for creating materialized view.

In database "DB1", a table "abc" is there in "user1" schema.
In database "DB2", in "user2", we are accessing the "abc" through DB link "DBL1" to DB1:
select * from user1.abc@DBL1;
we have created a public synonym for "user1.abc@DBL1" as "sabc".

In database "DB3", we need to create the materialized view to access "abc" in DB1 through DB2. we are not allowed to access "DB1" directly from "DB3". so we create a db link as "DBL2" in "DB3" to "DB2".

when i try to create a materialized view as below:
create materialized view abc_mv
as
select * from sabc@DBL2;

But got the error as "synonym translation invalid". When i access the SQL statement separately as below
select * from sabc@DBL2, its working but in Materialized view, it shown that error. Even i tried with schema name as well like "user2.sabc@DBL2" , but that also thrown same error.

Is there any option available for this type of creating materialized view?

View 1 Replies View Related

SQL & PL/SQL :: Getting ORA-04063 Error While Creating View In Oracle?

Aug 17, 2011

i am getting ORA-04063 error while creating view in oracle.

View 2 Replies View Related

Error In Creating Update Table Materialized View

Aug 28, 2013

we are trying to create a materialized view (MV) which would access the remote database through db link. Now we need to do update on the local MV so that it should be reflected on the master table.

There is no primary key on this table and we are using "complete refresh" option. since we dont have control over remote database, we are not allowed to create MV log over there.

in this scenario, if i try to create updatetable MV with complete refresh, we are getting below error:

SQL Error: ORA-12013: updatable materialized views must be simple enough to do fast refresh

how to create such MV on this scenario?

our environment is:

Oracle 11.2.0.3

View 1 Replies View Related

SQL & PL/SQL :: UNION ALL - View With Similar Indexes Each Table?

Jul 5, 2012

I have this sql (generated by discoverer plus), and work fine:

SELECT COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,

[code]...

We need change the table stock_por_sucursal for a view like this, with similar indexes each table

select * from stock_por_sucursal_old
union all
select * from stock_por_sucursal_new

and the result is not good:

SQL Statement from editor:

SELECT COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
GAR_DW.V_STOCK_POR_SUCURSAL o10475761,

[code]...

finally add a HINT /*+ gather_plan_statistics push_pred(TABLE) */ and the result was not very good in this case, but improved the resolution of the view, the rest got worse

SQL Statement from editor:
SELECT /*+ gather_plan_statistics push_pred(o10475761) */ COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,

[code]...

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 :: 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

How To Know Data Is Coming From Exact Table from Union All View

Feb 28, 2013

I am using view AB_EV_VIEW created with UNION ALL of 10 tables (ex. AB_EV_1, AB_EV_2.... )

I want to know the data comming in select * from AB_EV_VIEW is from which table ?

Suppose AB_EV_1 is having column ID, NAME

sql> select * from AB_EV_VIEW

100, A1
200, A2
300, A3
400, A4
500, A5

View 3 Replies View Related

Creating Materialized View Log

Feb 7, 2013

There are 2 databases, database A and database B. Database A is Oracle 11.2.0.2 which runs on linux and Database B is Oracle 11.2.0.2 which runs on windows xp machine. In database A, there are 100's of tables which are being updated every 10 minutes or 15 minutes. For reporting purpose, the developer wants to run report for the tables. But since database A is being updated every now and then, generating reports takes almost 15 to 20 minutes. So the reports can be generated in Database B. Once in a day the database B should have the updated data from database A so that the reports can be generated in database B with less time. What could be the best solution for the database B to have the updated data on daily basis from database A in oracle?

View 3 Replies View Related

SQL & PL/SQL :: Creating A View In Developer SUM

Oct 26, 2011

I am having trouble with creating a view based on the following: -

Total hours flown EVER, for each crew of the FBN001 on 20th October 2011.

Here is what i got so far (only works for each members flown on a particular flight, NOT on their whole hours flown in a period of a lifetime) : -

SELECT e.Employee_Number, e.FirstName, e.LastName,
SUM( ( (s.ESTIMATED_ARRIVAL) - (s.ESTIMATED_DEPARTURE) )*24)AS
TOTAL_HOURS_FLOWN
FROM SCHEDULE s, CREW_ASSIGNMENT ca, EMPLOYEE e
WHERE
s.Flight_Number = ca.Flight_Number
AND
s.Serial_Number = ca.Serial_Number
[code]...

View 20 Replies View Related

SQL & PL/SQL :: Creating A View From Procedure

Mar 4, 2011

I have create a procedure as

create or replace procedure aa is
abc varchar2(1000);
begin
abc:='create or replace view abc_view as select * from abc';
execute immediate abc;
end;

When i try to execute the same as

BEGIN
aa;
END;

i got error as

ORA-01031: insufficient privileges
ORA-06512: at "EARS.AA", line 5
ORA-06512: at line 3

why i get this error. there is no problem in grants. the same create or replace view query works if i directly executes in sql.

View 8 Replies View Related

SQL & PL/SQL :: Creating Materialized View For Getting Hierarchy?

Oct 3, 2013

I want to create a materialized view for the with same logic as below view

CREATE OR REPLACE FORCE VIEW GFL_MCP_CORE.VW_CLIENT_HIERARCHY
(KEY_INDENTED, CLIENT_LEVEL, ID, NAME, CLIENT_TYPE_CODE,
L2_CLIENT_ID, L2_CLIENT_NAME, TOP_CLIENT_ID, TOP_CLIENT_NAME, CLIENT_MCP_FLAG,
CLIENT_GROUP_NAME, ORG_REGION_NAME, COUNTRY_NAME, PROCESSOR_CLIENT_CODE)

[code]....

Here is the sample data for the client table and other tables are generic.

Row#IDNAMECOUNTRY_CODECLIENT_TYPE_CODECLIENT_GROUP_IDPARENT_CLIENT_IDSTATUS_FLAG
134801ZhuanshenguangchangCNBR10048232909A
212515229ConsolidatedFCUUSCL54812515A
314821286 Golden Mile BranchCABR53112712A
42050014513Bay CityUSBR56612009A
53864712-44-125 LeicesterGBBR56038630A
63869114736 Freestar BrickyardUSBR10020238686A
731141Walsall StoreGBL254631103A

View 1 Replies View Related

PL/SQL :: Creating View And Public Synonym With The Same Name?

Feb 15, 2013

I am able to create view and the public synonym with the same name but when I am quering view result is showing .

Is there any logic behind this .

CREATE OR REPLACE VIEW emp_dtls as SELECT * FROM emp;
CREATE PUBLIC synonym emp_dtls for emp;
SELECT * FROM emp_dtls;

view result is showing

View 3 Replies View Related

SQL & PL/SQL :: How To View Full Statement Of Creating Function

May 24, 2011

I executed the following statements

SQL> CREATE OR REPLACE Function test
2 ( name_in IN varchar2 )
3 RETURN number
4 IS
5 cnumber number;
6
7 cursor c1 is
[code]....

how I can view the full statement of creating the function.

View 1 Replies View Related

Forms :: Code Of Creating View In Pre_Form

Nov 25, 2010

Following

create or replace view dept_list as
select 1 state, 1 depth, 'DEPARTMENT' displ_value, null icn, null pk, 'A' sort_value
from dual
union
select -1 state, 2 depth, dname displ_value, null icn, null pk, 'A' sort_value
from dept
union
select -1 state, 3 depth, 'EMPLOYEES' displ_value, null icn, null pk, 'A' sort_value
from dept;

is placed in Pre_Form Trigger it generated error.

Error 103 at line 1, column 1
Encountered the symbol "create" when expected the following.

View 4 Replies View Related

SQL & PL/SQL :: Creating View On Basis Of Time Interval?

Apr 27, 2010

how to create a view from table which containes rows from every minutes. In view I want to select only rows at 15 or 30 minutes interval.

eg. my base table has 60 rows for 1 hour. The created view must contain 4 rows on 15 minutes interval basis at the boundary of that interval.

O/P of view should be like this.

Value Time
100 00:00:00
200 00:15:00
300 00:30:00
400 00:45:00
500 01:00:00

View 6 Replies View Related

SQL & PL/SQL :: Creating View - ORA-00972 Identifier Too Long?

Feb 13, 2013

I am getting error (ORA - 00972 : identifier too long) while creating the view.

create or replace view ELVW_ATM_REC_HANDOFF_1(Rectype,Recseq,Record_Type,Record_Sequence,MESSAGE_TYPE,PAN,PROCESSING_CODE,TRANSACTION_AMOUNT,TRANSACTION_CUR RENCY_CODE,SETTLEMENT_AMOUNT,SETTLEMENT_CURRENCY_CODE,Billing_AMOUNT,Billing_CURRENCY_CODE,Transaction_DATE,Settlement_Date,TRACE,REF

[code]...

i have checked the length of the column name. Its less than 30 characters

View 4 Replies View Related

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

Apr 6, 2012

When i try to create a trigger , i ended up with error.

SQL> create or replace
2 TRIGGER LOGON_TRG AFTER LOGON ON DATABASE
3 BEGIN
4 INSERT
5 INTO
6 user_audit_log
7 SELECT
8 user#,

[code]....

Warning: Trigger created with compilation errors.

SQL>
SQL> show error
Errors for TRIGGER LOGON_TRG:
LINE/COL ERROR
-------- -----------------------------------------------------------
2/5 PL/SQL: SQL Statement ignored
17/17 PL/SQL: ORA-00942: table or view does not exist

The command used to resolve the error is

GRANT SELECT ON v_$session TO jack;

Jack user has sysdba privilege. My question is 'sysdba' is a super and special user which has all the privileges in database. Then why does it need SELECT privilege on v$session to user to create the trigger?

View 3 Replies View Related

PL/SQL :: Creating Materialized View Utilizing Tables And A Job

Dec 5, 2012

This is just for learning purposes. What if I would like to create my own materialized view utilizing only tables and a job.

View 28 Replies View Related

PL/SQL :: Creating A Refresh On Commit Materialized View?

Jul 18, 2013

I am trying to create a commit on refresh materialized view so that whenever I perform DML followed by commit on Master table it should get quickly reflect on my Materialized view. I have given SELECT on table privilege to user where this view will be created On Master:

GRANT SELECT ON HR.EMPLOYEES TO SCOTT. From Scott:CREATE MATERIALIZED VIEW  scott.hremp  REFRESH FAST ON COMMITAS SELECT * FROM HR.EMPLOYEES; ERROR at line 4:ORA-01031: insufficient privileges Then I Grant all on EMPLOYEES table to SCOTT. GRANT ALL ON hr.EMPLOYEES TO SCOTT; Then got following error while creating materilized view:SQL> CREATE MATERIALIZED VIEW  scott.hremp  2 REFRESH FAST ON COMMIT  3  AS  4  SELECT * FROM HR.EMPLOYEES;SELECT * FROM HR.EMPLOYEES  *ERROR at line 4:ORA-23413: table "HR"."EMPLOYEES" does not have a materialized view log I create materialized view log on Master:SQL> CREATE MATERIALIZED VIEW LOG on HR.EMPLOYEES;Materialized view log created.

Even after this I am not able to create materialized view and now getting Table not Exist error:

SQL> CREATE MATERIALIZED VIEW  scott.hremp 2 REFRESH FAST ON COMMIT  3  AS  4  SELECT * FROM HR.EMPLOYEES;SELECT * FROM HR.EMPLOYEES *ERROR at line 4:ORA-12018: following error encountered during code generation for "SCOTT"."HREMP"ORA-00942: table or view does not exist. Also I want to know why I needed to GRANT ALL to HR for creating this materialized View.

View 6 Replies View Related

SQL & PL/SQL :: Creating And Refreshing Materialized View Group From Another Schema?

Nov 27, 2012

I have a list of materialized views in schema A. I want to create a refresh group and then refresh it from Schema B (Dynamically-run time based on some criteria). What Grants are necessary on schema B in order for it to be able to create and refresh the groups on Materialized views in Schema A.

I know that one of the Options is to, GRANT ALTER ANY MATERIALIZED VIEW as a SYS user. But I do not have any SYS privileges.

View 3 Replies View Related

Performance Tuning :: Creating Index On Base Tables Of A View?

Dec 9, 2010

I have a view on base tables holding historical data for previous 60 months(one table per month) with union all operators.create index on those base tables will improve performance or creating a primary key with disabled novalidate will improve for retrieving data?

The view has around 8 million data and used as a fact table with 4 dimension tables.A DTS package from MSSql side refreshes OLAP cube by retrieving data from these tables in oracle.

View 1 Replies View Related

Creating A View To Show Employee Names / Age And Total Number Of Projects Assigned

Apr 14, 2009

I have following tables:

EMPLOYEE (E-Number, Name, Department, age)
ASSIGNMENT (E-Number, P-Number )
PROJECT( P-Number, Project, Manager)

Create a view to show employee names, age and total number of projects they are assigned to

View 2 Replies View Related

SQL & PL/SQL :: Error With Creating Job

Sep 18, 2010

I am writing the following PLSQL code-

BEGIN

DBMS_SCHEDULER.CREATE_JOB
(
job_name=>'TRIAL JOB',
job_type=>'PLSQL BLOCK',
job_action=>
[code]........

The new table used above has the following structure

Name Null? Type
STAMP TIMESTAMP(6) WITH TIME ZONE

However I get the following error on execution and am not able to make out why?

BEGIN
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 3

View 4 Replies View Related

SQL & PL/SQL :: Is It Better To Use UNION (or) UNION ALL With DISTINCT

Apr 22, 2010

Is it better to use UNION (or) UNION ALL with DISTINCT,Which one will improve performance.

Query1:

SELECT deptno FROM emp
UNION
SELECT deptno FROM dept

Query2:

SELECT DISTINCT * FROM(SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept)

I mean to say query1 is better (or) query2 is better,which query improves the performance.

View 1 Replies View Related

Error While Creating Catalog DB

Aug 6, 2010

error while creating a catalog db

error creating db
ORACLE error from recovery catalog database ORA-01552: cannot use system rollback segment for non-system tablespace 'TS'
ORACLE error from recovery catalog database:ORA-00942:table or view doesnt exist

RMAN-00571:--------------------------------------------------------------------------------------------------
RMAN-00569----------------ERROR MESSAGE STACK FOLLOWS-------------------------------------------
RMAN-00571:---------------------------------------------------------------------------------------------------
RMAN-06433:error installing recovery catalog

View 1 Replies View Related

SQL & PL/SQL :: Error In Creating DBMS_SCHEDULER Job?

Feb 25, 2013

I have create the following procedure to create individual jobs dynamically on each schema.

CREATE OR REPLACE PROCEDURE APSP.JOB_TEST_RAM
AS
V_SQL VARCHAR2 (1000);
BEGIN

[Code]....

While executing this procedure I am getting the below error.

PLS-00103: Encountered the symbol "APPS_XX" when expecting one of the following:

View 5 Replies View Related

Error While Creating Table In Oracle 10g?

Aug 19, 2008

If i create a table in Oracle 10g it givea me the follwoing Error.. what couls be the error...

ORA-00911: invalid character
create table shopper
(
cShopperId varchar2(30) primary key,
cPassword char(20) not null,
vFirstName varchar2(20) not null,

[code]....

View 1 Replies View Related







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