Reputation
15,413
Top tag
Next privilege 20,000 Rep.
Access 'trusted user' tools
Badges
1 28 42
Newest
 Nice Answer
Impact
~1.0m people reached

Mar
3
comment How to total additional results from alias column results in Oracle SQL?
I'm not sure that I understand exactly what you're trying to accomplish. Some sample data and expected results might be quite illustrative. Are you trying to determine the number of invoices where the difference between the two dates is 1, the number of invoices where the difference between the two dates is 2, etc.? In which case you just need a GROUP BY and potentially a trunc. Or are you asking for the number of rows between an invoice_due_date of x and an invoice_date of y (in which case we'd need to understand what you mean by "between").
Mar
3
comment Inheritance between table and type in Oracle
What are you trying to accomplish? You can create a subtype of a type. You can create a table of a type. You can't do both in a single operation (and I'm not even sure what end result you'd hope to get).
Feb
28
answered ON clause of JOIN specific syntax?
Feb
23
answered Executing generated script from output
Feb
21
comment Using exception with raise_application_error
@DavidZurborg - I'm not sure I understand the question. You can use raise_application_error but not with a local exception type. You can use raise with a local exception type but not with a custom error message. If you are asking why raise_application_error doesn't use local exception types, I'm not sure what you are looking for other than "because that's the way Oracle is designed".
Feb
21
answered Using exception with raise_application_error
Feb
19
comment how to convert this varchar to datetime format?
Why would a string representing a datetime in 2015 be converted to a datetime in 2013? And are you trying to end up with a datetime (which does not have a format)? Or are you trying to end up with a varchar that is just in a different format?
Feb
19
comment Strategy To Remove Old Records
It could be 8 queries, it could be 1 depending on things like whether the foreign key constraints are set to cascade deletes. You'd need to ensure that things like your undo tablespace were sized to handle this one very large delete. You may want to shrink the tables as well (possibly in a later maintenance window if you don't have time in this one). Ideally, you would have partitioned the tables and then dropping old partitions would be nearly instantaneous.
Feb
19
comment Strategy To Remove Old Records
If you can delete the data using a single query, that should be the most efficient approach. I'm not sure why the fact that the query runs for a few minutes is a problem for you. Unless you are concerned that some other process needs to update the data that you are in the process of deleting, which seems like a poor requirement, I'm not sure that there is a problem to solve.
Feb
19
comment Not so Simple SQL question
livesql.oracle.com is another option. So is apex.oracle.com. Or you could download the database software from Oracle and install it on your local machine. Or download a pre-built VM from Oracle.
Feb
19
comment Not so Simple SQL question
OK. I assume this is a homework question. What have you tried so far? What problem(s) did you encounter?
Feb
19
comment PL/SQL: Temporary Table insertion in User Defined Functions
Are you testing fuction_two with all values from pt where the condition is met? Or with all values from pt? It is perfectly valid for Oracle to call function_two with values that are later excluded by the where clause predicates. What is the actual Oracle error message that you get and is it coming from function_one or function_two?
Feb
16
comment How to display the number of days elapsed between two dates denoted in columns-specifically those exceeding 30 days?
@alexanderjsingleton - updated.
Feb
16
revised How to display the number of days elapsed between two dates denoted in columns-specifically those exceeding 30 days?
added 126 characters in body
Feb
16
comment How to display the number of days elapsed between two dates denoted in columns-specifically those exceeding 30 days?
@alexanderjsingleton - It depends. For simple calculations like this, I find repeating them in the where clause easiest. But as you get more and more complicated calculations, it makes more sense to use a nesting approach.
Feb
16
answered How to display the number of days elapsed between two dates denoted in columns-specifically those exceeding 30 days?
Feb
16
comment Is there a way to automatically set triggers to update a field that is computed as an aggregation of other fields?
This is likely going to be database-dependent. In Oracle, for example, you may be able to define a materialized view that refreshes on commit to calculate the computed column. In lots of systems, you'd have a job that computed the value periodically and accept that the current value might be x minutes stale. Or you could end up with potentially numerous triggers.
Feb
11
comment Oracle Error: the sga requires more space than was allocated for it
@miracle173 - Ideally, yes. I don't know off the top of my head whether you can convert a spfile to a pfile if you can't start the database.
Feb
8
comment create table in a different schema
You could, I suppose, create a procedure in APPLICATION that accepts a DDL statement and executes it and then grant your user EXECUTE privilege on that stored procedure. But that generally doesn't make sense. The whole point of having a schema that owns the application tables and separate accounts for users is that it means that your application can be locked (possibly except during upgrades) and user accounts can't mess with the schema model. The idea that users will be creating tables at runtime seems exceptionally problematic.
Jan
29
answered difference between assingment and :