Client Tools :: Export Data In Nested Table

Feb 17, 2010

I have some problem when i try to export data from a table which contains a nested table, using toad.

When toad generate the file, in the column corresponding to the nested table, toad just write a (DATASET) instead of the data contained in the nested table

Here is an example:
INSERT INTO SSD_REV_S ( REV_ID, REV_TAB, REV_TS, REV_USER, REV_LOG )
VALUES (
TO_Date( '07/30/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), (DATASET), TO_TIMESTAMP('4/3/2009 11:20:51.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'operator', 'Add EVENT');

REV_TAB is my nested table

way to export data from a table which contain a nested table, as a list of insert statement, so i can move the data to a different database schema? I can also use different client tool.

View 3 Replies


ADVERTISEMENT

Client Tools :: Data Export To Excel

Aug 5, 2013

I have a specific requirement. Currently in our system a SQL script is automated trough TOAD DATA ANALYST (Contains 50 sql Select statement) and all the output are stored in a single excel file in multiple worksheet. All these sql statements are running sequentially. Now my requirement is

1) All the sql queries will be executed in database in parallel .
2) All the output of select statement will be stored in a single excel.
3) Idea using any Client but Toad is preferred.

View 1 Replies View Related

Client Tools :: Different Type Of Data Export

Feb 24, 2010

I need a free/open source tool for exporting different kind of data sets to oracle.

View 5 Replies View Related

Client Tools :: How To Export / Import A Table Using PL/SQL Developer

Aug 28, 2010

How to export and import table using Pl/Sql developer from one database to another.

View 9 Replies View Related

Client Tools :: Export Data From Oracle Database To Excel File?

Aug 21, 2012

I am using SQLTools 1.5 for writing Oracle SQL scripts.

I have to import data from excel file to oracle database. How can I do it?

Also how can I export data from Oracle database to Excel file?

View 2 Replies View Related

Client Tools :: Use Spool Command To Export Data For Other Purposes Within Application

Feb 1, 2012

I would like to use the Spool command to export data for other purposes within the application. We would like to use tab delimiter to seperate the fields but the client wants to know if the text datatype fields can be wrapped in double quotes along with the tab delimiter..

SQL>create table test (id number(2), first_name varchar2(15), last_name varchar2(15),var_no number(4),
type varchar2(1),type_no number(12));

Table created.

SQL> insert into test values(1,'mary','ross',132,'S',12);

1 row created.

SQL> insert into test values(3,'Sue','Bill',432,'S',12);

1 row created.

I tried the below spool command to use tab delimited for all the fields but not sure how to wrap double quotes for only the text fields and also would to have the column names in the 1st row but don't seem to get the full column name in the csv file.

set echo off
set feedback off
set linesize 1000
set pagesize 4000
set trim on
set headsep off
set colsep '' (used tab between the quote)

spool test.csv
select id,first_name,last_name,var_no,type,type_no from test;
spool off

View 10 Replies View Related

Client Tools :: Import Of Data From Excel File Into Table?

May 28, 2012

We have a table partymast. we want to import the data into this table using excel file having extension .csv. I am explaining all the things below:

select *from partymast

partyid partyname accountname aacname
aa aa 10014000023367 Ashish

select * from master

masterid mname
10014000023367 Ashish

aacname column in partymast table is fetching from master table mname column.

We have third table name IMPEXP

Iename iedesc ietype iedef
import party master import party master imp {ImportStart}
[Transid]=party
[ImportFile]=:
[FileType]=excel
PartyID=col_1
PartyCat=col_2
AccountName=col_3
{ImportEnd}

Sir, I am enclosing the Excel file. what will be the next step that we should filllow.

View 3 Replies View Related

Client Tools :: Generate Insert Script For Existing Table Data?

Aug 21, 2008

Is theree any way to generate the insert script (with the data) for an existing table.

In Toad, we can generate the DDL for a particular table. Just curious if it can be done using any Tool.

View 19 Replies View Related

Client Tools :: Out Of Memory Using DDL Export In Toad?

Feb 11, 2013

I am trying to export all indexes (DDL:s) to a file from our datawarehouse. But Toad gets a "out of memory".

Is there a way for Toad to manage this task without getting out of memory?

View 2 Replies View Related

Client Tools :: Export To Excel By Each Group?

Oct 17, 2013

I have to export the results of a query to an Excel spreadsheet. Easy enough; however it is that I have to do it for each group. What I want to do is to be able to save it in a file that has the group number.

This is the query Lets say that I have the groups in the valiable RegionID: 11213, 21345 and 6537

@export on;
@export set filename="C:11213IP_claims_11213_.xls"
select * from mytable
where RegionID=11213;

How I can make it that I do not have to manually change the folder name in the direction and the where statement for each RegionID?

View 17 Replies View Related

Client Tools :: Toad / Export And Import - Value Difference For Big Numbers?

Oct 13, 2010

I wanted to load some data(selected rows) from one system to other.

from source(production) system Toad: selected * from employee table where registration date is not today.

from data grid I exported the rows using save as (in insert statement format) to employee.sql.

the sql file(employee.sql) has the below values for emaplyee 2,60716197237375E17 in the source(production) system this value is 260716197237375278, Later I loaded the sql file to dev system at linux prompt with sql file

@emplaoyee.sql

I retrieved the data after loading using to_char

select toc_char(emp_code) from employee_t;

the value I got is :260716197237375000

where as my expectation is : 260716197237375000

Is there any configuration required on linux system to convert the data correctly while loading to database using @employee.sql?

To conclude:

Exported value : 260716197237375278 (but exported as 2,60716197237375E17)

After import the value is : 260716197237375000 instead 260716197237375000

View 3 Replies View Related

Export/Import/SQL Loader :: Export Table From Client Machine

Jan 8, 2013

I want to export a table ( using exp or expdp ) from client machine. Dump file should be created to client machine.
Is this possible ? How to do this ?

View 3 Replies View Related

Client Tools :: Export Toad Saved Username Password And Import On Sqldevloper?

Jul 1, 2013

I want to use the saved Toad username password into SqlDeveloper. is there any way to use the same username password without recreating new connection to users.

View 3 Replies View Related

Client Tools :: Ora-01017 Import / Export SQL Server 2008r Wizard From Oracle 10g

Jun 18, 2013

I would like to Import data from Oracle 10g using Microsoft Sql Server (2008 R2) Import/Export Wizard, but i get this error when i try too establish the connection :

ora-01017

this the step i followed;

1- i filled the server name
2- username & password
3 - test connection ---> success

but when i try to follow pressing next(Suivant) i got the ora-01017 Error

View 12 Replies View Related

SQL & PL/SQL :: How To Compare Data In Nested Table

Mar 12, 2012

create type nesttype as table of clob;

create table emp
(empno number,
ename varchar2(1000),
language_known nesttype
)

I want to check whether language is already there in database or not.

i have written the below query

select * from emp where language_known =nesttype('english','hindi');

i am getting the below error

SQL Error: ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type
22901. 00000 - "cannot compare nested table or VARRAY or LOB attributes of an object type"
*Cause: Comparison of nested table or VARRAY or LOB attributes of an
object type was attempted in the absence of a MAP or ORDER
method.
*Action: define a MAP or ORDER method for the object type.

How to compare data in nested table

View 16 Replies View Related

SQL & PL/SQL :: Update Nested Table Data

Mar 12, 2012

SCOTT@orcl_11gR2> create or replace type nesttype as table of clob;
2 /

Type created.

SCOTT@orcl_11gR2> create table emp
2 (empnonumber,
3 enamevarchar2(1000),
4 hobbynesttype)
5 nested table hobby store as hobby_nt
6 /

Table created.

SCOTT@orcl_11gR2> insert into emp values
2 (1, 'name1', nesttype ('dancing', 'cricket','listening music','dancing'))
3 /

1 row created.

SCOTT@orcl_11gR2> insert into emp values
2 (2, 'name2', nesttype ('cooking', 'dancing','cooking'))
3 /

1 row created.

I want to update nested table record based on index suppose i want to update 3rd number of hobby for name2 employee

i have written the below query

SCOTT@orcl_11gR2>update Table(select hobby from emp
2 where empno= 2) e
3 set value(e)='new_value' where to_char(value(e)) = (select 4 to_char(tab1_element)
5 from (select rownum rn,

[Code]...

2 rows updated.

but the above query is updating 2 records but my requirement is it should update only third record

How can we update nested table based on index number

View 10 Replies View Related

PL/SQL :: Inserting Data Into Nested Table

Jul 22, 2013

I am exploring the differences between OBJECT & RECORD. As i am still in process of learning, I found that both are structures which basically groups elements of different datatypes or columns of different datatypes, one is used in SQL and other is used in PL/SQL. Below i am trying to insert data into an table of type object but i am unsuccessful.

CREATE OR REPLACE type sam as OBJECT
(
v1 NUMBER,
v2 VARCHAR2(20 CHAR)
);

---Nested Table---
create or replace type t_sam as table of sam;
--Inserting data----
insert into table(t_sam) values(sam(10,'Dsouza')); Error Message:
[code]........

View 4 Replies View Related

SQL & PL/SQL :: Nested Table - Update Activity For Data Correction?

Mar 17, 2012

I've to do a update activity for data correction. There are about 1 crore records involved in this. I want to keep a log of the activity at least Sales No. so that I can validate my data correction. Main cursor is involved for this. The problem is if I insert each record for sales number in the loop then process will take about double time. So i decided to create a nested table and insert after each session finishes.

I'm talking about session here is. The table I'm updation is partition table. So I can have many sessions and each session updating different table partition. This way there is no lock, better performance. Server has 35 processors so I can at least have 25 sessiions.

I could have used varray for this. But I want to learn nested table also. Problem here is not to take different way to complete this activity but to learn why I'm facing this issue.

CREATE OR REPLACE TYPE OBJ_TXN_LOG AS OBJECT
(
SALES_NO VARCHAR2(24)
,sALES_SEQ NUMBER(4)
) ;

[code]...

View 18 Replies View Related

Client Tools :: Not Extracting All Of The Data

Jan 30, 2013

I exported and imported data from one oracle database to another, but not all the data got loaded in to the destination database. Basically it is filtering the data. Can it be because of the reason that the sql developer may be an express edition?

View 4 Replies View Related

Client Tools :: How To Transfer Data From Sql To Oracle

Jan 18, 2011

want to transfer data from sql to oracle having same structure in both database.

View 3 Replies View Related

Client Tools :: How To Remove Spaces After Data

Aug 14, 2012

I have a little problem, using SQLPLUS I use "set linesize 300", so in the END of output file file. I got spaces after info ! I mean

select client_id || ';' || date || ';' || amount info
from table

1;FEB-2012;5000........
2;DIC-2011;10000.......
500;MAR-2012;300000.... (. = spaces)

How I remove spaces after Data ?

View 5 Replies View Related

Client Tools :: Inserting Data With Substitution Variable?

May 24, 2012

I want to insert the value with '&'. But this is treated as substitution variable by oracle.So how can it is possible to do like this

INSERT INTO DEPT (DEPTNO, LOC, DNAME)
VALUES (50, '&NEW YORK', 'SALES');

View 3 Replies View Related

Client Tools :: How To Insert Data From SQL To Oracle Server

Dec 13, 2012

I have problem: I have 1 sql server already setup SQL Server 2012 Express and 1 Oracle Database server 10g. Now i want to insert data from SQL server to Oracle database through link server.

Some step i already make:
1. Setup oracle database 10g and configure listener (Finished)
2. Setup Sql server 2012 express on Windows 7 (Finished)
3. Setup ODTwithODAC1020221 on PC already setup SQL server (Finished)
4. Make Linkserver from SQL server to Oracle database (Finished), and can select data from Oracle Database on SQL server through Linkserver.

However when i insert data from SQl server to Oracle Server not success.

select * from OPENQUERY (QVHKTEST, 'SELECT * FROM QVSYSTEM')

After i run above script, result is OK
With: "QVHKTEST" is alias of Link server from SQL to Oracle server
: "QVSYSTEM" is a table on Oracle database, that table we want to get through Linkserver on SQL server

Both Server Database contain same name table is "QVSYSTEM"
-----------
INSERT OPENQUERY (QVHKTEST, 'SELECT BODY_NO,
MERCHANDISE,
MODEL_NAME,
LINE_NAME,
DATE_ENTRY
FROM QVSYSTEM')
values('VNF4619829','3227B002CA','L1068','01','2012/09/26 03:18:11');

If i run script above directly in SQL Window query can insert OK. This is code in trigger at table on SQL server:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author Name: Phuong Do Minh >
-- Create date: <Create Date: 10/12/2012>
-- Description:<Description: After data insert into table qvsystem on SQL server
-- This trigger will fire and insert that data into table qvsystem
[code].......

But when i make trigger after insert on table in SQL Server to insert data From SQL server to Oracle server, however not success and SQL server raise error below:

OLE DB provider "OraOLEDB.Oracle" for linked server "QVHKTEST" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
Msg 7391, Level 16, State 2, Procedure Insert_data, Line 16

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "QVHKTEST" was unable to begin a distributed transaction.

I don't know how to configure them.

View 8 Replies View Related

Client Tools :: Dynamically Spool Data In PL/SQL Block?

Nov 22, 2012

I have a master table A that has 10+ columns along with data and a configuration table B that has only 2 columns i.e table name and column name. For ex:-

Table A:-

C1 C2 C3 C4 C5 C6
-- -- -- -- -- -----
1 2 3 4 3 6
4 5 6 5 5 5

Table B:-

Table_Name Column_Name
--------- ----------
A C1
A C2
A C3

Now I need to write a sql script that should take the table A Columns defined in Table B and then based on the columns it should spool the columns output from Table A.

For above ex:- I need to spool the C1,C2,C3 columns data from Table A in to some Unix path using SQL SCRIPT.

View 2 Replies View Related

Client Tools :: DBMS-PROFILER Not Showing Any Data?

Nov 29, 2010

I am using Oracle 10G with Toad & want to use DBMS_PROFILER to identify which part of procedure is taking long time.

I created PLSQL_PROFILER_RUNS,PLSQL_PROFILER_UNITS,PLSQL_PROFILER_DATA tables. and I am running DBMS_PROFILER as below.

DECLARE
x INTEGER;
BEGIN
x := DBMS_PROFILER.start_profiler ('Test Profiler');

[code]...

The procedure run is successfully completed.I gave the following command to see the data,

SELECT runid, run_date, run_total_time, run_comment
FROM plsql_profiler_runs;
SELECT runid, unit_number, unit_type, unit_owner, unit_name, unit_timestamp,
total_time
FROM plsql_profiler_units
SELECT runid, unit_number, line#, total_occur, total_time, min_time, max_time
FROM plsql_profiler_data

No data is displayed. Then I created these tables in SYS and gave grant to public.Yet It's same. No data is coming into the tables.

View 5 Replies View Related

Client Tools :: SQL Developer Data Wizard - Row Terminator?

Sep 25, 2011

I'm new to SQL Developer and am using the wizard to import a dataset with a non-standard row terminator.The row terminator is essentially 2 spaces back to back.

In SQL Server I specified the following in the SQL Server wizard and it worked:

{CR}{LF}{CR}{LF}

What's the equivalent for oracle?

View 2 Replies View Related

Client Tools :: Return Data From Oracle For Crystal?

Aug 9, 2010

I have problems in Oracle returning data from a stored procedure. In MS SQL it's quite simple.

i.e.

CREATE OR REPLACE PACKAGE globalpkg
AS
TYPE RCT1 IS REF CURSOR;
TRANCOUNT INTEGER := 0;
IDENTITY INTEGER;
END;
/

next the stored procedure:

CREATE OR REPLACE PROCEDURE mytest
(
RCT1 OUTGLOBALPKG.RCT1
)
AS
BEGIN
OPEN RCT1 FOR
SELECT *
FROM EMPLOYEE;
END;

Next trying to run the Stored procedure:

VARIABLE resultSet REFCURSOR
EXEC mytest (:resultSet);

Result always returns this error:

REFCURSOR - Unrecognized type.

I have tried using sample on the internet, but all returns the same error, what am I missing

View 32 Replies View Related

Client Tools :: Sql Developer Online Data Move

Dec 1, 2011

I am using SQL developer 2.1 to migrate tables from Sybase 12 database to oracle 11g. I have used online data move option for moving sybase data into oracle tables, but even after data move is completed not all rows have been moved from sybase tables to corresponding oracle tables. Some rows are missing but still there is no error message being displayed, how to find out what's going wrong.

View 3 Replies View Related

Server Utilities :: Which Client Need To Use Data Pump Tools

Feb 13, 2012

I'm installing a new application-testing server, i have installed 11g r2 instant clients & SQL* Plus client.

when i'm trying to run an expdp command, i get this:

'expdp' is not recognized as an internal or external command

Now, i understand this is because i don't have the Bin directory of a client installation in my Path of the OS. My question is, which one exactly i need for using data-pump utility, and where to download it?

I've found lots of posts of people that had issues with defining the ORA_HOME$in in the $PATH, or having a client incompatibility issue throughout the web, but no answer to my specific question.

View 4 Replies View Related

Client Tools :: Creating Sequence Value After Data Import?

Feb 23, 2011

I have an excel sheet as follows: I have to import the data in the excel sheet to oracle database table through TOAD.

name1 name2 name3
ABS SDFG FHTR
DFR GHJK HJK
... ... ...

Now, I need an id column along with these three that would have id numbers like that of a sequence. That means, suppose I have 1000 records in the excel sheet, then the table should have as many numbers automatically after data import.

View 18 Replies View Related







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