I'm trying to form a query that returns a list of entities within a given rectangle, using SDO_WITHIN_DISTANCE. The query I've made seems like it should work, but Oracle is giving me some strange errors. Here's the query: SELECT * FROM TBLENTITYLOCATION TL INNER JOIN TBLENTITY TE ON TE.ENTITYID=TL.ENTITYID WHERE SDO_WITH...
This SQL statement works if I run it from my Oracle client (SQL Developer): insert into Person (Name) select 'Bob' from dual It also works if I issue it via Spring JDBC, without using a KeyHolder: final PreparedStatementCreator psc = new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(...
oracle i wish to select few rows at random from a table, update a column in those rows and return them using stored procedure PROCEDURE getrows(box IN VARCHAR2, row_no IN NUMBER, work_dtls_out OUT dtls_cursor) AS v_id VARCHAR2(20); v_workname VARCHAR2(20); v_status VARCHAR2(20); v_work_dtls_cursor dtls_cursor; BEGIN O...
Hi all, I've create a trigger which work on 2 tables like this: - when insert a new row on table A (GEDDFILES) then a number field on table B(GEDDIRS) will be increased. Code is getting compiled without errors but it doesn't work. the code is bellow: CREATE OR REPLACE TRIGGER "A"."TRGMAJNRFIC" AFTER INSERT ON "A"."GEDFILES" FOR EACH R...
Hi. Let's say I've got the following database table Name | Nickname | ID ---------------------- Joe Joey 14 Joe null 14 Now I want to do a select statement that merges these two columns to one while replacing the null values. The result should look like this: Joe, Joey, 14 Which sql statement manages this (if it'...
Hello. I'm an Oracle 10g user. I had to write some SQL queries, and spotted a mysterious (as I see it) behaviour. Let's pretend we have a table, which is able to join itself in some kind of simple two-level tree structure. The next query gives me "ambiguity error", which is expected: select title from table1 left join table1 on c...
hi, can i read column data in a cursor giving the index of the column? thanks... From the questioner's comments : "I need to create a generic procedure which will read a table or view (name of the view or table is the argument of the proc) and encrypt the data in the column and then will write the encrypted data to a OS file. " ...
I'm trying to run SQL in a PL/SQL procedure. If I were to say: SELECT * FROM myTable WHERE FNAME like 'Joe%' AND dept = 'xyz'; this runs instantly. if I try putting 'Joe' in a variable it takes forever to run, about 3 minutes. Here is some syntax I found for using like with variables. PROCEDURE myProcedure(firstName IN VARCHAR, ...
I want to filter counted query result. select count(distinct tmr_id) AS Count ,CONTRACTID from status_handling This query return 2 columns like: Count ContractID 1 23344 2 28344 2 34343 2 29344 1 26344 I just filter 2 (Count) values. How can I do this? Thanks. ...
I am wondering if there is a way to differentiate between warning messages and error messages in Oracle? Does a Warning message count as an exception? Are warning just errors? Is there a clear difference? And if so, is there a way to catch warning messages explicitly, or information messages? Thanks for any advice. ...
Hi, CREATE VIEW EVENT_LOCATION ("EVENT_ID", "STREET", "TOWN") AS SELECT A.EVENT_ID, A.STREET, A.TOWN FROM TBLEVENTLOCATION A JOIN TBLEVENTS B ON A.EVENT_ID = B.EVENT_ID WHERE B.REGION = 'South'; if I run SELECT ROWID, STREET, TOWN FROM EVENT_LOCATION then which ROWID should I get back? Reason I'm asking is: In the data...
Hopefully I can express this in a coherent and meaningful way: Two of our DBA's came up to me today after an email I sent explaining why we were using NHibernate and the benefits of an ORM framework. They have been pushing hard for refactoring our stored procs to include instrumentation like this: BEGIN DBMS_APPLICATION_INFO.set_module...
Friends, I'm converting an Oracle Form to Apex and have run into problem(s?) which I hope you can help with. Put simply the problem is how do I reference adjacent cells in an Apex tabular form? Is it possible? The reason for asking is that I need to create a tabular form with 3 dependent select lists, lets call them: Divisions ...
I have a multithreaded process which inserts several records into a single table. The inserts are performed in a stored procedure, with the sequence being generated INTO a variable, and that variable is later used inside of an INSERT. Given that I'm not doing mysequence.nextval inside the INSERT itself, it makes me think that it is pos...
I am losing precision in my ResultSet.getDate(x) calls. Basically: rs = ps.executeQuery(); rs.getDate("MODIFIED"); is returning dates truncated to the day where MODIFIED is an Oracle TIMESTAMP field of default precision. I think there may be some JDBC tweak I'm missing; usually TIMESTAMP is compatible with DATE, but I'm hoping I don...
I just installed the latest version of Oracle developer tools and couldn't install it in my 10g home directory because you can only do that if your home is 11g. Now when I'm in VS2008, create a new connection and test it. It doesn't connect because I'm assuming it can't find my tsnames.ora since it's located under my 10g home. Any way I...
I am brand new to NHibernate and Fluent NHibernate and I am trying to get the following confguration to work. private static ISessionFactory CreateSessionFactory() { return Fluently.Configure() .Database( OracleDataClientConfiguration.Oracle10.ConnectionString("Data Source=mysource;User ID=myid;Password=mypwd;") ...
Hi there I have some questions concerning the Oracle CEP. It would be fine if somebody could answer them. What are the differences between sequences and relations? How often each query is executed? What are the cases when we have to convert relation to sequence? How the join statement works? Is it applicable only for relations? Accord...
Is it possible to grab data from two tables (that have the same fields) into one view. Basically, so the view sees the data as if it was one table. ...
Hi, Recently we have developed an application which connects to oracle database for fetching records. For fetching records, we are using OLEDB for Oracle. Application runs fine on my machine by connectng properly with Oracle ,since i have oracle client installed on my machine. Now i tried to test application on another machine, which do...