Now, I am trying to come with an alternative. My first thought was to use materialized views with Fast Refresh - refresh only changed rows. This might not even work because there are many contraints when using Fast refresh. I am in the process of learning their data model so I do not know much.
My Oracle DB 10g R2 running on AIX 5.3 OS. When I am shutting down the oracle I see that it is waiting for complete the active calls and I determined that these active calls/processes are from remote machines.
When I try to run this ps -ef as shown below, it shown me number of oracle process with LOCAL=NO
I have a function that is being called three time using UNION and wanted to know if this can be improved to just one call while incorporating all the table joins.
select field1,fdate,fname,username,stepnum from ( SELECT M.FIELD1, TO_CHAR (M.FIELD_DATE, 'MM/DD/YYYY HH24MISS') AS FDATE, M.FIELDNAME AS FNAME, M.USERNAME,
Select t1.col1 as A, t2.col1 as B, t3.col1 as C, t4.col1 as D
[Code]...
with this above query i am getting result like this:
A B C D
2 null 2 null 6 6 null 6 8 null null null
However i need the result like this:
A B C D
6 6 null 6
Note:
I am looking for common value that presents in most of these 4 tables for different t1.col2 values like 'E','G','K',...etc. I cant use the NOT NULL condition in the where clause,since i need to check for different values in t1.col2,for each of this there may be null value in any of the 4 tables.
I have attempted to use the analytic function to keep a running total of the count of active calls based on the connect and disconnect times given for each record row.
I am working on the quality center oracle database to write a query that fetches all steps of a test case including the ones having calls to tests. Structure of table is explained below. I've made up an example and attached it as an image to this post. This image also has the expected result of the query I want to write.
Table Name: STEPS (This table contains steps belonging to tests. Some steps are simply calls/links to other tests)
Columns: STEP_ID (primary key - integer) STEP_NAME (char) STEP_DESC (char) ORDER (integer) TEST_ID (reference to table TEST.test_id) [code].......
Referring to the example (see attached image), I'm looking to write a query that gives me all steps (including steps from called tests) of the test - "Empty trash from mailbox" in the correct order.
To start with I can write the following query to get steps of the test - "Empty trash from mailbox". SELECT * FROM steps WHERE test_id = (SELECT test_id FROM test WHERE test_name = 'Empty trash from mailbox')
Is there any function avaialble in SQL that can return the highest common factor among a bunch of numbers. For example 10,20,25 have a highest common factor of 5.
I have a one column table that store lists of elements :
create table test_table (c1 VARCHAR2(4000)); insert into test_table values ('1,23'); insert into test_table values ('1,2'); insert into test_table values ('3,4,5');
[code]...
The output column would be something like that:
output_column 1,2,7,23 6,9,0 3,4,5
I'm grouping columns that have at least one element in common.
(1,23) and (1,2) merge into : (1,2,23) (1,2,23) and (7,2) merge into : (1,2,7,23) --> Output (6,9) and (9,0) merge into : (6,9,0) --> Output (3,4,5) and (5,5) merge into : (3,5,5) --> Output
I have made this logic using only PL/SQL, with loops and nested tables using function memberof, but I suppose that there is a way to improve the performance using only SQL.
Suppose, there is a package p1 with proc A, Proc B and Func C.Now when any of the package proc or func gets invoked from a stored proc G then a common code which reads the cols of table xyz should be executed. How to do that?
Currently if i let required field empty, then APEX puts systematic error message "XXX must have some value." near the field and into the page header. I want to create custom business rule, that would show message for example "This action cannot be performed, because this and that...".
That kind of custom validation should be in my plSql package and somehow it should throw/send the phrase "This action cannot be performed, because this and that..." to the page header where was message "XXX must have some value.".
How can i create such custom error message to appear out using PlSql package function that i define myself? I plan to create a common validation package, which has lots of plsql functions to validate lot of APEX pages/forms, all those functions should throw/send somehow error messages to page header. I looked that this unofficial solution does not suit for me:
[URL].......
Because it disables APEX systematic automatic validations as i understand, and is unofficial.
Problem is one form that i have to migrate from version 6 throu 6i I got very difficult (for me) error FRM-40734. Happens In WHEN-NEW-FORM-INSTANCE trigger when i try do go_block('NAVI');NAVI block is NOT database block (property set NO)
Database Oracle Database 10g Release 10.2.0.4.0 Forms 11.1.2.0.0,
Below i post some code
When-New-Form-Instance trigger ....some other code navi_pck.link_activated(); ... some other code navi_pck.Init(); ... some other code
Navi_pck is a package added to program modules
PACKAGE BODY navi_pck IS type t_navi_data is record ( name varchar2(64), primary_item varchar2(64),
[code]...
When When-New-Instance-Form fires on line navi_pck.link_activated(); we go to init(); in navi_pck packageeverything is ok - that mean it is executed without any errorthen when we hit navi_pck.Init();we got error at Bold line => go_block(g_block); that is error not exception (because adding begin... exception when others ... end; does not catch it)
Of course its needed to be done -> mean that go_block, because after it we clear block and work on it.I got 5 triggers on that block NAVE - but tried already to NULL theirs code but didnt solve the problem (didnt try to remove them...)
If i try to execute the below code i go the ora:00600 error. what is wrong in the query.
select FCT.* from (SELECT NVL(DD.DATE_KEY,1855) AS DATE_KEY, DP.PRODUCT_KEY AS PRODUCT_KEY, DP.VENDOR_KEY AS VENDOR_KEY, DP.VENDOR_CODE AS VENDOR_CODE, COUNT(DISTINCT FPL.PO_NO) AS PO_ASSIGNED, SUM(FPL.PO_TOT_QTY) AS UNIT_ASSIGNED, COUNT(DISTINCT FVL.PO_NO) AS PO_RECEIVED, SUM(VO_TOT_RECD_QTY) AS UNIT_SHIPPED,
