Client Tools :: Different Type Of Data Export
Feb 24, 2010I need a free/open source tool for exporting different kind of data sets to oracle.
View 5 Replies
ADVERTISEMENT
Client Tools :: Data Export To Excel
Aug 5, 2013I 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.
Client Tools :: Number Data Type Column Does Not Display As Entered?
Jun 28, 2010Now i have a problem which i am facing for the first time. The problem is:
I have created a table test1 with two column of number data type and the column witdth is 25. Then enter the data but when i select the recrod it does not display the column data as it was entered.
SQL> create table test1
2 (
3 startno number(25),
4 endno number(25)
5 );
Table created.
SQL> insert into test1
[code]...
Client Tools :: Export Data In Nested Table
Feb 17, 2010I 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.
Client Tools :: Export Data From Oracle Database To Excel File?
Aug 21, 2012I 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?
Client Tools :: Use Spool Command To Export Data For Other Purposes Within Application
Feb 1, 2012I 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
Client Tools :: Execute A Procedure With Parameter Of Object Type?
Mar 29, 2010I am trying to execute the procedure from toad, where the type is like an object.How can I do that?
CREATE OR REPLACE TYPE list_t AS TABLE OF list_objtype;
CREATE OR REPLACE TYPE list_objtype AS OBJECT
( Emp_ID NUMBER(5),
EMp_Name VARCHAR2(32 BYTE)
);
In the following procedure I am calling list_t, but how would i pass the values to it?
PROCEDURE modify_settings
( p_List in list_t,
p_info out varchar2)
DECLARE
p_List TABLE;
[code]...
Client Tools :: ORA-31180 / Type Mismatch In Invalid PL/SQL DOM Handle
Jul 23, 2010ORA-31180: DOM Type mismatch in invalid PL/SQL DOM handle
I am getting this error while debugging one of the package in PL/SQL developer or in TOAD. Is there any setting which we need to perform at the database level to debug the packages which have the code related to XML DOM. I am asking as the same code works exactly fine at one of the test database and it gives the above error on the other database.
Client Tools :: Out Of Memory Using DDL Export In Toad?
Feb 11, 2013I 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?
Client Tools :: Export To Excel By Each Group?
Oct 17, 2013I 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?
Client Tools :: Date Format And Calendar Type Are Not Changing In Oracle Form?
Oct 30, 2011i have changed date's format and the calendar type in sqlplus through the following command so the date's format and the calender type both changed successfully and work, but in oracle form the they haven't changed.
alter session set nls_calendar='persian';
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD';
ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY/MM/DD' SCOPE=SPFILE;
Note: i know session would change the format temporally but i want to change it permanently so i tried alter_system but still no result
Client Tools :: How To Export / Import A Table Using PL/SQL Developer
Aug 28, 2010How to export and import table using Pl/Sql developer from one database to another.
View 9 Replies View RelatedClient Tools :: Toad / Export And Import - Value Difference For Big Numbers?
Oct 13, 2010I 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
Client Tools :: Export Toad Saved Username Password And Import On Sqldevloper?
Jul 1, 2013I 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 RelatedClient Tools :: Ora-01017 Import / Export SQL Server 2008r Wizard From Oracle 10g
Jun 18, 2013I 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
Client Tools :: Not Extracting All Of The Data
Jan 30, 2013I 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 RelatedClient Tools :: How To Transfer Data From Sql To Oracle
Jan 18, 2011want to transfer data from sql to oracle having same structure in both database.
View 3 Replies View RelatedClient Tools :: How To Remove Spaces After Data
Aug 14, 2012I 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 ?
Client Tools :: Inserting Data With Substitution Variable?
May 24, 2012I 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');
Client Tools :: How To Insert Data From SQL To Oracle Server
Dec 13, 2012I 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.
Client Tools :: Dynamically Spool Data In PL/SQL Block?
Nov 22, 2012I 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.
Client Tools :: DBMS-PROFILER Not Showing Any Data?
Nov 29, 2010I 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.
Client Tools :: SQL Developer Data Wizard - Row Terminator?
Sep 25, 2011I'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?
Client Tools :: Return Data From Oracle For Crystal?
Aug 9, 2010I 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
Client Tools :: Sql Developer Online Data Move
Dec 1, 2011I 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 RelatedServer Utilities :: Which Client Need To Use Data Pump Tools
Feb 13, 2012I'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.
Client Tools :: Creating Sequence Value After Data Import?
Feb 23, 2011I 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.
Client Tools :: Entry Screen - Last Data Changed
Jul 27, 2011I have a entry screen, where number of rows and columns are there, now end user can change in any row and in any column multiple times, now i have to send only last changed data row and column wise to database,to complete the transaction.
View 7 Replies View RelatedClient Tools :: TOAD Data Extract Excel 2007
Nov 4, 2010I have a question regarding data extraction to Excel.Is there any patch or update that makes it possible to extract to Excel 2007 and not only 2003?
View 6 Replies View RelatedClient Tools :: Import Of Data From Excel File Into Table?
May 28, 2012We 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.