| Re: Dynamic Triggers [message #373236] |
Thu, 05 April 2001 13:30 |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Jes, my way only works when you execute from SQL*Plus. There is nothing wrong that I can see with a function. In 8i, you can use "execute immediate" to execute dynamic sql, it's more messy in < 8i. You can just build up the string in VARCHAR2 or use VARCHAR2S as in the following example for longer statements. If you use VARCHAR2, you probably still want to use chr(10) for newline characters, of put hard line feeds in your code like this:
str := 'string
with 3
line
feeds';
CREATE OR REPLACE PROCEDURE democalc (
/* The calculation string, for example: "result:=A*B;"*/
calc_text in VARCHAR2,
return_result IN OUT NUMBER,
retval IN OUT NUMBER
) IS
/* Create to hold the PL/SQL Block */
sqlstring dbms_sql.varchar2s;
cursor1 INTEGER;
i, j INTEGER;
new_line_flag BOOLEAN:=TRUE;
BEGIN
/* Populate the sqlstring array with the anonymous PL/SQL block */
sqlstring(1):='declare ';
sqlstring(2):='result number(38,2):=0.0;';
sqlstring(3):='a number(38,2):=2;';
sqlstring(4):='b number(38,2):=3;';
sqlstring(5):='begin';
sqlstring(6):=calc_text;
sqlstring(7):='myvar.result:=result;';
sqlstring(8):='end;';
i=1;
j=8;
/* Assign the cursor */
cursor1:=DBMS_SQL.OPEN_CURSOR;
/* Parse the PL/SQL array block */
DBMS_SQL.PARSE(cursor1,sqlstring,i,j,new_line_flag,DBMS_SQL.V7);
/* Execute the cursor */
retval:=DBMX_SQL.EXECUTE(cursor1);
/* Close the cursor */
DBMS_SQL.CLOSE_CURSOR(cursor1);
/* Execute the function check_calc to obtain the result from the anonymous
PL/SQL block. */
RETURN_RESULT:=check_calc;
END;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
