insert using jc_insert in sqlplus

Hi.


Could you please provide me with a complete code on how to


use the jc_insert functionality (or if there is some other way) of storing


a structure into a Jchem table through sqlplus.





Also, is there any way of inserting other data at the same time as the structure ? (ie insert into my_table (cd_structure, my_own_column) values(.... etc) ?





/Mikael

The only way to insert structures into a JChem table through sqlplus is by using the jc_insert function.

Code:

create procedure my_insert(my_jchemtable varchar2, my_structure varchar2, my_own_var varchar2) as   cdida cd_id_array; begin   cdida := jc_insert(my_structure, my_jchemtable, null, 'true', 'false');   execute immediate 'insert into ' || my_jchemtable       || ' (my_own_column) values(''' || my _own_var       || ''') where cd_id = ' || to_char(cdida(1)); end; /

The third argument to jc_insert is null, because (in this example) you use the default jchemproperties table. Note that you rarely (or never) need to use a jchemproperties table other than the default.


The meaning of the next to last argument to jc_insert: in this example, you do not want to insert a structure that already exists in the table. If you do not mind having duplicate structures in your table, set this argument to 'false'.


The meaning of the last argument to jc_insert: if my_structure already exists in the table do not raise exception (but do not insert the structure).





Peter





PS:


If you want to use more standard SQL syntax for inserts (and other DML operations) like

Code:

insert into my_table (cd_structure, my_own_column) values(.... etc)

, I suggest to use regular tables. Nevertheless, JChem Cartridge has better performance with JChem tables than with regular tables.