I have a PL/SQL script where I load data from a stored procedure into bind variables. Then, I insert that data from the bind variables into an Oracle table with a simple insert statement.
The execution of the stored procedure and the insert are in a FOR-LOOP. -
insert into ccd_t
-
(acct_id,
-
acct_seq_no,
-
acct_btn)
-
values
-
(to_number(v_acct_id),
-
to_number(v_acct_seq_no),
-
substr(v_acct_btn,1,10)
-
)
-
After exiting the for-loop, I do a commit. Everything works great unless there is an exception. When an exception occurs (unless it is ORACLE not available) the rows get inserted, but the v_acct_btn is NULL.
There is a WHEN OTHERS for exception processing that prints out the error.
I am thinking the acct_id and acct_seq_no are inserting valid values because of the conversion function, to_number. Since substr is a character function, the v_acct_btn is not dynamic?
Does that make sense? Can anyone verify my conclusion?
The stored procedure always returns an acct_btn, not NULLs so it is not from the stored procedure. Acct_id and acct_seq_no always return numbers.
Or is there some other reason for this odd behavior?
field: acct_id number(10)
field acct_seq_no number(3)
field: acct_btn varchar2(10)
field: v_acct_id varchar2(10);
field: v_acct_seq_no varchar2(3);
field: v_acct_btn varchar2(20);
Thanks.
6 6266
I have a PL/SQL script where I load data from a stored procedure into bind variables. Then, I insert that data from the bind variables into an Oracle table with a simple insert statement.
The execution of the stored procedure and the insert are in a FOR-LOOP. -
insert into ccd_t
-
(acct_id,
-
acct_seq_no,
-
acct_btn)
-
values
-
(to_number(v_acct_id),
-
to_number(v_acct_seq_no),
-
substr(v_acct_btn,1,10)
-
)
-
After exiting the for-loop, I do a commit. Everything works great unless there is an exception. When an exception occurs (unless it is ORACLE not available) the rows get inserted, but the v_acct_btn is NULL.
There is a WHEN OTHERS for exception processing that prints out the error.
I am thinking the acct_id and acct_seq_no are inserting valid values because of the conversion function, to_number. Since substr is a character function, the v_acct_btn is not dynamic?
Does that make sense? Can anyone verify my conclusion?
The stored procedure always returns an acct_btn, not NULLs so it is not from the stored procedure. Acct_id and acct_seq_no always return numbers.
Or is there some other reason for this odd behavior?
field: acct_id number(10)
field acct_seq_no number(3)
field: acct_btn varchar2(10)
field: v_acct_id varchar2(10);
field: v_acct_seq_no varchar2(3);
field: v_acct_btn varchar2(20);
Thanks.
Could you please post the error that thrown?
I am not able to make it from your explanation to what exactly is your problem. And also post the full procedure for my reference!!
Could you please post the error that thrown?
I am not able to make it from your explanation to what exactly is your problem. And also post the full procedure for my reference!!
I will do so. The exact problem is that rows get inserted with a valid acct_id, but an acct_btn with NULL values when an Exception occurs.
Here is one error that was thrown. I am not worried about the exact error. I know what is causing it. I am questioning why rows still get inserted, but with an acct_btn that is nulls and the acct_id is not nulls. acct_btn is not nulls coming from the stored procedure (btn_sp).
[ERROR]
Unknown Failure - SQLERRM: ORA-02068: following severe error from
tstdb
ORA-03113: end-of-file on communication channel
DECLARE
*
ERROR at line 1
[/ERROR] -
WHENEVER SQLERROR EXIT SQL.SQLCODE;
-
SET SERVEROUTPUT ON SIZE 200000;
-
SET ECHO OFF
-
SET TIME ON
-
SET TIMI ON
-
SET FEEDBACK ON
-
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD:HH24:MI:SS';
-
DECLARE
-
l_acct_not_found NUMBER;
-
v_lookup_cnt NUMBER;
-
v_acct_id VARCHAR2(20);
-
v_acct_seq_no VARCHAR2(20);
-
v_btn VARCHAR2(20);
-
-
-
CURSOR dss_cur IS
-
SELECT SUBSTR(etn,1,20) AS etn
-
, SUBSTR(btn,1,20) as btn
-
FROM ccd_hst
-
GROUP BY SUBSTR(etn,1,20), SUBSTR(btn,1,20);
-
-
dss_rec dss_cur%rowtype;
-
BEGIN
-
-
FOR dss_rec IN dss_cur LOOP
-
btn_sp(dss_rec.etn
-
, dss_rec.btn
-
, v_acct_id
-
, v_acct_seq_no
-
, v_btn
-
);
-
-
IF v_acct_id = '9999999999' THEN
-
l_acct_not_found := l_acct_not_found + 1;
-
ELSE
-
INSERT INTO cct_t
-
( acct_dim_id
-
, c_id
-
, acct_id
-
, acct_seq_no
-
, acct_btn
-
, insdate )
-
VALUES
-
( cct_t.NEXTVAL
-
, NULL
-
, TO_NUMBER(v_acct_id)
-
, TO_NUMBER(v_acct_seq_no)
-
, SUBSTR(v_btn,1,10)
-
, SYSDATE );
-
-
END IF;
-
-
END LOOP;
-
COMMIT;
-
-
EXCEPTION
-
WHEN OTHERS THEN
-
DBMS_OUTPUT.PUT_LINE('Unknown Failure - SQLERRM: '||SQLERRM);
-
RAISE;
-
END;
-
-
-
-
INSERT INTO ccd_t
-
(acct_id,
-
acct_seq_no,
-
acct_btn)
-
VALUES
-
(TO_NUMBER(v_acct_id),
-
TO_NUMBER(v_acct_seq_no),
-
SUBSTR(v_acct_btn,1,10)
-
)
-
-
Are you sure the value of the acct_btn retunred by the procedure btn_sp is NOT NUL??
Are you sure the value of the acct_btn retunred by the procedure btn_sp is NOT NUL??
I am absolutely sure that the acct_btn is never returned NULL. I have tested this procedure for over 1.5 million executions consecutively without ever returning a NULL. Moreover, there are no acct_btn with the value of NULL on the originating table.
-
-
INSERT INTO ccd_t
-
(acct_id,
-
acct_seq_no,
-
acct_btn)
-
VALUES
-
(TO_NUMBER(v_acct_id),
-
TO_NUMBER(v_acct_seq_no),
-
SUBSTR(v_acct_btn,1,10)
-
)
-
-
I am unsure what your post meant. Are you verifying that the problem is because the v_acct_id has a conversion function, and v_acct_btn has a character function? Can you direct me to some documentation, or provide an explanation?
Thanks.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Chris |
last post by:
I have tableA, defined as:
field1 varchar2(10),
field2 varchar2(10),
field3 varchar2(10)
I have host variables defined as:
v1 pic x(10) varying
v2 pic x(10) varying
|
by: FizzBin |
last post by:
We are writing a C application that is using ODBC to insert records
into a database. We have a NOT NULL column that can legitimately have
an empty value, i.e. we know the value and it is empty (i.e. a zero
length string).
We are using SQLBindParameter() to bind a variable to the
parameterized insert statement <<in the form: INSERT INTO...
|
by: Robert Stearns |
last post by:
Either I missed something, or ALTER TABLE does not have this
capability. Is there any way of doing it except
DROPping all constraints which mention this table,
EXPORTing the data,
DROPping the table,
reCREATEing the table without the 'NOT NULL property,
reCREATEing the INDEXes,
reloading the data,
redefining all of DROPped constraints...
|
by: Robert Smith jr. |
last post by:
Hello,
Please pardon my newbie question ...
I am building an ASP.NET page that displays a recordset with a Delete
statement enabled (this all works fine). I want to Insert the current
row *that is going to be deleted* into another table, before the
original data is deleted.
I am trying to use the RowDeleting method to call an Update...
|
by: Dean Slindee |
last post by:
I am looking for the "right" way to handle inserting and presenting null
date values.
Public Const c_NullDate As Date = #12:00:00 AM#
If I set the value of a date variable in an SQL Server insert statement to
c_NullDate, the insert statement works. When I re-read the row and display
the inserted date value in a text box, the string...
| |
by: Neil |
last post by:
I'd like to know what the best method is for handling NULL (or 0)
values within a DropDownList.
If a database lookup value is optional I would normally consider
leaving it NULL but this leads to problems with DropDownLists because
there is no value to bind to and an eror is raised.
I considered a method whereby the DropDownList has a an...
|
by: clickon |
last post by:
I am using ASP.net 2.0 and trying to take advantage of the updated data
editing facilities provided through the SQLDataSource control and the
DetailsView control. The data is a record from a customer complaints table
and one of the fields on the DetailsView control is called ComplaintType.
The field is a template field and in insert mode and...
|
by: rcoco |
last post by:
Hi,
I have a datagrid that is ment to insert data. But when I run the form
only the header appears. I would like some advise from you all and
solve this problem I'm using visual studio 2003. My code looks like
this:
private void Fill()
{
DataTable table = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * from
|
by: justice750 |
last post by:
Hi All,
I am using a FormView control. The allows me to update records in the database. However, when a database field is null I can not update the field on the form. It works fine when the field is not a null value. I am not using any code behind (C#) to bind the data or manipulate the data.
I have read that when there is a null value in...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |