SQL & PL/SQL :: Getting ORA-04063 Error While Creating View In Oracle?
Aug 17, 2011i am getting ORA-04063 error while creating view in oracle.
View 2 Replies
ADVERTISEMENT
Error Creating View With Union
Jan 14, 2011i 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].....
SQL & PL/SQL :: Error In Creating Materialized View
Oct 1, 2012Am 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
PL/SQL :: Error While Creating View From One Instance To Another?
Aug 22, 2012H 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
Error In Creating Materialized View With DB Link?
Aug 7, 2013we 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?
Error In Creating Update Table Materialized View
Aug 28, 2013we 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
Creating Materialized View On Remote Simple View
Dec 11, 2012I'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?
SQL & PL/SQL :: Create Primary Key On View And Use This View For Creating Foreign Key?
Oct 8, 2010is it possible to create primary key on view and use this view for creating foreign key .
View 3 Replies View RelatedError While Creating Table In Oracle 10g?
Aug 19, 2008If 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]....
Server Administration :: Error Creating Database Using DBCA Oracle 11g R2?
Nov 18, 2010This is on a vmware virtual server on Windows 2008R2 using ORACLE 11g R2 with patch set 6
I installed ORACLE (software only) and applied patch set 6 and tried to create a new database.
during the database creation I got an error as the system was adding the JVM that read:-
ORA-01927:cannot REVOKE privileges you did not grant.
I was given the options to ignore or abort
I chose to ignore the error and continue.
After the database creation completed I looked in the jserver log copied below and found two errors
I did not get the same error before installing patch set 6
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
No errors.
alter system flush shared_pool
drop role javauserpriv
[Code].....
Server Utilities :: ORA-04063 - Package Body Has Errors
Jan 30, 2013I am getting the below error when i am trying to import the dmp file into my oracle 11g.
impdp system/password@orcl dumpfile=aaa.dmp directory=datapump remap_schema=dev:user
ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_
LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 949
ORA-04063: package body "SYS.DBMS_LOGREP_UTIL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_UTIL"
Creating Materialized View Log
Feb 7, 2013There 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 RelatedSQL & PL/SQL :: Creating A View In Developer SUM
Oct 26, 2011I 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]...
SQL & PL/SQL :: Creating A View From Procedure
Mar 4, 2011I 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.
SQL & PL/SQL :: Creating Materialized View For Getting Hierarchy?
Oct 3, 2013I 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
PL/SQL :: Creating View And Public Synonym With The Same Name?
Feb 15, 2013I 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
SQL & PL/SQL :: How To View Full Statement Of Creating Function
May 24, 2011I 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.
Forms :: Code Of Creating View In Pre_Form
Nov 25, 2010Following
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.
SQL & PL/SQL :: Creating View On Basis Of Time Interval?
Apr 27, 2010how 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
SQL & PL/SQL :: Creating View - ORA-00972 Identifier Too Long?
Feb 13, 2013I 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
SQL & PL/SQL :: Creating Trigger - Table Or View Does Not Exist
Apr 6, 2012When 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?
PL/SQL :: Creating Materialized View Utilizing Tables And A Job
Dec 5, 2012This 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 RelatedPL/SQL :: Creating A Refresh On Commit Materialized View?
Jul 18, 2013I 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.
Server Administration :: While Creating Index ORA-00603 / ORACLE Server Session Terminated By Fatal Error
Jul 26, 2010While creating the index we are getting the error "ORA-00603: ORACLE server session terminated by fatal error".
We have the space in tablespaces and also in the file systesm.
SQL & PL/SQL :: Creating And Refreshing Materialized View Group From Another Schema?
Nov 27, 2012I 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.
Performance Tuning :: Creating Index On Base Tables Of A View?
Dec 9, 2010I 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.
Creating A View To Show Employee Names / Age And Total Number Of Projects Assigned
Apr 14, 2009I 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
SQL & PL/SQL :: Error With Creating Job
Sep 18, 2010I 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
Error While Creating Catalog DB
Aug 6, 2010error 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
SQL & PL/SQL :: Error In Creating DBMS_SCHEDULER Job?
Feb 25, 2013I 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: