Precompilers OCI & OCCI :: ORA-01002 By Select For Update In SQL Statement?
Jul 3, 2013
I'm having a strange case of ORA-01002 Fetch out of sequence when I perform a process in the application.
Application logs, trace files (produced with dbms_monitor),
A system trigger to capture the error - all proved that the culprit is a single SQL,
*Not a PL/SQL block* That caused the issue:
Select COL1, COL2, COL3, COL4, rowid
FROM TAB1
WHERE COL1 = :1
AND COL2 = :2
AND COL3 = :3
ORDER BY COL1 ASC, COL2 ASC, COL4 ASC
FOR UPDATE NOWAIT;
binds are: 'AAA' , 10000 , 0
Also, My trigger looks like this:
CREATE OR REPLACE TRIGGER after_error
AFTER SERVERERROR ON DATABASE
DECLARE
pragma autonomous_transaction;
[code]...
I've read some about this error and everywhere it says that it has to do with fetching from an invalid cursor,And all the examples I've observed are of PL/SQL block - never seen any example/explanation of how it happens in a SQL query.
if the OCI maybe is somehow doing things different than the logs/triggers show?
I am back with one more issue. I am fetching records through a cursor from database using Pro*C and inserting some records as shown below:
EXEC SQL DECLARE cur_num CURSOR FOR SELECT STUDENT_NUMBER FROM STUDENTS WHERE STDID = :temp_num ORDER BY STUDENT_NUMBER; EXEC SQL OPEN cur_num; EXEC SQL WHENEVER NOT FOUND DO break;
[code]....
This code works fine sometimes but ends up with this error sometimes: ORA-01002: fetch out of sequence and also do I need to do null termination once i fetch data from the database like this:
Quote:EXEC SQL FETCH cur_num INTO :std_num ; std_num.arr[std_num.len] = '�'; std_num is declared as varchar datatype.
way to run an update statement through OCI and make it stop on the first offending record and then identify that record? Here is an example of what I need to do: I run an update with 5 records in the input buffer like this:
- the 1st record exists in the table - the 2st record exists in the table - the 3rd record does not exist in the table - the 4rd records exists in the table but is violating some unique constraint - the 5nd record exists in the table
I need to report that the 4th record from the input buffer has a problem, and then commit records 1, 2 and 3. Do not commit record 5. So far I have tried the following:
- OCIStmtExecute with OCI_DEFAULT. It stops on the first error, but I don't know how to identify the bad record. OCI_ATTR_ROW_COUNT returns the number of affected records in the target table. I didn't find any other attributes that would return 4, for the 4th bad record in the input buffer.
- OCIStmtExecute with OCI_BATCH_ERROR. I can identify the bad records in the input buffer by using OCI_ATTR_DML_ROW_OFFSET. However, OCIStmt Execute does NOT stop on the first violating record. It loads the whole input buffer and then reports which records were in error. I don't know how to commit only up to the first bad record -- the 4th in the example.
I am using Pro*C/C++ Release 10.2.0.2 in HP-UX. But Pro*C/C++ application was written long back during oracle release 8. Now we are facing a problem like EXEC SELECT query is not fetching the records even though record is present in DATABASE.
This is not happening every time. This problem starts happening only after heavy use of the unix process.
For every request, unix process will fetch the record and updates the same at the end and process goes to wait mode to get the request again. Let say after 50 request, process is returned with no rows found error.
It started working fine only after restarting the process and problem starts again after 50th or 60th request. This problem we are facing only after upgrade to 10g.
I am trying to give back data from a stored procedure written in C. I registered the functions as follows:create or replace procedure version(versioninfo OUT clob) as external name "version" library myLib language c with context parameters (context, versioninfo, versioninfo INDICATOR SB4); It compiles fine. The function being called look like this:
If I execute the procedure with SQLDeveloper by pressing "play" it is getting executed but there is no result. If I try to execute it from an anonymous block it results in ORA-22275 instead of doing anything.
declare res clob; begin -- the following doesn't work much --dbms_lob.createtemporary(res,true); version(res); dbms_output.put_line(res); end;
Actually I have to questions: 1.) Why does Oracle give me the error? In my opinion all requirements mentioned by the error description are met. 2.) Why is there no output when executing the function via SQL Developer? Is the usage of OCILobWrite wrong?
I am trying to describe an STP in a package, but it gives me an error.
e.g. In package ABC suppose there is an STP XYZ, I am trying to describe ABC.XYZ function but it gives me an error code 4043 and error message object XYZ.ABC does not exist.
i need to compile a proc program, say prog.pc.have oracle 10g in my system. Since i am new to proc programming, me on the steps to compile the proc program in oracle proc compiler.
in my oci applications,if i get a column of number that is in the scope of int,i can use value = *(int *)field.data; get the value,but if the column size is larger than 10,the code can't be available,how can i get the value.
I am trying to call procedure from PRO C Procedure has many parameters and I do not need to put all of them when I call procedure. Is there way to make the same way as in PL/SQL
My OS is Linux and I installed Oracle 10.2. Everything is fine.I can use sqlplus, exp, imp etc with no problem.Now I have created another linux user test in /home/test. I unzipped basic-10.2xxxxxx.zip (/home/test/instantclient_10_2) and exported LD_LIBRARY_PATH.I guess I have installed instant client in this way.
My testOra.cpp:
#include <occi.h> int main() { return 0; }
This test.cpp would not compile. It cannot find occi.
I declare a cursor for a table with 8000 records, when I fetch the cursor this message appear ORA-03113: end-of-file on communication channels when the fetch reach the 6500 element, What is the problem here?. All data are ok, not null fields.
other problem
How I can reuse a cursor, I declare a cursor for a table where code_part = 300, then I fetch all elements until end cursor, then I close the cursor, Then I declare the same cursor again for the same table where code_part = 359, but it is not successful, when I tried to fetch the cursor again the cobol program show me the last record for the first code, How I can restart the cursor or delete it or freed the cursor position?
I am inserting empno in a table1 and updating another table2 using table1 empno and getting ora-01427 error. I want to print empno for whis this error is coming. How to print that value?
how to integrate SQLnet & c and I'm quite losted at the moment.
Searching with google gives some random stuff, which does seems to be irrelevant.some oracle db somewhere and need for good way to use that remote db (one solution seems to be using SQLnet).
If doing an insert into DATE type fields like below... how do I employ null indicator values with the TO_DATE sql to cope with NULL values for the End Date? I can test the NULLness of the pServiceRecord->itemTo value and set the indicator ind_dbToDate to -1 but I don't know how to incorporate this with the to_date syntax (if I can)?
EXEC SQL BEGIN DECLARE SECTION; char dbFromDate[MAX_DATE_LEN]; char dbToDate[MAX_DATE_LEN]; short ind_dbToDate; [code]...
I am using dynamic Pl/SQL with ProC and having problems with cursor. I've to execute stored procedure dynamically and get the result of select list into the cursor. The EXECUTE command is working fine but the FETCH gets failed with error "ORA-01002: fetch out of sequence". I've read that cursor variable can not be used with dynamic SQL.
Stored Procedure: ------------------ ROCEDURE open_mod_cur ( curs IN OUT cur_type, module_id IN varchar2)
I use a cursor to select records from a database table into a C structure as follows...
{ int iLoop = 0; int iResult = 0;
[Code]....
The otc_multiplier field is NULL. As is the to_date sometimes. However, when I output the records later, the entries where the to_date is NULL come out fine (no value). But the otc_multiplier is getting output as 0.0 using...
// this is output later in another fuction using the following.. sprintf(newRecord, "%.1f",daServiceRecs->itemMultiplier);
If using c structures in this manner, what is the method for ensuring that numeric values are set to NULL when required?
I am using the following piece of code to close the connection to the oracle database, I am using the context created in the previous step and passing it to the below code(connection.context)to release the connection.
if (connection.context == 0 || connection.ca == 0) return true; // Multithreading: get the communication area of the context struct sqlca & sqlca = *(connection.ca); EXEC SQL BEGIN DECLARE SECTION;
[code].....
When the above code is executed there is no exception thrown but the connection is not getting close. I am using the V$SESSION system views to monitor the connection created.
I there anything I am missing in my code for the connection closing to fail.
I am receiving the following error while inserting records.
"Oracle Error:ORA-12899: value too large for column "MFG_ADMIN"."GAGE_RESULTS"."COMP
whereas I am checking the length of all values before inserting and am sure that none of them are larger than column lengths.I did some research and found this error might be due to character set.
select * from nls_database_parameters where parameter like '%CHARACTERSET';
PARAMETER VALUE ------------------------------ ---------------------------------------------------------------------------------------------------- -------------------- NLS_CHARACTERSET UTF8 NLS_NCHAR_CHARACTERSET UTF8
how can this be related to my problem or if something else is causing this error.
I am having a core dump while running exe that uses services in pro c to go to the DBthe sentence that causes the dump is: sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
I need to print the &sqlctx, &sqlstm, &sqlfpn just before the call.
I am running a pro*c its working fine but my connection gets disconnect to my db server so i want to continue my proc if connection between my machine & server reestablish again weather it starts from start or from where it was stooped because of connection break
