SQL & PL/SQL :: Does Force View Option Works In Oracle 11g
Aug 13, 2010when i am trying to excute below query.. it says ORA-00942: table or view does not exist.
create or replace force view v2 as select * from employee;
ADVERTISEMENT
Force View Number Data Type Precision?
Aug 18, 2011I am working on an application that pulls data from an Oracle view into Microsoft Excel (Oracle 11g, MS Excel 2003). It is an automated pivot table created through vba. The problem I'm having is that the decimal points from number columns are being truncated - they don't make it to the pivot table.
When I use the pivot table wizard and set the external data source using a SQL string (select * from view), the precision is not lost. When I create the pivot table automatically in vba, the precision is lost. The pivot table settings, regardless of how the data is brought in, shows the format of the number column as general. This tells me that Excel is trying to figure out what the data type is, and can't (not smart enough).
When looking at the description of the view, the data type is NUMBER (no precision). The table that it is pulling from has the precision set (NUMBER(11,3)). I have tried using the following, but it doesn't work:
select to_number(field_name, '99999999.999') field_name from view
SQL & PL/SQL :: SELECT Works But View Doesn't
Jun 11, 2012I'm trying to create a view that includes both custom and delivered tables. The SQL code produces the desired results in SQL Developer. However, when I create and test the view using that code, I get very different and incorrect results. The SQL part of the view is below:
SELECT j.emplid, pt.deptid, pt.y_policy_id, pt.effdt, pt.title, pt.comments, pt.y_policy_covg, pt.doc_url
FROM psoprdefn a
, ps_job j
, ps_y_policy_tbl pt
WHERE substr(a.oprid,1,1) = '0'
AND a.emplid = j.emplid
AND j.job_indicator = 'P'
AND j.effdt = (SELECT MAX(effdt) FROM ps_job
WHERE emplid = j.emplid AND empl_rcd = j.empl_rcd AND effdt <= sysdate)
[code]....
Replication :: Materialized View - Using Refresh Option?
Dec 22, 2005How can i use refresh option as 1 hr in creation of materialized view, Can i use unoin all in the materialized view and other clause like CONNECT BY PRIOR .
View 22 Replies View RelatedSQL & PL/SQL :: How Oracle Background Process Works
Jul 12, 2011how oracle background process works and in what sequence when we file an insert or update command?
View 4 Replies View RelatedServer Administration :: Whether Oracle 9i Works In Windows 2012
May 14, 2013know whether Oracle 9i works in Windows 2012? As we are planning to upgrade.
View 1 Replies View RelatedWindows / .NET :: Oracle ODBC As User Works But Fails As SYS
Feb 1, 2013Windows 7
Oracle XE 11gR2
From Windows, I can successfully create and connect with a system data source using an user Oracle account.But when doing then same configuration process I can not create or connect using the 'SYS' account.
i get the error 'ORA-2009: connection as SYS should be as SYSDBA or SYSOPER'.
What is the solution to creating an ODBC DSN for the user 'SYS'?
Reports & Discoverer :: How To Disable Printing Option In Oracle 10g
Sep 17, 2012By setting the parameters in set_report_object_property parameters we can disable the printing option in 11g. Do we have some kind of option , to do the same in 10g.
View 1 Replies View RelatedReports & Discoverer :: Disable Print Option In Oracle 10g?
Apr 11, 2010I want to disable print option in oracle reports 10g while it is opening in browser.Internet explorer version is 6.
View 3 Replies View RelatedClient Tools :: No Server Option In Oracle SQL Developer?
Jan 31, 2013Using SQL Developer I am able to login using oracle credentials but I dont see any option for SQL Server. I see both Oracle and Access. Why I am not able to see the SQL Server option in SQL Developer tool? I need the SQL Server option because I want to transfer some tables from SQL Server database to Oracle database.
View 1 Replies View RelatedSQL & PL/SQL :: Creating Oracle Query To Fetch Information Using PIVOT Option?
Feb 27, 2013creating Oracle SQL query to fetch the information using PIVOT option.We are populating audit table using triggers. For every update, there will be two rows into audit table, one row with all OLD values and another with all NEW values. Also every updated is uniquely identified by Sequence No. Example for phone audit is mentioned below :
CREATE TABLE test_audit_phone
(
emplid VARCHAR2(10),
seqno NUMBER,
action VARCHAR2(3),
office NUMBER,
mobile NUMBER
);
Insert some rows into table.
INSERT INTO test_audit_phone VALUES ('100',1,'OLD',1111,9999)
/
INSERT INTO test_audit_phone VALUES ('100',1,'NEW',2222,9999)
/
INSERT INTO test_audit_phone VALUES ('100',2,'OLD',2222,9999)
/
INSERT INTO test_audit_phone VALUES ('100',2,'NEW',2222,8888)
/
Table will look like the following :
SQL> SELECT * FROM sysadm.test_audit_phone ;
EMPLID SEQNO ACT OFFICE MOBILE
---------- ---------- --- ---------- ----------
100 1 OLD 1111 9999
100 1 NEW 2222 9999
100 2 OLD 2222 9999
100 2 NEW 2222 8888
Now we have to present data in different format. For each field, display OLD and NEW values in column format.
EMPLIDFIELDOLDNEW
----- ------ ---- -----
100OFFICE11112222
100MOBILE99998888
Challenges :
1) Make pivoting with old and new values
2) For each field we have to show old and new values
3)if old and new values are same, dont show in report.
Real Application Clusters :: Best Option 10g Or Oracle 11g RAC For Certification Point?
Oct 5, 2012which one is best option RAC 10g or oracle 11g RAC for certifiction point of view?can any one detail difference between above 2 options?
View 4 Replies View RelatedSQL & PL/SQL :: Oracle View Have Dynamic View Function?
Jul 28, 2010Are oracle view have Dynamic view function?
View 8 Replies View RelatedServer Administration :: Oracle 10G Client Works With 11G Server?
Jul 17, 2013Does a 10g oracle client works with an Oracle 11g server?
View 1 Replies View RelatedPerformance Tuning :: Force Subquery To Use Index?
Aug 5, 2010I have the following query:
Select
tag0.TAG_VALUE pid, tag1.TAG_VALUE, tag2.TAG_VALUE From TAGGER.TAGGABLE_RESOURCE r
, TAGGER.TAG tag0
, TAGGER.TAG tag1
, TAGGER.TAG tag2
where 1=1
[code]....
This runs in about 400ms. Now I replace this:
ANDtag0.TAG_TYPE in (4602, 5228)
ANDtag1.TAG_TYPE in (4612, 5225)
ANDtag2.TAG_TYPE in (4613, 5226)
with this:
ANDtag0.TAG_TYPE in (select COLUMN_VALUE from ( select * from table( TAGGER.GET_IDS_OF_SIMILAR_TAG_TYPES('Patient ID') ) x1 ))
ANDtag1.TAG_TYPE in (select COLUMN_VALUE from ( select * from table( TAGGER.GET_IDS_OF_SIMILAR_TAG_TYPES('Patients Sex') )x2 ))
ANDtag2.TAG_TYPE in (select COLUMN_VALUE from ( select * from table( TAGGER.GET_IDS_OF_SIMILAR_TAG_TYPES('Patients Birth Date') ) x3 ))
So instead of hard coding the IDs there is a function that looks them up. The function itself is reporting that it runs in 0ms. But when I run the new query:
Select
tag0.TAG_VALUE pid, tag1.TAG_VALUE, tag2.TAG_VALUE From TAGGER.TAGGABLE_RESOURCE r
, TAGGER.TAG tag0
, TAGGER.TAG tag1
, TAGGER.TAG tag2
where 1=1
[code]....
it takes around 6s to run. I have looked at the explain plans it it seems as though the function based approach is triggering a full table scan of 'TAG'.
I have tried it with query hints to use index, but it doesn't change the execution plan, or the query time.
The explain plan for the quick query is:
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 1031492929
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
[code]....
And the slow one is:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2741657371
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Te
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20M| 1602M|
[code]....
Force Query To Always Bring Back Certain Amount Of Rows
Jun 24, 2010Is there a way to force a query to ALWAYS bring back a certain amount of rows, even if some of the rows are blank?
I know there's a way in MSSQL but not sure how to in SQL+
Performance Tuning :: Force Optimizer To Consider All Join Permutations?
Oct 14, 2013I'm looking to see if there is a way (fully expecting it to be an underscore, or two...) to force the optimizer to keep churning until all permutations are exhausted.I'm aware that it, to paraphrase, cuts out when it's spent more time parsing than it would just running it based on it's estimates.
I've got some irritating problems with xml rewrite, xml indexes and access paths/cardinalities etc and I'm really needing the entire thing considered as a one off for debugging this. I've already cranked up the maximum permutations to the max but it's not enough, it shorts out after 5041 permutations (I'd set that to 80000 max).
I know you'd not want to do this in the real world but I cant get the damned thing to run the plan I want in a 10053 so I can see the values it has there. I know I can hint it, but I'm trying to ascertain why it's not even considering it in a "normal" parse.
Performance Tuning :: How To Force SQL To Use Index Range Scan
Jul 15, 2011How can i force a sql use index range scan?
View 10 Replies View RelatedPerformance Tuning :: Force Index Unique Scan?
Jun 14, 2012Is there any hint to force "Index Unique Scan" over "Index Range Scan".
My query is generating different plans with the above two, and very slow when it uses "Index Range Scan".
Forms :: How To Force User To Enter Exactly 3 Digits Into Number Field
Mar 28, 2011There is a text item on my form with NUMBER data type. I want to force the user to enter exactly 3 digits in to that text item. My current FORMAT MASK is set to 990. But this allows user to enter even a single digit where as my requirement is user should enter exactly 3 digits. Can we achieve this by changing the FORMAT MASK property?
View 6 Replies View RelatedClient Tools :: Force Parameter Value To Change Filename For Every Execution Of Sql Script
Jan 24, 2012I wrote an sql script (spooler) which runs a select query and produces results into a file.
I want to have the filename with the year attached. Can i pass a parameter when i run the script from SQL*PLUS
@'B: Year Of FinancingPLSQLspoolersSpooler_File_Creation' 01/01/2010 2010;
the first parameter 01/01/2010 is passed to the select query and taht works fine.
Since the output file contains 2010 data, I want the spooled file to mention in its name the 2010, so User's can know the file contains 2010 data in it.
So i wrote the statement within the spooler script
SPOOL X:HARSOrganizationsFinancialDataFor&2.txt
&2 should take 2010 has its value and produce the file FinancialDataFor2010.txt
how to use Substitution variables to produce the Filename i want.
Forms :: Force User To Save Or Discard Record Before Child Window?
Aug 10, 2012I have created a form where the main canvas has number of buttons. When I click each button it will open a new canvas in a new window. User will enter some details there. now I want to force the user to save or discard the record before they leave that child window. The code I have written is
DECLARE
n_button_selection INTEGER;
BEGIN
IF :SYSTEM.BLOCK_STATUS = 'CHANGED' then
[code]...
But I am not sure in which trigger I need to write the code. When I am writting in post block it is behavior is becoming unpredictable and also APP_STANDARD.EVENT('KEY-COMMIT') does not work in post_block trigger. When I am writting this in ON close trigger then also it is not firing as expected.
Application Express :: How To Force APEX To Fire Submit After Processes Before On-Demand
Jul 2, 2013We have an application that has "After Submit" and AJAX "On-Demand" processes. We need the "After Submit" to fire and complete first, set an application item (by referencing the next value in a sequence), and then fire the "On-Demand" processes. We have the sequence number set accordingly, with all of the "After Submit" processes given a lower (APEX) sequence number than the "On-Demand" processes; however, it seems the "After Submit" processes are still not firing and completing because only intermittently are the "On-Demand" processes able to access the application item's value--80% of the time this value is simply blank. Making matters more complicated is that the application item is derived from an Oracle sequence, so in my "On-Demand" processes, I cannot simply re-run some of the same logic used in one of the "After Submit" processes that sets the sequence--the sequence may only be set once, and only by this "After Submit" process; we have no choice in that regard. Is there any way possible in APEX to 100% force processes to fire AND complete, one after another, in the order in which they are listed by their (APEX) sequence? In other words, if we have this:
(APEX) Seq Name & Process Type
1 process A ("After Submit")
2 process B ("After Submit")--this sets application item :XYZ
3 process C ("On-Demand)--this uses application item :XYZ and MUST have this item value available to do any processing Can we instruct APEX to fire process A, complete it, fire process B, complete it, and only then, fire process C?
Application Express :: Force Report To Return To Top Of Page / Screen When Previous / Next Clicked?
Jan 10, 2013We have several interactive reports setup and some of them can display more than one screen of data at a time, depending on the results returned.
All of the reports have prev/next pagination links at the top and bottom of the reports. For the longer reports, if you click prev/next at the bottom of the reprot, the prev/next page will display. But, you will still be at the bottom of the page/screen. Is there a way to force the report to return to the top of the page/screen when prev/next is clicked?
SQL & PL/SQL :: How Does Loop Works
Jun 27, 2011[CODE]
DECLARE
D1 DATE:='&D1';
D2 DATE;
BEGIN
WHILE D1<=D2 LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D1,'DAY DD-MON-YYYY'));
D1:=D1+1;
END LOOP;
END;
Here I want to display all the seven days. One more question is here I'm d1<=d2 but d2 is null. So how does <= works here a null. A null is always null. What will be there in d2 how does the loop works with this comparision.
SQL & PL/SQL :: Query Works In 10g But Not In 11g?
Apr 29, 2012I have the query below that works absolutely fine in Oracle 10g but is not running in Oracle 11g. Couple of things I notices about this query.
1. If I remove the alias 'FACILITY_W_CODE' in the ORDER BY clause, query works fine in 11g. If I replace 'FACILITY_W_CODE' with the actual fields - 'F.SERVICE_AREA || F.DISTRICT_OFFICE || F.DO_GROUP || F.DO_SSC' even then it works fine.
2. If I remove the GROUP BY clause in the inside query but keep the alias 'FACILITY_W_CODE' in the outside ORDER BY clause, the query works fine.
I am not sure where the issue is, GROUP BY or the ALIAS. Also, the alias issue seems to be only where the alias is used for multiple fields.
SELECT F.SERVICE_AREA || F.DISTRICT_OFFICE || F.DO_GROUP || F.DO_SSC FACILITY_W_CODE, PF.FAC_TYPE FACILITY_TYPE, PF.FAC_IDENT LOCATION_IDENTIFIER, P.DISTRICT_CODE USER_W_CODE, P.EMAIL_ADDRESS, PF.CERTIFICATION_AUTHORITY, PF.CALLBACK_TECHNICIAN, PF.CALLBACK_ENVIRONMENTAL
FROM PEOPLE P
JOIN (
SELECT I_PF.ID, I_PF.FAC_TYPE, I_PF.FAC_IDENT,
[code]....
PL/SQL :: How Where Condition Works
Jun 1, 2013How oracle retrieves data from a table.like let say a table product as
create table product(id number,name varchar2(10))
table product dont have any index on it
now if this table conatain 500 rows and we have two query
1. select * from product where id=26
2. select * from product
does both query take same time or first query will execute in less time since it has where condition
Code Works In Firefox But Not IE7?
Jan 29, 2008I am fighting with some code to work in IE7. It is currently working in Firefox.
A little bit more on the context of the situation is that I am entering the code as a personalization on an Oracle Self Service Page. I am entering in as a raw text personalization.
[Code]
<html>
<head>
<style>
</style>
</head>
<body lang=EN-US style='tab-interval:.5in'>
[code].....
SQL & PL/SQL :: ORA-00979 In 11G But Works Fine In 10G?
Aug 1, 2011I tested the SQL in 11G and get the ORA-00979 (not group by error) , the same SQL is working in 10G.
553322.1 - ORA-00979 IN 11.1.0.6 BUT NOT IN 9I OR 10G
814423.1 - ORA-00979 AFTER UPGRADE TO 11G
Do we have simple soluation for it if I don't have Oracle support account?
SQL & PL/SQL :: DML Statements - How Does Merge Works
Jul 23, 2013My teacher taught the lesson of DML statemnts, he told us how does merge works , but he did not give us any query for that,provide query for Merge and if possible then explain it too , I am using Oracle 10g Sql Plus.
View 3 Replies View Related