pl/sql dynamic variable names

[cisco999]

In PL/SQL, how are dynamic variable names built? For example, a cursor has fields week_1_date, week_2_date and so forth along with week_1_qty, etc. In a FOR LOOP, the date would be checked, "week_"+i+"_date", if date is within a certain timeframe then the corresponding qty would be zeroed out. Any suggestions how to handle this? Many thanks!

 

[taupirho]

Does the PL/SQL you're talking about reside in a FORMS application?


In order to understand recursion, you must first understand recursion.

 

[taupirho]

I'll short circuit things by saying that , if it is FORMS based PL/SQL you should have acess to a couple of built-in's called NAME_IN and COPY which you'll find useful. If not then you'll have to write a couple of little additional PL/SQL SET and GET functions for your dynamic variables. Have a look near the bottom of this URL for some exapmles

http://www.experts-exchange.com/Database/Oracle/Q_21032538.html

In order to understand recursion, you must first understand recursion.

 

[cisco999]

No, Forms is not involved here. I was unable to bring up the examples using the link provided.

 

[taupirho]

OK, here is what it said

Here is an example from Steve Feuerstein's book (can be found at O'Reilly Book site) which I think does what you are looking for:

CREATE OR REPLACE PACKAGE dynvar
AUTHID CURRENT_USER
IS
PROCEDURE assign (expr_in IN VARCHAR2, var_inout IN OUT VARCHAR2);
FUNCTION val (var_in IN VARCHAR2) RETURN VARCHAR2;
PROCEDURE copyto (val_in IN VARCHAR2, nm_in IN VARCHAR2);
END dynvar;
/
CREATE OR REPLACE PACKAGE BODY dynvar
IS
PROCEDURE showerr (prog IN VARCHAR2, sql_string IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE (
'DynVar Failure in ' || prog || ': ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE (
' on statement: "' || sql_string || '"');
END;

PROCEDURE assign (expr_in IN VARCHAR2, var_inout IN OUT VARCHAR2)

IS
plsql_string VARCHAR2(4000) :=
'BEGIN :var := ''' || expr_in || '''; END;';
BEGIN
EXECUTE IMMEDIATE plsql_string USING OUT var_inout;
EXCEPTION
WHEN OTHERS
THEN
showerr ('assign', plsql_string);
RAISE;
END;

FUNCTION val (var_in IN VARCHAR2) RETURN VARCHAR2
IS
plsql_string VARCHAR2(4000) :=
'BEGIN :val := ' || var_in || '; END;';
retval VARCHAR2(2000);
BEGIN
EXECUTE IMMEDIATE plsql_string USING OUT retval;
EXCEPTION
WHEN OTHERS
THEN
showerr ('val', plsql_string);
RAISE;
END;

PROCEDURE copyto (val_in IN VARCHAR2, nm_in IN VARCHAR2)
IS
plsql_string VARCHAR2(4000) :=
'BEGIN ' || nm_in || ' := ''' || val_in || '''; END;';
BEGIN
EXECUTE IMMEDIATE plsql_string;
EXCEPTION
WHEN OTHERS
THEN
showerr ('copyto', plsql_string);
RAISE;
END;

END dynvar;
/


/*======================================================================
| Supplement to the third edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2002 O'Reilly &
| Associates, Inc. To submit corrections or find more code samples visit
|

http://www.oreilly.com/catalog/oraclep3/

*/



In order to understand recursion, you must first understand recursion.