2 Replies Latest reply on Oct 28, 2013 9:29 PM by b2c91498-bada-45a2-a967-df1255034f37

    How to move functions and procedures from packages into a schema?

    b2c91498-bada-45a2-a967-df1255034f37

      Hello,

       

      I have below requirements for a homework and my question is if someone can point me in the right direction to find documentation which can help me solve the below. Any information will be very much appreciated. Thank you.

       

      Write procedures and functions (included or not in packages)under the form scripts  .txt or .sql . Once they have been launched in SQL developer they should more the functions and the procedures from the packages in the current schema. If the current schema contains only 2 packages, pac1( with procedures p11,p12 and the functions f11,f12,f13) and pac2( contains the following procedures p21,p22,p23 and the functions f21 and f21( overloading cases), the execution of the scripts will generate the following effects:

       

      • The procedures p11,p12,p21,p22 ,p23 and the functions f11,f12 and f13 will be created in the current schema.
      • pac2 will contain 2 instances of the overloading function- f21; they will be kept in the package, without being created in the current schema; so all the procedures/functions overloaded will be kept in the original packages
      • If pac1 contains variables, cursors and public types, we will keep only the package specifics( and delete the body);generally if the packages do not contain procedures or functions overloaded the body will be deleted and if no variables, cursors, public types then we will delete the header.
      • If in the triggers, procedures, functions  we will call procedures/functions from the packages(the procedures and the functions moved in the current schema) they reference will need to be updated ( via amending the body or recompile) for example if pa1.p12 will need to be replaced with p12.
      • The scripts will have to have numbers in the following series( 01....n) and characters that explain the content.