ora procedure

Overview

This resource allows you to manage PL/SQL procedures in the Oracle database.

ora_procedure { 'testuser.proc_manage_my_objects':
  ensure  => 'present',
  source => '/vagrant/tests/procedure.sql',
}

This puppet definition ensures that the procedure testuser.proc_manage_my_objects is available in the database and that its content matches the content defined in the specified source.

To decide if the procedure needs an update, the puppet type compares the content in the database, with the content in the source file. This comparison is done insenitive to case, white spacing and used quote’s (the “ or the ` ).

When you have specified report_errors => true (which is the default), the type will fail on PL/SQL compilation errors. procedures with compilation errors do however end up in the database. On a second Puppet run the won’t be updated. Puppet reports a warning these resources though?

Warning: procedure TESTUSER.PROC_MANAGE_MY_OBJECTS@test up-to-date, but contains 4 error(s).

Experience the Power of Puppet for Oracle

If you want to play and experiment with Puppet and Oracle, please take a look at our playgrounds. At our playgrounds, we provide you with a pre-installed environment, where you experiment fast and easy.

For Oracle   here at our playground...

Attributes

content

A propert describing the content of the function.

Because puppet uses the contents of this property to decide if something needs to be done, the contents of this file must be very strict to the way Oracle stores the function source in the database.

To decide if a change must be done, Puppet will fetch the function definition from the database and the contents of the source. For both it will remove all:

• comments
• empty lines
• spaces
• newlines
• quotes

and do a comparison.

Back to overview of ora_procedure

cwd

The default directory from where the scripts will be run. If not specified, this will be /tmp.

  ora_exec {...:
    ...
    cwd => '/opt/my_scripts'
  }

This parameter is convenient when the script you run, expects a default directory. For example when running other scripts, without a specified directory:

@execute.sql

Back to overview of ora_procedure

disable_corrective_change

Disable the modification of a resource when Puppet decides it is a corrective change.

(requires easy_type V2.11.0 or higher)

When using a Puppet Server, Puppet knows about adaptive and corrective changes. A corrective change is when Puppet notices that the resource has changed, but the catalog has not changed. This can occur for example, when a user, by accident or willingly, changed something on the system that Puppet is managing. The normal Puppet process then repairs this and puts the resource back in the state as defined in the catalog. This process is precisely what you want most of the time, but not always. This can sometimes also occur when a hardware or network error occurs. Then Puppet cannot correctly determine the current state of the system and thinks the resource is changed, while in fact, it is not. Letting Puppet recreate remove or change the resource in these cases, is NOT wat you want.

Using the disable_corrective_change parameter, you can disable corrective changes on the current resource.

Here is an example of this:

crucial_resource {'be_carefull':
  ...
  disable_corrective_change => true,
  ...
}

When a corrective ensure does happen on the resource Puppet will not modify the resource and signal an error:

    Error: Corrective change present requested by catalog, but disabled by parameter disable_corrective_change
    Error: /Stage[main]/Main/Crucial_resource[be_carefull]/parameter: change from '10' to '20' failed: Corrective change present requested by catalog, but disabled by parameter disable_corrective_change. (corrective)

Back to overview of ora_procedure

disable_corrective_ensure

Disable the creation or removal of a resource when Puppet decides is a corrective change.

(requires easy_type V2.11.0 or higher)

When using a Puppet Server, Puppet knows about adaptive and corrective changes. A corrective change is when Puppet notices that the resource has changed, but the catalog has not changed. This can occur for example, when a user, by accident or willingly, changed something on the system that Puppet is managing. The normal Puppet process then repairs this and puts the resource back in the state as defined in the catalog. This process is precisely what you want most of the time, but not always. This can sometimes also occur when a hardware or network error occurs. Then Puppet cannot correctly determine the current state of the system and thinks the resource is changed, while in fact, it is not. Letting Puppet recreate remove or change the resource in these cases, is NOT wat you want.

Using the disable_corrective_ensure parameter, you can disable corrective ensure present or ensure absent actions on the current resource.

Here is an example of this:

crucial_resource {'be_carefull':
  ensure                    => 'present',
  ...
  disable_corrective_ensure => true,
  ...
}

When a corrective ensure does happen on the resource Puppet will not create or remove the resource and signal an error:

    Error: Corrective ensure present requested by catalog, but disabled by parameter disable_corrective_ensure.
    Error: /Stage[main]/Main/Crucial_resource[be_carefull]/ensure: change from 'absent' to 'present' failed: Corrective ensure present requested by catalog, but disabled by parameter disable_corrective_ensure. (corrective)

Back to overview of ora_procedure

editionable

Whether to use the Edition-based redefinition (EBR) functionality of Oracle12c and higher.

When you specify a true value, Puppet will allow you to use create scripts with the EDITIONABLE keyword in it. When comparing the actual value in the database with the value in the specified creation script, this keyword must be specified for the puppet type to recognise they are the same.

ora_package {...:
  ...
  editionable   => true,
  ...
}

You can use this on both ora_package, ora_procedure and ora_trigger.

When you specify a value of false, the EDITIONABLE keyword will be filtered before comparison. This is useful when you have scripts that need to work for a large range of Oracle versions.

The default value is false

Valid values are true, false.

Back to overview of ora_procedure

ensure

The basic property that the resource should be in.

Valid values are present, absent.

Back to overview of ora_procedure

logoutput

Whether to log command output in addition to logging the exit code. Defaults to on_failure, which only logs the output when the command has an exit code that does not match any value specified by the returns attribute. As with any resource type, the log level can be controlled with the loglevel metaparameter.

ora_exec {...:
  ...
  logoutput => true,
}

The default value is on_failure

Valid values are true, false, on_failure.

Back to overview of ora_procedure

name

The full procedure name including a SID.

ora_procedure { 'owner.my_procedure@sid':
  ...
}

The SID is optional. When you don’t specify an SID, Puppet will take the database instance which has the default attribute set to true in /etc/ora_setting.yaml file and use that as the SID. We recommend to always use a full qualified name (e.g. a name including the SID).

Back to overview of ora_procedure

owner

The owner of the procedure. This is the first part of the title string. The first part before the ..

  ora_procedure { 'OWNER.PROCEDURE_NAME@SID':
    ...
  }

Back to overview of ora_procedure

procedure_name

The name of the PL/SQL procedure you want to manage. The procedure name is the second part of the title.

ora_procedure { 'owner.procedure_name@sid':
  ...
}

Back to overview of ora_procedure

provider

The specific backend to use for this ora_procedure resource. You will seldom need to specify this — Puppet will usually discover the appropriate provider for your platform.Available providers are:

simple

This is the generic provider for a easy_type type

Back to overview of ora_procedure

report_errors

Report any errors in the SQL scripts.

When you set this value to true, the type will report any errors that occur in the SQL statements or scripts and stop processing. When you set it to false, it will ignore any SQL errors and just continue processing. The default value is true, so it will stop processing when an SQL error is generated.

Here is an example:

ora_exec{'delete from user_config':
  ...
  report_errors => true,

}

Valid values are true, false.

Back to overview of ora_procedure

sid

SID to connect to.

All types have a name like resource@sid. The sid is optional. If you don’t specify the sid, the type will use the database from the /etc/ora_setting.yaml with the property default set to true. We advise you to either use @sid in all your manifests or leave it empty everywhere.

Back to overview of ora_procedure

source

A file describing the content of the procedure.

Because puppet uses the contents of this file to decide if something needs to be done, the contents of this file must be very strict to the way Oracle stores the procedure source in the database.

To decide if a change must be done, Puppet will fetch the procedure definition from the database and the contents of the source. For both it will remove all:

• comments
• empty lines
• spaces
• newlines
• quotes

and do a comparison.

Back to overview of ora_procedure

timeout

Timeout for applying a resource in seconds.

To be sure no Puppet operation, hangs a Puppet run, all operations have a timeout. When this timeout expires, Puppet will abort the current operation and signal an error in the Puppet run.

With this parameter, you can specify the length of the timeout. The value is specified in seconds. In this example, the timeout is set to 600 seconds.

ora_type{ ...:
  ...
  timeout => 600,
}

The default value for timeout is 300 seconds.

Back to overview of ora_procedure