When I try to execute this statement in Oracle SQL Developer 2.1 a dialog box "Enter Substitution Variable" pops up asking for a replacement value for TOBAGO, update t set country = 'Trinidad and Tobago' where country = 'trinidad & tobago'; How can I avoid this without resorting to chr(38) or u'trinidad \0026 tobago' which both obscur...
I have a project whereby I'm reading huge volumes of data from an Oracle database from Java. I have the feeling that the application we are writing is going to process the data far faster than it will be given to us using a single threaded SELECT query and so I've been trying to research faster ways of obtaining the data. Does anyone h...
I have a table for which i have written a trigger: CREATE OR REPLACE TRIGGER ac01_control_trigg AFTER INSERT ON ac1_control_test FOR EACH ROW DECLARE BEGIN IF :NEW.cur_pgm_name = 'LSN' AND :NEW.nxt_pgm_name ='MD' AND :NEW.file_status='RD' THEN INSERT INTO ac1_control_test (FILE_NAME, FILE_PATH,FILE_STATUS,CUR_P...
I've been somewhat haphazardly grouping subsections of code in BEGIN...END blocks when it seems right. Mostly when I'm working on a longer stored procedure and there's a need for a temporary variable in one spot I'll declare it just for that portion of the code. I also do this when I want to identify and handle exceptions thrown for a ...
What are the consequences, (if any) not calling the conn.logoff() method after the following script when connecting to an Oracle database using the Ruby OCI8 library. conn = OCI8.new('scott', 'tiger') num_rows = conn.exec('SELECT * FROM emp') do |r| puts r.join(',') end puts num_rows.to_s + ' rows were processed.' The reason I'm...
I have a table that has a one to many relationship with itself. Each record can have n number of children from that same table. For example create table folder ID: Number 20 PK PARENT_ID: Number 20 FK references folder.ID SIZE: NUMBER 20 ... Given an ID, I want to select the SUM(SIZE) of all folder records recursively. The target d...
I have the relation instructor(ID, name, dept name, salary). The question in our assignment asks us to: Use the rank function in SQL to write a query to nd the id and name of those instructors in the top 10 most highly paid. I'm able to rank the instructors through using select id, name, rank() over(order by(salary) desc) as sal from i...
I'm getting the infamous invalid number Oracle error. Hibernate is issuing an INSERT with a lot of columns, I want to know just the name of the column giving the problem. Is it possible? I hate Oracle messages, in 15 years they haven't improved a bit (the reason why is beyond my imagination). FYI the insert is this: insert into GEM_INV...
A homework assignment I have asks the following question: Show how to express \group by cube (a, b, c, d)" using rollup rather than cube. I really don't have the faintest clue how to do this. Where would I start or where could I look for some help? ...
I have this simple example I can't seems to get working : MERGE INTO mytable mt USING dual ON (mt.id = 'AAA' ) WHEN MATCHED THEN UPDATE SET mt.name = 'updated' WHEN NOT MATCHED THEN INSERT (mt.id , mt.name ) VALUES ('AAA', 'Gooood' ); If a 'AAA' record exists in the table, it is updated successfully. But if does no...
I know it seems like a basic thing, but I've never done this before. I'd like to return a single record from an existing table as the result of an Oracle PL/SQL function. I've found a few different ways of doing this already, but I'm interested in the best way to do it (read: I'm not all that happy with what I've found). The jist of w...
I am getting ORA-30926: unable to get a stable set of rows in the source tables in the following query MERGE INTO table_1 a USING (SELECT a.ROWID row_id, 'Y' FROM table_1 a ,table_2 b ,table_3 c WHERE a.mbr = c.mbr AND b.head = c.head AND b.type_of_action <> '6')...
I have an AFTER INSERT OR UPDATE OR DELETE trigger that I'm writing to store every record revision that occurs in a certain table, by copying the INSERT and UPDATE :NEW values into a mirror table, and for DELETE the :OLD values. I could un-clutter my code considerably by conditionally passing either the :NEW or :OLD record into a proced...
Hello I want to test my application (especially SQL statements) against different databases. Actually I'm using Cruise Control and Oracle. I want expand the tests with other databases. Any suggestions? ...
I have been working on porting some Oracle DB code to Postgres, and I am noticing some nomenclature differences (Schemas vs Databases) etc. What are some good resources for dealing with these gotchas? Any tips? What needs to be kept in mind? ...
I have been trying to find out what the difference is between the IS and AS keywords in PL/SQL when creating an Oracle function or procedure. I have searched and have been unable to find any information on this. Does anyone know the difference? ...
Hi, I have one flat MAIN_TABLE. I need to insert the records from this table to multiple TABLES. eg. MAIN_TABLE col1, col2, col3, col4, col5 PARENT1_TABLE PT1_ID(PK), col1,col2 PARENT2_TABLE PT2_ID(PK), col3,col4 PARENT2_CHILD_TABLE P2C_ID(PK), PT2_ID(FK), col5, col6 so on. The goal is, I have to move the record from that flat M...
I have read only access to a few tables in an Oracle database. I need to get schema information on some of the columns but i'm having trouble doing so. I'd like to use something analogous to MS SQL's sp_help. I see the table i'm interested in listed in this query. SELECT * FROM ALL_TABLES When I run this query, Oracle "tells me table ...
In Oracle, if the current user tries to revoke all his permissions, what'll happen? For example, if I'm a user(John) created with WITH GRANT OPTION - can I revoke my permissions? ...
I am new to triggers. I created a trigger below: CREATE OR REPLACE TRIGGER ac01_control_trigg AFTER INSERT ON AC01_CONTROL_TEST FOR EACH ROW DECLARE BEGIN IF :NEW.cur_pgm_name = 'LSN' AND :NEW.nxt_pgm_name ='MD' AND :NEW.file_status='RD' THEN UTL_FILE.Fcopy (:NEW.FILE_PATH,:NEW.FILE_NAME,:NEW.FILE_PATH,'CP.txt'); INSERT ...