Unable To Extract NULLS

Dec 20, 2011

Am trying to extract data from oracle onto flatfile(.txt), am using UTL_FILE but, NULLs in oracle tables are getting converted into space and if i try loading into table it is getting loaded as space,

View 1 Replies


ADVERTISEMENT

Server Administration :: Unable To Extract Ddl Of Tablespace

Feb 24, 2012

[code]SQL> set heading off
SQL> set pagesize 0
SQL> set long 1000000
SQL> set feedback off
SQL> select
2 dbms_metadata.get_ddl('TABLESPACE','TBLS_DATA')
3 from
4 dual
5 /
ERROR:
ORA-31603: object "TBLS_DATA" of type TABLESPACE not found in schema
"JACK"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2806
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

When i try to extract ddl of tablespace from a schema JACK, i get error.This tablespace TBLS_DATA does exist in the database.

View 4 Replies View Related

Server Administration :: Oracle JDBC Classes12.jar File - Unable To Extract

Apr 21, 2010

Upgrading from 10.1.0.2 to 10.1.0.5. Enterprise Manager requires 'newest' version of Oracle JDBC drive.Downloaded what I believe to the correct file (classes12.jar). I'm unclear what to do with this, my readings have pointed me in the following direction:

1) copy to c:oracleproduct10.1.0db_1jre1.4.1in
2) extract

here is the problem...tried:

1) just clicking on it (nothing)

2) c:program filesjavajre1.6.0_03injavaw -jar classes12.jar
Error:
Failed to load Main-Class manfest atrribute from
c:oracleproduct10.1.0db_1jre1.4.1inclasses12.jar

Is my location correct, I've been hunting everywhere..making no progress.

View 4 Replies View Related

SQL & PL/SQL :: Query For Non Nulls In A Row

Mar 21, 2010

I AM HAVING A TABLE WITH BELOW STRUCTURE AND VALUES.

Table A

Col1Col2 Col3
M1D1V1
M1D2
M1D3V2
M1D4
M1D5

[Code]...

Below is the scenario provided with sample data to replicate the actual requirement.

I am trying to write a query to get below output. But I am not able to succeed.

The column having null values should have the non null value as showed in example.

OUTPUT
------

Col1Col2 Col3
M1D1V1
M1D2v1
M1D3V2
M1D4v2
M1D5v2
M1D6v2
M1D7V3

[Code]...

Below is the script for table and values

create table tmp (col1 varchar2(10),col2 varchar2(10),col3 varchar2(10));

insert into TMP (COL1, COL2, COL3)
values ('M1', 'D1', 'V1');
insert into TMP (COL1, COL2, COL3)
values ('M1', 'D2', null);

[Code]...

View 1 Replies View Related

SQL & PL/SQL :: How To Concatenate While Removing Nulls

Jan 26, 2012

This is a simple question, but I cannot seem to find a solution. Here's the basic query:

select distinct accountno, parcelno, streetno||' '|| predirection ||' '|| streetname||' '|| streettype||' '|| postdirection||' '|| unitname||', '|| propertycity
from tblacctpropertyaddress ....

What I want to do is add is this logic: If Predirection is null, then no space between streetno & streetname. Same for postdirection and unitname. (for example, if both postdirection and unitname are null, there are no spaces between streettype and the comma before propertycity)

Also, when unitname is not null, I want to add the string "Unit " prior to the returned value in unitname.

View 5 Replies View Related

PL/SQL :: Running Total For Each Day - Nulls?

Oct 2, 2013

I'm on 11.2.0.3.  I want to write a query to get calculate a running total of incidents per day - this query will be used for an APEX line chart.Sample table and data:

create table sales (
  id number primary key,
  time_of_sale date,
  item varchar2(20));
insert into sales values (1, to_date('02-JAN-2013','DD-MON-YYYY'), 'book'); ....

View 6 Replies View Related

SQL & PL/SQL :: How To Remove Nulls In Select Statement

Apr 8, 2010

I have a sql like

select TRIM(column_name)
from user_tab_columns
where table_name = 'MTL_SYSTEM_ITEMS_VL' AND COLUMN_NAME IN ('DESCRIPTION ');

[Code]....

Like this i have many rows. What clause other than trim i should apply to no cosider the spaces?

View 27 Replies View Related

SQL & PL/SQL :: Are NULLs Indexed In BITMAP Indexes

Jul 24, 2012

Are NULLs get indexed in bitmap indexes? How can I verify that.

View 3 Replies View Related

SQL & PL/SQL :: Query To Find Number Of Nulls For Each Record

Aug 18, 2013

We have data as below in the table. I need the to display the records in the order based on number of NULL values and position for each record.

provide a simple query using case in ORDER BY clause.

ID CLASS NAME DIST_ID DIST_NAME
0 NULL KIRAN 0 AP
0 C1213 NULL 0 AP
0 NULL NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
NULL NULL NULL NULL NULL
0 C123 RAJESH 0 AP
NULL C123 RAVI NULL AP

We have to give the rank based on NULL values and NULL value column position.Let us assume column positions as

1 2 3 4 5
ID CLASS NAME DIST_ID DIST_NAME

for the following three records number of NULL values are same. but positions are different.

0 NULL NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
NULL C123 RAVI NULL AP

Based on the column positions the ranks as
2*2+3*3=13
1*1+3*3=10
2*2+3*3=13
1*1+4*4=17

Which is having high rank (greatest number) that record should come last . The record which is having all values that should come first. The record which is having all NULL values should come last. The out put I want as

ID CLASS NAME DIST_ID DIST_NAME
0 C123 RAJESH 0 AP
0 NULL KIRAN 0 AP
0 C1213 NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
0 NULL NULL 0 AP
NULL C123 RAVI NULL AP
NULL NULL NULL NULL NULL

View 15 Replies View Related

SQL & PL/SQL :: Collapse All Nulls For Every Table Column In Statement?

Jul 19, 2013

How do I collapse all nulls for every table column in a SQL Statement?

View 15 Replies View Related

SQL & PL/SQL :: Default Values / Distinguishing Between Passed And Default Nulls

Nov 16, 2010

I was looking for a way to see if a default value for a procedure was passed NULL or it got NULL by default. [URL]

View 11 Replies View Related

SQL & PL/SQL :: How To Extract Data Between [ ]

Jun 8, 2012

I have a requirement where the data between [] or ][ has to be extracted from a string.

Here is my situation :

INPUT:
[abc] [def]-[ghi][jlk]

OUTPUT:
row_num field_name
1 abc
2 <blank_space>
3 def
4 -
5 ghi
6 null
7 jkl

View 5 Replies View Related

SQL & PL/SQL :: Extract One Schema's DDL

Apr 22, 2011

Is there any option available in DBMS_METADATA.GET_DDL in such a way that I can extract the script (user creation+grants)only for that particular schema?

View 5 Replies View Related

SQL & PL/SQL :: Extract DDL Of DBMS_JOB

Dec 20, 2011

How to extract DDL of DBMS_JOB in sqlplus ?

View 30 Replies View Related

SQL & PL/SQL :: How To Extract CSV File

Jun 14, 2010

I have to extract a csv file running a sql file.

SQL>@d: estEndItem_Vio_Item_Material_Violations.sql;

This works on the sql prompt. I have to do the same using schedular now for which I want to do the same embedded in a procedure.

create or replace procedure test_csv
as
begin
@d: estEndItem_Vio_Item_Material_Violations.sql;
end;

How can I run the sql file in a procedure.

View 4 Replies View Related

PL/SQL :: Extract Value From Column Name

Nov 14, 2013

I'm stuck on 1 scenario

I have the following table: 

Create tabledrop table age_rate;
CREATE TABLE age_rate(age_0_4 NUMBER(4),age_5_20 NUMBER(4),age_21_34 NUMBER(4),age_35_44 NUMBER(4));
-------------------------------Insertion 
INSERT INTO age_rateSELECT 45, 50,  60,  90
FROM dual UNION ALLSELECT 45, 50, 60,  88
FROM dual UNION ALLSELECT 40, 50,  60,  90 FROM dual UNION ALLSELECT 5,  50, 60,   88
FROM dual ;  
-------------------------------Query on table
SELECT * FROM age_rate; Query Output age_0_4           age_5_20      age_21_34      age_35_44 45                          50                     60                     9045                          50                     60                     8840                          50                     60                     905                            50                     60                     88 Required outputRate     Min_age     Max_age

----The below rate is for age band 0_445   0           445            0           440            0           45              0           4

--The below rate is for age band 5_2050            5           2050            5           2050            5           2050            5           20

--The below rate is for age band 21_3460            21          3460            21          3460            21          3460            21          34

--The below rate is for age band 35_4490            35          4488            35          4490            35          4488            35          44   

Rules--I have all data in rows so each column in row create separate rows and add 2 columns automatically Min_age and Max_age and insert value on these column on basis of column name for example  if column name like age_0_4 then put 0 in min_age and put 4 in max_age means values for Min_age and Max_age extract from the basis of column name. I don't know if it is possible or not

View 6 Replies View Related

PL/SQL :: Query To Extract Changes

May 7, 2013

I am using Oracle database version 11.2.1 and would like to extract the level change and level start date where reason_code is 'PROMO' split by ID.

The test script is below:

create table test(
id number,
start_date date,
reason_code varchar2(10),
level number
)
insert into test values(001, '01-JAN-13', 'PROMO', 2);
[code]....

The expected output would be:

Fields - ID, old_level, old_level_start_date, new_level, new_level_start date

e.g.
001, 2, '08-MAR-13' , 3, '05-MAY-13'
002, 4, '13-APR-13' 5, 02-MAY-13'

View 2 Replies View Related

PL/SQL :: Extract Numbers Only

Sep 18, 2012

Here i face probelm that he numbers must be follw by DOT "." , this is not correct if the statment only conatines numbers without DOT that not extract. As the

SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;

but mean i have to add . after numbers . as follow

SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;

but this is not right

i want to extract numbers without DOT also.

View 6 Replies View Related

Extract All Employees Who Have 2 Functions

Feb 24, 2013

I have a table:

create table employee_function
(
id_emloyee number,
id_function number
);
with clients and their functions.

I want to extract all employes who has 2 functions (ex:id_function = 1 and id_function=2)

View 3 Replies View Related

Extract Hour From A Date?

Mar 29, 2004

how to extract hour part from a date?

suppose my date is like

29-mar-2004 09:20:34

i wanna get only hour from the above date-

View 3 Replies View Related

How To Extract The Data From DBF Files

Aug 13, 2010

I'm a SAP consultant working in SQL on NT platforms. This is the first conversion from Oracle that I have done. My client has provided us with a "Cold" backup of the Oracle dbase on a HD formatted in Unix, I have the partition mounted and I'm able to view the files. I have the ORDATA folder with all the .DBF files.

Q: How do I extract the data from the .DBF files. I need to export to something workable with SQL.

Original database was on Unix, I'm operating on Windows platform.

View 4 Replies View Related

Creating Extract File From DB?

Jun 22, 2010

I have data in multiple oracle tables. I have to create a extract flat file after applying some validation and business logic on it and store it in unix server with naming convention FF_RMS_SC_<<YYYYMMDDhhmm>>.txt.This job will be scheduled to run daily to create the flat file. I guess pl/sql and unix needs to be used.

View 1 Replies View Related

Extract DDL's Without Storage Parameters?

May 11, 2005

I need to extract DDL's without storage parameters. if i use the export and import using indexfile or if i try to extract using the DBMS_METADATA.GET_DDL package , in both ways my output is with the storage parameters

eg:
CREATE TABLE "SEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),

[Code].....

View 6 Replies View Related

Extract Index DDL From Database?

Apr 27, 2011

Is there a way, i can extract index DDL from my database?

View 1 Replies View Related

SQL & PL/SQL :: How To Extract Substring From A Set Of Strings

Jan 25, 2012

Im trying to extract, *THIS IS MY STRING* from *<YUVRAJ THIS IS MY SRTING YUVRAJ>* .

In this <YUVRAJ and YUVRAJ> is constant, need to remove which is being appended at begin and end for a set of strings.

View 3 Replies View Related

SQL & PL/SQL :: How To Extract The Rights Of Users On ERP

Mar 2, 2013

I would like to extract the user rights from the ERP that I use with SQL/Oracle.

There are 3 databases. The first gives me all the users with the main key id_user. The second one gives me the actions with the main key id_action. The third one only gives me what the user can't do.

I would like to know all the actions that all users can do or not.

The result must be something like:

Action 1
User 1 Yes
User 2 No

Action 2
User 1 No
User 2 Yes
....

View 9 Replies View Related

SQL & PL/SQL :: Extract Date From String

Jan 8, 2013

I need to extract the First portion of date entered in remarks column , i have prepared a test as below.

CREATE TABLE LEAVE_DYS (LV_EMP_CODE VARCHAR2(12), LV_DESC VARCHAR2(2000));
INSERT INTO LEAVE_DYS VALUES ('R0754','LEAVE SALARY FOR APRIL 16, 2010 - APRIL 15, 2012 (33 ABSENTS)');
INSERT INTO LEAVE_DYS VALUES ('R0667','LEAVE SALARY FOR JULY 14, 2009 - JULY 13, 2011 (44 ABSENTS)');
INSERT INTO LEAVE_DYS VALUES ('R0841','LEAVE SALARY FOR MAY 29, 2010 - MAY 28, 2012 (NO ABSENT)');
INSERT INTO LEAVE_DYS VALUES ('R0854','LEAVE SALARY FOR MAY 29, 2010 - MAY- 28, 2012 (1 ABSENT)');
[code].....

--Required output is

LV_EMP_CODE LV_DESC
R0754 16/04/2012
R0667 14/07/2012
R0841 29/05/2012
R0854 29/05/2012
R0313 09/09/2012

View 15 Replies View Related

SQL & PL/SQL :: Extract String Between The Braces?

Sep 13, 2011

I have following column data

NZ(abc_mode)/ (NZ(bch_mode) + NZ(cdh_mode)

Now i need query to extract the string inside the braces NZ() with or without using regular expressions in SQL

the output should be

abc_mode
bch_mode
cdh_mode

View 5 Replies View Related

SQL & PL/SQL :: Extract Data Into Excel

Mar 21, 2012

Data that enter by user in database having " in the sentences
example:

column1
----------
"My Smurf Village

column2
--------
Farmvile

column3
--------
"Testing

My select statement is

Select column1,column2,column3 from table. Output in toad perfect but When I export into excel using pipe | as a separator, the output become

column1 in excel
----------------
My Smurf Village|Farmvile

column2 in excel
---------------
Testing

How I can code my program so that when extract to excel " is recognized as sentences. output are correct as in database?

View 5 Replies View Related

SQL & PL/SQL :: Extract Minute From Time?

Sep 7, 2010

1) Extaract time from date field

2) Extract minute form that time ..

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved