Creating a procedure to insert comments into the database

[MAQAQM]

dir gurus,

how do I create a procedure to insert text comments to my database? this comments comes from offshore operators.

 

[Beilstwh]

What is the name and structure of the table that you want to insert into? What is the maximum size of the comment? More information is needed.

Bill
Oracle DBA/Developer
New York State, USA

 

[MAQAQM]

my table structure is:

CREATE TABLE FV_EVENT_CODES (
CODE_ID VARCHAR2 (23) NOT NULL,
CODE_TYPE NUMBER (10) NOT NULL,
DELETE_DATE DATE,
NAME VARCHAR2 (120),
CODE VARCHAR2 (15),
DESCRIPTION VARCHAR2 (768) ) ; ---this is the table where the codes are

CREATE TABLE FV_EVENT_COMMENT (
EVENT_ID VARCHAR2 (23) NOT NULL,
LANGUAGE_ID NUMBER (10) NOT NULL,
DESCRIPTION LONG ) ; -*- this is the table where the comments will be inserted.



CREATE TABLE FV_EVENT (
EVENT_ID VARCHAR2 (23) NOT NULL,
ENTITY_ID VARCHAR2 (23) NOT NULL,
ENTITY_TYPE NUMBER (10),
START_TIME DATE NOT NULL,
END_TIME DATE NOT NULL,
DATE_STAMP DATE NOT NULL,
ORIGINAL_LANGUAGE_ID NUMBER (10) NOT NULL,
TITLE VARCHAR2 (768),
VALUE_1 FLOAT,
VALUE_1_CODE_ID VARCHAR2 (23),
VOLUME FLOAT,
VOLUME_UNIT VARCHAR2 (69),
CUSTOM_FIELD_1 VARCHAR2 (768),
CUSTOM_FIELD_2 VARCHAR2 (768),
CUSTOM_FIELD_3 VARCHAR2 (768),
CUSTOM_FIELD_4 VARCHAR2 (768),
CUSTOM_FIELD_5 VARCHAR2 (768),
CUSTOM_FIELD_6 VARCHAR2 (768),
CUSTOM_FIELD_7 VARCHAR2 (768),
CUSTOM_FIELD_8 VARCHAR2 (768),
CUSTOM_FIELD_9 VARCHAR2 (768),
CUSTOM_FIELD_10 VARCHAR2 (768),
CUSTOM_FIELD_11 VARCHAR2 (768),
CUSTOM_FIELD_12 VARCHAR2 (768),
CUSTOM_FIELD_13 VARCHAR2 (768),
CUSTOM_FIELD_14 VARCHAR2 (768),
CUSTOM_FIELD_15 VARCHAR2 (768),
CUSTOM_FIELD_16 VARCHAR2 (768),
CUSTOM_FIELD_17 VARCHAR2 (768),
CUSTOM_FIELD_18 VARCHAR2 (768),
CUSTOM_FIELD_19 VARCHAR2 (768),
CUSTOM_FIELD_20 VARCHAR2 (768),
CUSTOM_FIELD_21 VARCHAR2 (768),
CUSTOM_FIELD_22 VARCHAR2 (768),
CUSTOM_FIELD_23 VARCHAR2 (768),
CUSTOM_FIELD_24 VARCHAR2 (768),
CUSTOM_FIELD_25 VARCHAR2 (768),
CUSTOM_FIELD_26 VARCHAR2 (768),
CUSTOM_FIELD_27 VARCHAR2 (768),
CUSTOM_FIELD_28 VARCHAR2 (768),
CUSTOM_FIELD_29 VARCHAR2 (768),
CUSTOM_FIELD_30 VARCHAR2 (768),
CATEGORY_CODE_ID VARCHAR2 (23),
RELATED_ID VARCHAR2 (23) NOT NULL,
USER_ID VARCHAR2 (38),
APPLICATION_NAME VARCHAR2 (768),
INSERT_USER VARCHAR2 (114),
INSERT_TIME DATE,
UPDATE_USER VARCHAR2 (114),
UPDATE_TIME DATE ) ; --- this is the table that controls the events.

I actually need this PL/SQL procedure to generate a sequence that will be used to insert each comment line into the FV-event table.
THE MAXIMUM SIZE OF THE COMMENT IS 2050.

please help me on this.
AQM

 

[Beilstwh]

First, I strongly suggest that you use a number instead of a varchar2 to hold you event_ID,code_id, and entity_id Then simply use an oracle sequence for each table. Also do NOT use a long to hold the comment, It will cause you major problems. If the maximum size of the comment is 2050, then use a varchar2. And you should include an internal ID number in the comment table so you know the order of entry. See the following code fragments.

CREATE SEQUENCE FV_EVENT_COMMENT_S
START WITH 1
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
NOCYCLE
NOORDER;

CREATE TABLE FV_EVENT_COMMENT (
comment_id number(23) primary key,
EVENT_ID number(23) NOT NULL,
LANGUAGE_ID NUMBER (10) NOT NULL,
DESCRIPTION varchar2(4000) ) ;

And you will not need a procedure, only a simple insert

insert into fv_event_comment(comment_id,event_id,language_id,description) values(fv_event_comment_s.nextval,the_event_id,the_langauge_id,'This is a comment');


Bill
Oracle DBA/Developer
New York State, USA

 

[MAQAQM]

I already have this tables, what I need is a procedure that will constantly insert the comments captured from offshore by operators from one database into another different database with the same data.

thats why i need a procedure to do that.

please help

AQM

 

[Beilstwh]

Can you apply a trigger to the comments table in the offshore database and do you have a database link between the offshore database and the second database. This could be done with a trigger that would immediatly mirror the data inserted into the first database into the second.

Bill
Oracle DBA/Developer
New York State, USA

 

[MAQAQM]

what i just need is a procedure, because offshore facilities are far.

thanks
AQM

 

[Beilstwh]

The offshore facilities can be on the moon and a trigger will work fine as long as a fast link is available between the facilities. If you don't have a link, how are you getting the comments in the first place? are they being put on tape and mailed to you?

Bill
Oracle DBA/Developer
New York State, USA