I need the output like...
OUTPUT(MM/DD/YYYY): 2/03/2011
02/03/2011
create table test (trans_date varchar2(1000));
Insert into test values ('2/03/2011')
Insert into test values ('02/03/2011')
Insert into test values ('12/33/2011')
Insert into test values ('xxx')
Insert into test values ('33/33/2011')
Insert into test values ('03/03/11')
I have a table and no related backup available.If some block is broken, then , how can I get the data with the blocks that are not broken?
Test environment:
SQL> create tablespace test datafile '/u01/app/oracle/oradata/test/test.dbf' size 1m;
Tablespace created.
SQL> create table scott.test_bad_block (id int, name varchar2(20)) tablespace test;
Table created.
SQL> r
1 begin
2 for i in 1..1000000 loop
3 insert into scott.test_bad_block values (i, 'test' || i);
4 commit;
5 end loop;
6* end;
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.TEST_BAD_BLOCK by 8 in tablespace TEST
ORA-06512: at line 3
SQL> select count(*) from scott.test_bad_block;
COUNT(*)
----------
40984
Now I know, I have 40984 rows in the table.In order to simulating the block corrupt, I shutdown the database and I used the bvi (a binary file editor to modify a line of record) , then start the database, when I am issuing the same statement, the problem shows:
SQL> select count(*) from scott.test_bad_block;
select count(*) from scott.test_bad_block *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted[b] (file # 7, block # 72)[/b]
ORA-01110: data file 7: '/u01/app/oracle/oradata/test/test.dbf'
It says that the block is corrupted.Now, here is what I can do to get the data right now:
#get the object id
SQL> r
1* select OBJECT_NAME, DATA_OBJECT_ID from dba_objects where owner='SCOTT' and object_name='TEST_BAD_BLOCK'
OBJECT_NAME DATA_OBJECT_ID
-------------------- --------------
TEST_BAD_BLOCK 10327
[code]....
Here are my questions:
1. How can I know, that if there is valid data(the block is not corrupted) after this bad block?
2. How can I get the data after this bad block?
I have a requirement like to validate the data in PL/SQL script dynamically.
I have 4 tables
TEST_TBL: Data available in this table
TEST_VALID_TBL: Contains field names of TEST_TBL and condition
VALID: Need to insert valid records
INVALID: Need to insert invalid records
I have to insert data into valid table when validation are full filled otherwise it should be insert invalid table .
Validation are based on TEST_VALID_TBL
While checking the data validations FIELD_NAME should be passed dynamically,because i have four columns in TEST_TBL but at present I am validating only 3 columns in feature it may be add more columns to validate.
I have to Insert data from one table to another table. First table contains some clob datatype columns and second table have carchar2 datatype.
Below are the structures of both tables;
SQL> DESC TBL_MAINSQL> DESC TBL_MAIN2
Name Type Name Type
----------------------------- ----------------------------------------- --------------
TXT_FEIN_NUMBER VARCHAR2(9) TXT_FEIN_NUMBER VARCHAR2(9)
TXT_QUOTE_NUMBER VARCHAR2(12)TXT_QUOTE_NUMBER VARCHAR2(12)
TXT_POLICY_NUMBER VARCHAR2(12)TXT_POLICY_NUMBER VARCHAR2(12)
TXT_AGENT_CODE VARCHAR2(10)TXT_AGENT_CODE VARCHAR2(10)
[code]....
The Table contains 5000 records. how to convert CLOB to VARCHAR2.
I used DBMS_LOB.SUBSTR but I received BUFFER TO SMALL ERROR.
CREATE TABLE CHECK(
ADM_DATE VARCHAR2(10)
)
INSERT ALL
INTO CHECK VALUES ('122012')
INTO CHECK VALUES ('112012')
INTO CHECK VALUES ('102012')
INTO CHECK VALUES ('092012')
INTO CHECK VALUES ('082012')
SELECT * FROM DUAL;
ADM_DATE has the data as in format 'MMYYYY' but I've to make it as 'YYYYMM' while the datatype of ADM_DATE is VARCHAR2.
How can I do it?
I have problem to convert simply varchar to date data type.
For example: 2012-05-28 22:36:08 and I would like to get format 28.5.2012 22:36:08
However I try to do it I get always some errors.
select '2012-05-28 22:36:08',
to_date( '2012-05-28 22:36:08', 'dd.mm.yyyy HH24:MI:SS') ,
to_char('2012-05-28 22:36:08', 'dd.mm.yyyy HH24:MI:SS')
from dual
I have an index on column of table which of data type varchar2. While selecting data from that table I am using following scenarios in where on the indexed column
like '%abc%'
like 'abc%'
like '&abc'
Will be the corresponding index will be for those cases?
bookshelf_test table structure
title varchar2(100) y
publisher varchar2(20) y
categoryname varchar2(20) y
rating varchar2(2) y
my query is,
select ROWNUM AS "Rank",title,publisher from (select rating,title,publisher from bookshelf_test order by rating desc ) where ROWNUM <=3
returns result ,
1 1 MY LEDGER KOCH PRESS
2 2 TO KILL A MOCKINGBIRD HARPERCOLLINS
3 3 THE MISMEASURE OF MAN W.W. NORTON
But inner query (select rating,title,publisher from bookshelf_test order by rating desc ) returns,
1 5 WONDERFUL LIFE W.W.NORTON
2 5 THE MISMEASURE OF MAN W.W. NORTON
3 5 TO KILL A MOCKINGBIRD HARPERCOLLINS
4 5 MY LEDGER KOCH PRESS
5 4 TRUMAN SIMON SCHUSTER
6 4 GOSPEL PICADOR
7 4 HARRY POTTER AND THE GOBLET OF FIRE SCHOLASTIC
8 4 INNUMERACY VINTAGE BOOKS
9 4 JOHN ADAMS SIMON SCHUSTER
10 4 JOURNALS OF LEWIS AND CLARK MARINER
11 4 LETTERS AND PAPERS FROM PRISON SCRIBNER
12 4 PREACHING TO HEAD AND HEART ABINGDON PRESS
13 4 THE SHIPPING NEWS SIMON SCHUSTER
14 4 THE GOOD BOOK BARD
15 4 THE DISCOVERERS RANDOM HOUSE
16 3 THE COST OF DISCIPLESHIP TOUCHSTONE
17 3 SHOELESS JOE MARINER
18 3 KIERKEGAARD ANTHOLOGY PRINCETON UNIV PR
19 3 EMMA WHO SAVED MY LIFE ST MARTIN'S PRESS
20 3 EITHER/OR PENGUIN
21 3 CHARLOTTE'S WEB HARPERTROPHY
22 3 BOX SOCIALS MARINER
23 3 ANNE OF GREEN GABLES GRAMMERCY
24 3 WEST WITH THE NIGHT NORTH POINT PRESS
25 3 UNDER THE EYE OF THE CLOCK ARCADE PUB
26 3 TRUMPET OF THE SWAN HARPERCOLLINS
27 2 COMPLETE POEMS OF JOHN KEATS VIKING
28 1 POLAR EXPRESS HOUGHTON MIFFLIN
29 1 GOOD DOG, CARL LITTLE SIMON
30 1 MIDNIGHT MAGIC SCHOLASTIC
31 1 RUNAWAY BUNNY HARPERFESTIVAL
why final queries top 3 rows r different than inner query ?
Is there a seeded function by which I can check all the rows which stored dates in varchar column.
I have a table say test (test_data varchar2(100));
Now I will insert all types of records into the table varchar,number dates and then i will write q query to etch all those records only which has dates only
INSERT INTO test(1);
INSERT INTO test('ABC');
INSERT INTO test(SYSDATE);
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY'));
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
INSERT INTO test('15/01/2012');
...
commit;
Is there some functions to convert the long type field data to varchar2 type?
View 2 Replies View RelatedIn SQLDeveloper (I use oracle 11g), I can see a table with its all data. However, in sqldevelopr, I cannot select some data of it.
If I use sqlplus, it works fine.
Why I cannot select some data of that table in SQLDeveloper?
I am wondering how can I select data between two dates (ie from 08/12/2004 to today?)
Name DateS
Bob 08/11/2004
Tom 08/12/2004
Bill 09/21/2004 <-- today
return
Tom 08/12/2004
Bill 09/21/2004
Is there any way i can select * from all the tables owned by particular schema at once.
View 22 Replies View RelatedI have 3 users in schema.
User09
User10
User11
Table Name PURCHASE exists in all above three schema with different data(according to date)
Like
User09 ----> purchase ---- data date Jan09 to Dec09
User10 ----> purchase ---- data date Jan10 to Dec10
User11 ----> purchase ---- data date Jan11 to Dec11
I want to select * from purchase, from above all three user with select statement.I can select data one by one with union all
select * from user09.pruchase Union all
select * from user10.pruchase Union all
select * from user11.pruchase
after creating this query I add another User User12 and that User has same purchase table so I will have to add one another line IN Union all Query. when I see this query. It gives me the all owner name of User09,User10,User11......
SELECT * FROM ALL_ALL_TABLES
WHERE OWNER LIKE 'USER%'
and table_name ='PURCHASE'
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
USER PURCHASE USERS
USER09 PURCHASE USERS
USER10 PURCHASE USERS
3 rows selected
Can I get data from Purchase Table of all users with select statement .
I have a table with multiple column. A column named address have value of multiple lines. Now i want to select the address only of the first line. How does it possible.
Example:
address
319/a, Bashirvila
Tilpapara
Dhaka
Now i want to display only 319/1, Bashirvila.
I have an PL/SQL query which gives the data between the date interval submitted by the user.The problem is that i want all the date irrespective of it has data or not for eg: Let say date parameter is from 1-Jan-2012 to 5-Jan-2012
Now, in the database the available dates are:
1-Jan-2012
2-Jan-2012
5-Jan-2012
So as you can see here that dates 3-Jan-2012 and 4-Jan-2012 is not resulted out by the query. I want all the dates.
So the output should be like this:
1-Jan-2012
2-Jan-2012
3-Jan-2012
4-Jan-2012
5-Jan-2012
I have a script which is used to run a job based on the users choice. For example: I have two table, Files and Requests
User select the files to be executed for each request. This data will be stored in Requests table.
Table 1: Files
files
======
file-1
file-2
file-3
..
..
file-n
Table 2: Requests
request file lup_date
==================================
request-1 file1,file2,file3 04-JAN-2009
request-2 file1,file4,file5 06-JAN-2009
request-3 file6,file2 021-JAN-2009
request-4 file1,file2 04-FEB-2009
request-5 file1,file2 08-JAN-2009
request-6 file1,file2 04-MAR-2009
.......... ........... ................
request-n-1 file6,file2,file4 04-DEC-2009
request-n file6,file3,file4 04-DEC-2009
how to get the output in below format. Count how many times each file is selected in a month.
Output format should be like below..
==============================================
File_Name Jan Feb Mar Apr ---------- Dec
==============================================
file1 2 1 3 0 ---------- 2
file2 1 0 2 1 ---------- 3
file-n 8 2 3 0 ---------- 2
I have an athletics participation table that only has the relevant emplid and effective date field. There is no term field on the table. I'm trying to only select those emplid's where the max( effdt) for the emplid is between the begin and end date of the current term. I only want to select current athletes. I would much rather it be for the current academic year but it seems impossible. Why can't I use max(effdt) here?
sql Code
AND h.emplid IN(SELECT b.emplid FROM PS_ATHL_PART_STAT b
where max(b.effdt) between (SELECT term_begin_date AND term_end_date
from PS_TERM_TBL
[Code].....
The description field in the item table has the single quote used as the symbol for feet. I have the same issue pulling from a last name field in other tables. (Like O'Connor)
select descrip into v_result
from c_ship_hist
where shipment_dtl_id = :SDID;
exception when others then null;
The error I get is "Missing right quote". How do I code around this issue without having to change the data?
I have a table in which a column has some data like this:
prod¿ct_liste¿_25175892
prod¿ct_liste¿_76815456
Is there any way to select this data?
how to display REFS from within a REF. I've not used my exact code here as its quite a big file so i've made a similar scenario to get me point across. Here is the code first:
1 CREATE OR REPLACE TYPE object1 AS OBJECT (
2 object_id NUMBER(5),
3 object_name varchar2(10),
4 object_added DATE);
5 /
6 CREATE TABLE object1_tab OF object1 (object_id PRIMARY KEY);
7 /
8 INSERT INTO object1_tab
9 VALUES (1,'Daniel',sysdate);
10 /
11 show errors;
12 /
13 CREATE OR REPLACE TYPE object2 AS OBJECT (
14 object_id NUMBER(5),
15 object_job varchar2(10),
16 object1_ref REF object1 );
17 /
18 CREATE TABLE object2_tab OF object2(object_id PRIMARY KEY);
19 /
20 INSERT INTO object2_tab
21 VALUES (1,'Developer',(SELECT REF(p) FROM object1_tab P
22 WHERE VALUE(p).object_id = &object_id));
23 /
24 select DEREF(object1_ref)
25 FROM object2_tab;
26 /
27 CREATE OR REPLACE TYPE object3 AS OBJECT (
28 object_id NUMBER(5),
29 object_location VARCHAR2(20),
30 object2_ref REF object2);
31 /
32 CREATE TABLE object3_tab OF object3 (object_id PRIMARY KEY);
33 /
34 INSERT INTO object3_tab
35 VALUES (1,'New York',(SELECT REF(p) FROM object2_tab P 36 WHERE VALUE(p).object_id = &object_id));
37 /
38 show errors;
39 /
40 select object_id,object_location,DEREF(object2_ref)
41 FROM object3_tab;
42 /
As yot can see in the code each object refernces from the previous. When I view the DEREF in the third table (object3_tab) i get the following output:
OBJECT_ID OBJECT_LOCATION DEREF(OBJECT2_REF)
--------- -------------------- ----------------------------------
1 New York [SANTA.OBJECT2]
Contained within the object2 is the following:
SANTA.OBJECT2(1,'Developer','oracle.sql.REF@c4cb4aa6')
How would i view the ref for object1 within object3? I will also need to be able to verify the data in these REF in the new table they are used in.
I wanted to select data which is inserted on the same time.
Is that possible? I am trying with below query but facing isses.
SQL> SELECT a,b,c,d FROM tb;
no rows selected
SQL> SELECT a,b,c,d FROM (INSERT INTO tb VALUES(1,1,1,1));
SELECT a,b,c,d FROM (INSERT INTO tb VALUES(1,1,1,1))
*
ERROR at line 1:
ORA-00903: invalid table name
How do i insert or select date data type value in am or pm Eg 10:30:00 am or 10:35:00 pm
View 3 Replies View RelatedI am attempting to use the following select to get a specific emplid. However, the ps_names table contains some alphabetic characters. I want to only focus on the emplid's that contains numbers. Is there a way to modify the following select to do this?
bubbagumpshrimp
"ORA-01722: invalid number"
SELECT x.y
from (select PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY to_number(emplid)) over () y
from PS_NAMES
where emplid > '000000000' and emplid < '999999999') x
where rownum = 1;
I have a table that has hierarchical data within it. I need to select this data in a specific way, showing the hierarchy.
E.g. Data in table (Key is unique)
Lvl KeyParKey HasChild
1k101
1k200
1k301
1k401
2k34k10
2k22k11
2k24k10
2k13k30
2k52k30
2k35k30
2k13k30
2k11k40
3k56k220
3k109k221
3k67k220
4k61 k1090
Etc etc....
That�s generally the format the values would appear in the table if I just did a standard select. I want it displayed in a more hierarchical Parent � child way.
The format I need to get out is as follows:
Lvl KeyParKey hasCh
1k101
2k34k10
2k22k11
3k56k220
3k109k221
4k61 k1090
3k67k220
2k24k10
1k200
1k301
2k13k30
2k52k30
2k35k30
2k13k30
1k401
2k11k40
I am new to oracle. I am creating a view, where in the inner query returns below kind data.
column1 column2
-------------------
a y
a y
b y
b n
c y
d n
I want to extract all column1 values which always has "y" in column2.
In this case, output has to be
output
------
a
c
SELECT
contgrp_num,
cpgrp_desc,
strategy_id,
perftyp_cd,
stiertyp_id_calcbase,
stiertyp_id,
cpgrp_flg_aggr,
pgrptyp_cd
[Code]..
The above query is not returning any value. But, when I put this within a begin-end block, its fetching data.
why its not returning any data without the begin-end block.
The entire code has an outer begin-end block within which the cursor is also defined.
I have a complex requirement to be resolved, i have one table of quantities from where i want to show or select the data based on 2 criteria.
1) if the ps_qty is greater than 1000 then there should be two lines , like the qty should not be displayed greater than 1000, instead it should be lesser than 1000.
2) The weight should not be more than 50,000, it should be less than 50000 if its more it should be displayed as 2 lines.
The following is the test case.
CREATE TABLE OW_STAG_SHIP (PS_CODE VARCHAR2(12),PS_DESC VARCHAR2(30),PS_QTY NUMBER,PS_WT NUMBER);
INSERT INTO OW_STAG_SHIP VALUES ('A','AAA',400,30000); -- this will be displayed as its because qty and wt are ok
INSERT INTO OW_STAG_SHIP VALUES ('B','BBB',1100,4000); --Need to be displaed in two lines as qty is more than 1000
INSERT INTO OW_STAG_SHIP VALUES ('C','CCC',2500,6000); --Need to be displayed in three lines as qty is more
INSERT INTO OW_STAG_SHIP VALUES ('D','DDD',600,60000); --Need to be displaed in two lines as wt is more.
select * from ow_stag_ship
PS_CODEPS_DESCPS_QTYPS_WT
AAAA40030000
BBBB11004000
CCCC25006000
DDDD60060000
Output what i want is as below
PS_CODEPS_DESCPS_QTYPS_WT
AAAA 40030000
BBBB10002000
BBBB 1002000
CCCC10002000
CCCC10002000
CCCC 5002000
DDDD 500 50000
DDDD 100 10000
I'm trying to retrieve data with multiple select statements. The query works fine for 1 account (segment6) but fails for more than account. Below is the query.
select PERIOD_NAME "Month/Year",segment1 "Company" ,Segment6 "GL Account", currency_code "Currency",Mmt "Movement",
BEGIN_BALANCE+PERIOD_DR-PERIOD_CR "Balance At Date"
FROM
(select gb.period_name "PERIOD_NAME",decode (gcc.segment1, '20', 'Internode', '10', 'Agile') SEGMENT1,
gcc.segment6 "SEGMENT6", gb.currency_code "CURRENCY_CODE", gb.begin_balance_dr "BBDR", gb.begin_balance_cr "BBCR",
[code].....
Here is my case,
create table t (id number,row_id number primary key ,value number);ID row_id value
1 1 10
1 2 11
1 3 1
2 4 11
3 5 11
3 6 12
4 7 12
4 8 12
4 9 13
4 10 11
4 11 10Requirement is
1) To get all the ID that have the value 10
2) The row_ids for the ID I got in I
Can I do this with Hierarchical query. If not which one of below will be fast?
select * from t where id in (select id from t where value=10);
or
select T2.* from t T1, t T2 where T1.ID=t2.id and T1.VALUE=10;
I have billion of rows so I am looking for either Hierarchical query to solve it or some other way . All the three column the index.