I have a column COL1 in table TAB1 which is varchar2. I want select only rows which has number and not alphanumeric value? I don't want to use regexp for this since
FOOTBALL PLAYERS < ID, NAME, ATTRIBUTE> 100-JIM-TALL 101-BOB-STRONG 102-MARK-SMART ...etc
I want to form a query that regardless of the total returned records, I will be able to specify how many of each "kind" of players I want returned. There are several good reasons that it has to be one query and not many.
I am facing a problem in bulk insert using SELECT statement.My sql statement is like below.
strQuery :='INSERT INTO TAB3 (SELECT t1.c1,t2.c2 FROM TAB1 t1, TAB2 t2 WHERE t1.c1 = t2.c1 AND t1.c3 between 10 and 15 AND)' ....... some other conditions.
EXECUTE IMMEDIATE strQuery...These SQL statements are inside a procedure. And this procedure is called from C#.The number of rows returned by the "SELECT" query is 70.
On the very first time call of this procedure, the number rows inserted using strQuery is 70. But in the next time call (in the same transaction) of the procedure, the number rows inserted is only 50.And further if we are repeating calling this procedure, it will insert sometimes 70 or 50 etc. It is showing some inconsistency.On my initial analysis it is found that, the default optimizer is "ALL_ROWS". When i changed the optimizer mode to "rule", this issue is not coming.I am using Oracle 10g R2 version.
I am importing some data from Oracle into another database on a regular basis. It works fine for most of the queries but couple of queries don't work sometimes (random). I don't get any errors or any data.
We switched on the Oracle auditing to find out the queries being sent to oracle db. We can see all the queries in the Audit log. Is it possible to configure Auditing to get the "Number of Rows" returned by Select statements so that we can be sure that some data was returned.
I am working on a script in which I want to retrieve multiple rows but I get error ORA-1422.I tried solving it using the following script , but it still gives error.
CREATE OR REPLACE PROCEDURE proc_query DECLARE TYPE all_dest IS TABLE OF NUMBER; destIds all_dest; BEGIN SELECT dest_id from sb_packet WHERE src_id = 32; RETURNING dest_id bulk collect into destIds; END;
I understand what the message "subquery returning multiple rows" means but I have a case where I'm not 100% sure why it's happening to my update query (which in turn probably means I don't fully understand what's going on behind the scenes)
Here is my query:
Update A set (A.id, A.alt_name, A.min_rank)= (SELECT B.id, B.fullname, MIN(B.nm_rankval) FROM B, A WHERE A.id = B.id AND A.name <> B.fullname AND B.nametyp = 'ON' GROUP BY B.id, B.fullname) ;
The subquery returns 6 rows but they are all unique in terms of the id, name, rankval, etc...I naturally thought that the update statement wouldn't have a problem with this since the subquery is returning rows that are not duplicates and match one for one between table A and B. I used the group by to ensure I return unique values from table B (which does have duplicate id values)
Each of those 6 rows from the subquery of table B can be matched 1-1 with table A...so what am I missing.
The following code is indicative of what I'd like to do (as in not correct at all ). Would there be a more immediate way to accomplish this other than executing a SELECT statement after the UPDATE?
-- Incorrect indicative example 1.
DECLARE v_cur SYS_REFCURSOR; BEGIN UPDATE table1(f1, f2) SET ('v1', 'v2') WHERE f3 = 'v3' RETURNING <updated_rows> INTO v_cur END;
-- Incorrect indicative example 2.
DECLARE v_cur SYS_REFCURSOR; BEGIN OPEN v_cur FOR UPDATE table1(f1, f2) SET ('v1', 'v2') WHERE f3 = 'v3' END;
This is a surprisingly common one I've found on the web...even on devshed forum
I am updating one table from another (Updating Table A from Table B):
Table A ID, Value -- ----- 1 A 1 A 2 B
Table B ID, Value -- ----- 1 Animal 2 Box
Table A (modified) ID, Value, Name 1 A Animal 1 A Animal 2 B Box
No I need to update a new column in Table A with the value in Table B.Value where the ID's from both tables match. Problem is: When I do this I get multiple rows and hence Oracle won't let me update this column. Now, I keep reading that for these types of updates, there has to be a one-to-one relationship...
Is this true...is there anyway of telling Oracle to update wherever it finds that ID, regardless of how many duplicate ID's there are?
This is quite a frustrating problem and most of the sites that I've looked for solutions try get the query one-to-one...problem is...with my table sets it's impossible to do that - I need to update wherever the id's match (even if it return multiple rows).
I'm trying to write a query that counts how many sessions are active during a 1 second time interval, then returns the maximum number of sessions active during any time interval, and all the time intervals that hit that max.
I am trying to run a dynamic select statement form a function and return the result into a variable, everything goes fine but the return is always null!
CREATE TABLE AFESD.MAJOR_ACCOUNT ( NUMBER0 NUMBER(2) NOT NULL, SHORT_NAME CHAR(35 BYTE) NOT NULL, FULL_NAME CHAR(50 BYTE) )
--Actually any table can do
CREATE OR REPLACE FUNCTION F_GEN_SELECT_INT (S_APP_USER IN VARCHAR2, I_MODULE_ID IN NUMBER, S_TABLE IN VARCHAR2, S_COLUMNS IN VARCHAR2) RETURN NUMBER AS I_RETURN NUMBER; S_SQL VARCHAR2(300); --S_DB_ERROR VARCHAR2(100);
[code]....
B.S. I didnt delete the commented lines to have your review comments.
I am trying to find records in table A that don't have any corresponding records in table B
select a.ID,a.templatenames from cbe a where not exists (select 1 from cbe_child b where a.ID=b.cbe_id)
Both tables have about 100 mil rows. And there is no Parent/Child relationship here, A.ID is a PK column.how can i write this select to perform better.
how can I select whole table in parts of 100 rows?
If I have primary key I can:
CODEstart=0; end=100; select * from table where ID>=start_point and ID<end; start=end; end=end+100; and repeat: CODEselect * from table where ID>=start_point and ID<end;
How can I do it without primary key? Is there another posibility to getting 100 number of rows? Maybe using rowid?
How to select last 10 days rows only (from sysdate)
Error
1) SQL> select * from Test where to_date(start_ts, 'DD-MON-YYYY HH24:MI:SS') > to_date(sysdate, 'DD-MON-YYYY HH24:MI:SS')-10 ; (or)
2) SQL> select * from Test where to_date(start_ts, 'MM/DD/YYYY HH24:MI:SS') > to_date(sysdate, 'MM/DD/YYYY HH24:MI:SS')-10 ; ORA-01858: a non-numeric character was found where a numeric was expected
I need to load 2 trillion data from an external table to Oracle Heap table. I am using Direct Path insert for that. how to commit after inserting n number of rows.
I have a table ABC with two columns NAME and SEQUENCE. The valid values of sequence are from 1..50. The table may not have all the Sequence number. For example following is the data in my table..
Basically I want to have rows for the sequence numbers which are not present i my table. I thought of using the following query to generate a sequence and then join, but does not work.
select rownum from dual connect by rownum <=50
I tried something like
with temp as (select name, sequence from abc where supp_ref_order is not null order by sequence ) select rownum as num, temp.name from dual, temp connect by rownum <=50 where temp.sequence =num
I've a table where I've to select the greatest value from each row.
create table find_max ( colA number, colB number, colC number ); insert all into find_max values (8,12,13) into find_max values (-1,-22,3) into find_max values (1,null,13) into find_max values (-1,-2,-3) into find_max values (-1,0,-10) select * from dual; the desired format of output is: three fields for colA, colB, colC and max_value max_value for 1st row is 13, 2nd is 3, 3rd is 13, 4th is -1,5th is 0.
how can I find the max_value without using any built in functions??
I would like to know how to increase the result set of a 'Select' statement? I did a 'Select' that should have returned 36,000 rows and got only 5000 rows. What access level do I need to change this and what do I need to change? I am trying to do a migration of data from a delimited file to a table in Oracle. Yet, the data has to be filtered out prior to loading to the table?
SQLSELECT MIN(major_zipcode) FROM TEMP WHERE MAJOR_CITIES IN (select distinct major_cities from temp);
My table has the follwoing 3 columns (in addition to others)
Col Name = active ; type=number ; values=1 (true) or 0 (false) col name start_date ; type=date; format=dd-mmm-yy col name end_date ; type=date; format=dd-mmm-yy
I need to select all rows where all active=1, start_date=<today and end_date=>today
my sql is: SELECT id, start_date, end_date FROM offers WHERE (active='1' AND start_date<='14-SEP-09' AND end_date>='14-SEP-09');
However the results are not right. Example, the first row returned is: Offer5000312 01-JAN-09 11-DEC-08
This is not correct. Due to the end_date this row should not be part of the results.