Ok SQL and Oracle gurus I have a somewhat complicated query that I'm trying to build. Here is my current query: select distinct person_info.person_name table2.value, table3.value, table4.value, table5.value from person_info left join table2 on table2.person_name=person_info.person_name left join table3 on ta...
Have excel set up to pull some count and sum information from a database view in oracle using ODBC. In my sheet I want to populate about 10 different fields in one sheet with the single result from different queries. It works except for one thing. Every time I open the file it asks for the database username and password 10 times. ...
This is a snippet of my query: and ( ADDR_FULLTEXT like upper(:streets1) ) Whenever i execute the query this is a part of, it executes in about 0.05 seconds. This is perfect! But, when I do the following: and ( ADDR_FULLTEXT like upper(:streets1) AND ADDR_FULLTEXT like upper(:streets2) ) it results in a ...
SELECT a,b,count(*) FROM t GROUP BY rollup(a,b) result: a1, b1, 10 a1, b2, 90 a1, , 100 i need: a1, b1, 10, 100 a1, b2, 90, 100 how? ...
Howdy, I'm running into problems with JMS in Oracle AS 10.1.3.3. The error I am getting is 'connectors\ojms.rar (The system cannot find the path specified)' from EM. Below are a few bullets on the environment. This is on a Windows XP SP3 machine. All configuration is through EM. The EM is a local instance that has been used for ov...
I'm not sure if I phrased that correctly but here's my dilemma. I inherited an application which needs to look up the company associated with each contact in a list. The way it is currently written the application queries the DB on the following: SELECT c.name FROM company c WHERE c.id = (SELECT u.company_id FROM u...
I have multiple data sources inserting and updating to an oracle10g database. I also have multiple clients that would like to know when an insert or update has occured. I would like to know if oracle has some kind of native ability for my clients to realize that an insert or update has occured? I feel like the answer is no, but wanted to...
I have two tables, and I need to determine the company that offers the highest average salary for any position. My tables are as follows: employer eID (primary key), eName, location position eID (primary key), pName (primary key), salary) The code I wrote determines all avg salaries that are higher than one, but I need to find only ...
I want to find records in a (Oracle SQL) table using the creation date field where records are older than 30 days. It would be nice to find records using a operators like > but if anyone can suggest quick SQL where clause statement to find records older than 30 days that would be nice. Please suggest Oracle syntax as that is what I am us...
I'm accessing a view table with 6 million lines from a oracle database using the ADOdb for PHP library. running the following SQL query from a web server in a SUSE machine takes about 1 min: $adodb_handle->Execute("SELECT COUNT(BEPROTEXT) FROM (SELECT BEPROTEXT FROM V_E_LOG WHERE BEPROTEXT LIKE '%jaya%' )"); running the same SQL...
We have a product using Oracle, with about 5000 objects in the database (tables and packages). The product was divided into two parts, the first is the hard part: client, packages and database schema, the second is composed basically by soft data representing processes (Workflow) that can be configured to run on our product. Well, the b...
Is there any way to get the Oracle query from PreparedStatement .I know its not possible as such I can't use log4jdbc or p6spy as it is a secured application and using this will create bigger problems..toString won't work as I am using Oracle? I can't change PreparedStatement to Statement either. ...
I had a 'procedure A' in which it has to return a null cursor to front end and then immediately it should continue with the next step in which it will call a procedure which will take 20 min to complete the proc. Procedure A(cur_out refcursor) begin OPEN cur_out for select null empname, null empid from dual; procedure B();//Wil...
hello, i am having problem with my query... this one works.. select name, bday, address, dbms_random.value(1, 100) as joker from employee order by joker asc but when i try to get what i want using either the 'where' and group/having clause, i am having a ora-09004 (invalid identifier) ERROR.. e.g. select name, bday, addres...
Hello. I have oracle dump file from Oracle8. I am trying to import data in Oracle 10. For importing data I am using PL/SQL Developer(buy the way, I have TOAD client). I am doing that by click Tools->Import Tables choose Oracle Import, and then choose Import file. after that click Ok, Done. so How I can find imported tables? I am using "s...
Is it possible to create/replace PL/SQL via the Oracle JDBC driver, i.e not via SQLPLus* ? Update 1: Driver: ojdbc14 (JDBC) Update 2: Change is being applied to oracle via Ant <SQL> task, which is passed a sql script file. In this instance using SQLPlus in the Ant script is not possible (long story). ...
Just want to know the exact effects of statement.setEscapeProcessing(false) as anywhere in documentation i am not finding proper explaination here are my questions 1) statement.setEscapeProcessing(false) will not any effects if we dont have escape clause in query . is this correct ? 2) If first one is correct why query modification ...
Hi lads, I have another simple one. How to make a long decode inside a function in oracle? My select looks like this: select something, sum(ofsomethingelse) from a_table where code in ('390','391','392','393','394','395','396','397','398','400','402','406', '407','408','409','410','411','412','413','414','416','418','471','473', '173...
Hi all, I have an oracle table which has a column that stores XML in a CLOB format. The XML has the following syntax: <?xml version="1.0" encoding="ISO-8859-1" ?> <BaseXML Version="2009.4"> <InvoiceCanvasDetails> <Grouping> <ParentGroup Name=”Parent group 1” ID=”100”> <ParentGroupLineItems> <Item ID="46...
I have a table containing roles. Many users can be assigned to a role. Permissions can be assigned to a role, granting all users to that role with the permission. When I assign a permission to a role that has 500 hundred people, I've effectively created 500 permission assignments. With each permission that is assigned to a role I spe...