So i need to update some dates on an Oracle Database, the field is a datetime, but i only want the date updated and leave the time as it is... There query goes like this: update table SET field = to_date('07312010','MMDDYY'); But it's overriding the hours, minutes and seconds from the field, i want to update the date but i want th...
I'm working on a vb.net application that executes an Oracle stored procedure. The stored procedure displays a value using dbms_output.put_line. How do I retrieve that value from the oracle database into my vb.net code? ...
I need to query the orders table to get a count of all orders for yesterdays transactions, grouped by the ship date. Then I need to have an additional column to give the total orders for the ship date for all transactions. When I added this second column, time of processing grew exponentially (which was expected) to 109s. Is there any...
Can you suggest me some free and good PL/SQL Editor? I find PL/SQL developer quite good but its not free. ...
Here is the script I am running DROP SEQUENCE S_JobStatus; CREATE SEQUENCE S_JobStatus INCREMENT BY 1 START WITH 1 NOMAXVALUE NOMINVALUE ; -- -- TABLE: JobStatus -- DROP TABLE JobStatus; CREATE TABLE JobStatus( Id NUMBER(10, 0) NOT NULL, ShortName NUMBER(10, 0) NOT NULL, Descripti...
Hi, I have a table, with abt 22 columns and 6-7 thousand rows in the following format Seq_num unique_id name ... ------------------------------------ 1 1 abc 1 1 cde 2 1 lmn 2 1 opq 3 1 pqr ...
I have been trying to figure this out for a little while now and I think it is time to ask for help.. I am building a schema provisioning script and I want to add some script output and error handling. The idea is that the script output window would only show me key messages without all the noise. Create Temporary Error Table Begin Tr...
Is there any easy way to save BLOB as a binary file into client-side file system with using of only standard Oracle utilities (such as sqlplus or sqlldr for example)? I've already looked onto UTL_FILE package, but actually I have two problems with it: I have doubts that it can work with client-side file system. I have no privilege to...
I'm new in oracle and i don't know what is wrong with this trigger: CREATE OR REPLACE TRIGGER "propuesta_casas" BEFORE INSERT ON "PROPUESTA_TIENDA_BARRIO" FOR EACH ROW WHEN (new."CASASCAL" IS NULL) BEGIN SELECT PROPUESTA.CASAS INTO :new."CASASCAL" FROM PROPUESTA WHERE PROPUESTA.IDPROPUESTA=new.IDPROPUESTA ; END; / Er...
Please let me know why this query is not working together. They are working separately: ( (select item_name, sum(p_qty) from stock where p_date between '08/06/2010' and '08/07/2010' group by item_name) - ( select item_name, sum(p_r_qty) from stock where p_r_date between '08/06/2010' an...
person(id, dogs) how would I find the person with the most dogs select * from person p1 where p1.id in ( select p2.id from person p2 where p1.id = p2.id having count(dogs > ( select p3.id ...etc am I on the right track or will this not work? thanks for taking a look ...
A) select decode(count(*), 0, 'N', 'Y') rec_exists from (select 'X' from dual where exists (select 'X' from sales where sales_type = 'Accessories')); B) select decode(count(*), 0, 'N', 'Y') rec_exists from (select 'X' from sales where sales_type = 'Accessories'); C) ...
CREATE PROCEDURE A(tab IN <table - what should I write here?>) AS BEGIN INSERT INTO tab VALUES(123); END A; How can I specify that the parameter tab is a table name? ...
Here is the code that I use to create a table, a sequence and a trigger DROP TABLE CDR.ExtDL_JobStatus; -- -- TABLE: CDR.ExtDL_JobStatus -- CREATE TABLE CDR.ExtDL_JobStatus( Id NUMBER(38, 0) NOT NULL, ShortName NUMBER(38, 0) NOT NULL, Description NUMBER(38, 0) NOT NULL, CONSTRAINT PK_ExtD...
Building on Tony's answer on this question: If I want to do something like this, CREATE PROCEDURE A(tab IN VARCHAR2) IS tab.col_name <column> --static declaration (column name always remains the same) BEGIN EXECUTE IMMEDIATE 'INSERT INTO ' || tab(col_name) || 'VALUES(123)'; END A; How can I use Dynamic SQL in the above case? ...
I am writing a query in Oracle. I want to get a string from the right hand side but the string length is dynamic. Ex: 299123456789 I want to get 123456789 substr(PHONE_NUMBERS,-X,Y) X is different for each record. I tried this: substr(PHONE_NUMBERS,-length(PHONE_NUMBERS),Y) and it didn't work.. How can I write this query? Th...
I have a script to create table and related structures DROP TABLE CDR.ExtDL_JobStatus; -- -- TABLE: CDR.ExtDL_JobStatus -- CREATE TABLE CDR.ExtDL_JobStatus( Id NUMBER(38, 0) NOT NULL, ShortName NUMBER(38, 0) NOT NULL, Description NUMBER(38, 0) NOT NULL, CONSTRAINT PK_ExtDL_JobStatus PRIMA...
I am looking to replace values in a particular column. For example the following column values column name ---------- Test1 Test2 Test3 Test12 Test14 should be (replacing est1 with rest1) column name ---------- Trest1 Test2 Test3 Trest12 Trest14 I know this should be simple but not able to get the exact function. Any help is apprec...
Hi all, is there any way compiling plsql from command prompt not by opening sqlplus and writing the command or @filename? We want to import the output to a file and parse it for a code review tool we are working on Thanks... ...
When you execute the following block in Oracle SQL Developer set serveroutput on format wraped; begin DBMS_OUTPUT.put_line('the quick brown fox jumps over the lazy dog'); end; You get the following response anonymous block completed the quick brown fox jumps over the lazy dog I am basically trying to use PRINT so I can track prog...