Client Tools :: Bash Script - Extract Fields And Make Inserts In SQL File

Aug 13, 2010

I have a Bash script that counts the rows of a csv file, extracts the fields and makes inserts in a sql file. Then it logs into SqlPlus and calls the insert file. The sql file looks like this:

WHENEVER SQLERROR EXIT
INSERT INTO SCHEMA.TABLE1 (FIELD1, FIELD2, FIELD3)
VALUES (VALUE1, VALUE2, VALUE3);
INSERT INTO SCHEMA.TABLE (FIELD1, FIELD2, FIELD3)
VALUES (VALUE1, VALUE2, VALUE3);
INSERT INTO SCHEMA.TABLE (FIELD1, FIELD2, FIELD3)
VALUES (VALUE1, VALUE2, VALUE3);
[code]....

I rely on "WHENEVER SQLERROR EXIT" for things to go the right path. However sometimes because of the contents of the CVS files (which I can't control) some rows don't get inserted but SqlPlus doesn't see that as an error, doesn't exit and I end up with the wrong number of rows being informed in the second insert.Is there some kind of "if-then-else" construct in Sql? After all the inserts are made, do a "select count (*)" and compare that number to the one informed by the script. If they match, make the final insert and commit; else exit.

View 9 Replies


ADVERTISEMENT

Client Tools :: TOAD Data Extract Excel 2007

Nov 4, 2010

I 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 Related

Client Tools :: Not Able To Make ORA Connection Through Macro In Excel

Mar 15, 2012

i have windows 7 on machine. When i am trying to connect to oracle client using macros from excel like

OraSession = CreateObject("OracleInProcServer.XOraSession")

its giving me error saying "activex component cant create the object."

View 3 Replies View Related

How To Make Multiple Inserts In DB

Sep 4, 2009

i have multiple inserts to make in a table that is in an Oracle database...i already try several ways to do it but it always giving erros... how to make multiple inserts at same time.

View 2 Replies View Related

SQL & PL/SQL :: Improve Procedure Which Is Looping Through Query To Make Inserts

Oct 10, 2013

I am trying to improve a procedure which is looping through a query to make inserts.

FOR P IN (
SELECT O.TYPEID
,o.KEY
,O.ID
,O.NAME
,O.LGNUM
,O.LGNAME
[code]....

View 12 Replies View Related

Forms :: Unable To Make The Form Fields Available To Edit

Jan 30, 2013

My requirement is to call a form through special menu icon from one main form and display some information. Whenever i open a new form it should fetch a record and display where some of the fields should be available to edit. I am displaying a record based on a view.

For that i have used below code in when-new-form-instance

- GO_BLOCK('Block name');
- DO_KEY('Execute_Query');

After executing the query my block status is becoming 'QUERY' where i am not able to set the block/item property "Update allowed" to true.

I have added below piece of code

set_item_property('block_name.item name',insert_allowed, PROPERTY_TRUE);
set_item_property('block_name.item name',UPDATE_ALLOWED,PROPERTY_TRUE);

in almost all the triggers and tried. I could still see all the fields are read only.

View 4 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 :: 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

Application Express :: Make Fields On Form-page More Accurate?

Oct 18, 2012

How to make fields on form-page more accurate? I want make fields one under another one, and control the distance between them.

View 3 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 :: 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 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 :: 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 :: 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

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 :: Exporting Result Into Excel Or Text File?

Jul 2, 2012

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....

View 6 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 :: SP2-0310 / Unable To Open Temp File

Apr 14, 2012

when 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"

View 4 Replies View Related

Client Tools :: Display Clob Column In Text File In Sqlplus

Mar 30, 2012

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]....

View 2 Replies View Related

Client Tools :: Import Excel File To Database Table Using TOAD?

Jan 16, 2011

want to load data from an excel file to a database table in Oracle. I am using Oracle 11 and the excel file has 3 columns as compared to 5 columns in the destination table. I want to generate sequential nos also for the table.

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







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