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.
the following situation, I have a directory named /dat/global/stock/ inside this i will get files named differently for example below.abcdef.112dfgrt.2......
Here i want to load this file one by one into the external tables and generate one more file based on some enrichment.
Step 1. Have to take first file and to load into the ext table.
Step 2. Enrichment
Step 3.File generation.
Now here i am facing a problem that in that particular directory i usually get 1000 files so i need to get file one by one and to put in one more directory. how can i get file one by one and generate file by using oracle loader
1) I mistakenly changed my 'oracle_home' some days ago, as below: my computer --> environment variable ...
2) Then i realised that and edit the oracle home and saved as below: C:oracleproduct10.2.0db_1
But i am now not able to open sqlplus. it shows me the below error. 'sqlplus' is not recognized as internal or external command.
Actually what i am trying to do is to extract data form tables and place them in an external text file....i wrote the following code
FUNCTION
create or replace
FUNCTION dump_data ( p_query in varchar2,
p_separator in varchar2 ,
[Code].....
using oracle 10g currently create many external tables like so..
CREATE TABLE "XT_UNITS"
(
"Q1_2012" VARCHAR2(25 BYTE),
"Q2_2012" VARCHAR2(25 BYTE),
"Q3_2012" VARCHAR2(25 BYTE),
"Q4_2012" VARCHAR2(25 BYTE)
[code]....
is there any way I can use 1 flat file (csv) to populate many external tables ?
1. I logged in the oracle sqlplus using cmd with sys as sysdba user and run the attached script for creation of some tables.
2. The script ran successfully and I could see the data in all tables which are , select * from authors; select * from books; select * from inventory;
3. I logged in with same user using toad and no such table is showing.
PS: I used commit after step 2.
how to create a script that will describe only the structure of 150 tables of a database.have it just like this, but I cannot produce the name of the table:
spool desctab.sql
select 'desc '||owner||'.'||table_name||'' from all_tables where owner='SKIMA'
order by table_name;
spool off
.. the output of the spool file is something like this:
Name Null? Type
----------------------------------------- -------- -------------
OFC_CODE VARCHAR2(3)
OFFICE VARCHAR2(35)
DESCRIPTION VARCHAR2(50)
REMARKS VARCHAR2(200)
OFFICER VARCHAR2(35)
I can do any sql commands against the schema I want (PSUSYS) with my username(SCHWAN) in the sql window, but when I want to export a table(created with a different schema) no tables are listed in the export window. This does work for my coworkers with the same access.
At one time this used to work so I'm wondering if it is a parameter that I inadvertently changed.
best method (easy and efficient) to load tables from sql server database to oracle database.
View 2 Replies View RelatedI am loading data using Oracle External Table.
I am faced with certain errors which I am unable to proceed with.
Forwarding the source code snippet of the script of the oracle procedure.
The Source Code
drop table nar_temp_xtern;
create table nar_temp_xtern
(
cost_centre varchar2(06),
description varchar2(80),
field3 varchar2(80),
[code]....
Errors received :
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "badfile": expecting one of: "column, enclosed,
[code]....
if there is any particular DBFS settings to increase the performance on external table loading currently I have just mounted it with direction just looking for any other ways to improve the reading from the flat file that sits on dbfs on exadata x-2 half rack
View 1 Replies View RelatedIs 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 RelatedI 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?
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?
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.
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
i m unable to run the following script in sqlplus
SQL> @D:vasucount.sql
SP2-0310: unable to open file "D:vasucount.sql"
SQL>
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
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.
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
I heard .MDD file is Database Designer for MYSQL(i don't knw exactly).how to create it..
View 1 Replies View RelatedI 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 RelatedI 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"
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
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.
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
i'm working on sql developer my table contains 40 columns and contains around 4 to 5 lakhs records........
when i'm trying to export the results into excel or text file my sql developer is getting hanged... if the result is less than 2lakh record its copying....
I want to spool CLOB data into flat file ?
View 6 Replies View Relatedwhen i run the script file 1.sqlIt throws me following error
SQL> @/c/temp/1.sql
SP2-0310: unable to open file "/c/temp/1.sql"
I have extracted data from table and write into one text via sqlplus utility in shell scripts. i got correct output. i am having two issues on the output file
1) Outfile file size is huge high compare then table segment data.
2) last column having extra space.
The output column is clob datatype. so i have added set long 50000 and set longchunksize 50000 parameter. after adding these only i got above issues. without two options, i am not getting this isssue but lines are wrapped.
#Set the scripts Path
SCRIPTS_PATH="/usr/local/ccms/gpa/svr/scripts"
echo $SCRIPTS_PATH
[Code]....