I'm stuck with a SQL Query. I have this table: [Reserve] ID NUMBER START_DATE, DATE END_DATE, DATE .......... (more cols) I need to look if the next count is more than 0 for any of the dates between START_DATE and END_DATE (including both). If the count is more than 0, the query must stop inmediately and return "There is no loca...
hi I have A anb B in String format A = 14/01/2007 B = 22:10:39 I try to insert date and time: SQL = "insert into MyTbl(Tdate,Ttime) value ('" + Convert.ToDateTime(A) + "','" + Convert.ToDateTime(B) + "')"; i got ORA-01843 error, what I can do ? thank's in advance ...
Hi, We plan to configure a stored procedure to run as a batch job daily using Oracle DBMS scheduler package. We would like to know what would be the best way to log an error message when there is an error occured. Is logging to a temporary table an option? or is there a better option. Thanks in advance. ...
I have a query select * from myTable ...and I want to wrap this query inside a stored procedure, and have the store procedure output the results of this query. How do I do it? In ms-sql, i can store my query as a string to a string variable. And then do "Execute (variable)". Why no such thing in Oracle? ...
Hi, I am going to create an app for managing grid-like model - cells at [X,Y] coordinates. This model will be persisted in a RDBMS. I want to keep much of the processing right in the DB server. So I am looking for an PL/SQL library for 2D grid processing (each row represents one cell). It should know to do algorithmic things like s...
Given: Oracle 10.2g is the database I have a table called emp. emp has a VARCHAR2 column called SECRET. SECRET might contain a plaintext string, or it might contain an encrypted string, but I can distinguish one from the other A function called DECRYPT already exists that, given the encrypted string, will return an unencrypted string. H...
Hey all, I've recently started a new position as a developer and I'm having a bit of trouble with PL/SQL. I've used MS SQL for a number of years but I'm finding PL/SQL a bit trickier. One of the things I used to do when writing functions and stored procedures in MS SQL was to put reoccuring result sets into a table variable so I would...
Following on from my last question (http://stackoverflow.com/questions/1573326/table-variables-in-oracle-pl-sql)... Once you have values in an array/table, how do you get them back out again? Preferably using a select statement or something of the like? Here's what I've got so far: declare type array is table of number index by b...
I am on Oracle 10g. I have a table that contains all the files stored in the system during the past year. I want to make statistical monthly deposits, grouping them by file size. eg 0-1m 1m-10m 10m-100m 100m + So my results would look like : Month, 0-1m, 1m-10m, 10m-100m, 100mplus 2009-03, 999, 999, 999, 999 I want to use Oracle's ...
I've spent hours searching the web for an answer to this question... Here's what I currently have: select * from order_header oh where tran_date = sysdate-1 Thanks in advance. ...
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...
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...
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...
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 have a string. Let's say: "abcdea" I have to find out what is the most used letter in this string using a anonymous pl/sql block. How can I do this? ...
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 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...
Hi. Im working on a pl-sql script, in which I have about 10 TO_CHAR conversions. One of them is throwing an ORA-06502: PL/SQL: numeric or value error: character string buffer too small exception. Currently, im logging the message with this piece of code EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('Exception message is '|...