| |
Welcome to the dBforums forums.
You are currently viewing our boards as a guest which gives you limited access to view most discussions, articles and access our other FREE features. By joining our free community you will have access to post topics, communicate privately with other members (PM), respond to polls, upload your own photos and access many other special features. Registration is fast, simple and absolutely free so please, join our community today!
If you have any problems with the registration process or your account login, please contact contact support.
If you prefer not to see double-underlined words and corresponding ads, place your cursor here for ContentLink opt out.
|
 |
06-30-08, 13:12
|
|
Registered User
|
|
Join Date: Nov 2007
Location: Connecticut
Posts: 40
|
|
|
BULK Insert procedure
|
I have declared the following types and a small procedure to insert using bulk
I got the following error message on line 10 PLEASE HELP not sure whats wrong -- Have not posted in quite a while -- ANy help greatly appreciated
line10=insert into tetris_execution values te_data (indx);
SQL> show errors
Errors for PROCEDURE INS_TETRIS_EXEC_DATA:
LINE/COL ERROR
-------- -----------------------------------------------------------------
10/4 PL/SQL: SQL Statement ignored
10/4 PL/SQL: ORA-00932: inconsistent datatypes: expected - got
Code:
create or replace type te_object_rec_type is object (
TCAPREFERENCE VARCHAR2(50),
EXECTIME TIMESTAMP(3),
FIXMSGSEQNUM NUMBER(14),
FIXORIGSENDINGTIME TIMESTAMP(3),
ISMANUALEXECUTION NUMBER(1),
TXNTYPE VARCHAR2(12),
SIDE VARCHAR2(12),
ACCOUNT VARCHAR2(20),
PRODUCT VARCHAR2(20),
PRODUCTSOR VARCHAR2(20),
CURRENCY VARCHAR2(4),
QUANTITY NUMBER(18,8),
PRICE NUMBER(18,8),
EXECID VARCHAR2(100),
EXECREFID VARCHAR2(100),
TRADEDATE TIMESTAMP(3),
TRANSACTTIME TIMESTAMP(3),
SETTLEMENTDATE TIMESTAMP(3),
ORIGINATINGSYSTEM VARCHAR2(20),
CREATED_BY VARCHAR2(20),
CREATED_DATE DATE,
MODIFIED_BY VARCHAR2(20),
MODIFIED_DATE DATE
)
/
create or replace type te_assoc_array_typ is varray(1000) of te_object_rec_type
/
Code:
create or replace procedure ins_tetris_exec_data (te_data IN te_assoc_array_typ)
as
bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
--
BEGIN
--
FORALL indx IN te_data.FIRST .. te_data.LAST
SAVE EXCEPTIONS
insert into tetris_execution values te_data (indx);
EXCEPTION
WHEN bulk_errors
THEN
FOR j in 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
dbms_output.put_line ('Error from row #'||to_char(SQL%BULK_EXCEPTIONS(j).error_index) || ': ' || SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code));
END LOOP;
END ins_tetris_exec_data;
/
__________________
Code:
select * from client where clue > 0;
0 rows returned
|
|
06-30-08, 13:24
|
|
Registered User
|
|
Join Date: Nov 2007
Location: Connecticut
Posts: 40
|
|
|
forgot create table as per below
Code:
create table tetris_execution (
tcapreference varchar2(50) not null,
exectime timestamp(3),
fixmsgseqnum number (14),
fixorigsendingtime timestamp(3), --number(20),
ismanualexecution number(1),
txntype varchar2(12),
side varchar2(12),
account varchar2(20),
product varchar2(20),
productsor varchar2(20),
currency varchar2(4),
quantity number (18,8),
price number(18,8),
execid varchar2(100),
execrefid varchar2(100),
tradedate timestamp(3),
transacttime timestamp(3),
settlementdate timestamp(3),
originatingsystem varchar2(20),
created_by varchar2(20),
created_date date,
modified_by varchar2(20),
modified_date date
--constraint tetris_execution_pk PRIMARY_KEY (tcapreference)
);
__________________
Code:
select * from client where clue > 0;
0 rows returned
|
|
07-01-08, 10:49
|
|
Registered User
|
|
Join Date: Nov 2007
Location: Connecticut
Posts: 40
|
|
|
FIgured it out -- answer for any who cares --
|
FInally found some obscure syntax afer googling all day yesterday
heres how it works
types defined as
Code:
create or replace type te_object_rec_type is object (
TCAPREFERENCE VARCHAR2(50),
EXECTIME TIMESTAMP(3),
FIXMSGSEQNUM NUMBER(14),
FIXORIGSENDINGTIME TIMESTAMP(3),
ISMANUALEXECUTION NUMBER(1),
TXNTYPE VARCHAR2(12),
SIDE VARCHAR2(12),
ACCOUNT VARCHAR2(20),
PRODUCT VARCHAR2(20),
PRODUCTSOR VARCHAR2(20),
CURRENCY VARCHAR2(4),
QUANTITY NUMBER(18,8),
PRICE NUMBER(18,8),
EXECID VARCHAR2(100),
EXECREFID VARCHAR2(100),
TRADEDATE TIMESTAMP(3),
TRANSACTTIME TIMESTAMP(3),
SETTLEMENTDATE TIMESTAMP(3),
ORIGINATINGSYSTEM VARCHAR2(20),
CREATED_BY VARCHAR2(20),
CREATED_DATE DATE,
MODIFIED_BY VARCHAR2(20),
MODIFIED_DATE DATE
)
/
create or replace type te_varray_typ is varray(10000) of te_object_rec_type
/
Insert proc modified to this
Code:
create or replace procedure ins_tetris_exec_data (te_data IN te_varray_typ) as bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
--
BEGIN
--
FORALL indx IN te_data.FIRST .. te_data.LAST
SAVE EXCEPTIONS
insert into tetris_execution values (
TREAT(te_data(indx) as te_object_rec_type).TCAPREFERENCE,
TREAT(te_data(indx) as te_object_rec_type).EXECTIME ,
TREAT(te_data(indx) as te_object_rec_type).FIXMSGSEQNUM ,
TREAT(te_data(indx) as te_object_rec_type).FIXORIGSENDINGTIME ,
TREAT(te_data(indx) as te_object_rec_type).ISMANUALEXECUTION ,
TREAT(te_data(indx) as te_object_rec_type).TXNTYPE ,
TREAT(te_data(indx) as te_object_rec_type).SIDE ,
TREAT(te_data(indx) as te_object_rec_type).ACCOUNT ,
TREAT(te_data(indx) as te_object_rec_type).PRODUCT ,
TREAT(te_data(indx) as te_object_rec_type).PRODUCTSOR ,
TREAT(te_data(indx) as te_object_rec_type).CURRENCY ,
TREAT(te_data(indx) as te_object_rec_type).QUANTITY ,
TREAT(te_data(indx) as te_object_rec_type).PRICE ,
TREAT(te_data(indx) as te_object_rec_type).EXECID ,
TREAT(te_data(indx) as te_object_rec_type).EXECREFID ,
TREAT(te_data(indx) as te_object_rec_type).TRADEDATE ,
TREAT(te_data(indx) as te_object_rec_type).TRANSACTTIME ,
TREAT(te_data(indx) as te_object_rec_type).SETTLEMENTDATE ,
TREAT(te_data(indx) as te_object_rec_type).ORIGINATINGSYSTEM ,
TREAT(te_data(indx) as te_object_rec_type).CREATED_BY ,
TREAT(te_data(indx) as te_object_rec_type).CREATED_DATE ,
TREAT(te_data(indx) as te_object_rec_type).MODIFIED_BY ,
TREAT(te_data(indx) as te_object_rec_type).MODIFIED_DATE ); EXCEPTION
WHEN bulk_errors
THEN
FOR j in 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
dbms_output.put_line ('Error from row #'||to_char(SQL%BULK_EXCEPTIONS(j).error_index) || ': ' || SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code));
END LOOP;
END ins_tetris_exec_data;
/
__________________
Code:
select * from client where clue > 0;
0 rows returned
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
BULK Insert procedure
