Example of jchem_table_pkg.jc_insert PL/SQL Function

User 0908c5ccdd

14-03-2007 10:07:33

Hi





Please does anybody have an example of how to return a varray of integers from the jchem_table_pkg.jc_insert procedure? I only want it at this stage to return a single CD_ID integer. However I am still learning PL/SQL so I would appreciate a little help.





I have set up a varray type using:





Code:
create or replace type cdarray is varray(10) of integer;






Next I attempted to create a function that I could call from VBA that would insert a single row into my JCHEMISTRY table and return the inserted CD_ID. I have the following code:





Code:
create or replace function register(smiles in varchar2, tablename in varchar2) return integer as





cd_num integer;


cd_return cdarray := cdarray();





begin


 


  cd_return := jchem_table_pkg.jc_insert(smiles, tablename, null, 'true', 'false', '');


  cd_num := cd_return(1);


 


  return cd_num;





end register;






However I get a PL/SQL error message when I attempt to compile the function of:





Code:
Compilation errors for FUNCTION JCHEM.REGISTER





Error: PLS-00382: expression is of wrong type


Line: 8


Text: cd_return := jchem_table_pkg.jc_insert(smiles, tablename, null, 'true', 'false', '');






Can anybody help me with what I am doing wrong please?

User 0908c5ccdd

15-03-2007 10:19:18

Hi





I have now solved the problem, I needed to define my aCDReturn variable as cd_id_array as below:





Code:
create or replace function register(smiles in varchar2, tablename in varchar2) return integer as





cd_num integer;


cd_return cd_id_array;






Function now compiles OK.