Client Tools :: Creating Spool File Dynamically

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


ADVERTISEMENT

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 :: Add Application Name In A Spool File?

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

Client Tools :: Format Output File By Command Spool?

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

Client Tools :: Spool Clob Column To A Flag File

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

Client Tools :: How To Spool Clob Data Into Flat File

Oct 11, 2011

I want to spool CLOB data into flat file ?

View 6 Replies View Related

Client Tools :: How To Get SQL Query Output Into Text File Without Spool Statement

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

Client Tools :: Spool Only Time And Stats?

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

Client Tools :: Spool Output And Rollback

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

Client Tools :: Create Sub Folder Using SPOOL

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

Client Tools :: Spool In UTF8 Not Always Works

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

Client Tools :: Spool Output In PDF Format?

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

Client Tools :: Spool Without SQL Statement And Commands In PLSQL?

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

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 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 :: Suppress Oracle Script Message Dynamically?

Jan 30, 2012

I wiuld like to suppress oracle messages conditionally. I am running oracle view creation scripts from command line, After successful view creation, it is showing me "view created" message. To suppress this, I have created following

set serveroutput on format wrapped;
SET TERMOUT OFF
@@test1.sql
SET TERMOUT ON

[Code]....

but still it is giving me "PL/SQL procedure successfully created" message along with "'View test831 created successfully". I need to suppress "PL/SQL procedure successfully created".

View 7 Replies View Related

Client Tools :: Creating Procedure Using SQL Developer?

Jan 23, 2012

I am trying to get my stored procedures together again after many years of working on MS SQL server. I'm alternating between using SQL-Plus and Oracle SQL Developer for Mac. In SQL Developer - which I like to use - I keep getting errors at the point where the END command for the package header occurs and the create command for the package body starts.

I've found that if I run the following in SQL-Plus I'm OK but if I try it from SQL Developer I get a compile error such as following or else an error telling me that it expects function, or pragma or something to that effect:

Error(8,1):PLS-00103:Encountered the symbol "/"

The code is as follows:
--------------------------------------
CREATE OR REPLACE PACKAGE MACR_SAMPLE_PROC_4 AS
PROCEDURE sampleproc_4(
pParam1 in integer,
pParam2 out varchar2);
END MACR_SAMPLE_PROC_4;

[code].....

View 9 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

Client Tools :: Create Utl File Setup?

Jul 23, 2012

Is there any setups required for creating a utl file using sql developer. If yes, what are the setups to be done.

View 1 Replies View Related

Client Tools :: Saving A Database To File?

Oct 10, 2011

I am connected to an oracle database on campus thru my laptop. The interface i am using is oracle sql developer. We are practicing entering SQL commands. The connection name is DBA120. So, the name of the database is DBA120, correct? Also, I must review the respective ERD (i.e., to know the table relationships) in order to determine the proper sql commands, correct?

Well, in this database connection (DBA120), there are several tables that actually pertain to three different databases, ACCOUNTING, BOOK, and DJ. (I have the respective ERDs.)

I want to be able to save a database to file so I can run it on another computer. How do I save each of the three databases to a file or can't I? Or must I save the DBA120 database? how do i do that? do i save it(them) as sql files?

View 2 Replies View Related

Client Tools :: How To Get Rid Of First EMPTY Line Of Spooled File

May 30, 2011

how to get rid of the first EMPTY line generated by SQL*Plus when spool'ing to a file.

I tried using set pagesize 0 and while that get rid of the empty blank line, it also disabled the heading ...

Currently using sed as a work around. But unfortunately, can't use sed on Windows?

set pagesize 200
set heading on
set trimspool on
set lines 200
set verify off
set echo off
set feedback off

[code].......

As mentioned, using pagesize 0 remove the blank line but I also lost the heading.

View 4 Replies View Related

Client Tools :: Execute Multiple SQL Files In BAT File

Nov 27, 2012

Is it possible to execute multiple sql files in one .bat file?for single sql file it is working properly.

.bat file
-------------------------------

sqlplus /nolog @ c: est01.sql

View 5 Replies View Related

Client Tools :: SP2-0310 / Unable To Open File

Feb 2, 2010

i m unable to run the following script in sqlplus

SQL> @D:vasucount.sql
SP2-0310: unable to open file "D:vasucount.sql"
SQL>

View 5 Replies View Related

Client Tools :: Running Query Using CSV File As Reference

Feb 19, 2012

I need to say I am an absolute NOOB when it comes to SQL.I need a script to run in TOAD that will reference a CSV file saved onto my local hard drive. I'll try and describe exactly what I need to do.

The current script which I use via TOAD on our companies READ ONLY database is this:

SELECT d.number_id,
d.status_id,
FROM table.number_t d
WHERE d.number_id IN ('1230001', '1230002', '1230003')

This will return a result for each number that exists within the table.number table along with the status of each number i.e. active or inactive. A very basic query.

What I need to be able to do is run that query but instead of having to copy each number into TOAD manually, I need TOAD to check a .csv file of said numbers and then return the results.So I imagine the query would look something like:

SELECT d.number_id,
d.status_id,
FROM table.number_t d
WHERE d.number_id IN (check

View 13 Replies View Related

Client Tools :: OWB File Loading Through External Tables

Mar 15, 2011

Is it possible to trim the file name while loading into OWB through external tables?

Like suppose I am trying to read a file which has a timestamp value appended in its name. In that case loading into external file would give an error.

View 3 Replies View Related

Client Tools :: Conditionals In A SQLPlus Batch File?

Oct 23, 2012

From within a DOS batch file I make the following call:

sqlplus [User]/[password]@[server] @batch_script.sql [Server] [User]

Within the file batch_script.sql I have the following lines:

spool output_batch.lst
connect &&2/[password]@&&1;
@SomeOtherFile.sql

I want to change the last line to something like this:

if &&1 = 'Something' then
@SomeOtherFile1.sql
else
@SomeOtherFile2.sql
end if;

I get the following result

SQL> if &&1 = "something" then
SP2-0734: unknown command beginning "if &&1 = "..." - rest of line ignored.

I took a shot at using the "If" statement and missed.

View 12 Replies View Related

Client Tools :: Create MDD File - Database Designer

Jun 5, 2013

I heard .MDD file is Database Designer for MYSQL(i don't knw exactly).how to create it..

View 1 Replies View Related

Client Tools :: SQL For Saving Query Result In A Text File

May 17, 2012

I want to execute a query which automatically saves the result of a set of queries in a text file and have to send that as an attachment thru a mail (using UTL_MAIL or UTL_SMTP) thru a stored procedure...saving the result in a text file in Toad.

View 2 Replies View Related

Client Tools :: Exporting Result To Text File Through Query?

Dec 12, 2012

I'm running this query on sql developer trying to export large file but its not executing.

set head off
spool c:myoracle.txt
select txt_name_insurer||'~'||txt_policy_number from Table_Name where rownum<'10';
spool off
set head on

Error:- line 1: SQLPLUS Command Skipped: set head on

View 16 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







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