Hi Guys. I've been reading up on database cursors, and every bit of sample code I've seen explicitly opens and closes the cursor. I've only used them a few times and I've never had to do this. Can anyone tell me why it is necessary to do this? I know if you don't close a cursor you can create memory leakes but i've never had to open one...
I have this query: select total.lecgrouplecture(l.groupcode) lecturename, total.lecgrouptime(l.groupcode) lecttime from total.lecgroup l where term = (select term from total.CURENTTERM) and rownum < 10 order by lecturename I want to know what total.lecgrouptime(l.groupcode) is, and get this informat...
hi all. i have used this query: SELECT text FROM all_source WHERE owner = 'TOTAL' AND lower(name) = lower('LECGROUPTIME') ORDER BY line to find context function that named LECGROUPTIME. ive received: Function LECGROUPTIME (inGroupCode in varchar2) Return Varchar2 is Cursor GetTime is Select Day,BeginTime,Endtime From Program W...
Let's say I have a table that has "user_id, date, score", and every user has exactly one score every month, but not always on the same day. I want a query that has "user_id, date, score_delta" where score_delta is how much the score will change between "date" and the next month? Am I going to have to do something lame like to_date(to...
Background: I'm calling a Web Service written in ASP.NET that queries an Oracle database. I know the Web Service itself works, because I've used it before other applications. So I have a web application in Visual Studio that I've been switching back and forth to point from a 'DEV' web service to a production configured version of the s...
I recently was reading about Oracle Index Organized Tables (IOTs) but am not sure I quite understand WHEN to use them. So I have a small table: create table categories ( id VARCHAR2(36), group VARCHAR2(100), category VARCHAR2(100 ) create unique index (group, category, id) COMPRESS 2; The id column is a foreign ...
I'm a bit new to Oracle's PL/SQL (using 10g), I was wondering if there's a way to make a private method in an object type, as is often done for private helper methods in other languages (Java, C++, C#, etc...). I know it is possible to make private methods in packages, but I can't seem to find a way to do this for object types. I keep ge...
Hey all, I've just finished my first "real" APEX app but it's looking a bit messy. Is there any way I can renumber the pages so that they're back in order of the application flow? Thanks in advance, Matt ...
For the various popular database systems, how do you list all the columns in a table? ...
I have two date's: 1) 01/05/2009 2) 06/05/2009 How can I know if there's 5 days between them? ...
I have a table with ~30,000,000 rows that I need to iterate through, manipulate the data for each row individually, then save the data from the row to file on a local drive. What is the most efficient way to loop through all the rows in the table using SQL for Oracle? I've been googling but can see no straightforward way of doing this....
Situation: I am creating an app in C# that uses Oracle.DataAccess.Client (11g) to do certain operations on a Oracle database with stored procedures. I am aware that there is a certain enum (OracleDbType) that contains the Oracle data types, but I am not sure which one to use for certain types. Questions: What is the equivalent Orac...
I need to convert an oracle table into xml and then return it to table form. I converted a table using xmlgen, but I don't know how to reverse the conversion. I'm looking for an example of converting an xml file into a table. ...
I am trying to get the string_agg function, detailed here to work with the distinct keyword. Sample usage: select string_agg(distinct a.name), a.id from tbl_a a group by a.id The problem is that this works in sqldeveloper, but when running it in application express in an anonymous pl/sql block, it refuses to accept the distinct keywo...
I am using Nhibernate for oracle, and I need to increment my primary key value for each insert. Which one is the best way and efficient way? Oracle sequence, Nhinerbate increment or another way? ...
Hi, I am developing a enterprise software for a big company using Oracle. Major processing unit is planned to be developed in PL/SQL. I am wondered if there is any ORM like Hibernate for Java, but the one for PL/SQL. I have some ideas how to make such a framework using PL/SQL and Orcale system tables, but it is interesting - why no one h...
Hi I have a database with loads of columns and I want to insert couple of records for testing, now in order to insert something into that database I'd have to write large query .. is it possible to do something like this INSERT INTO table (SELECT FROM table WHERE id='5') .. I try to insert the row with ID 5 but I think this will create...
Hi, I have a table containing a column of type Number create table tmp ( /*other fields*/ some_field Number ) and in a PL SQL script, I want to convert that field to a varchar. However, i don't know its length, so I get an exception Exception message is ORA-06502: PL/SQL: numeric or value error: character string buffe...
I have some large tables (millions of rows). I constantly receive files containing new rows to add in to those tables - up to 50 million rows per day. Around 0.1% of the rows I receive are duplicates of rows I have already loaded (or are duplicates within the files). I would like to prevent those rows being loaded in to the table. I ...
I want to extract data from database where field "end_ date" is less than today's date and where end_date is not equal to null; how would I do that? I figured the second part out .. I have trouble with first part select * from table where to_char(end_date) IS NOT null ...