How to replace single-quote with double-quote in sql query - oracle 10g ?
How can I replace single-quote (') with double-quote (") in sql query - oracle 10g? ...
oracle
Using an object type in a select statement in Oracle
I have a function that returns an object that has 3 values. Is there a way to call that function from a select statement and have each value be a different column? I could break it into 3 functions, but the values are related so I wanted to keep it as one for performance reasons. (So oracle doesn't have to call 3 very similar complex fun...
best time to SELECT value in Oracle nested query
From an optimization standpoint, I want to know when it's best to SELECT some arbitrary data as columns. Specifically, I have this query: Where do I put this part? SELECT 'String 1' AS segment FROM DUAL UNION ALL SELECT 'String 2' AS segment FROM DUAL UNION ALL SELECT 'String 3' AS segment FROM DUAL In Outermost Query I need access...
A query: select * returns data, but select Column does not return results
I have a query like this: select pln.* from plan pln where pln.id = '0003' and pln.seq = (select max(pln_es.seq) from plan pln_es where pln_es.id = pln.emplid and pln_es.career =pln.career and pln_es.nbr = pln.nbr and pln_es.d...
Sql Developer 2.1 query results window: turn off auto resizing?
I recently upgraded to Oracle Sql Developer 2.1 It's far better, but the query results window automatically makes itself really big each time I run a query, covering most of my worksheet. Does anyone know a way to turn this off, so that it stays the same size when a query is run? ...
Oracle - difference or changes between two rows from two tables
I have two tables both have same schema, one will have previous day records other will have current. I want to compare both and find only changes or only rows that have different value in atleast one column. How is this possible in pl/sql, oracle? (I did code something similar using checksum in T-SQL but not sure how to do in pl/sql) ...
How to calculate distances between coordinates EFFICIENTLY in Oracle
Hi Everybody! I have a large Oracle database ( 720,000 records aprox) where each record has its own geographic coordinates (lat & lng) and i need to select just the records that are in a specific distance from a point ( inside a specific radius). Currently i've implemented a distance function (based on haversine) that i've found in a...
Is `Delete From Join` Standard SQL?
Among other questions, this one asked how to delete from a join. My question: How much of this is standard SQL? On which databases would this actually work (most notably for me would be Oracle, MySQL and SQLServer)? ...
Opposite of INTERSECT in Oracle
I have two selects and I want to combine them in such a way, that only rows unique in both selects are returned. Is there any built-in way in Oracle 10g to achieve this? I know I can do something like this: (select1 UNION select2) MINUS (select1 INTERSECT select2) but I would like to avoid it. Both select1 and select2 have 20 lines,...
Hibernate mapping - compile error
Hello all, I get a compile error from eclipse with this hibernate query. Below is the code and the picture of eclipse compile error <sql-query name="endDateChecker"> <return-scalar column="PId" type="java.lang.Long"/> select pid as PId from info where end_date < tr...
number format in oracle
Hai, i have a problem with number format.i'm using oracle. I have a number field in database.But when i retreive it i need to be seen as floating point number For example: while retreiveing,now i got the result as 200 DR (DR for Debit,it is given manually). Now i need to get the result as 200.00 DR as the result. How can i solve this?Ca...
Mapping Calculated Fields in Hibernate
I'm not sure if this is possible in Hibernate but it would be really great if it was :) I've not actually got past the conceptual stage with this but I'll explain as best as I can. I want to make use of Oracle Spatial features to do proximity based searching. Imagine I've got a Location entity which stores a latitude/longitude value. Th...
Blob into Oracle: about 15% are filled with \00
Under Weblogic 10, I am using Hibernate to store data into several tables with BLOBs. It's always worked fine but the customer found specific circumstances where 15% of the BLOBs have the correct size but only contain null characters. I can't figure out what makes it good or full of emptiness. The BLOB type I am using does a: public vo...
PL SQL - Resembling (!a?0:a) functionality
Hi, I have a simple query in a cursor Cursor some_cursor IS select sum(some_field) from some_table table_1 where table_1.TYPE =1 AND TO_CHAR(table_1.date,'YYYYMMDD') = '20090905' AND table_1.f2 = 962 AND table_1.f3 = 41813; Then i do fetch some_cursor into some_var;--some_var is o...
Selecting transactions as a single row
Hey all, I have a requirement where I need to generate a report about current software projects. Two of the columns are the date of the latest milestone and the date of the milestone previous to that. Obviously, milestones are stored in a transaction table so we can have many milestone per project. I've gotten to here so far, but now...
Oracle external tables. Optimising select queries.
I have to perform many selects from an Oracle external table. I have 10 cursors that look a lot like this (ext_temp is the external table) CURSOR F_CURSOR (day IN varchar,code Number,orig Number) IS select NVL(sum(table_4.f),0) from ext_temp table_4 where --couple of conditions here, irrelevant for the question ...
Is using a SELECT inside a pipelined PL/SQL table function allowed?
The docs for pipelined functions say that DML is not allowed when they are used in a SQL statement (typically a SELECT), and in most examples the pipelined functions are used for data generation or transformation (accepting a custor as parameter), but not issuing any DML statements. Now, technically, it is possible to use SELECTs withou...
What is wrong with this PL/SQL? Bind Variable * is NOT DECLARED
Here is: declare v_str1 varchar2(80); begin v_str1 := 'test'; print :v_str1; end When I run it using SQLDeveloper just in a sql worksheet I get this: Bind Variable "v_str1" is NOT DECLARED anonymous block completed ...
Oracle DB - How to perform a batch search an replace across all the tables in a DB
I want to perform a search for a specific value "04L" in the column across all the tables,procedures,functions etc in the DB. I also want that output ( table,procedures that have same value) to the text file i.e the tables , procedures functions that have that value. Any help appreciated. ...
Oracle 10g express home page is not coming up
Hello All: My Oracle 10g Express Edition , I can login in the SQL plus but I cannot login into oracle via SQL developer and cannot view the Home page at link http://127.0.0.1:8080/apex. This was working fine until yesterday. I have checked via (WINDOWS)netstat -ab and no other app is using the 8080 port. The only thing I did today was...