Client Tools :: Spool Out Hangs After Successful Execution - How To Avoid It
Oct 17, 2011
My SQLPULS script below generates 2 million records and it works fine without any errors, which I run from my batch file. (sqlplus -S %CONNECT% @"SCRIPTSmysql.sql"
The script itself runs 2 hours and exits via sqlplus.exe properly without any errors based on errorlevel from my batch and proper generated data. However, I have noticed that spool output file stops generating any records after 1 hour. It appears that client sqlplus.exe is sleeping or verifying something after spooling completed exactly the same period of time that takes to generate my out file. Is there a way to configure my script to avoid this 1 hour sleep/verification process?
whenever sqlerror exit sql.sqlcode
alter session set current_schema=blah;
/
set linesize 1000
set feedback off
[Code]....
View 3 Replies
ADVERTISEMENT
May 9, 2010
m spooling the results of some performance tests to a text file, the test script im using calls various sql statements in other scripts and spools to the results, stats and time to the same file...Howver I dont want to spool all the results... i only want the stats and time.. how can i do this?
my script looks like:
=====================================================
SET TERMOUT OFF
SET AUTOTRACE ON STAT
SPOOL f:/testresults.txt
[code]...
View 39 Replies
View Related
Oct 26, 2011
I need to do the following via the sql plus command prompt :
1. Execute a large ".pls" script file which does various inserts and updates to a table.
2. Spool the output of "select * from updatedtable;" to a text file to see the changes made.
3. Rollback all the updates and inserts in the script.
View 2 Replies
View Related
Mar 5, 2010
I want to ask if we could create a sub folder using the SPOOL command. I tried the following but it gave "Cannot create Spool file" error.
SPOOL C:TESTABCABC.sql
where TEST is already present in the C drive whereas I want SPOOL command to create a sub-directory named 'ABC' and then create the spool file 'ABC.sql' isnside it.
Is this possible ? My OS version is MS XP.
View 4 Replies
View Related
Jul 26, 2011
To add application name in a spool file, I am using the below way.
sqlplus username/pwd@tns @xyz.sql APP0115
SQL> define appname="'&1'"
Enter value for 1: APP0115
SQL> prompt &appname
'APP0115'
SQL> spool &appname._html_jobs.csv;
SP2-0768: Illegal SPOOL command
Usage: SPOOL { <file> | OFF | OUT }
where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]
But I am getting the above error in the spool file clause because of single quote printing infront of the spool file. But the method of defining a character is "'&1'". So I cannot avoid this single quote in the define clause.
'APP0115'
print the appname like APP0115 instead 'APP0115'.then only I can use this in the spool file clause?
View 3 Replies
View Related
Oct 25, 2011
I have a problem spooling flat files in utf 8. I have 3 different sql that needs to be spooled regulary in utf8 format.the first two containing special chars is spooled without any problem, but the third that is containg only numbers and pipe-s is spooled in ansi format.
i have set nls_lang=.utf8 in my shell script, but that only worked at the first two sql the last doesnt work.
I have also tried to convert file using iconv command, but the output is still in ansi (only works if i put a special char in the ansi file then it converts to utf8 without problem) if i open the file in notepad and save as utf8 then it works, but i cannnot reach the same from unix script.
View 4 Replies
View Related
Jan 3, 2011
I am looking forward for getting the output of audit records in a non-editable format as in pdf or something of that sort. Is it possible from Oracle 10g Rel-2?Can i spool output as PDF format?
View 6 Replies
View Related
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
Jan 3, 2011
I have a PLSQL script.
sqlplus
$DB_ACCT << EOSQL
set heading off
set termout off
set pagesize 0
[code]....
My output in FILE.txt looks like this
SQL>
SQL> Select col_with_a||col_with_b||col_with_c from
2 alphabet_table;
abc
[code]....
I tried several options by putting set echo off, etc.What do I include in the sql script to get the output as :
abc
abc
abc
without the SQL prompt and the statement, and the spool off command?
View 2 Replies
View Related
Aug 20, 2010
how to create a file in a folder based on todays date. i need to know how to define a variable in sqlplus and assign a value to it.Here is the code below. The code gets executed without creating a spool file.
DEFINE _DATE = replace('C:\_sysdate_EU001.csv', '_sysdate_', TO_CHAR(SYSDATE, 'DD-MON-YYYY'))
spool _DATE
set serveroutput on size 100000
select * from dual;
spool off
View 14 Replies
View Related
Jan 20, 2011
I use sqplus in oracle to output the command output to text file .
I use below set environment varialble.
SQL> set echo off;
SQL> set linesize 3999;
SQL> set feedback off;
SQL> set feedback off;
SQL> set termout off;
SQL> set pagesize 0;
SQL> spool mapping.txt
select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
In ouput file , it look like
SQL> select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
060010007 10007
:
:
:
SQL> spool off;
any setting or command that allow me to remove the first sql command line" SQL>select XXXX" and the last command "SQL>spool off" after start up the "spool mapping.txt"
View 8 Replies
View Related
Jul 15, 2013
I would like to spool a clob column to a flag file, however some of the clob are greater than 32k, and I have to have the same record in a single line in the file. Is there any way to achieve this through spooling?
set heading off
set feedback off
set term off
set long 1000000
set longchunksize 500000
set line 32767
set trimspool on
set pagesize 50000
spool file.txt
@--this is my select statement.
spool off
exit
View 1 Replies
View Related
Oct 11, 2011
I want to spool CLOB data into flat file ?
View 6 Replies
View Related
Mar 7, 2011
I would like to store my sql query output into text file.Like for example:
select name from emp where emp_id=101;
Here output should be in text file as
swapna.
I dont want to use spool statement here,since If I use it,spool statement will also be printed in text file which is not my requirement.I just want to take only output.
View 1 Replies
View Related
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
Aug 10, 2011
My need is to check whether procedure execution is successful and write sysdate and procedure name into log table.
Sure I can check target table which procedure uses to write result and count lines after execution. But I've got different procedures, some of them can write 0 lines and it's not error. I think Oracle should have something like return codes and some number means "ok".
View 12 Replies
View Related
Aug 13, 2008
I have two scripts (test1.sql and test2.sql) that need to be executed one after the other. I would like to call them both using a single command line:
sqlplus testuser/testpwd@testdb @c: est1.sql @c: est2.sql
This does not work
creating a test3.sql script that calls test1.sql and test2.sql is not an option.
View 22 Replies
View Related
Jan 24, 2012
I wrote an sql script (spooler) which runs a select query and produces results into a file.
I want to have the filename with the year attached. Can i pass a parameter when i run the script from SQL*PLUS
@'B: Year Of FinancingPLSQLspoolersSpooler_File_Creation' 01/01/2010 2010;
the first parameter 01/01/2010 is passed to the select query and taht works fine.
Since the output file contains 2010 data, I want the spooled file to mention in its name the 2010, so User's can know the file contains 2010 data in it.
So i wrote the statement within the spooler script
SPOOL X:HARSOrganizationsFinancialDataFor&2.txt
&2 should take 2010 has its value and produce the file FinancialDataFor2010.txt
how to use Substitution variables to produce the Filename i want.
View 10 Replies
View Related
Dec 5, 2012
In my database is present undesirable parallelization of the query. All involved tables has degree=1,
if I right understood there is no method to avoid this except reducing of degree of indexes ? I'm using 11.2.0.3
View 3 Replies
View Related
Feb 7, 2011
I am trying to understand on how ORACLE Continuous Query Notification works. I used the sample code from the ORACLE site:
//download.oracle.com/docs/cd/B28359_01/appdev.111/b28395/oci10new.htm#LNOCI13111
created employees and departments table under my schema.When I executed the sample code. I received an error
"ORA-24912: Listener thread failed. Failed to get IP address of host." that was generated from OCISubscriptionRegister.
Upon setting the client IP address through calling OCIAttrSet, the client can register successfully. I can see the registration entry in USER_ CHANGE_ NOTIFICATION_REGS.
I tried to trigger the notification by updating employee table as recommended. However, the client never received the notification.Is there a way to check if the server created notification and tried to send it out to the client?
View 3 Replies
View Related
Jan 5, 2012
which are recommended Tool for load testing (for performance) on Oracle-J2EE, 3 Tier applications?
Is 'Oracle Application Test Suite' the best for such test where we can simulate numbers of users and their various actions?
Does it come with Oracle Database license or we have to buy it separately?
View 1 Replies
View Related
Aug 26, 2011
I'm looking for 3 simple things.
1) A reverse engineering tool that I can point to an Oracle schema and get a "baseline" script to re-create that schema from scratch, with decently formatted DDL files (1 per object) neatly organized in a directory tree (by object type) and called in the correct order. Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to populate (insert) those tables as part of the script.
2) a diff tool that I can point to a pair of Oracle instances (source and target) containing a given schema and get a "delta" script to alter the target schema so that it becomes identical to the source schema. If data loss occurs on the target instance (i.e. drop a column) I would like to find a warning comment inserted in the script (e.g. "-- Attention: data migration DML needed here?"). Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to update (delete, update and insert) the data in the target tables to become identical to the contents in the source tables *without* deleting and re-inserting all rows (or dropping, recreating and repopulating the table).
3) I would like the above two tools (that, as you will have recognized, are basic to putting your database design under version control) to be open-source, with a command-line interface and a vibrant community backing them.
View 9 Replies
View Related
Feb 21, 2013
The problem is that this connection errors when I try to connect using my SQuirreL client. My developers want to connecting using the SID or the SERVICE_NAME and by using the "New Method" syntax, not the "Old Method" syntax. According to the documentation the "New Method" syntax works with the SERVICE_NAME or the SID. The "Old Method" only works with the SID.
# Host name is vmwwin7634.na.SAS.com
# SID is sting2
# SERVICE_NAME is sting2.na.sas.com
PROBLEM using SID errors ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2
THIS WORKS using SERVICE_NAME ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com
I am using SQuirreL Client version 3.4.0
# Here is the tnsnames.ora entry on the server.
STING2 =
(DESCRIPTION =
[code]...
# In this documentation it says, "On new syntax SERVICE may be a oracle service name or a SID."You can find this on the orafax wiki under JDBC#Thin_driver.
# I was able to connect with the "old method" using the SID only, SERVICE_NAME errors jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2.na.sas.com
(SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.")
jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2 (this works)
# "New Method"
jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com (this works)
jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2 (this errors)
(SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.")
View 10 Replies
View Related
Jan 13, 2011
can we use oracle client version to create a database in that server.
View 3 Replies
View Related
Dec 11, 2012
I have new virtual UNIX machine and I installed oracle client on /usr/lib/oracle. Also I have a oracle database and I am able to connect to this database from my desktop sql developer.
So now I am trying to connect from new UNIX machine. Where I created tnsnames.ora file under /usr/lib/oracle/network/admin and before connecting did export the following
export TNS_ADMIN=/usr/lib/oracle/network/admin
export ORACLE_HOME=/usr/lib/oracle/
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$HOME/bin:/sbin:$ORACLE_HOME/bin
export ORACLE_SID=VFIODSD1
when I try
# sqlplus
username :xxxxxxx
password : xxxxxxx
ORA-12545: Connect failed because target host or object does not exist. Not sure what I missed here. using same tns file I am able to connect from sql developer on windows.
View 1 Replies
View Related
Jul 20, 2012
Can i install Oracle 10g Client 32 bit on our Windows Server 2008 R2 64 bit?
View 1 Replies
View Related
Jul 11, 2012
I need to install Oracle client to get latest sql plus working on client pc as the problem is, i am not able to find out where i can download this , i just need the sql plus as i am not able use many commands because when i installed oracle forms 6i client tool , sql plus 8.0 got installed.
View 4 Replies
View Related
Jan 16, 2013
I want to use BR*Tools to administer an Oracle database. I have installed BrGUI local, set the JAVA Home variable and edited the brgui.properties file accordingly.I am able to login, but the menues are mising. The login screen is incomplete.
View 5 Replies
View Related
Jan 22, 2011
I usually work in oracle using the different editors like toad, plsql developer etc. I felt command line is good utility because of various supporting points. I am not sure what kind of command line tools is available/used. I am using pretty straight command line
(run->cmd, then enter username and password).
The best command line tools available. What is generally used command line tools.
View 1 Replies
View Related
Jun 28, 2010
The version is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi. for Unix AIX 5
I install the ODBC Driver Manager, Basic Instant Client, ODBC Instant Client packages (libsqora.so, odbc_update_ini.sh).
The error:
[oracle@d4 oracle] $ isql OracleODBC-10g user passtemp -v
[01000][unixODBC][Driver Manager]Can't open lib '/home/oracle/instantclient_10_2/libsqora.so' : file not found
[ISQL]ERROR: Could not SQLConnect
Configuration
odbcinst.ini
[Oracle10gODBCdriver]
Description = Oracle ODBC driver for Oracle 10g
Driver = /u01/app/oracle/product/10.2.0/db_1/lib/libsqora.so
Setup =
FileUsage =
CPTimeout =
CPReuse =
The documentation explic with exists the file libsqora.so.10.1 but not exists in the ODBC Instant Client packages.
I applied ln -s libsqora.so.10.1 libsqora.so but the connection not function
odbc.ini
[oradns]
Application Attributes=T
Attributes=W
BatchAutocommitMode=IfAllSuccessful
CloseCursor=T
[code].......
View 1 Replies
View Related
