package, procedure, string

[LRGood]

I want to write an "Update" procedure which creates a string. The string contains the sql. Then I want to run the sql. The procedure:

procedure formula_Modify(serieskey in formula.series_key%type,
formulaname in formula.formula_name%type,
formulacolumn in formula.formula_column%type) is
begin
sqlstr := ' insert into formula ';
sqlstr := sqlstr | | ' (series_key , formula_name, formulacolumn) values ';
sqlstr := sqlstr
| | '( ' | | serieskey
| | ', ' | | formulaname
| | ', ' | | formulacolumn
| | ')';
run sqlstr;
end formula_Modify;

I have tried several "words" which might cause the string to be executed, including execute, to no avail. what is the syntax to execute sql statements BTW i am using "open chemsynsearchdetails for sql_stmt" successfuly in another proc.

PS: I eventually want to add some "if then elses" to the proc but right now I am just trying to get the correct syntax

 

[Marco Kalter]

Instead of "run sqlstr" you can use:

execute immediate sqlstr;

It will be necessary to add quotes around the character values though.

This requires Oracle8i. On older Oracle Server versions you can use the dbms_sql package instead.

------------------
Marco Kalter
Allround Automations

[This message has been edited by mkalter (edited 10 September 2003).]