Given the following tables, how might I build a SQL query that includes a list of all the items from the "items" table, and a column for each color from the "colors" table that, for each item listed, indicates what colors the item has a relationship with. If that is unclear at all, please let me know what additional information will hel...
Assume we have the following table: Id A B 1 10 ABC 2 10 ABC 3 10 FFF 4 20 HHH As result of a "group by A" expression I want to have the value of the B-Column that occurs most often: select A, mostoften(B) from table group by A; A mostoften(B) 10 ABC 20 HHH How do I achieve this in Oracle 10g? Rema...
How to get the dynamic select results of EXECUTE within pl/sql from Oracle sqlplus? I'm writing a simple sqlplus script to collect the sum of all NUMBER columns of a given table -- SET SERVEROUTPUT ON DECLARE CURSOR column_cur IS SELECT column_name FROM ALL_TAB_COLS WHERE owner = '&scheme_name' ...
I have a query which returns a list of objects like such: select distinct ref from references where [...] I'd like to order these by a field not on ref but on an association of ref, i.e.: select distinct ref from references where [...] order by ref.foo.name Unfortunately this doesn't work in Oracle (once the query is translated to ...
Can anybody please tell me what is the use of %NOTFOUND operator? We can use "NOT %FOUND" in cursor also. It will perform same functionality. ...
hi, we have one table as following Table name: metadata_table strucutre is as follows S.No Tablename Column 1 Fct_sales_summary wk_units 2 Fct_exp_summary mn_units wk_units as in the table Fct_sales_summary are columns and are in range wk_units1 to wk_units105 and similarly in Fct_exp_summary ...
And while we're at it, how do I switch between autocommit and non-autocommit? ...
Hi all, I am working on a database middleware application again. I want to retrieve the names of all key values of a given table. For instance: CREATE TABLE foo(A INTEGER PRIMARY KEY, B VARCHAR(255)) I want to ask Oracle for the primary keys of 'foo' and I want to get 'A'. Is this possible in Oracle at all? I tried to search their d...
please let me know the issue with following script (sql,oracle 10g) 1 DECLARE @colname AS NVARCHAR(50) 2 DECLARE @tablename AS NVARCHAR(500) 3 DEClARE @query AS NVARCHAR(500) 4 SET @colname = 'select wk_units1 from cnt_sls_dm.fct_sales_summary' 5 SET @tablename = 'SELECT tablename from dmi_user.fct_sales_meta' 6 set @q...
I have a number of scripts used to build a database. These need to be executed in a certain order. Is it possible to create a script to run other scripts in SQL Developer (1.5.3)? I know I can do this with sqlplus, but I was hoping there's a similar ability I just don't know about. ...
I have this procedure i my package: PROCEDURE pr_export_blob( p_name IN VARCHAR2, p_blob IN BLOB, p_part_size IN NUMBER); I would like for parameter p_blob to be either BLOB or CLOB. When I call this procedure with BLOB parameter, everything is fine. When I call it with CL...
Surely this should be the same as a termination of a session and cause a rollback? It seems to me to be the most un-Oracle thing possible. I was actually shocked when I found out that it did this More importantly - would anyone object if Oracle changed it to rollback on exit? ...
I need to call some PL/SQL procedures from my Java application. I can do it with JDBC. But the problem is that procedures are using the "PL/SQL Web Toolkit" and its packages (htp, owa _ util, owa _ cookie, ...). When I call them I get some exceptions as this: Exception in thread "main" java.sql.SQLException: ORA-06502: PL/SQL: numeric o...
I'm starting a new semester and for my class "relational databases" they are using Oracle. Now I'm experienced in using MySQL and was wondering what the main differences are between Oracle and MySQL. So I have a couple of questions. What are the fundamental differences in using the database? What are some of the differences in SQL syn...
I'm trying to find a good tool (open source or commercial) for doing comparisons of database instances, for example: Compare 2 database schemas; generate platform specific change script (either direction) to bring one into synch with the other Compare data (table contents), generate platform specific change script (either direction...
I'm going to analize some Excel files that the user has to upload in my webapp, I already have the stored procedures that works. But i have no idea how to save the uploaded file to my oracle directoy. I think there are two steps, first creating the file into the specified path. And filling tha file with the info. If somebody has made thi...
Does anyone have a simple example that explains why one would want to use an Oracle instead of trigger ? ...
Using Oracle, how can I find index names and creation dates from systables/information_schema? How can I reproduce, from systables/information_schema, the DDL that created the index, e.g., create index indexname on tablename(column_name [, column_name....]) [local]; ...
For example, if we have a table Books, how would we count total number of book records with hibernate? Thanks ...
Hi, I'm working on the following code below, (edited for clarity), that is giving me a few problems with open cursors in Oracle. Basically I am trying to select data from the DB and for each row returned there is 0 or more rows of sub data to be selected and appended to the record. This is currently being achieved by calling out to anot...