The module_name variable should be assigned the value 'covenants', because :GLOBAL.FORMS_PATH is '' (nothing). I stepped thru the code and in the debug system values window there is nothing in :GLOBAL.FORMS_PATH. But instead, it gives me: 'FORM_NAMEcovenants' where 'FORM_NAME' is the form the menu was called from.
Table 1 have 3 columns ID, CUS_NAME, LOC insert into table1 values (001,ABC,North); insert into table1 values (002,DEF,South); insert into table1 values (003,GHI,West);
Table 2 have 3 columns ID, CUS_NAME, LOC insert into table2 values (001,ABC,North); insert into table1 values (002,DEF,East); insert into table1 values (003,JKL,South);
Table 3 is Result_Tab table having 8 columns ID, TAB1_CUS_NAME, TAB2_CUS_NAME, Cus_Name_Res, TAB1_CUS_LOC, TAB2_CUS_LOC, Cus_LOC_Res, Comment.
I have written two cursors which fetches data from both the tables and compares each data between each other and inserts the value into the result table.
the code is as follow:
Insert into Result_Tab values (T1.ID, T1.Cus_Name, T2.Cus_Name, decode(T1.Cus_Name,T2.Cus_Name,'Y','N'),T1.LOC, T2.LOC, decode(T1.LOC,T2.LOC,'Y','N'),Null);
Now I want the resul as follows: ID T1.N T2.N N_Res T1.L T2.L L_Res Comment 001 ABC ABC Y North North Y Null 002 DEF DEF Y South East N Loc 003 GHI JKL N West South N Name, Loc
Is there a way wherein i could capture the column names in decode function when it doesn't match, so that I can insert the same in the comment column.
My problem is I have 3 tables (TEST_TBL1, TEST_TBL2, TEST_TBL3). TEST_TBL2 and TEST_TBL3 are in remote database and I use database link to join them. The following query returns incorrect result (I seems that it ignore the where clause)
SELECT * FROM TEST_TBL1 JOIN TEST_TBL2@db_remote USING (KEY1) JOIN TEST_TBL3@db_remote USING (KEY2) WHERE KEY1=XXX OR KEY2=YYY;
I am on 11R1 (11.1.0.7)
FOR EXAMPLE:
Local database: CREATE TABLE TEST_TBL1 ( KEY1 NUMBER(5) NOT NULL,
I have the following Union All query. It throws the following error in SQL plus
ERROR at line 27: ORA-01789: query block has incorrect number of result columns
After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like
I am simply trying to execute a couple of select statements in a single dynamic query. I also want it to return a seperate result set from each statement (something quite easy in T-SQL - but seemingly impossible in PL-SQL!)
This query is being run from C#, but i also get the same results from Sql Developer.This is what i'm trying to execute:
begin select * from studies; select * from studyprogressions; end;
The error i get is "an INTO clause is expected in this SELECT statement" for each of the select lines - As you may already have worked out - I don't use Oracle very often and am starting to feel very stupid considering i have certifications in SQL Server/T-SQL!
I have searched online for a solution, but it seems that everyone with the same error is trying to do so much more.Why is nothing in Oracle simple? Give me SQL Server any day of the week.
OK, Now that the syntax has been corrected with "Chanchal Wankhade" I have an entirely new issue. I am sure this issue has to do with my case statement logic. I am getting multiple rows, when I am only looking for one. Here is my code:
SELECT CASE WHEN EP.PHYSICAL_DATE IS NULL THEN CASE WHEN EC.ORIGINAL_CONTRACT_START < ((SYSDATE) - 365) THEN 'NEEDS PHYSICAL' [code].....
However, only one of these rows should be the output, which is "No". How do you get a nested case statement to evaluate to one result, instead of multiple? I'm quite sure it is in the logic. To spell it out, this is what I am trying to accomplish with the above code: If the "EP.PHYSICAL_DATE" is null, then use these sets of formula's to evaluate the output, BUT if the "EP.PHYSICAL_DATE" is not null, then use these set's of formula's to evaluate the output.
As it stands now, it appears as if my nested case statement is doing exactly what I told it to do, which is to evaluate both conditions, and output both.
I'm working with Object types containing other object types, and I'm getting the error PLS-00363 (Expression cannot be used as an assignment).I'm putting exlpicity all 'SELF' parameters as 'IN OUT', but still get the error...
CREATE OR REPLACE TYPE TYP_PERSON AS OBJECT ( strName VARCHAR2(100), -- CONSTRUCTOR FUNCTION TYP_PERSON RETURN SELF AS RESULT, -- MEMBER FUNCTION getName (SELF IN OUT TYP_PERSON) RETURN VARCHAR2, MEMBER PROCEDURE setName (SELF IN OUT TYP_PERSON, pNewName VARCHAR2) ) NOT FINAL; [code]....
How can I do this parentObject.getChildObject().setChildFunction()?
?I have the following requirement, the output should be:
Ticket count (sr_number) % of tickets inside DL Number of tickets inside DL Average cycle time (cycle time = closed date - created date) Total cycle time (cycle time = closed date - created date) Number of reassignments (sum)
DL - (deadline) formula is, closed date <= target_date
This should be displayed, grouped by year, then month and then by assignment group. The values should be in descending order(dates) Not sure how group by will work here.I am able to write the basic code for the above, but group by based on year, month and assignment group is pretty confusing to me.
It appears that the Oracle RAC cluster does not recognize the 'dba' group membership assignment to a user, unless it is assigned as the user's primary group.This also only affects newly created users, older users do not have this issue.
This affects us in the following way:
We are unable to create new cluster resources with the SAPCTL tool with the <sid>adm user.
When we try to create a resource using sapctl as the <sid>adm user we receive the following error:
CRS-0259: Owner of the resource does not belong to the group. SAP ABAP VIP creation failed.
The description of this error is that the resource owner (<sid>adm) is not a member of the group defined by 'osdbagrp' (what the cluster expects group membership for os users to control cluster resources), in our case this group is 'dba'.This is despite the user definitely being a member of the group.
Resource creation workaround:We are able to get around the resource creation by creating the resources either as root user, or by assigning 'dba' as <sid>adm's primary group.However, although we are able to control the VIP cluster resource, we are unable to control the other SAP enqueue resources as we assume that they need to be created by a user with 'sapsys' as a primary group (i.e. the resources do not start or it appears that there is not even an attempt to start the resources as there are no SAP startup logs created).
We have attempted multiple variations of creating and controlling cluster resources: changing <sid>adm primary group, changing cluster resource permissions, with nothing working for us.
The bottom line is that we need to be able to create these sapctl resources with a <sid>adm user that has 'sapsys' as a primary group while also being a member of the 'dba' group.
Update employee e set e.dept_id = (select d.dept_id from dept d
[Code].....
The above is not the exact code which I am executing but an exact replica of the logic implied in my code.
Now, when i display the value of 'rows_updated' it returns a value greater than 0,i.e 3 but it should ideally return 0 since there are no records matching for the condition: (select d.dept_id from dept d where e.dept_name = d.dept_name)
So, I executed the statement: select count(*) from employee e where emp_id = 1234 and exists (select 1 from emp_his ee where e.emp_id = ee.emp_id) and the result was 3 which is the same value returned by %rowcount.
why this is happening as I am getting incorrect values in %rowcount for the number of rows updated.
I currently have around 560 rows of bad data where
GL_Encumbered_Date and TO_CHAR(PRD.GL_ENCUMBERED_DATE,'YYYY' display correctly.
The problem lies in
PRD.GL_CANCELLED_DATE which displays correctly (example: 30-APR-10) but TO_CHAR(PRD.GL_CANCELLED_DATE, 'DD-MON-YYYY') for the same row displays as 30-APR-0010.
I have separated out the incorrect year with:
to_char(prd.gl_cancelled_date,'YYYY') where each of the 560 rows display 0010 instead of 2010. I am unsure if arithmetic with dates is allowed within SQL- but am curious if the operation date + number will result in a date.
If this is so, how could one go about taking 0010 as a date and add 2000 to create 2010 for: PRD.GL_CANCELLED_DATE.
Would like to ask expert here, how could I insert data into oracle table where the value is 03 ? The case is like that, the column was defined as varchar2(50) data type, and I have a csv file where the value is 03 but when load into oracle table the value become 3 instead of 03.
am facing a Problem in creating display items .First I create a display item and name it ITEM6This Display item will show Department name of employees .I create a Block Level Trigger to invoke this display item .
WHEN-VALIDATE-ITEM select dname into :ITEM6 FROM DEPT where :EMP.DEPTNO=DEPT.DEPTNO;
In Property Palette I edit following Data Type : char canvas :canvas4
Then I save ,compile and Run it successfully .But when i press Execute Query button . It shows me Following error message .
SELECT ROWID,ITEM6,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM scott.emp
Is there any way to Create Form in which i give a query and in result it return the query result in detail block in form of fields just like we return in toad or pl/sql developer.
I installed 11.1.0.6. on Linux RedHat 5.5. Then upgraded to 11.1.0.7. However when I issue sqlplus /nolog and do select banner from v$version it shows 11.1.0.6. Although the install and upgrade were successful. I wonder why it does not show 11.1.0.7. I upgraded using the installer and not dbua could this be the reason why?
We just upgraded to 11g and have run into incorrect results for some of our LEFT JOINs. If the table, view, subquery, or WITH clause that is being LEFT JOINed to contains any constants, the results are not correct.
For example, a test (nonsensical) view such as the following is created:
create or replace view fyvtst1 as select spriden_pidm as fyvtst1_pidm, 'Sch' as fyvtst1_test from spriden where spriden_last_name like 'Sch%' ;
When I run the following query, I get correct results; that is, only those with "Sch" starting their last name are listed.
select spriden_pidm, spriden_last_name, fyvtst1_pidm, fyvtst1_test from spriden join fyvtst1 on fyvtst1_pidm = spriden_pidm ;
However, when I change the JOIN to a LEFT JOIN, the last column contains "Sch" for all rows, instead of NULL:
select spriden_pidm, spriden_last_name, fyvtst1_pidm, fyvtst1_test from spriden left join fyvtst1 on fyvtst1_pidm = spriden_pidm ;
We've discovered other quirky things related to this. A WITH clause with similar logic as the above view, when LEFT JOINed to a table will also cause the constant to appear in each row, instead of NULL (and only the value where there is a join). But when additional columns are added to the WITH, it behaves correctly.
This is easy enough to rewrite - but we have WITHs and views containing constants in numerous places, and cannot hope to track down every single one successfully before the incorrect results are used.
Finally, the NO_QUERY_TRANSFORMATION hint will force the query to work correctly. Unfortunately, it has a huge negative performance impact (one query ran for an hour, vs. 1 second in 10g).
CREATE TABLE tmp_guid ( c1 raw(16) not null ,c2 raw(16) not null ); begin
[code]...
It seems that a combination of a unique index and extended stats are to blame. Removing any one of them causes the query to also produce correct results.Extended stats basically captures the fact that despite being unique, c1 depends on c2.
I have migrated from Oracle 8i (8.1.7) to Oracle 10g, but when I execute a query in 8i without any order by clause, I get a result in ascending order. The same query when executed in 10g gives a result which is not ordered. How to get an order result in 10g. There are many forms and reports which use lov which are not ordered. Can I set the ordering at the database, so that I do not have to alter all the forms and reports.
I have also migrated my forms from 5 to 6, but the combo box in some forms in 6i do not appear at run time. How I can solve this problem. I have attached an forms5 .fmb file/.
I can't seem to understand why the hour is incorrect. Below query "dte_computation_on_data" is the old function they use to convert date and insert it to the table. Problem is when I revert it to the actual date the hour is incorrect.
CODE SELECT -- THIS HERE IS MY TEST TO REVERT TIME AND DATE ON THE FORMULA OF WITH RESPECT TO THEIR FUNCTION to_char(TO_DATE('19700101', 'YYYYMMDD')+(tb1.dte_computation_on_data/86400),'MM/DD/YYYY') || ' ' || to_char(to_date(mod (tb1.dte_computation_on_data,86400) ,'sssss'),'hh24:mi:ss ') revert_test, systimestamp,tb1.dte_computation_on_data from ( SELECT -- THIS IS THE FORMULA OF THE OLD FUNCTION THEY USE TO CONVERT DATE TO NUMBER AND INSERTED ON THE ROW floor((CAST(SYS_EXTRACT_UTC(systimestamp) AS DATE) - TO_DATE('19700101', 'YYYYMMDD')) * 86400) dte_computation_on_data FROM dual)tb1;
I am working on the Reports Builder 10.1.2 at the Database Oracle 11.2.0. I have developed a report , which shows the page number at top right cornet. So I have selected a field and set the Source as "page Number". While I run the report through Reports Builder,it is properly displayed the page number. But while I copied the report to unix and run through "$ORACLE_INSTANCE/config/reports/bin/rwrun.sh", the page number is showing as 65536 . By changing the Source of the field to "Total Page" or "Physical page Number" make no difference.Every times It is showing the page number 65536 in the pdf output of the report.
dbms_metadata is giving me code that will create more datafiles than the original, and furthermore won't run: firstly, because two files are named with a null string, and secondly because it includes RESIZE commands which won't work because they nominate OMF file names. Or is dbms_metadata unreliable?
Any success using the sys.anydata.ConvertClob member function when trying to insert data into a Anydata column? This function has been listed in Oracle documentation since 9i, but even in 11G I get the error :
ORA-22370: incorrect usage of method AnyData Insert.
Code is pretty straight forward using a CLOB column in a after insert trigger :
I am having issue with configuring listner name on Oracle Server.My default listener is working,But I have stopped the default listener and tried to create another listener is differnt port,but no success,It always says The address of the specifed listener name is incorrect.Below is the listener.ora file
The following query is used to generate a flash chart:
select null link 'Available' as "Available", to_number(max_licenses - consumed_licenses) from software_detail where capture_date = trim(GET_CAPTURE_DATE) and software_product = :p1 and software version = :p2 [code].....
But the 2d doughnut flash chart shows:
Available = 39 Consumed = 1
How is this possible?
Oracle 11.2.0.3 RAC on Windows 2008 R2 APEX 4.2.1.00.0 with Apex Listener 2.0 on Apache Tomcat 6.
When I tried to have a Cursor with SELECT CASE statement in Forms 6i , it is not working. But the same query is working in SQL PLUS . We cannot use case in Forms 6i ?
I try to apply update statement on the button for the specific records, the command does executes if in the end i write execute_query it always asks for do u wanna save the records, yes or no. I want to remove that message. Also if i write commit it does remove that message but everytime i press the button it updates the old record fine but also adds one more entry for the same record.
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Solaris: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production
Current written logic for reconciliation:
1. Load data from source_a in a staging table using date filter
2. Load data from a file (Source_B) in temp table
3. Algo for reconciliation:
fetch value from source_B and if an entry exists in source_a then match say 10 columns if they match update reconciliation_oke = TRUE
there is an and written for testing all the 10 columns There is report generated out this which shows non matched columns and the entries which are missing....Now the requirement is to modify the logic in a way which shows which all columns are mismatched on the report in case present.
Since there are around 10 thousand records which would be reconciled on a daily basis, performance also needs to be taken care of...I guess i would be required to use PL/SQl tables...
