I'm SELECTing some aggregate data and grouping on the date and a particular field. I want to display all values in that field and a count for those values even if there was no data matching that field on that day. E.g. Date MyField Count 2009-09-25 A 2 2009-09-25 B 0 2009-09-24 A 1 2009-09-24 B ...
We are calling a .NET web service from our oracle database using the sys.utl_http package. We have also tested with the sys.utl_dbws package. This works fine when there is no security on the .NET web service. However, we would like to use sys.utl_http or sys.utl_dbws to call a .NET web service with Kerberos or NTLM authentication. We'r...
I want to find the largest sale for each of my employees (and display the name of the employee). In MySQL, it's pretty straightforward: select * from employee, sale where employee.id = sale.employee_id group by employee_id order by sale.total desc This does pretty much what one would expect, it would return a list of emplo...
I've got an sql query that selects data from several tables, but I only want to match a single(randomly selected) row from another table. Easier to show some code, I guess ;) Table K is (k_id, selected) Table C is (c_id, image) Table S is (c_id, date) Table M is (c_id, k_id, score) All ID-columns are primary keys, with appropriate FK ...
Not sure if that makes sense, but say I have this code... $updateSql = oci_parse($conn, 'update "table" SET "column"=:column where "Unique_Record_Id" = :Unique_Record_Id'); OCIBindByName($updateSql, ":Unique_Record_Id", $absenceData['Unique_Record_Id']); OCIBindByName($updateSql, ":column", $column); if(oci_execute($updateSql)){ // np...
I was trying to compile a PL/SQL package and I got the following error: ORA-04043: object SYS_PLSQL_77721_489_1 does not exist After this, I can no longer recompile or drop the package. Do you have any suggestions? ...
I'm running queries that look something like this: INSERT INTO foo (...) VALUES (...) RETURNING ROWID INTO :bind_var SELECT ... FROM foo WHERE ROWID = :bind_var Essentially, I'm inserting a row and getting its ROWID, then doing a select against that ROWID to get data back from that record. Very occasionally though, the ROWID won't b...
In the world of oracle, I have the impression that views based on other views are considered to be bad practice. I myself have complained about this when the trying to solve performance issues and the nesting seemed excessive and hid away unneeded complexity in the underlying views. Now I find myself in the situation of thinking that it ...
I work on two projects with a lot of PL/SQL code since few months. However, I didn't find any really interesting tools to develop on this langage. For the moment, my configuration includes the following tools: Eclipse (the rest of the application is developed in Java), with PL/SQL Editor plugin As I really don't like and trust the PL...
Is there pl/sql package or engine avalable which would enable the creation of PDF reports from stored procedures? ...
I am attempting to use a LIKE clause in a SQL statement to match a certain pattern within Oracle. I would like to do something as such: LIKE '[A-Z][A-Z][1-4]%' ..but I can't use a regex because this is on Oracle9i (regex support came in 10g). I am attempting to match something that has two characters before it, then a number between...
we are migrating over to oracle from sql server side. on sqlserver we used to have a view like the following create view blah AS Select column1, column2 FROM blah; but doing this on oracle produces circular view error. is this not allowed on oracle side? ...
Scenario: I need to list all the columns in a table1 and all stored procedures that depends on those columns of this table1. I need to populate column name and stored procedures to a new table. I created new_table(col1, sProc) and tried to populate the column name and respective stored procedure on this new_table. Code I wrote is given...
EDIT: Far simpler example. (Formerly titled: Oracle Column Injection) GOAL: Complete the query below generate the following results? PURPOSE: Create a column dependent on an existing column in a table without putting the table in a subquery. Rules: Restructuring the query to put tbl in a subquery is not an option. The query must use...
How would i write an expression that gives me the difference between two dates in days, hours, minutes, seconds, etc? By default subtracting two dates in oracle returns days as a decmial. ...
I have a customer who has legacy data stored in Oracle database. Such data contains mixed DATE values in one column in database (that field is VARCHAR(32)). For example they store '30-Sep-2009' and sometime '1254431689' (that is a timestamp in epoch time). I have no option to split the data into multiple columns (so I have to deal with...
I have a parent-child relationship in an Oracle 9i database-table like: parent | child 1 | 2 2 | 3 2 | 4 null | 1 1 | 8 I have an absolute parent (e.g. child 1) and i need a csv list or resultset of all childs of this parent. ...
I have many stored procedures and functions in a folder. I have a batch file that loops over the .sql files in the folder and writes the file names to a single SQL file for execution. After this, the batch file executes the single file in SQLPlus. The problem that I am running into is the ordering of the creation of the stored procedures...
I am trying to return a list of dates. Each row has multiple dates concatenated as a single string. I want to order the rows by the minimum date in each row. Here is my query: SELECT br.bm_tracking_number, (SELECT TOLIST(APPT.fact_date) FROM bm_fact APPT WHERE APPT.bm_review_sk = br.bm_review_sk ...
I'm trying to export a schema in an oracle 10 database using the EXP command. Let's call the schema "myschema" and the tns name "mydb" to protect the names of the innocent. Anyway, here's the command line that I'm using exp myschema/mypassword@mydb file=myschema.dmp grants=y This works when I try to run an export on other instan...