Forms :: Batch Creation At 10 Records

Dec 13, 2011

I'm working on a Self assessment project regarding our tax returns. Currently, this is how it works - a return lodged generates a return number, but is batched later. In the change proposed, they want the same process whereby a return is generated still, but at a count of 10 returns generated on the same screen, a batch is to be created and these 10 returns will have to be added to that batch. We are on Oracle 10G and work with Forms, Reports 10G and TOAD/SQL Plus as tools so I was thinking of changing it on Post-Query but suggestions are to add on to System Parameter table.

View 3 Replies


ADVERTISEMENT

Forms :: Run Batch File Through Form?

Jan 12, 2012

I create a form with button....whenever i click button my batch file should be run..

My batch file code :

@ECHO OFF
cd
ipconfig /all
pause

When-button-pressed trigger :

======================================
client_host('D: est_ipa.bat');

When i double click .bat file, it will get proper info.But when i call this .bat file thru form, it will show blank screen..why this .bat file not running ?

View 2 Replies View Related

Forms :: Call Batch File From Oracle 10g?

Jun 25, 2010

I want to call .bat file from oracle froms 10g.

I tried given below code but failed.

---------------------------------
declare
v1 varchar2(200);
begin
v1 := 'D:FolderLogicalbackup.bat';
HOST('cmd /c start'||v1,no_screen);
--HOST(v1);
end;
-----------------------------

The Logicalbackup.bat file contains:-

Cd d:oracleproduct10.2.0db_1in
d:
exp mw6/mw6@mw file=d:mw6ackupackupRGLHR.dmp log=d:mw6ackupackuplogRGLHR.log

View 4 Replies View Related

Forms :: Batch Program Retrieve Data From Oracle To Excel

Jul 5, 2011

I'm developing a new batch program retrieve data from oracle to excel.Normally i insert those data into single sheet. Is it possible if I want do this into multiple sheet.

Eg: I need to insert data base on branch category. different sheet for different branch but still in a single workbook?

View 2 Replies View Related

Forms :: How To Call Batch File Existing In Network Path Through 6i

Aug 10, 2011

How to call a batch file existing in network path through oracle forms 6i. I am using following code in Forms 6i which is supposed to FTP some .text files to a UNIX box.

Host( '\guww.netdfs.root.Global Shared Data$(LEV)Leven Shared DataLeven Shared DataPublicCOMMONMatsEmpty Bottle ReportsSchedules Preliminaryftp_files_to_ddd.bat');

When I am running this batch file ftp_files_to_ddd.bat from local directory of my PC,it is working properly.

View 1 Replies View Related

ASM Disk Groups Creation After ASM Instance Creation

Jul 9, 2012

During ASM Disk Groups creation after the ASM instance creation, receive the following error: Disk Group ORAASMGROUP2 already exists. Cannot be created again

The Grid infrastructure was deinstall one time and still the same issue.

View 4 Replies View Related

Forms :: Creation Of Master Details Using Builder 6i

Oct 11, 2010

1.steps to create master-detail form using form builder 6i ,db oracle 9i.

2.Using dept and emp(database table)

3.one more thing difference between cascading/isolated while creating relation b/w dept and emp using join method

View 6 Replies View Related

Performance In Batch Run

Sep 15, 2012

We are having a major issues with the batch run. we are using oracle 11g db. We run the scripts to populate the tables and then call scripts to run the extractions. The issue here each time we run the sql it takes so much inconsistent time.We have created index and run the db stats then run the extractions.The sql sometimes takes 10 minutes or sometimes takes hours to run? This is major show stopper of the project.

View 3 Replies View Related

Windows :: PL/SQL With Batch File

Aug 17, 2012

I am executing multiple PL/SQL files(.sql) with single batch file. The batch file sql.bat has got 3 sub sql sub-tasks to complete once its run. The sql.bat is show below

@Echo off

CD C:Report
echo Loadin tables from text file Report.txt
sqlplus security/password <c:Reportloader_security.sql
echo Creating Security table
sqlplus security/password <c:Reportcreating_security_final.sql
echo Inserting text file Security table
sqlplus security/password <c:Reportinsert_security_final.sql

PAUSE

The sql.bat runs perfectly if I double click on the sql.bat file separately. But if I call the sql.bat from a different batch file 'Final.bat' it throws the below error.

Error
-----------
Executing SQL commands and loading file into SQL tables
Loadin tables from text file Report.txt
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
[code].....

The Final.bat file calls other bat files too. It is as show below.

CD C:ReportSecurity
echo Merging all Files
CALL merge.bat

CD C:ReportSecurity
echo Deleting old files
CALL del.bat

CD C:ReportSecurity
echo Executing SQL commands and loading file into SQL tables
CALL sql.bat

View 11 Replies View Related

SQL & PL/SQL :: Batch ID From Child Status

Mar 27, 2013

Im looking for a query which returns the batch for which all the child should either be in 'A_STATUS','B_STATUS' or 'C_STATUS'. In this query im expecting a query which returns batch 2,3 and 4.

create table batch (batchid number);
insert into batch values(1);
insert into batch values(2);
insert into batch values(3);
insert into batch values(4);

[Code]...

View 9 Replies View Related

SQL & PL/SQL :: How To Run A Batch File In Oracle

Aug 18, 2009

I have a .bat file in my client system,which will open a web page after executing(after double clicking on it).I want to execute the same batch file from my pl/sql block.So,after executing my pl/sql block that .bat file should execute,and it should open the same web page.

View 12 Replies View Related

Updating Millions Of Rows In A Batch

Nov 9, 2011

I have a table which has plenty of rows. In production, I would estimate it to be from 30 millions to 300 millions. I need to update on column (flag) in all the rows (created before certain date).Now saying just:

UPDATE MyTable SET flag = 3 WHERE created < to_date('2010-10-08 23:59:59', 'YY-MM-DD HH24:MI:SS');
COMMIT;

Does not seem like a good idea - the commit-buffer would become too big.I will write a PL/SQL script for this. The question is, whether I should:

a) Update each row separately, and commit after every 10000 rows. ( WHERE RowId = [rowId] )
b) Update 10000 rows with set of dates ( WHERE rowId > [some_row_id] AND RowId < [some_row_id_2]

In the latter example the some_row_ids would naturally be fetched. The rowIds come from sequence. So which one would be more effective?I am not too familiar with PL/SQL or Oracle for that matter.

View 2 Replies View Related

Tuning SQLs In A Batch Process

Mar 18, 2013

I'm having an issue with stale optimizer statistics for some SQLs that are run in a batch process. The problem is that the process runs many times during the day - sometimes 20 to 30 times. And each time, the tables are updated, i.e. rows are inserted or deleted, etc.

So eventually the optimizer statistics for those tables become stale and the performance of the SQLs start to slow down (a lot). How best to gather the optimizer stats on the tables so they don't become stale when the batch process runs each time? The problem is that I also can't add/modify the code in the batch process because it is delivered by the vendor as is.

View 1 Replies View Related

Generate Last Seven Days Batch Run Times

Jan 8, 2013

I would the query to do the following:

1. Make the jobname distinct, because it keeps giving me multiple entries for each jobname
2. Add the the start_time of SOD_start_data9_UAT1 to end_time fodba_MUAT1 to get the combined duration
3. CONCAT jobnames SOD_start_data9_UAT1 and end_time fodba_MUAT1
4. Generate the last seven days batch run times
5. Generate a report into .csv format and email out
6. I have access to sqlplus and plsql developer

select distinct JOBNAME, schedtab
, to_char(to_timestamp(trim(timestmp)
, 'YYYYMMDDHH24MISS') - numtodsinterval(elaptime / 100
, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') as start_time
[code]...

View 3 Replies View Related

SQLPlus Script To Run In Batch File

Nov 1, 2010

I am trying to create a batch file which will be executed with windows scheduled task. This batch file will have sqlplus script running Oracle query. I can run this query from the command prompt, no problem,

This is the sqlplus scipt to run query:
C:\Documents and Settings\testuser>sqlplus
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 1 19:54:25 2010
Copyright © 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: TEST1
Enter password:
Connected to:
Oracle Database 10g Release 10.2.0.4.0 - Production
TEST1@DB> spool testuserfile
here goes my query
TEST1@DB> spool off
TEST1@DB> exit
Disconnected from Oracle Database 10g Release 10.2.0.4.0 - Production
C:\Documents and Settings\testuser>

How do I put this in a batch file. I was able to figure how to logon, but I get stuck at the spool command. My batch file starts like this:

cd C:\Documents and Settings\testuser
sqlplus TEST1/Password

View 1 Replies View Related

Critical Application Batch Performance

Sep 21, 2012

We have a critical application batch that runs daily and had a 7.5 hr window after which the application needs to come online. During the peak batch times the truncates are running very slow leading to slow down of the batch jobs quite considerably due to which the batch is going beyond the window.

Technical details:
Database version: 11.2.0.2
OS: HP-UX 11.31

The wait events that show up when the truncates are running are local write wait

enq: RO fast object reuse
enq: CR block range reuse ckpt
db file parallel write

The ASH reports show that the top sessions are those executing DMLs(insert, merge, update) and DDLs(Create/alter index & truncate). In addition to this it also shows that the blocking sessions are background wait events: CKPT and DBWR. Changes to DB configuration done with respect to addressing these issues are:

1) We have increased the DBWR processes to 2
2) Reduced the buffer cache size to 20G(from the original 30G)
3) Flushing the buffer cache before the batch begins in order to reduce the load on DBWR during the batch peak time
4) Set the parameter filessytem_io to SETALL(from none)
5) Tuned the EVA(SAN storage) to improve its performance - by distributing the loads evenly between the controllers, reducing the IO transfer block size, etc
6) Suggested using the reuse storage clause to improve truncate performance.

All of these have worked bring a semblance of control but the fact remains that the batch is generating more jobs(hence increasing data volume) over time due to it being the peak season. This causes an inevitable increase in the number of sessions all running DMLs and DDLs which are IO intensive operations.

Suggestions pending from our end:

1) Increase DBWR beyond 2. For this we need a H/W upgrade since we have maxed out the maximum number of DBWR that can be configured
2) Implementing asynchronous IO for DBWR which on HP-UX requires moving to raw disks. Hence have suggested using ASM.
3) Tuning the application to either reduce the IO generated or redistribute the jobs such that those with maximum loads don't run together

instead of truncating tables, can we rename the tables and delete them later . will this improve performance ?

View 1 Replies View Related

SQL & PL/SQL :: Error Handing With Batch SQL Scripts

Jul 16, 2012

I want run servral scripts in batch, and I use autorun.bat to call main.sql, which including servral scripts. If there has any pl/sql error in script, then the script will stop to run, but not exit SQL*Plus. If the pl/sql must exit, can it output the error messages in a file?

Please don't use "whenever sqlerror exit|continue...", because it will exit pl*sql tool or continue to run the other sql, it's not easy to know where the error happened

autoRun.sql
---------------------------------------------------------------------------
sqlplus "sys/manager@ORADB as sysdba" @main.sql

main.sql
---------------------------------------------------------------------------
--WHENEVER SQLERROR Exit SQL.SQLCODE
select sysdate from dual;
@@test1.sql
@@test2.sql
[code]....

View 3 Replies View Related

PL/SQL :: Validating Connection From Batch File?

Sep 3, 2012

I am trying to create a .bat file where I need to take inputs for below variables

DNAME
UNAME
PSWD
So I have done like this
SET /p DNAME="Enter the Database Name "
SET /p UName="Enter the Username "
SET /p Pswd="Enter the Password "

Now I need to validate whether I am able to establish the connection

sqlpus -s "%UName%/%Pswd%@%DNAME%"

if the above connection is valid one then perform certain action else need to echo user that Access is denied.

(ex: I am trying to say if cond is valid

stmt-1
else
echo message to user. )

View 3 Replies View Related

Tuning Searching Into Clob In Batch Operation?

Jul 3, 2012

My application runs a batch procedure weekly once for searching 'A_Text' from a column in Table1 in a clob column in Table2 and inserts accordingly into another Table3.

code snippet is like this -

---
CURSOR cr_sn
IS
SELECT serial_number

[Code]....

TABLE1 will have at least 1.1 Million rows but not significantly more than this.

This procedure takes 24+ hours to complete. I tried -

1. putting parallel hint ( INSERT /*+ PARALLEL*/INTO Table3)

2. partitioning TABLE2 based on last_update_date and putting a where clause in the above query last_update_date ( last_update_date between date1 and date2)

View 3 Replies View Related

Server Utilities :: Backup Through Batch File

May 18, 2011

exp system/abc@c file=c:dmp_%date%.dmp log=c:log.txt owner=xyz

I run this script through batch file its working.Problem is dump file showing with this format C:dmp_wed.dmp.I want to date format like that C:dmp_18052011.dmp.

for date formatting.How I can add date format in a batch file.

View 5 Replies View Related

Precompilers, OCI & OCCI :: Batch Error Mode?

Mar 4, 2010

what is OCI_BATCH_ERRORS and BATCH ERROR Mode ?and how the user can set it ?

View 6 Replies View Related

Windows :: Unattended Install Via DOS Batch For Client 8.1.7

Apr 27, 2004

I have got an unattended install (via the Oracle provided response file) working fine for Oracle Client 8.1.7, however the setup isn't working quite as I would like it...

I am calling the setup.exe with appropriate switches for the response file from a DOS Batch file.� Once setup.exe has been called, the batch file then assumes it has finished its job with the Oracle setup, and continues processing as normal, however the Oracle setup has only just started.

command line switch to add on the end of setup.exe to make sure that the batch file pauses until the Oracle client installation has completed?� Just using Sleep.exe from the Windows Res Kits isn't really good enough, as I don't want to be guessing how long an Oracle install takes - I'd much rather have the setup.exe tell the batch file when it has finished, so the Batch can carry on.

View 3 Replies View Related

Check If Database Is Idle Or Not From Batch File?

Aug 4, 2012

I am running Oracle Database 10g R2 on windows 2003, I want to create a batch file to check if the database is idle or not, and if it is idle shut it down and start it up.

View 13 Replies View Related

Windows :: Batch File Versus SQL Developer

Aug 23, 2012

I have a particular sql code which works perfectly fine on sql developer. But if I run the same sql code through a batch file it does not get executed. It does not throw an error too.

SQL code - clean_tables.sql

begin
execute immediate 'drop table external_tables';
execute immediate 'drop table security';
exception
when others then
null;
end;

Batch file - Clean.bat

set ORACLE_SID=orcl
set ORACLE_HOME=C:oracleproduct11.2.0dbhome_1
set PATH=C:oracleproduct11.2.0dbhome_1BIN

sqlplus security/password@orcl <c:Reportclean_tables.sql

pause

View 2 Replies View Related

Windows :: Passing Values / Arguments To Batch File

Aug 26, 2012

I have a batch file Menu.bat which should pass accept the input parameters from the end users and pass the entered values to a different batch file.

After entering option 2 the user should be prompted with an option to enter the path. E.g. /shared/folder.

Now '/shared/folder' should be passed to a different batch file named R.bat

Below is the Menu.bat file.

@echo off
color 00
title Security Audit Adaptor

:main
cls
echo //MAIN MENU\
echo.
echo 1. Got to Google
echo 2. Enter path
[code].......

View -1 Replies View Related

Performance Tuning :: How To Set Commit_write Parameter To (BATCH) In Another Session

May 16, 2013

How to set commit_write parameter to 'BATCH' in another session.

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

SQL & PL/SQL :: Function Batch - Cannot Perform DML Operation Inside A Query

Oct 8, 2013

Am calling the Function Batch to insert an update statemtnt into Batch_statement table in the DOWNLOAD_FUNC .But its failing with the error

SQL Error : ORA-14551: cannot perform a DML operation inside a query

Below Is the

FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS
BEGIN
INSERT INTO BATCH_STATEMENT(QUEUE_ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID)
VALUES (numQUEUE_ID,numTABLE_ID,varSTMT,1);
RETURN 1;

[code].....

View 27 Replies View Related

Backup & Recovery :: Execute A BATCH File Through PLSQL?

May 30, 2012

execute a BATCH File through PLSQL.

BEGIN
dbms_scheduler.Create_schedule(schedule_name => 'RMAN_TICKER_STARTING',
repeat_interval => 'FREQ=DAILY;BYHOUR=9; BYMINUTE=15,30,45,59',
comments
=> 'schedule to run daily');
dbms_scheduler.Create_program (program_name => 'TICKER_PROGRAME',

[code]....

It was created successfully, but when I execute, it shows the error message.

BEGIN
dbms_scheduler.Run_job ('RMAN_TICKER_JOB', TRUE);
END;
begin
dbms_scheduler.run_job (
'RMAN_TICKER_JOB',TRUE);
end;
Error at line 1
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing execution agent failed with status: 2

[code]....

But I have the CMD file in the location - "F:FEEDLGRTOOLSfeedlgr.cmd".

View 2 Replies View Related

Server Utilities :: Handling Errors In Batch File?

Aug 21, 2010

i get flat file and i have set up a control M job so that it runs at a particular time.

initially my control file was as below:

LOAD DATA
INFILE 'DFILEabcd.dat'
BADFILE 'BDabcd.bad'
REPLACE
INTO TABLE abcd_table
(A position(01:09) CHAR,
B position(11:12) CHAR,
C position(14:33) CHAR,
D position(37:50) char)

this was working fine. control M did not send FAIL message.but later i had to change the ctl file due to requirement. i had to add a when clause.

my code after modification is:

INFILE 'DFILEabcd.dat'
BADFILE 'BDabcd.bad'
REPLACE
INTO TABLE abcd_table
when A<>'10'
(A position(01:09) CHAR,
B position(11:12) CHAR,
C position(14:33) CHAR,
D position(37:50)

now the control M is sending an erro message after it runs the job. error is Return 5. thats all it gives.

i think it is due to errorlevel 1. in log file it says zero records inserted due to data error. then what is causing control M to send fail message??

sqlloader is loading all the required records correctly.

View 10 Replies View Related







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