I don´t care about the order of the values in the row. In other words, I want to get disjoint sets of data connected by any of both values.Every pair in the input table is unique.
I have seen in the web that it is possible to do using connect by and hierarchical retrieving but I've been trying to make a lot of combinationts and I can reproduce the output.
we have a Oracle 11g database, but it does not support UTF8 character set. Now we have received a file which contains records with Trade Mark symbol (special character). Now when we are trying to load the record, it is getting loaded with a sign "?". As a result when we try to display record in web application, the application is not able to show the records properly.
I have 2 tables, AFF_TEMP and COUNTY AFF_TEMP has the following columns FNAME, LNAME, EMAIL and COUNTY COUNTY has 2 columns COUNTY_ID and CNAME
Both tables have the following test data AFF_TEMP Joe, Bloggs, joe@gmail.com, '' Ann, Bloggs, anne@gmail.com,Donegal
and COUNTY column in AFF_TEMP can contain a NULL value
County table has the following Test data, 1, Dublin 2, Donegal 3, Tipperary, 4, Galway
I am trying to select the following from both tables FNAME, LNAME, EMAIL, COUNTY_ID.Tried the following queries select a.FNAME, a.LNAME,a.EMAIL, C.COUNTY_ID FROM temp_aff A LEFT OUTER JOIN COUNTY C ON A.COUNTY=C.CNAME OR (A.COUNTY IS NULL) select a.FNAME, a.LNAME,a.EMAIL, C.COUNTY_ID FROM temp_aff A, COUNTY C WHERE C.CNAME IN (SELECT UPPER(A.COUNTY) FROM TEMP_AFF A)
We have database with multiple fields containing NULL values and in many queries we have NVL function which in turn is suppressing the index usage when in fact it is really essential (selectying very few rows from massive data) instead of creating lot of Function based indexes (NVL) or composite indexes with (nullable_column, constant) I am thinking of settting a default value for most of the fields In that regard I have some queries :
Which approach is better - setting default value for the fields or updating the fields with default value and modyfing inserts to take care of future data? Though altering table and modifing column to set default value looks better considering it will take care of data inserted in the future, it will invalidate the subroutines.I understand in 10g both statement will generate lot of undo (though in 11g, I heard things changed for setting default value of a column) How to take care of all the queries which are using the criteria 'where column1 IS NULL' or 'where column1 IS NOT NULL'. It will be really difficult task to manually change each and every occurrence of such condition even using user_source.
Finally for numeric values say for ID field which starts from 1 onwards 2,3,4 etc, we can set 0 as sensible default so that the performance is not affected.
Is there such precaution for varchar2 field purely from performance point of view?
I'm trying to add edmx file in my project for first time. I want to choose the oracle provider ODP.net but cannot find Oracle in the data source list. I have oracle 11g installed , odp and odt installed and can access it from the solution as well. I saw the Oracle listed under data source when I tried to connect the solution to the database through server explorer. The solution is connected to Oracle database through ODP.
I need to export only the data from schemas or tables, how to do that with Oracle Data Pump? when we use schemas parameter this export all schema, not only the data right?
I have set up a cross platform (Microsoft Windows IA (32-bit) -> Linux x86 64-bit) data guard and it worked fine.Then I did a switch over (which again worked) and found out the data is not getting replicated at all.. checked the data files available from the new primary database and found out they are in the windows format as below..
SQL> select name from v$datafile;
NAME -------------------------------------------------------------------------------- D:ORACLEAPPADMINISTRATORORADATAMFSSYSTEM01.DBF D:ORACLEAPPADMINISTRATORORADATAMFSSYSAUX01.DBF D:ORACLEAPPADMINISTRATORORADATAMFSUNDOTBS01.DBF D:ORACLEAPPADMINISTRATORORADATAMFSUSERS01.DBF D:ORACLEAPPADMINISTRATORORADATAMFSRMANRMAN_TS01.DBF
and physically they were created at '/home/app/oracle/product/11.2.0/db_1/dbs/' and as
I want to drop some users in Oracle DB using sqlplus but I am getting error:
SQL> DROP USER test CASCADE; DROP USER test CASCADE * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected.But when I ran below command to know sessions connected I am not getting any results:
SQL> select sid,serial# from v$session where username = 'test';
I have an requirement like below and would like to have SQL for that.
Source Table:
EMP_NO EMP_CODE 1 'A' 1 'D' 1 'E' 1 'F'
2 'S' 2 'A' 2 'W' 2 'Q'
3 'A' 3 'T' 3 'D' 3 'E'
4 'D' 4 'A'
I want to load only data which has EMP_CODE as A and doesn't have subsequent 'E' or 'F' in it. In the above source you can see EMP_NO 2 and 4 satisfy the condition and rest wont. So i want the output data like below.
I am facing difficulty in connecting Oracle 10g client to Oracle 10g database. Both the machines are in windows xp with service pack-2. The following observations are made in server machine
1. The loopback test of the server is successful. 2. One net service name created in the server end. 3. Database connection is successful with above service name. 4. tnsping <server ip address> is successful
# This file is actually generated by netca. But if customers choose to # install "Software Only", this file wont exist and without the native # authentication, they will not be able to connect to the database on NT.
# This file is actually generated by netca. But if customers choose to # install "Software Only", this file wont exist and without the native # authentication, they will not be able to connect to the database on NT.
I'm a beginner in the PRO*C and I have this issue/I have to maintain a multithreaded C++ PRO*C application and has the following structure
1) Application runs waiting for requirements 2) When detects a requirement it creates a Thread to work 3) The thread connects to Oracle with a EXEC SQL CONNECT :uid IDENTIFIED BY :pwd; instruction 4) Calls some anonymous calls to execute a stored procedure
EXEC SQL EXECUTE BEGIN commit; :Status:=stored_procedure(:param1, :param2); commit; END; END-EXEC;
5) If anything went wrong then issues a EXEC SQL ROLLBACK WORK RELEASE; then reconnects and the thread die 6) If everything was OK then the thread dies, but never called a EXEC SQL COMMIT WORK RELEASE; to disconnect from Oracle
I don't know why never the application disconnects from Oracle, maybe is for performance. Sometimes the application crashes and I believe that maybe the error is for never freeing the connections, the PROC compile line is this
Which is the way to implement the connections?, connect and disconnect when done? or just keep the connection alive? Which way has better performance, connect and disconnect or just keep the opened connection?
I am writing a after trigger for one of my tables on every insert update and delete for my dataware house staging area. The process here is when ever there is a change in the production database we need to capture this change in our changing area through triggers.
I am able to create the triggers but i am stuck with the exception handling portion of the trigger. I want to write an exception in the trigger where when the staging area is locked or for any other matter the data needs to be able to go to a error table when the staging area is not able to accept the data for some reason.
how i can write this excepyion in the trigger or anyother method i can follow to be able to handle this scenerio.
I am attempting to use the following select to get a specific emplid. However, the ps_names table contains some alphabetic characters. I want to only focus on the emplid's that contains numbers. Is there a way to modify the following select to do this?
bubbagumpshrimp "ORA-01722: invalid number" SELECT x.y from (select PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY to_number(emplid)) over () y from PS_NAMES where emplid > '000000000' and emplid < '999999999') x where rownum = 1;
Why it's not excluding '0-5' and '25-30', how I should write code to exclude this and Is there is any function in oracle to check for numeric in column and print.
SELECT * FROM ( SELECT 1 ORDR_ID, TO_DATE('01-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 5 SIZE_, 'APD' Colr_CD FROM DUAL UNION ALL SELECT 1 ORDR_ID, TO_DATE('15-JAN-2012', 'DD-MON-YYYY') INWD_DATE, NULL SIZE_, 'KPD' Colr_CD FROM DUAL UNION ALL SELECT 1 ORDR_ID, TO_DATE('16-JAN-2012', 'DD-MON-YYYY') INWD_DATE, NULL SIZE_, 'ALD' Colr_CD FROM DUAL UNION ALL SELECT 2 ORDR_ID, TO_DATE('02-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 9 SIZE_, 'APD' Colr_CD FROM DUAL UNION ALL SELECT 2 ORDR_ID, TO_DATE('05-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 10 SIZE_, '' Colr_CD FROM DUAL UNION ALL
[code]....
Where every first row is the initial entry for an order and following rows are the changes done to the order with modification date
I have tried to make a form in form 6i to fulfill above mention scenario. But when two or multiple users tried to enter data from this form all users entered into deadlock or hang stage.
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET'; PARAMETER VALUE ------------------------------ ---------------------------------- NLS_CHARACTERSET AL32UTF8 SQL>
There is table (VIN_TEMP) in my company database containing following records. It seems like this table should contain some greek language special chracter values instead of this weird data.
Client are reporting these records as invalid and requesting us to fix. As i investigated i found out, this table was created and loaded few year back. Client sent us one time files which we loaded into this table. I was able to find the code which was actually used to load this table, but unfortunately i was not able to find the raw files where we load this data from...
It seems like Previous Developer specified character set "UTF8" statement, in his sql loader script, to load this data. It seem those file contain some Greek language special character data which was not support by "UTF8" charater set and result in creating those invalid data. My Job is to fix these invalid records and convert them back to its original values which were present in the raw file. I tried to contact client and see if i can find out the raw files but no luck. I tried to use convert function as mention to convert this data from "UTF8" to our current character set format but no luck.
I am working on a form having three data blocks Travel_Inconvineance (Master) TI_Card_Info (Detail) TI_Limit_Indemnity (Detail)
On working TI_Card_Info i have 2 columns Plastic_Card_Id and Plastic_Card_Type which i want to be paste on TI_limit_Indemnity data block as i enter in TI_Card_Info.
I should get values as 0, -34 and 77. But I don't get this as all operations with null gives null. These are fetched data and don't exist in the form shown. I have to use these row values of "Change" further. Is there any way of obtaining these results?
As data completeness, data consistency, data integrity are some of features of good quality data. What is best approach and best practice to deal with missing or null values in database? I am facing such type of situation. In a main table, there are several columns have null or missing values.
Get rid of null or missing values. Should I consult source documents from where data was fed into database to achieve maximum data completeness?
How to handle special charater '&' in stored procedure.
Following is a test code I want to Implement
CREATE OR REPLACE PROCEDURE SP_Test(QueryID NUMBER,DateFirst DATE,DateLast DATE) IS BEGIN INSERT INTO TempTable(QueryID,LineID,data,Datetime) SELECT 1,6,Emp,startdate FROM salary
[code]....
The code above resuts into INVALID state of stored procedure and cannot be executed.
I have a sample table where I am arranging data in required format using 'decode' by hardcoding all values, how can we do same using pivot method, can pivot be only used when we want aggregate values in oracle?Also can't we do what i did, using generic max(decode) with select query, if it were a large table (i don't know which would be pivot column here?) ?
SELECT waarde1,waarde2, APEX_ITEM.POPUP_FROM_QUERY (3,waarde3,'select select ((waarde1-0.1)+(level*0.1)) d, ((waarde1-0.1)+(level*0.1)) r from (select * from lov_test where waarde1 = c001) connect by level <= (((waarde2-waarde1) *10)+waarde1)') waarde3 FROM lov_test ORDER BY 1
The idee is to get a popup or dropdown box for "waarde3" in witch the selectable values are waarde1 to waarde2 rising with 0.1 at a time.
The error I get is:Error in init lov: ORA-00936: Ontbrekende uitdrukking. p_lov:select select ((waarde1-0.1)+(level*0.1)) d, ((waarde1-0.1)+(level*0.1)) r from (select * from lov_test where waarde1 = c001) connect by level <= (((waarde2-waarde1) *10)+waarde1)wwv_flow_security.g_security_group_id:1264429985836387wwv_flow_security.g_curr_flow_security_group_id:1264429985836387 Unable to initialize query. For every row in the table lov_test.
Now I have another db where I wish to import the above dump. This is not a empty db, however, I have dropped the particular user from this db for which I have created a dump above. Then I have created the same user using 'create user....'.And now I am trying to import the above dump into this.
import goes fine but I gets the circular synonym error for some of the types.
IMP-00003: ORACLE error 4055 encountered ORA-04055: Aborted: "O_BULK_TARGET_SELECTOR" formed a non-REF mutually-dependent cycle with "T_GENERAL_IDLIST". ORA-06550: line 5, column 25: PLS-00421: circular synonym 'PUBLIC.T_GENERAL_IDLIST'
The db from which I created the dmp has no errors for these objects, but while impoting it gives these errors.I have even tried taking the whole database dmp(not a particular user), and importing it into full empty database. Then also I gets the same error.
I'm trying to determine if/when a possible Hierarchical circular reference will occur in my data
Sample Hierarchical structure that I have
Emp -> Supv A BA CB DC EC
[Code]....
Finally, to my question. It seems that I can detect the problem After it happens but do I need a trigger on the update statement to detect if/when a possible circular reference will occur?? or can I run a sql statement prior to update to detect possible circular reference?
SQL> select view_name , text from dba_views where VIEW_NAME='EMP1_VIEW';
VIEW_NAME TEXT
EMP1_VEW select empid , ename , qual, dept from emp1 where empid between 1000 and 1010
Now i tried to use create or replace option
SQL>create or replace view emp1_view as *2 select * from emp1_view with read only;* create or replace view emp1_view as *+ ERROR at line 1: ORA-01731: circular view definition encountered
Actually i want to make read only view (emp1_view) ; without doping ;
I am getting "ORA-03114: not connected to ORACLE" while installing "Oracle Database 11GR2" on "Oracle RAC 11R2" cluster setup. I am getting this error at the time of "Oracle Database Configuration Assistant" configuration reaches till 39% on HP-UX 11.31 IA64 platform.
