In any application, we will have many commit statements issued in many places. How can we find out as to where all commits have happened during runtime.
Basically in our application, when am trying an operation, am getting the error ORA-01086: savepoint <save point name> never established.
My guess is that there is a commit somewhere because of which system is not able to rollback to that save point.
I'm working on a Java-based web application and we have unit tests that we use to test all our all code that interacts with the database or code that interacts with our DB code. The Spring framework allows us to perform some DML within a transaction before each test and then rollback the changes. For the most part, this works, however when I run the full suite of unit tests, it will randomly commit data to the database causing the rest of the tests to fail.
will Oracle's auditing let me see where this odd-ball commit is occurring? Is there another way for me to see when data is being committed?
This does not appear to be happening on any of the systems we've deployed, however this is a bit unsettling and would like to know why this is occurring so that we can prevent it from happening in production.
When a user session commit he is waiting on 'log file sync' untill LGWR sends the message back to user session after writing log buffer content into redo log file.As far as i understand this is serialize operation(one at a time).
So how come i have 7 ms average 'log file sync' wait time and i can still perform 200 commits per sec ?
7 ms * 200 waits = 1400ms = 1.4 sec
i tried the following update on one table:
update siebel.s_contact
set marital_stat_cd =
case
when (marital_stat_cd = 'Casado') then 'Married'
when (marital_stat_cd = 'Solteiro') then 'Single'
when (marital_stat_cd = 'Divorciado') then 'Divorced'
end
As you can see i forgot the else, so my update is wrong.
I thought i could rollback the update issuing the rollback statement, but when i have issue the rollback, the i query the table to confirm that the update was rollbacked and for my suprise the update is commited.
I didn�t issue the commit statement after the update and i confirmed that the auto-commit feature to worksheets is disabled, so i don�t understand whit the update was commited.
I have been testing out a form using 10.1.2.0.2 on a v10.2.0.1 db and in my local env. the form works correctly i.e. if I make a change and 'post' it and then exit and press NO (when asked to save changes) then it correctly leaves the value in the database as it originally was.
The process works by the user pressing a button in form A (read only form) and this opens form B (using open_mode,session,activate) and the user makes their change(s) in form B (a 'post' command is issued in a When New Rec Inst trigger on a db block when the user navigates to a new record within the same block if it is determined that the block status <> 'QUERY') before returning to form A and pressing 'NO' when prompted to save changes.
However, if I run the same process in the TEST env. using the same executable against the same database then it actually updates the database value.
I have tested this by adding a debug message at the end of form B to retrieve the db value back AFTER having issued a clear_form(no_commit) just for the sake of the test and it still returns me the 'new' i.e. amended value - which is obviously incorrect. From what I can see it would appear that the commit occurs straight after the 'post' has been issued and well before the user even exits the form.
Is this a known bug with the 'post' built-in or could it be that a parameter is set to act in this way (i.e. is there an 'autocommit' setting that is 'ON') within the application server?
We have a fact table t1 in the warehouse which has above 6 million records.There is to be an update like this where t2 has aid+bid as composite primary key. column aid repeats in t1.There's performance problem and we'v been told to break this huge update into pieces with few commits in the middle.
update t1
set t1.aid =
(select t2.aid from t2
where t1.bid = t2.bid
)
I've tried cursor loop with 3 commits in the middle based on if condition that evaluates on every iteration.
I would like to write a select that would return all places in DB that are commiting transaction.
E.g. package for testing:
CREATE OR REPLACE PACKAGE test.TEST_COMMIT AS
PROCEDURE THE_ONLY_COMMIT_IN_DB ;
END TEST_COMMIT;--not a match
CREATE OR REPLACE PACKAGE BODY test.TEST_COMMIT AS
[code]....
The select should return 4 rows with --ok.
SELECT *
FROM ALL_SOURCE ASO
WHERE
REGEXP_LIKE(ASO.TEXT,'commit(s*);','i' )
AND name = 'TEST_COMMIT'
This is a daily batch job which captures end of the day changes from a set of Oracle 10G "Source" tables in (.exp) file and another overnight batch job will read from (.exp) files and Insert into Oracle 10G "Target" Tables. The .exp files are created by Oracle 10G Data Pump utilities.In future we will be migrating Target to Teradata. All the tables will be migrated. No change in table names or structure.
The plan of action to write the later bit of PLSQL (which read from .exp files and loads it into Target tables) into Java so that the programe can be re-used on teradata.
Questions
1) Can Java read the contents with in .exp files and insert into Oracle tables?
2) Can Teradata read the contents with in .exp files and insert into Teradata tables?
3) Will we be achieving any benefit if we execute DDL (Insert) using Java rather than PLSQL. Obvious one being Platform independent.
there is no transformation of data in flight between .exp to Target tables.
I want to load lakhs of records into a table. My problem is when after loading the ¼ of records my process is abend due to the size of my rollback segment area. I don't have an option to increase it. So, Is there any way to go for intermediate commits when I am using the imp or sqlldr utilities to load the entire data without abend?
View 2 Replies View RelatedI have excel file which I am reading through plsql procedure using UTL_FILE utilities, one of the column in the excel has multiple values in the same column, I am getting the values into plsql, but when it is coming to where clause its not working.
Example:
in excel the column has : 'ABC','GEH','HGT',LTP'
create or replace procedure abc(temp_col varchar2)
.
....
....
...
SELECT COLA, COLB, COLC
FROM TABLE_TEMP
WHERE TCOL IN temp_col;
This is not working, if the column in excel has one value say ('ABC') then the above sql is working, if it has more than one value its not working.
I have upgraded my Operating Syatem from Vista to Win7. I have SQL nav version but is not supported on Win7. So I have installed PLSQL developer. When I try to connect its giving the error connection not open. But when I tried the same credentials with SQLPLUS it's working fine.
View 3 Replies View Relatedwe have a program that uses SQLPlus to run PLSQL scripts - this program then checks the return code of SQLPlus to determine whether the script ran successfully. Our PLSQL scripts often call other PLSQL scripts via "@@OtherScript.sql" - one problem we have is that if (due to an engineering mistake) the OtherScript.sql file is missing, SQLPlus throws an SP2 error stating that the file was not found. However, the return code of SQLPlus does not indicate any problem. I have found, by searching the internet, that I can simply add a line "whenever OS error exit failure" to our script which then allows our program to detect the failure... however, the error message is not as informative:
CODESQL*Plus: Release 11.1.0.7.0 - Production on Tue Oct 19 10:28:45 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[code]....
is it possible to get BOTH types of error messages? With "oserror exit" set it does not tell you which file was not found.. which is useful information. I would like the SQL Plus session to return failure but also print out what file it did not find.
If in a PlSQL procedures there are many DML statements which are part of the same transaction. and there are many calls to other PLSQL blocks also.
what is the best practice to use commit either after each DML statement or to use it in the End of Block?
how can i display the out in matrx format.
example;
select country_code
, item_number
, sum(quantity)
FROM item_table it
[Code]...
output format:
--------- country_code1 country_code2 country_code3 country_code4
item1 10 77 99 87
item2 30 67 56 76
item3 35 45 66 66
item4 50 33 56 67
FOR my case PIVOT_ won't work. I am using 10g database.
My application reads a binary file in C++ using fstream.read and needs to save that binary data into the database as a blob.
I am not using OCI.
My struggle is trying to determine the proper way to pass this unstructured data to a PLSQL procedure.
1 > What data type should the input paramter be for the PLSQL procedure?
2 > My binary data is in a char variable. Do I need to convert that to something else before passing it as a parameter.
I am trying to implement a Houskeeping program for files generated in 4 different servers. This housekeeping program is run as a batch job and I need to use PLSQL to implement it. The files need to be housekept on the basis of the file creation date.
how I can go about doing this using a PLSQL stored procedure.
I want to debug the procedure using PLSQL Developer tool?How to use the debug option in that tool?.
View 1 Replies View RelatedAt PLSQL level how can we make our program re-startable such that if there is some abort after the commited update program will restart from the last commit checkpoint.
View 2 Replies View RelatedI am create procedure send the message in form bulider10g to Internet email and the procedure is work .
but now I want to modify this procedure , I want to add picture of my country ( logo ) and the original message . why cant display the image in my email ?
this code of image
<p align="center"><img border="0" src="logo.gif" width="20" height="20"></p>
I Think the problem of this code because 'Content-type: text/html' not image ?????!!!!!!!
utl_smtp.data(v_Mail_Conn, 'MIME-Version: 1.0' ||CHR(13)|| CHR(10)||'Content-type: text/html' || CHR(13)||CHR(10)||mesg);
procedure
EMAIL_SEN VARCHAR2(250);
EMAIL_REC VARCHAR2(250);
DESC_EN VARCHAR2(250);
UB_SEQ NUMBER(10);
BEGIN
DECLARE
v_From VARCHAR2(80) :=admin@yahoo.com ;
[code].........
my db version is:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
entry in my listener.ora file
===========================
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = extproc)
(SID_NAME = PLSExtProc)
[code]......
entry in tnsnames.ora file:
===============================
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc0))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.example.com)
)
)
listener is running perfectly. created c program, compile the program by
gcc -c <filename>
ld -shared -o <filename.so> <filename.o>
cp <filename.so> $ORACLE_HOME/bin
create library & function from db. when I am invoking this function it is throwing error message:
ORA-28546: connection initialization failed, probable Net8 admin error
I have one PLSQL package that does a join of two tables of remote database instance via the datalink. I just wonder where the most calculation(the join) is done, local machine or remote machine? Is there any best practice to have a better performance for such configuration.
View 3 Replies View RelatedI wanted to know how to find the links between cursors that have been created in v$sql after compiling a PLSQL procedure.
For example, if i compile the following procedure :
create or replace
PROCEDURE PROCEDURE1
AS
BEGIN
insert into t values(1,1);
END PROCEDURE1;
, the 2 following cursors are created in v$sql :
select SQL_ID, SQL_TEXT from v$sql
where sql_id in ('71pj8t5nz1d80','2s567zb6684sh');
"SQL_ID""SQL_TEXT"
"2s567zb6684sh""BEGIN PROCEDURE1; END;"
"71pj8t5nz1d80""INSERT INTO T VALUES(1,1)"
Thus, i would like to know how to find that the cursor 71pj8t5nz1d80 is called/linked by the cursor 2s567zb6684sh.
This would be useful for interpreting some sql statistics in v$sqlstats in where such cursors appear and related sql statistics are accounted twice.
I have imported a java class file that inturn gets content from a webservice. One of the functions on that imported class file is as follows ...
Funtion getcontent(A1 JOBJECT,A2 JOBJECT, A3 String) return JOBJECT ....
In my PLSQL package ...How do I invoke this class file ?
So far I have done this ...
Declare
abc ORA_JAVA.JOBJECT;
xyz ORA_JAVA.JOBJECT;
xxx VARCHAR2(25);
value ORA_JAVA.JOBJECT;
BEGIN
[code]...
I am getting an error saying the object type is wrong .... I would like to know how to assign a hardcoded value to the JOBJECT just to test before I continue.
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?
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".
From last couple of days, the paste option is not working for me in the PLSQL developer. I selected the text in a SQL window, selected copy.
At the desired location when I right click paste option is not visible in a menu. If it is visible then also it did not paste. I tried Ctrl-V also.
How can I create PLSQL table type dynamically?
I will have to take the Employee Names and create a table structure. Number of employee names can vary from day to day. So, whenever I execute my procedure with Table type, I will have to build the table columns with the employee names.
I have problems with calling procedure directly from webbrowser. I have a procedure test in test_pkg package. I granted execute on it to anonymous , added it to wwv_flow_epg_include_mod_local function and still have error 404.searched this forum and found this: Does listener use wwv_flow_epg_include_mod_local
So I granted to apex_public_user and created synonym for him to my package. And it starts working. But then I wanted to add next proceudre to my package the same way, and change my test procedure and it stop working again. Now I have 404 error again.
Application Express 4.1.1.00.23 ( plus all earlier versions that I've ever used)
When using the wizard to create a Validation of type "PLSQL Function returning Boolean", why is it mandatory to enter a value in the text field "Error Message" on the screen that follows? This message is never used as the message actually displayed comes from a PLSQL return statement.
We want to copy a file from a remote machine to the DB server. copy \<IP_ADRESS>SHARED_FOLDERsome_file.sql d:copied_file.sql
I've used Java and a wrapped it into a PL/SQL procedure so I can do :
exec run_os_command(p_command => 'copy \<IP_ADRESS>SHARED_FOLDERsome_file.sql d:copied_file.sql');
The file was not copied.
I have mapped \<IP_ADRESS>SHARED_FOLDER to x: for example and issued : exec run_os_command(p_command => 'copy x:some_file.sql d:copied_file.sql');
The file was not copied.
But when I issued from the OS prompt copy x:some_file.sql d:copied_file.sql then the file is copied.
What can I add to the PL/SQL procedure so it wil work ?