How do you test a function from the SQL*Plus command line? Well first you must define a variable. Then you can run the function, assigning its value to the variable as such:EXECUTE :my_var := my_proc(param);
Note the semicolon in front of the variable name. When writing the function, you must remember that you can only use SQL types. They cannot be PL/SQL specific types. You must also use positional notation for parameters. Finally you must be logged in as the owner, or be granted EXECUTE privilege on the function.
Here is a rule that is not specific to calling functions from SQL*Plus. The functions called from the SELECT statement are not allowed to do any DML. That includes anything called by the function, including triggers. This is just like another rule enforced by Oracle. You cannot query the table that is changing in an UPDATE/DELETE statement.
Now let's talk a little more about variables. A package body can have its own variables. These are private to the package. Variables exported in the specification are automatically initialized to NULL by Oracle.
I will end with dependencies. Suppose procedure A depends on procedure B. Also assume that procedure B depends on procedure C. Then we say that procedure A indirectly depends on procedure C. You can use "utldtree.sql" to set up the environment to trace dependencies.
There is still a lot more to learn before I become an Oracle Certified Professional. Come along as I bring you on my journey.
