I have some tables with the same structure and I want to make a select in a group of them. Rather than just making a loop to all of those tables, I would like to put a subquery after the FROM of the main query. Is it possible or it will fail? Thanks! (Using Oracle) Additional info: I don't have the name of the table right away! Th...
I am making a function (PHP). In this function it basically uses the dbms specified and connects, submits the appropriate query, <insert my problem here>, and disconnects. DBMSs include MySQL, MSSQL, Postgres, and Oracle. I am assuming that outside of this function, the user would not have access to specified DMBS, so whatever results I...
I asked this question before but from comments I realized that the question was not enough information in the question to get help so here I am providing the whole query + explain plan: Query: WITH gtt_1 AS (SELECT r.user_id, r.role_id, r.participant_code, MAX(status_id) FROM user_role r, cmp_role c WHERE r...
Hi I would like to backup an Oracle 10G as simple as possible. It is in NOARCHIVELOG mode and I can shut down for backup (it is only a development server). After reading tons of documentation abour rman I tried this way in rman: shutdown immediate; startup mount backup database; sql 'alter database open'; As I see it works fine, lis...
Back in 1989, when I used to program with Oracle 5.2.3 on UNIX and VAX/VMS platforms, I considered SQL*PLUS as having the richest super-set of built-in functions. ORACLE*FORMS also had the ability to embed SQL statements within triggers. That was then, 21 years ago. At present, which other RDBMS' have come close, have the same, or more f...
I have a GLOBAL TEMPORARY table in Oracle. It uses ON COMMIT DELETE ROWS. One of the columns in the table is an XMLType column. I have used GLOBAL TEMP tables quite a bit...with success. However, after introducing the XMLType columne and running a function against the TEMP table I get this error message: ORA-14453: attempt to use a ...
I have a select query on a relational table in a plsql procedure. I want to convert the results of this query into a user defined type object to return via odp.net. How would I go about doing this? ...
INFORMIX-SQL 7.32 (SE) Linux Pawnshop App. I have some users who own several pawnshops within a 100-mile radius. Each pawnshop app runs with SE. The only functionality these owners need are: ability to remotely login to any store in order to view transactions, running totals and consolidate daily totals at end of business day. This can ...
Hi, Is there a way to make selecting random rows faster in oracle with a table that has million of rows. I tried to use sample(x) and dbms_random.value and its taking a long time to run. Thanks! ...
I have a hierarchical structure stored in a table. Each element has a pointer to its previous, next and a parent create table CATALOGUE ( NAME VARCHAR2(300) not null, NEXT_ID NUMBER(38), PARENT_ID NUMBER(38), PREVIOUS_ID NUMBER(38), XID NUMBER(38) ); I have a java application, which uses O/R mapping to access and modify this...
I need to consume a file that is being generated on an Oracle server and I have been told to get it using the UTL_FILE package. I intend on consuming the file in a groovy script and have a connection to the database. It is a simple text file and I'd like to pull it down to the server and consume it as I would any other text file but I ...
My first experience developing DBMS-based apps was with IMS, using COBOL calls to DLI. Later on, I worked with several other products, on a smaller scale, such as: DBASE, INFORMIX 3.3/SQL and ORACLE on DOS, UNIX-XENIX and VAX/VMS platforms. When I was a product planner for AT&T-IS Labs, I had the opportunity to eval several RDBMS-based a...
I have this table: CREATE TABLE perarea ( id_area INT primary key, nombre VARCHAR2(200), id_areapadre INT references perarea(id_area) ); And this was added to access the data: My intention is to create a heirarchy showing areas and their subordinates in the treeview. Then when a user clicks a node, have...
Here's what I have: I have a table called Area that is self referential. If the column ParentAreaID is null it means it's a parent area. If it has a value, it means that it has a parents. How can I show a table with ID, Name and NAME (of the parents using the self referential ID)? ...
For example, I have this table: CREATE TABLE perarea ( id_area INT primary key, nombre VARCHAR2(200), id_areapadre INT references perarea(id_area) ); Instead of showing: 1 IT null 2 Recursos Humanos null 3 Contabilidad 2 4 Legal 2 I want: 1 IT 2 Recursos Humanos 3 Contabilidad Recursos Humanos 4 Leg...
All, I have the following Package Description: CREATE OR REPLACE PACKAGE ashish.PKG_Customer AUTHID CURRENT_USER AS TYPE cursorType IS REF CURSOR; PROCEDURE CreateCustomerTable; PROCEDURE SelectCustomers(o_ResultSet OUT cursorType); END PKG_Customer; and here is the package body: CREATE OR REPLACE PACKAGE BODY ashish.PKG_...
I have firstName, lastName and fullName columns in oracle. I want to be able to create a trigger that updates the fullName column with the firstName and lastName values when they are either updated or inserted. I would think this would be possible to do in oracle. Does anyone have any ideas. Thanks very much. ...
I have four fields: Start_Date = 16-Jun-10 Start_Time = 14:37 End_Date = 16-Jun-10 End_Time = 14:38 I need to calculate the difference from start to end. I tried using the to_date(Start_date || Start_Time) but keep getting this error message: ORA-01830: date format picture ends before converting entire input string 01830.00000 ...
After retrieving a result set from an Oracle 11g database, it takes roughly 75 seconds to convert the XMLType (this is a structured XML Storage, registered with an xsd) into either a java String or Document. I'm using Java 1.6, have the xdb.jar and xmlparserv2.jar This xsd is <100 lines and the xml document is also <100 lines. Sample c...
Hello I am trying to run this oracle query in toad and I am getting the above mentioned error. How can I fix it? INSERT INTO IMAGE ( IMAGE_SEQ_NO,BLDG_ID,BU_ID,DT_TAKEN, NEGATIVE_NO,FILENAME,FILE_TYPE,DESCRIPTION, STORAGE_SRCE,DOC_ID,PICT_SRCE_TYPE,TAKE_BY, INFO_SRCE,DOC_REF_NO,DOC_TYPE,SUB_DOC_TYPE, DDW_IMAGE_DOC_...