| Oracle® Life Sciences Data Hub Application Developer's Guide Release 2.4 E54089-01 |
|
|
PDF · Mobi · ePub |
| Oracle® Life Sciences Data Hub Application Developer's Guide Release 2.4 E54089-01 |
|
|
PDF · Mobi · ePub |
This section contains information on the following topics:
Figure 5-1 Process of Creating a Program Definition and Instance
To create an Oracle Life Sciences Data Hub (Oracle LSH) Program you write or upload source code in SAS, PL/SQL, or Oracle Reports as you normally would. In addition, you must create a defined object in Oracle LSH called a Program, and create defined objects for each of the following included in your source code:
You must define a Table Descriptor object for each source and target table or data set that your program reads from and writes to. You must then map each Table Descriptor to the actual Table instance the program reads from or writes to.
You must define a Source Code object for the primary source code you write and for any separate subroutines, macros, or formats you use.
You must define a Parameter object for each input, output, or input/output parameter you declare in your source code.
You must define a Planned Output object for each output the Program will produce, including reports, error files, and log files (except SAS log files, which the system creates automatically).
As with other Oracle LSH executables, you must define at least one Execution Setup to enable users to run the Program. See "Creating, Modifying, and Submitting Execution Setups" for further information.
Because these Program components are defined objects, Oracle LSH can keep them and the Program as a whole under version control, and you can validate the Program.
A Program of any technology type (SAS, PL/SQL, or Oracle Reports) can operate on any Oracle LSH data, regardless of the type of external system where the data originated because all Oracle LSH Tables are compatible with both Oracle tables and SAS data sets. Only one Program instance can write to any particular Table instance.
When you run a Program, the system launches the appropriate engine to execute the code, compiles source code files as necessary in that environment, and launches the primary source code file, running in batch mode. You must create an instance of a Program definition and install it and the Table instances to which it is mapped before you can run the Program; see "Installing Program Instances".
Program Usage You can use a Program in several basic ways:
Standalone Object. You can use a Program to do one or both of the following:
generate one or more reports on data
manipulate data and write the transformed data to tables
Component of a Report Set. Report Sets must contain Programs in order to generate the reports contained in the Report Set (see Chapter 9, "Defining Report Sets").
Component of a Workflow. Any data transformation or generation of reports done within a Workflow can only be accomplished by Programs (see Chapter 10, "Defining Workflows").)
Container. Source Code objects must be stored in Programs. You may want to create Programs specifically to store reusable source code files for reference as subroutines in other Programs
Data Viewer. After the Program and its source Table instances are installed, you can view data in the database tables in the Integrated Development Environment (IDE). After the Program runs, you can also view data in the target Table instances.
Note:
For information about writing Programs that touch blinded data, see "Setting and Modifying Table Attributes" and "Managing Blinded Data".Reports on Program Definitions and Instances From the Actions drop-down list, you can generate reports that provide information on a Program definition or instance; see Chapter 14, "System Reports" for information.
When you create a Program in a Work Area, you are actually creating an instance of a Program definition.
To create a new Program instance:
In a Work Area, select Program from the Add drop-down list.
Click Go.
The system displays the Create Program screen.
Choose one of the following options:
Create a new Program definition and instance. Choose this option if no Program definition exists that can meet your needs, either as it is or with some modification.
Create an instance from an existing Program definition. Choose this option if a Program definition already exists that meets your needs.See "Finding an Appropriate Definition" and "Reusing Existing Definitions" for further information.
Depending on your choice, follow one of these sets of instructions:
When you select Create a new Program definition and instance in the Create Program screen, additional fields appear.
Enter values in the following fields:
Name. See "Naming Objects".
Description. See "Creating and Using Object Descriptions".
Program Type. The options are: PL/SQL, Oracle Reports, SAS Program, SAS Format Catalog, SAS Macro Catalog, BI Publisher, and View. Your company may support other Program Types and they may appear in this list. Follow your company's instructions for such Program Types.
In the Classification section, select the following for both the definition and the instance:
Subtype. Select a subtype according to your company's policies.
Classification Values. See "Classifying Objects and Outputs" for instructions.
Click Apply to save your work and continue defining the Program.
The system opens the Properties screen for the new Program instance.
Force Output Validation Status to 'Development' If selected, outputs of instances of this Program definition are always created with a validation status of Development. If deselected, the outputs inherit the validation status of the Execution Setup that produced them, which in turn can inherit its validation status from the Program instance. Your company can determine the default setting using an Oracle profile; see "Setting Profile Values" in the Oracle Life Sciences Data Hub System Administrator's Guide. To change this value, do the following:
Click the hyperlink to the Program definition in the Program in the Instance Properties section of the screen. The Program definition's Properties screen opens.
Click Update. Fields become enterable.
Select or deselect Force Output Validation Status to 'Development'.
Click Apply. The system saves the change.
To return to the Program instance and continue defining the Program instance and definition at the same time, click the breadcrumb link to the Program instance just above the screen title.
Notes:
This flag is a property of the Program definition, not the Program instance. Its value applies to all instances of this Program definition version.The setting of this attribute is version-specific; if you change it in one version, any other existing versions retain their existing value. Subsequently created versions of this Program definition get their default setting from the previous version.
Your company can set the default value for this attribute in a lookup. For further information see "Adding and Modifying Lookup Values" in the Oracle Life Sciences Data Hub System Administrator's Guide. Newly created Programs get their default setting for this attribute from the lookup.
Define the Program details. For information and instructions see:
Click Check In. The system checks in Version 1 of both the Program definition and instance.
Install the Program instance (see Chapter 12, "Using, Installing, and Cloning Work Areas"). You can use the Install button on the Program instance Properties screen or install the Program instance as part of a Work Area installation. The Install button always performs an installation of type upgrade, installing the Program instance only if the current version has not been installed previously.
Validate both the definition and the instance according to your company's policies.
For information on creating the different types of Programs, see:
If you use an existing Program as a definition source, its Source Code, Table Descriptors, Parameters and other properties are already defined. See "Creating an Instance of an Existing Definition" for instructions.
After you have created the Program instance, you must map the Table Descriptors to Table instances; see "Mapping Table Descriptors to Table Instances". You must also create at least one Execution Setup for the Program instance; see "Creating, Modifying, and Submitting Execution Setups".
This section contains the following topics:
Subtabs:
See also Figure 5-1, "Process of Creating a Program Definition and Instance".
See "Modifying Programs" for information on modifying Programs.
If you are working in a Work Area, you see the properties of both the Program instance and the Program definition it references. If you are working directly on the definition in an Application Area or Domain, you see only the properties of the definition.
You can see the following instance properties:
Name You can click Update and modify the name. See "Naming Objects" for further information.
Description You can click Update and modify the description. See "Creating and Using Object Descriptions" for further information.
Definition This field specifies the Program definition to which this Program instance points. For further information, see "Definition Source".
To upgrade to a new version of the same definition, use the Upgrade to Latest button. See "Upgrading to a Different Definition Version from an Instance".
Blind Break This field indicates whether you can see real or dummy data in blinded Table instances when you work on this Program from an IDE. Click Launch Settings to make this selection. The choices depend on your privileges. If none of the Table instances mapped to the Program instance contains either blinded or unblinded data, the only possible setting is Not Applicable. See "IDE Launch Settings" for further information.
Shared Snapshot Label The default value you see here comes from the default Execution Setup for this Program, if there is one. Otherwise the default value is determined by your privileges. If the source Table instances have shared snapshot labels, you can click Launch Settings and select one of them. See "IDE Launch Settings" for further information.
Validation Status This field displays the current validation status of the Program instance. If you have the necessary privileges, you can change the validation status by selecting Validation Supporting Information from the Actions drop-down list. See "Validating Objects and Outputs" for further information.
Status This field displays the installable status of the Program: Installable or Non Installable. Programs have an additional status called Installable IDE. You can install a Program without a Source Code, if the Program has the Installable IDE status. You can work on such a Program's Source Code in an IDE. See Appendix A, "Installation Requirements for Each Object Type".
Version This field displays the current version number of the Program instance.
Version Label This field displays the version label, if any, for the current Program instance version.
For further information on object versions, see "Understanding Object Versions and Checkin/Checkout".
Checked Out Status This field displays the status of the definition: either Checked Out or Checked In. You must check out the definition to modify Table Descriptors, Source Code, Parameters, or Planned Outputs. However, you can change Table Descriptor mappings without checking out the definition. See "Understanding Object Versions and Checkin/Checkout" for further information.
Latest Version If set to Yes, this Program instance is pointing to the latest version of the Program definition. If set to No, this Program instance is pointing to an older version of the Program definition.
View Latest You can see this button only if the current Program instance does not point to the latest definition version. Click this button to view the latest Program definition.
Upgrade to Latest This button is grayed out if the current Program instance already points to the latest Program definition. Click this button to upgrade the current Program instance to the latest definition version. For more information on upgrading instances, see "Upgrading Object Instances to a New Definition Version".
Checked Out By This field displays the username of the person who has the Program definition checked out. See "Understanding Object Versions and Checkin/Checkout" for further information.
Version Label This field displays the version label, if any, for this definition version.
Program Type This field displays this Program definition's type: Oracle Reports, PLSQL, SAS Program, SAS Format Catalog, or SAS Macro Catalog. See "Defining PL/SQL Programs", "Defining SAS Programs", and "Defining Oracle Reports Programs".
Development Tool This field displays the tool required to work on the source code of the Program: Oracle Reports, PL/SQL, or SAS.
Validation Status This field displays the current validation status of the Program definition. If you are working directly in the definition in an Application Area or Domain and you have the necessary privileges, you can change the validation status by selecting Validation Supporting Information from the Actions drop-down list. If you are working in an instance of the Program in a Work Area, and you want to change the validation status of the definition, you must go to the definition. See "Validating Objects and Outputs" for further information.
Status This field displays the installable status of the Program: Installable or Non Installable. See Appendix A, "Installation Requirements for Each Object Type".
From a Program instance in a Work Area, you can use the following buttons:
Install Click Install to install the Program instance, including mapping target Table Descriptors and installing mapped target Table instances; see "Installing Program Instances". For a list of reasons a Program instance may not be installable, see Appendix A, "Installation Requirements for Each Object Type".
Launch IDE Click Launch IDE to launch the integrated development environment (IDE) in which you write your program source code.
Submit Click Submit to run the Program instance. Before you can run the Program, you must install it and create an Execution Setup for it (select Execution Setups from the Actions drop-down list).
Update Click Update to modify the Program instance properties. See "Modifying Program Instance Properties".
Launch Settings Click Launch Settings to set the blinding status and currency of the data you want to view while developing your Program; see "IDE Launch Settings".
Check In/Out and Uncheck Click these buttons to check out, check in, or uncheck the Program definition. Different buttons are displayed in the Program Definition Properties section depending on the Checked Out Status and whether or not you are the person who has the definition checked out. If someone else has checked out the definition, you cannot check it in or uncheck it. The username of the person who has checked it out is displayed. See "Understanding Object Versions and Checkin/Checkout".
View Latest/Upgrade to Latest If the definition is not the latest version, you can click to view the latest version and upgrade to the latest version if you want to. See "Upgrading to a Different Definition Version from an Instance".
To enable different instances of a single Program definition to run against different source or target Tablesâeven Tables with different names or structureâOracle LSH requires Table Descriptors as part of the Program definition. You must include one Table Descriptor for each Table instance the Program will read from or write to. Like a Table instance, a Table Descriptor contains a pointer to a Table definition. The difference is that a Table Descriptor exists only inside a Program or other executable object definition, while a Table instance is installed independently in the database. See Defining and Mapping Table Descriptors for further information.
You must map each Table Descriptor to the corresponding Table instance that the Program instance will read from or write to. The system can do the mapping automatically if you choose to create Table Descriptors from existing Table instances or if the Table Descriptor has the same name as the Table instance. However, if the Table instance is different enough from the Table Descriptor, you must map them manually. See "Mapping Table Descriptors to Table Instances").
The Program definition's source code refers to the Table Descriptor by name and Column name as if it were an actual table or SAS data set containing data. The system uses the mappings to translate the names used in the source code to those of the Table instance to which the Table Descriptor is mapped.
Note:
Only one Program can write to any particular Table instance. The system prevents you from mapping a Table instance to more than one target Table Descriptor.Target Table instances must have a processing type. Be sure that the processing type of each target Table instance is compatible with your source code; see "Data Processing Types" for further information.
There are several ways to create a Table Descriptor:
Creating Table Descriptors from Table Instances and Simultaneously Mapping Them
Adding a Target Table Descriptor from an Existing Table Definition
See also "Mapping Table Descriptors to Table Instances".
For information on how you can make data available to Program instances in an Integrated Development Environment (IDE), see "IDE Launch Settings".
This section contains information about Source Code in Oracle LSH, including:
See also:
A Source Code definition encapsulates the file containing the actual source code, so that the source code is stored under version control, in compliance with industry regulations (see "Versions of Component Objects").
Every Oracle LSH Program must contain one primary source code object and may contain any number of additional (secondary) source code objects serving as subroutines.
Note:
The Source Code for Oracle LSH Programs of the BI Publisher adapter type is automatically generated by the system. Do not edit or upload the Source Code manually or the BI Publisher Program may not work properly.Primary Source Code Each Program contains one primary Source Code definition, listed first and given an Order number of one (1). When you execute the Program, the system launches the Source Code definition you have defined as Primary. Normally you write the primary source code especially for a particular Program. The primary Source Code definition contains the file the system executes when the Program is submitted. The primary source code references Table Descriptors, Parameters, Planned Outputs, and other Source Code instances defined in the Program by their Oracle or SAS name, as appropriate for the technology type (see "Writing Primary Source Code in PL/SQL" and"SAS Program and Source Code Types").
Secondary Source Code Secondary Source Code objects are those with any order number other than one (1). They are SAS macros or formats or PL/SQL packages that are, in most cases, called by the primary source code.
Source Code definitions have a Sharable attribute that, if set to Yes, makes them available for use as a definition source for Source Code instances in other Programs. You can create Program definitions especially for the purpose of storing sharable Source Code definitions. If you are working in SAS, you must create a Program definition of type Macro Catalog to store macros, or a Program definition of type Format Catalog to store formats. If you are working in Oracle technologies (Oracle Reports or PL/SQL) you can create a Program definition of type PL/SQL to hold sharable PL/SQL packages.
You can also create secondary Source Code especially for a particular program. In this case Oracle LSH stores both the Source Code instance and its definition in the Program in which you create them.
See "Upgrading Source Code And Undoing Source Code Upgrades" for information on how to upgrade Source Code instances pointing to a sharable Source Code definition.
For further information, see:
Execution When you submit an Execution Setup to run a Program, the system does the following in sequence:
Initializes the batch environment for the appropriate technology
If necessary, compiles all the Source Code files in the order in which they are listed in the Source Code subtab (all SAS macros and formats included in the Program are compiled)
Launches the primary Source Code file using the Parameter values specified in the Execution Setup
Note:
You cannot set a Static-reference Source Code as primary. Also, you cannot set a Source Code that points directly to a Program definition as primary.Launches any secondary Source Code files when they are called from the primary file
Writes data to Table instances, if so directed by the source code
If directed by the source code, generates reports and classifies them as specified in Planned Output definitions
When you create Source Code in a Program, you can either create an instance of an existing Source Code definition, or a new Source Code definition and instance at the same time:
In the Source Code subtab of a Program, click Add. The system displays the Create Source Code screen.
Choose one of the following options:
Create a new Source Code definition and instance. Select this option in any of the following situations:
You are creating the primary Source Code for a Program
The source code you want to use exists only outside Oracle LSH (you can upload the actual source code file, but you must create a new Source Code definition to store the uploaded file)
You are creating a subroutine that does not yet exist in Oracle LSH (see "Creating a SAS Macro Catalog", "Creating a SAS Format Catalog", and "Creating a PL/SQL Package Storage Program").
Create an instance of an existing Source Code definition. Select this option in any of the following situations:
You are working in a PL/SQL Program and you want to reference a package in another Program.
You are working in a SAS Program and you want to reference a macro or format in another Program.
Your company may have Programs created especially to store sharable Source Code files in a designated Library or Application Area.
For more information on using SAS Format Catalogs and SAS Macro Catalogs, see "Using a SAS Macro Catalog" and "Using a SAS Format Catalog".
Depending on your choice, follow one of the following sets of instructions:
You can also create a source code instance that points to a Program definition. This is so that you can use the same SAS Macro Catalog and SAS Format Catalog in other Programs. See "SAS Program and Source Code Types".
When you select Create a new Source Code definition and instance in the Create Source Code screen, additional fields appear.
This section contains instructions for all technology types. For instructions specific to each technology, see the following sections:
Defining Oracle Business Intelligence Publisher Programs
Note:
Oracle LSH creates the Source Code automatically for Oracle LSH BI Publisher Programs. Do not create or edit this Source Code. See "About Oracle BI Publisher Program Source Code" for more information.Enter values in the following fields:
Name. The system uses the name you enter for the actual source code file. If you do not specify an extension, Oracle LSH appends the default extension for that technology type: .sas for SAS source code, .rdf for Oracle Reports, or .sql for PL/SQL. Do not use reserved words or special characters. See "Naming Objects" for further information.
Description. See "Creating and Using Object Descriptions".
File Type. Select the file type from the list. The choices vary depending on the Program Type. For an explanation, see "Defining SAS Programs" or "Defining Oracle Reports Programs". PL/SQL Programs' Source Code must have a SQL file type.
Sharable. Select Yes to make the Source Code definition available for reuse. See "Creating a PL/SQL Package Storage Program" and "Creating a SAS Macro Catalog" for information about sharable Source Code definitions.
Depending on your company's validation policies you may choose not to set the Sharable flag to Yes until you have fully tested and validated the Source Code. You can change the setting at any time in the Source Code subtab by selecting the Source Code and clicking either Set Sharable or Set Not Sharable.
Note:
You cannot set any SAS Macro Catalog or SAS Format Catalog Source Code instance that points directly to a Program definition as Sharable.Enter additional fields specific to each Program type:
SAS File Reference Name. If the Program is of type SAS, you may need to enter a SAS File Reference Name:
The first Source Code you create of type Program is automatically created as the Program's primary Source Code. The SAS File Reference Name value defaults to MAINPRG and you cannot change it. If you later set another Source Code as primary, the system automatically changes its SAS File Reference Name to MAINPRG and changes the original primary Source Code's SAS File Reference Name to the Source Code instance name, truncated to 8 characters.
If the Source Code is a secondary Source Code that is not shared from another Source Code, the value defaults to the Source Code's name, truncated to 8 characters. You can change this value.
If the Source Code is shared from another Program, the SAS File Reference Name defaults from the shared Source Code and you cannot change it.
Note:
In cases where the SAS File Reference Name value defaults, if there are two names with the same value, the system truncates the second one by one character and appends
1 (or increments the number if there are three or more).Oracle Package Name. If the Program is of type PL/SQL, enter a package name. The package name must be unique within the Program. The package name must match the package name in the actual PL/SQL source code.
Oracle Procedure Name. If the Program is of type PL/SQL, enter a procedure name. The procedure name must match the procedure name in the actual PL/SQL source code.
Note:
If the Program is of type PL/SQL and you are defining the primary Source Code, you must enter both a package name and a procedure name.Write or upload the actual source code. Do one of the following:
For text-based source code files (PL/SQL and SAS), write code directly in the Source Code Editor box.
If a source code file that fits your needs already exists on your PC or network, click the Upload button to look for and select the file.
Note:
For Informatica Programs, you must only upload existing XML files that contain Informatica mappings and workflows.Click Launch IDE to open the development environment. Write source code there to use in the Program, then upload the source code file to the Source Code definition in Oracle LSH.
Note:
Before you can use the Launch IDE button you must install the Program. To make the Program installable you must either create and map at least one Table Descriptor or add one Source Code object (Steps 1 and 2 above). For more information see "Installing Program Instances".Note:
If you are using SAS in a connected mode and the SAS development environment does not open when you click Launch IDE, you may need to define services for the environment and start Oracle LSH Distributed Processes Server code in the same location. See "Stopping and Starting Services and Queues" in the Oracle Life Sciences Data Hub System Administrator's Guide.If you are using SAS in Disconnected mode, SAS does not open when you click Launch IDE. Instead, Oracle LSH puts the required data set files on your personal computer and displays a message giving the location. You must then open SAS locally and access the files as necessary.
See "Connecting to SAS" for further information.
Click Save and Continue to save your work. The system saves your work in the database and returns to the Program instance screen.
By default the system sets the first Source Code you create for a Program to Primary. If you want to specify that a different Source Code is the primary oneâthe one executed firstâin the Source Code subtab check the Select check box of the Source Code you want to set as Primary and click Set As Primary. The system changes the Primary setting for that Source Code to Yes, and for the previous primary Source Code to No.
Validate both the definition and the instance according to your company's policies.
If you use an existing Source Code as a definition source, its contents are already defined. However, you must specify whether or not to use a static reference; see "Creating and Using Static Reference Source Code" for information.
See "Creating an Instance of an Existing Definition" for general instructions.
Note:
If the Program containing the Source Code or the Source Code itself is ever deleted, the Programs that contain instances of the deleted Source Code will no longer compile.Oracle publishes public PL/SQL APIs that allow you to do many things programmatically that you can do through the user interface, including creating, modifying, and installing objects. You can call these APIs within the context of defined Oracle LSH Programs without any extra security. Refer to the Oracle Life Sciences Data Hub Application Programming Interface Guide for details.
For an example of calling a PL/SQL API from a SAS Program, see "Calling an API to Capture Output Parameter Values".
This section contains the following topics:
You can write source code (PL/SQL package or SAS format) that is intended for reuse in other Programs. If that Source Code is located in an installed Program instanceâusually because it reads data such as lookup values from an Oracle LSH Table instanceâyou must specify a static reference when you create an instance of the Source Code in another Program.
For example, if you want to supply an Investigator ID and return an Investigator Name, you can do either of the following:
Create a Table Descriptor in your Program and map it to a Table instance that includes columns for both Investigator ID and Name and reference that Table Descriptor in the Source Code you create for your Program. This method does not involve a static reference Source Code.
Reference a sharable Source Code in another Program instance whose purpose is to take an Investigator ID and return the corresponding Investigator Name. The Program instance that contains this sharable Source Code must have a Table Descriptor that is mapped to a Table instance that includes columns for both Investigator ID and Name. This is a static reference Source Code.
Note:
Oracle LSH does not support creating a static reference to Source Code contained in a Program instance inside a Report Set.If you are creating a PL/SQL Program or SAS Format Catalog to store static reference Source Code definitions, you must create an instance of the Program and map one of its source Table Descriptors to each Table instance required to be read, and install both the Program instance and the Table instance. Do not define snapshot labels for these Table instances and do not set their Blinding Flag to Yes.
In the Program in which you want to use the Source Code:
Add a Source Code object by creating an instance of an existing Source Code definition. The Static Reference attribute appears.
Select Yes for Static Reference.
Click the Search icon for the Definition Source field.
The system then searches for installed Program instances rather than Program definitions, because the Program containing the static reference Source Code must be mapped to the necessary installed Table instance. Also, because only one version of a Program instance can be installed at any given time, your Program always references the current installed version of the static reference Source Code's Program instance.
Enter the Domain, Application Area, and Work Area where the Program instance is located and select the Program instance.
Apply.
This section contains the following topics:
If the Source Code definition your Source Code instance is pointing to is not the most current version, Oracle LSH sets the Latest Version field to No and allows you to view the latest version and upgrade to it if you want to; see "Upgrading a Single Source Code Instance".
Note:
You cannot upgrade a Source Code instance whose definition is statically referenced. See "Creating and Using Static Reference Source Code".You can also undo an upgrade or choose a noncurrent version of a Source Code definition at any time; see "Undoing Source Code Instance Upgrades".
Working in the Source Code definition, you can upgrade all instances to the latest version, using the Upgrade All Instances button or the Actions drop-down list.
If you created a Source Code instance along with a definition, then your Source Code definition and instance are synchronized unless at some point you or someone else manually pointed the Source Code instance to another version of the definition; see "Undoing Source Code Instance Upgrades".
If you created a Source Code instance that points to an existing Source Code definition, any changes that have been made to the Source Code definition after you created the Source Code instance are not reflected in the instance and Latest Version is set to No.
For Source Code instances that point to an existing Source Code definition, you can see a hyperlink to the Source Code definition that the Source Code instance points to, in the instance properties section of the Source Code screen.
Note:
A Source Code instance and the Source Code definition it points to, both always share the same screen. Therefore, in this section Source Code screen refers to the screen that shows Source Code instance properties in the upper portion and Source Code definition properties in the lower portion of the screen.You can upgrade a single Source Code instance in one of the following ways:
Upgrade to Latest button. This method allows upgrade to the latest version of the Source Code definition only.
Upgrade Instance from the Actions drop-down list. This method allows changing to any version of the Source Code definition.
Upgrade to Latest Use the Upgrade to Latest button to upgrade the Source Code instance to the latest version of its definition.
This button is not available if:
The Source Code definition to which you want to upgrade is not checked in.
The Program definition containing the Source Code instance you want to upgrade is not checked out, or is checked out by someone else.
The Source Code instance is already pointing to the latest version of its Source Code definition (Latest Version is set to Yes).
The Source Code Definition is a static reference Source Code; see "Creating and Using Static Reference Source Code".
You do not have Modify privileges on the Source Code instance
To upgrade a Source Code instance to the latest version of its definition using the Upgrade to Latest button, do the following:
Navigate to the Program instance or definition that contains the Source Code instance you want to upgrade.
In the Source Code subtab, click the Source Code's hyperlink in the Name column. The Source Code screen opens.
If you want to look at the latest version of the Source Code definition before upgrading, click View Latest in the Definition Properties section of the screen.
To upgrade, click Upgrade to Latest. You receive a confirmation message.
In the confirmation message, click Yes. The system upgrades the Source Code instance to the latest version of its source definition.
Upgrade Instance Use the Upgrade Instance item from the Actions drop-down list on the Source Code screen, to upgrade the Source Code instance to any version of its definition.
This option is not available (the Go button is grayed out) if:
The Source Code definition to which you want to upgrade is not checked in.
The Program definition containing the Source Code instance you want to upgrade is not checked out, or is checked out by someone else.
The Source Code Definition is a static reference Source Code; see "Creating and Using Static Reference Source Code".
You do not have Modify privileges on the Source Code instance.
To point a Source Code instance to any version of its definition, do the following.
Navigate to the Program instance or definition that contains the Source Code instance you want to upgrade.
In the Source Code subtab, click the Source Code's hyperlink in the Name column. The Source Code screen opens.
If you want to look at the latest version of the Source Code definition before upgrading, click View Latest in the Definition Properties section of the screen.
To change the underlying definition to a different version, select Upgrade Instance from the Actions drop-down list.
Click Go. The system displays the available versions of the Source Code definition in the lower portion of the screen.
For each version of the definition, the system displays the following information:
Source Code Version. The version number of the Source Code definition version.
Upgrade. The Upgrade icon is grayed out if the current Source Code instance already points to that Source code definition version, or if that Source Code definition version is currently checked out (someone is currently modifying it).
Note:
If someone is currently creating a new version, the new version is not displayed at all.Program Name. The name of the Program definition that owns the Source Code definition. This remains the same for one Source Code definition.
Program Version. The version number of the Program that owns that Source Code definition version.
Program Validation Status. The validation status of the Program that owns that Source Code definition version.
Program Version Label. The label associated with the Program definition version, if any.
Program Checked Out By. If the Program definition version is checked out, the system displays the username of the person who checked it out. You cannot upgrade to a version that is checked out, and only the person who checked it out can check it in.
Note:
People with Checkin Administrator privileges can check in objects checked out by other users.Click the icon in the Upgrade column for the version to which you want to point the instance.
The system upgrades the Source Code instance and grays out its row, as it now points to the version you selected.
A Source Code instance may point to a Source Code definition or, in the case of SAS Source Code instances, to a SAS Macro or Format Catalog Program definition. You can upgrade Source Code instances from a Source Code screen or in the case of SAS Source Code instances, from a Program instance's Properties screen.
As with other object types, you can go to a version of a definition and upgrade all the instances that point to it.
If the Source Code definition is a SAS Macro or Format Catalog, follow the instructions for upgrading Program and other object definitions at "Upgrading One or More Instances from the Definition".
From the Source Code definition screen. To upgrade one or more Source Code instances from a Source Code definition, do the following:
Navigate to the Program that contains the Source Code definition whose instances you want to upgrade.
Check in the Program definition if it is not already checked in.
In the Source Code subtab, click the Source Code's hyperlink in the Name column. The Source Code screen opens.
Click the Upgrade All Instances button in the Source Code definition properties section of the screen.
The system opens the Upgrade Instances screen with all instances of the Source Code definition displayed.
Note:
If the check box next to an instance is grayed out, then either the Program instance that owns it is checked out by someone else, or the Source Code instance already points to the latest Source Code definition.For each instance, the system displays the following information:
Program Name. The Program name that contains the Source Code instance.
Program Version. The version number of the Program that contains the Source Code instance.
Program Version Label. The version label of the Program that contains the Source Code instance.
Program Validation Status. The validation status of the Program that contains the Source Code instance.
Program Checked Out By. The name of the person who has checked out the Program definition, parent to the Source Code instance. If a person other than you has the Program checked out, then you cannot upgrade the Source Code instance: the check box next to it is grayed out.
Source Code Definition Version. The version number of the Source Code definition to which that instance currently points.
Source Code Name. The name of the Source Code instance to which that instance currently points.
SAS File Reference Name. The SAS file reference name, if any.
Container. The Domain > Application Area hierarchy for the instance.
Select one or more instances to upgrade. You can use the Select All and Select None functions and/or select or deselect instances individually by checking or unchecking their Select checkbox. Instances that already point to the current version of the definition cannot be selected.
Click Upgrade. The system changes the source definition of the selected instances to the version of the definition where you are working.
Note:
If the Upgrade button is not enabled, then the Source Code definition you want to upgrade to, is not checked in. To check in that Source Code definition, you must check in the Program definition that owns it.Note:
The newly upgraded version of the definition is not necessarily the latest version. It is the version you are currently working on. To go to the latest Source Code definition version, click the View Latest button from the Source Code definition properties section of the Source Code screen.From the Program Definition screen. To upgrade multiple Source Code instances that refer to a Program definition, do the following:
Navigate to the Program definition in the Applications tab.
Check in the definition if it is not already checked in.
From the Actions drop-down list, select Upgrade All Instances and click Go. The system opens the Upgrade Instances screen displaying all Program instances and Source Code instances that point to the Program definition.
From the View drop-down list, select Program Definition (Source Code Instances). The system refreshes the screen and lists only the Source Code instances that point to this Program definition.
For each instance, the system displays the following information:
Note:
In the following, Object refers to the Program definition that owns the Source Code instance. If you are viewing this information with the selection Program Instance or Both in the View drop-down list (see point 4 above), then Object refers to a Program instance, where it is a Program instance that is referring to this Program definition.Object Name. The name of the Program definition that contains the Source Code instance.
Object Type. The type of the object. See Note above.
Object Version. The version number of the Program definition that contains the Source Code instance.
Version Label. The version label of the Program definition that contains the Source Code instance.
Installed Version. This field is not applicable for Source Code instance upgrades because a Source Code instance is referred to by its owning Program definition and Oracle LSH object definitions cannot be installed. This field refers to the most recent version of the Program instance that was successfully installed. It is relevant only for Program instances pointing to this Program definition.
Validation Status. The Program definition's validation status.
Checked Out By. The name of the person who has checked out the Program definition, parent to the Source Code instance. If a person other than you has the Program checked out, then you cannot upgrade the Source Code instance that points to it: the check box next to it is grayed out.
Definition Version. The version number of the definition to which the instance currently points.
Note:
In the following, Parent refers to the Object's parent. So for Source Code instances, it is the parent of the Program definition that owns the Source Code. That is, parent of a Program definition - such as an Application Area.Definition Validation Status. This field does not apply to Source Code instances. It is the validation status of the Program instance that points to a version of the current Program definition.
Parent Name. The name of the Parent object.
Parent Object Type. The type of object that contains the Program definition that owns the Source Code instance.
Parent Status. The parent's installation status.
Parent Validation Status. The parent's validation status.
Source Code Name. The name of the Source Code instance.
SAS File Reference Name. The SAS file reference name, if any.
Container. The Domain > Application Area hierarchy for the Program definition that owns the Source Code instance.
Select one or more instances you want to upgrade. You can use the Select All and Select None functions and/or select or deselect instances individually by checking or unchecking their Select checkbox. Instances that already point to the current version of the definition cannot be selected.
Click Upgrade. The system changes the source definition of the selected instances to the new version of the definition.
Note:
When upgrading a Source Code instance, you are actually upgrading the Program definition that owns the Source Code instance. However, for the sake of readability, the document describes operations on the Source Code instance. Please remember that you cannot check in or check out a Source Code instance, you can perform these operations on only the Program definition that owns it.Note the following:
The Upgrade button is grayed out if the Program definition you want to upgrade to, is not checked in. Click Return to go back to the previous screen and check in the definition first.
If a version of the Source Code Instance already points to the Program definition you want to upgrade it to, then its row is grayed out.
If the latest version of the Source Code instance is checked out, then all older versions are grayed out.
If you want to upgrade an older version of the Source Code instance, then you should check in the latest Program definition that owns the Source Code instance and return to this screen. Older version are now available for upgrading. When you select an older version and click Upgrade, the system checks out the Source Code instance, creates a new version and then upgrades it. The system refreshes the screen with this information.
You undo a Source Code instance upgrade by pointing the Source Code instance to an earlier version of the Source Code definition than the one you upgraded the Source Code instance to.
You can undo Source Code upgrades for a single Source Code instance or for multiple Source Code instances.
Single Source Code instance To undo a Source Code instance upgrade, see "Upgrade Instance".
Multiple Source Code instances To undo multiple Source Code instances, see "Upgrading Multiple Source Code Instances".
Parameters enable you to use the same Source Code definition to achieve multiple results, controlling the processing flow differently under different conditions, or processing different data in different executions of the same Program. For example, you can use the same Program to process data for different studies by defining a Parameter for the study name.
If you use a parameter in your source code internally only, you do not need to create a Parameter definition for it. However, if you want the Parameter to be settable at runtime or in the Execution Setup definition, you must define it. When you define a Parameter you can give it a default value and/or a list of values. See "Defining Parameters" for further information.
Note:
Programs of BI Publisher adapter type contain predefined Parameters that you should not modify. See "Setting Oracle BI Publisher Program Parameters".This section contains the following topics:
A Planned Output is a placeholder for a file to be generated by a Program during execution. There are three types: Primary, Secondary, and Error File. You can define any number of any type of Planned Output.
Planned Outputs are not required; you can create Programs whose purpose is to transform and write data to a Table rather than to produce a report.
You must create a different Planned Output for each file you want to generate. If you want to create the same report in two file types, define a Planned Output for each of them (in that case you might also want to use a Parameter to determine which one to produce at runtime).
The source code for the Program must specify how to create each Planned Output defined in the Program and refer to each one by the name appropriate to the development environment (for example, Oracle or SAS).
Primary Output A primary output is a report on data generated during the successful execution of the Program. The purpose of the Program is to produce one or more primary outputs (and may also transform data). You must write source code that produces the report you want, and refer to the primary Planned Output by name in the source code.
You can define more than one primary output for a single Program. For example, you could create two primary Planned Outputs to present the same information in two ways, such as a table and a graph; or you could divide the data results into two or more categories for presentation, with a Planned Output for each category.
Secondary Output A secondary output is one that is not defined as either Primary or Error File; for example, a log file. The execution engine produces a log file for every execution of an Oracle LSH Program, but you may or may not need to define a Planned Output as a placeholder for the log file:
The system automatically creates one secondary Planned Output for each SAS-type Source Code definition, to hold the log file. SAS log files therefore appear in the Reports tab navigation tree.
You can view the log file for Programs of all Oracle types from the Job screen. If you want to have the log file appear as an entry in the Reports tab navigation tree as well, you must create a secondary Planned Output for it.
For Oracle LSH Informatica Programs, defining Planned Outputs for the log files has no effect. You can see the log files through the job. The Oracle LSH Informatica Program generates an unexpected output for each log file at the time of execution, but does not fail on account of this.
Error File An Error File Planned Output is a file generated automatically by the system if a Program execution fails to generate a primary output defined as Required. To define an Error File Planned Output you must also define at least one primary Planned Output as Required in the same Program definition.
The very existence of an error file as an output indicates a failure.
Classifying Planned Outputs You can classify Planned Outputs. The system assigns the same classification values to the actual output when the Program generates it. Users find report outputs by their classifications in the Reports tab of the Oracle LSH user interface. See "Defining Planned Outputs" and "Classifying Outputs".
Report Sets and Planned Outputs To use a Planned Output as a report in a Report Set, you must create an instance of the Program that generates the Planned Output in a Report Set Entry in the Report Set.
All the primary Planned Outputs generated by the Program are included in the Report Set Entry, in the order in which they are displayed in the Program. If there are more than one primary Planned Outputs and you want to be able to choose which one(s) to include, you should create the Program with an input Parameter for this purpose.
Secondary outputs and error files are not included in the Report Set's table of contents.
The Report Set produces the output in the file type you specify as part of the Planned Output definition. If you have Publishing Light installed and want to use those features for this Report, you must specify a file type of PDF.
To define a Planned Output:
In the Planned Output subtab of a Program, click Add. The system displays the Planned Output For screen.
Enter values in the following fields:
Name. See "Naming Objects".
Title. Enter text or accept the default value. The system automatically creates the default from the text you entered in the Name field.
Description. See "Creating and Using Object Descriptions".
File Name. You must define a File Name for each defined Planned Output of a Program of any type. For most technology types it must include a file extension. The system converts any spaces you enter to underscores (_). The system uses the File Name to match the actual generated output to the corresponding Planned Output in order to classify the actual output file.
In a non-SAS Program, refer to the output as the File Name in your source code. In a SAS Program, make the File Name the same as the File Reference Name plus a file extension; for example, if the File Reference Name is out1, make the File Name out1.pdf.
Primary. If Yes, indicates that the output file will contain a report on data generated by the Program during execution.
Error if generated. If Yes, the system generates an error if Program execution fails to generate the output.
Error if not generated. If Yes, the system generates this output only if Program execution fails to generate a Primary Planned Output. Its presence is an indication that the Program failed. You must write the source code to generate the text of the file. Note: You cannot define an Error file as Required.
Note:
You do not need to define a Planned Output for the .log file.File Reference Name. You must define a File Reference Name for each Planned Output of a SAS Program. It should conform to SAS rules. You should also define a File Reference Name if you plan to use an instance of the Program in a Report Set and to pass Report Set Entry properties' values to the Program. Oracle recommends making Planned Output File Reference Names unique within a Program, but this is not enforced. See "Passing Report Set Entry Values to and from Programs".
The File Reference Name defaults to out1 for the first Planned Output, out2 for the second, and so on.
Click Apply. The system saves the changes and returns you to the Program Instance screen.
You must define default classification values for the Planned Output. The system applies these classifications to the actual output when it is generated by the execution of the Program. The classifications you define for a Planned Output determine who can see the actual output when it is generated, and where it appears in the navigation tree in the Reports tab in Oracle LSH.
For further information, see "Classifying Outputs".
This section includes information on:
See also: Setting Up Oracle SQL Developer or SQL*Plus as an IDE
Note:
Each PL/SQL Source Code definition within a particular Program must have a unique Oracle package name.When a Program is executed, the system launches its primary source code file. You must use a specific syntax at the beginning of the PL/SQL source code and also write the source code in such a way that it calls every secondary Source Code instance you define and refers to all defined subcomponents by their Oracle name.
Required Syntax: Must Match Definitions In the primary Source Code of a PL/SQL Program, the source code must begin by providing the Oracle Package name and Oracle Procedure name defined for the Source Code, and declare all Parameters defined in the Program with their data type, as shown in the following example, where the first Parameter is a number and the second Parameter is a varchar2:
Example 5-1 Required Beginning of PL/SQL Code in a Primary Source Code File
create or replace package PACKAGE_NAME asprocedure PROCEDURE_NAME (parameter_1 number,parameter_2 varchar2);end USER_PACKAGE_A;/create or replace package body USER_PACKAGE_A asprocedure MAIN (parameter_1 number, parameter_2 varchar2) isbegin
Required Security Syntax There is a potential security hole in PL/SQL Programs because they can be executed directly in the database outside of Oracle LSH security.
Oracle LSH can prevent this if you add a specific code template. You should add this to the beginning of the initialization block of your primary PL/SQL source code, as shown below. When you first install the Program, the system compiles the PL/SQL source code and inserts the actual Program ID generated by the system for the Program. At runtime, the system checks that a database account corresponding to the Program ID has been created. The service instance creates this database account to allow execution of the PL/SQL packages. If the account exists, then the job has been created through proper channels and is allowed to proceed. If it has not, the system does not allow execution to proceed.
If you do not include the recommended code template, when you install the Program, the system looks for either END; or END package_name; beginning at the end of the source code, and inserts the security code at that point. However, at runtime the Program is allowed to run up until that point. Any statements that appear in the initialization block before the security code are allowed to execute.
Add the following template exactly as appears:
BEGIN /*Package initialization here*/ /* LSH GENERATES SECURITY CODE HERE, DO NOT REMOVE THIS COMMENT. */ /* Define your package initialization here */ NULL;
Insert the above template into the package initialization block of the package body, as follows:
CREATE OR REPLACE PACKAGE pkg1 AS /* define your procedures here */ PROCEDURE proc1; END pkg1; / CREATE OR REPLACE PACKAGE BODY pkg1 AS /* define your parameters here */ /* define your procedures here */ PROCEDURE proc1 IS BEGIN /* Define code here */ NULL; END proc1; BEGIN /*Package initialization here*/ /* LSH GENERATES SECURITY CODE HERE, DO NOT REMOVE THIS COMMENT. */ /* Define your package initialization here */ NULL; END pkg1;
The first time you install the Program, the system updates your source code by inserting the following code, including the actual program_id generated for the program by the system:
IF NVL(SYS_CONTEXT('CDR_RUNTIME', <program-id>), 'X') <> 'Y'
THEN
RAISE_APPLICATION_ERROR(-20005, 'EXECUTE NOT enabled.');
END IF;
Subcomponent References in PL/SQL You must refer to the defined subcomponents of the Program in your PL/SQL source code as follows:
Table Descriptors. For each table you read from or write to in your source code, you must define a source or target Table Descriptor. Refer to each Table Descriptor as if it were a real database table, using its Oracle name. If the Table instance to which a Table Descriptor is mapped has a different name from the Table Descriptor, use the Table Descriptor's name, not the Table instance's.
Note:
In a PL/SQL Program a Source Code and a Table Descriptor cannot have the same Oracle name.Secondary Source Code. Refer to secondary Source Code instances by their Oracle name.
Parameters. You must create a defined Parameter for each input and output Parameter you use in your primary source code, and declare them in your source code (see "Required Syntax: Must Match Definitions"). Refer to defined Parameters by their Oracle name.
Planned Outputs. You must create a defined Planned Output for every output generated by the primary source code at execution, including the log file. Refer to each defined Planned Output by its File Name.
API for Ending PL/SQL Programs with a Status of Success, Warning, or Failure Normally PL/SQL programs end with a status of Success unless there is a system failure or unhandled SQL exception. However, if you are using a Program in a Workflow, you may need to write your code so that the Program completes with a status of Warning or Failure, depending on circumstances. In a Workflow, you can use the completion status of a Program to determine which branch of activities to execute.
Oracle LSH ships with an API for this purpose called:
CDR_EXE_USER_UTILS.setCompletionStatus()
To call the package, enter one of the following lines of code in your source code exactly as it appears below, at the point where you want the Program to return a status of Success, Warning, or Failure:
CDR_EXE_USER_UTILS.setCompletionStatus(1); CDR_EXE_USER_UTILS.setCompletionStatus(2); CDR_EXE_USER_UTILS.setCompletionStatus(3);
To test PL/SQL code, you must first map the Table Descriptors to the Table instances and check in and install the Program instance and all the Table instances it reads from and writes to. You can then execute the Program. If the Program writes data to tables, you can check the data; see "Viewing Data".
If the Program generates a report, you can see the report in the Reports tab. You can link to the report and the log file from the Job ID link in the Job Executions section of your My Home tab.
You can create PL/SQL Programs especially for the purpose of containing PL/SQL packages as reusable (sharable) Source Code definitions, so that Definers can more easily find them. You can group logically related packages in the same container Program.
You can then use these sharable PL/SQL packages in Oracle LSH Programs of type PL/SQL or Oracle Report (see "Using a Sharable PL/SQL Package").
To create a storage Program for PL/SQL packages:
Create a Program of type PL/SQL. Give it a name and description that describe its purpose, such as "Sharable Demography Subroutines."
(Optional) Add a primary Source Code definition of type PL/SQL to test the packages. Set its Sharable flag to No.
Add the secondary Source Code definitions you want to share. You can create them and write the code from within the storage Program, or you can copy them from other Programs and paste them into the storage Program. They must all be of type PL/SQL.
Check in, install, and test the Program.
When you have tested each one, set its Sharable flag to Yes. You can change this setting without checking out the Program definition.
In Oracle LSH Programs of type PL/SQL or Oracle Report, you can create an instance of a Source Code definition stored in another Program and marked as Sharable. Your company may have Programs created especially for the purpose of storing sharable PL/SQL packages.
To use a Sharable Source Code definition:
In a PL/SQL or Oracle Reports Program, create a secondary Source Code object as an instance of an existing Source Code definition (see "Creating an Instance of an Existing Source Code Definition").
When you search for the definition source, specify the PL/SQL package storage Program as the Program search criterion.
You can select one or more of the PL/SQL packages. The system includes the source code for each package you select in the Source Code instance of your Program.
See "Upgrading Source Code And Undoing Source Code Upgrades" for information on how to upgrade Source Code instances pointing to a sharable Source Code definition.
The system compiles PL/SQL source code when you install the Program instance. Therefore, when a Program containing PL/SQL packages is submitted for execution, the system does not need to compile its source code.
You can write a PL/SQL Program to retrieve BLOBs (binary large objects such as documents created using the Microsoft Office Suite) from the database and use them, for example, as a Planned Output of the Program for inclusion in a Report Set. You can also stream a BLOB into a Program.
This section includes information on:
The View-type Program creates a database view of data in multiple Table instances. When you install the View Program, the system creates a Table definition, a Target Table Descriptor and a Table instance object of type View; and maps the Table instance to the target Table Descriptor.
The resulting View Table instance created in the Work Area schema functions like any database view and always displays current data available in the source Table instances. You can combine data from various Table instances without storing the data multiple times in the database. Other Programs, Business Areas and Data Marts can also read from the View Table instance. View-type Programs cannot be executed and therefore you cannot create them under a Report Set or Workflow.
The status of a View Program is 'Installable' if the View Program contains both a Source Code and a source Table Descriptor. The View Program's Launch IDE behavior is the same as that of a PL/SQL Program.
When you launch the View Program, the system launches its primary source code file.
The following is a format of a SELECT statement that can be customized and saved as Source Code in a View Program:
SELECT [ hint ] [ { { DISTINCT | UNIQUE } | ALL } ] select_list FROM table_reference [, table_reference ]... [ where_clause ] [ hierarchical_query_clause ] [ group_by_clause ] [ HAVING condition ] [ model_clause ] [ { UNION [ ALL ] | INTERSECT | MINUS } (subquery) ] [ order_by_clause ]
The SELECT query in the View Program Source Code can only reference the source Table descriptors. It may call functions which are part of PL/SQL programs that are statically referencedby the View Programs.
A View Program cannot contain any definition, runtime parameter or Planned Outputs.
During installation, the system validates the SQL statement for any external references. Therefore, the SQL statement cannot reference any public object in the database.
You can only create a View Table instance by installing a View Program. You cannot create or update a View Table instance from the Oracle LSH User Interface or through APIs. Once created, the target Table Descriptor cannot be unmapped from its Table instance. If you remove a View Program instance, its corresponding target Table instance is also removed.
If you modify the SELECT statement before installing the program, the system synchronizes the Target Table Descriptor and the Table instance at the time of install.
A View Table instance has full currency traceability and can be used as a source table for other Oracle LSH Programs, Data Marts and Business Areas. Every time you access the view's current data, a currency timestamp is applied to the View Table.
You can use the Browse Data UI to view data in the View Table instance. You can use the Apply Snapshot Label UI to select snapshot labels for all source Tables and apply a snapshot label to the target View Table instance. You can remove a snapshot label using the Manage Snapshot Label UI.
The validation status of a View Table instance must always be equal to or lower than that of the source Table instances.
Any destructive change to a source Table Instance invalidates the View Table instance. You must update and reinstall the View Program to resolve differences. The system does not allow you to remove source Tables if the validation status of a View Table instance is Production.
The blinding status of the View Table instance is determined by that of the Table Instances mapped to the source Table Descriptors in the View Program.
If you have blind-break privileges on source Table instances that support blinding, you can authorize a view based on those Tables. You can authorize reading from blinded sources only if all the source Table Descriptors are mapped. Use the Authorize Read from Blinded Sources radio button on the View Program instance Properties page to authorize a View Table. By default, the setting is No.
If mapping of any source Table Descriptors becomes incomplete or new Descriptors are added, the Authorize Read from Blinded Sources setting is automatically set to No. When the authorize setting is modified, the Program instance is implicitly checked out.
You cannot map source Table Descriptors of a View Program to a View Table instance.
In a View Progam, if the target Table Descriptor and its corresponding View Table instance exist, and if the SELECT query in the Source Code is modified, installation automatically updates the corresponding Table definition in the Application Area containing the Work Area. The system then updates the Table Descriptor and Table instance to refer to the modified Table definition.
If the Table definition for the Table Descriptor is updated during installation, the system checks if the Table definition exists in the local Application Area. If not, a copy of the Table definition is created in the local Application Area and modified.
This section includes information on:
See also: Setting Up SAS as an IDE
There are three basic ways to use SAS source code in Oracle LSH:
If you have the SAS client installed on your PC, you can launch the SAS integrated development environment (IDE) from an Oracle LSH Program instance.
If you plan to use the SAS (IDE) to develop an Oracle LSH Program, before you launch the IDE:
Define and map the source Table Descriptors you need; see "Defining and Mapping Table Descriptors".
Install the Program instance; see "Installing a Work Area and Its Objects".
Oracle LSH then downloads the data views or files to SAS (depending on the type of connection you are using; see Connecting to SAS) when you launch SAS and you can read the data as necessary while you write the source code in SAS.
You can go back and forth between working in SAS and working in Oracle LSH as you develop a Program. For example, if you declare an input or output parameter in your SAS code, you can immediately go to Oracle LSH and create the required corresponding Parameter in the Oracle LSH Program, and then go back to writing SAS code.
When you are ready, go to the Source Code definition in the Oracle LSH Program instance and upload your SAS source code.
You may have many legacy SAS programs that you want to use on Oracle LSH data. You can upload an existing SAS program to a Source Code definition in an Oracle LSH SAS Program and create defined Parameters, Source Codes, Table Descriptors, and Planned Outputs as required by Oracle LSH for the SAS source code.
When you create a new Source Code definition and instance at the same time, you can type or copy and paste source code text directly into the large Source Code field.
There are three ways to connect to SAS for Program development: Connected Mode, SAS Connected Mode with Work Area Data, and Disconnected Mode. You can specify the mode you want to use in your User Preferences, although your choices may be restricted by your company.
Connected Mode. Your PC has the SAS client and SAS Access to Oracle installed and is connected to the Oracle LSH database through a network. When you launch SAS from a Program instance, Oracle LSH downloads views based on the Table Descriptors defined in the Program. You write your program locally on the SAS client, using the views to see data in Oracle LSH. You cannot write data to Oracle LSH Table instances. If you run the Program locally, you write data to local data set files.
When you are ready, go to the Program instance in Oracle LSH, upload the SAS source code and upload any target SAS data sets you have created as Table Descriptors.
SAS Connected Mode with Work Area Data. This mode is the same as Connected mode except that it connects to the Work Area schema in the database. From SAS, you can browse views of current data in all Table instances in the Work Area, not just the Table instances linked to Table Descriptors of the Program.
You must use the SAS Access to Oracle tool to connect to Oracle LSH.
Disconnected Mode. Your PC has the SAS client installed and is connected to the Oracle LSH database through a network. When you launch SAS from a Program instance, Oracle LSH downloads data sets with the same structure as the Program's Table Descriptors. In addition, Oracle LSH downloads the actual data contained in the Table instances to which the Table Descriptors are mapped. You can write your program, working locally on the downloaded data.
Oracle LSH creates a directory structure on your personal computer based on the location of the Program, starting with the Domain (if you are using multiple levels of Domains, all are represented): Drive:/CdrWork/your_LSH_database_acount_name/Domain_name_(all_existing_domains)/Application_Area_name/Work_Area_name/Program_instance_name/Program_instance_version/Table_Descriptor_SAS_libname/data set file.
For Source Codes, Oracle LSH creates directories on your PC to contain the source code files. The system creates one directory for Source Code definitions of type Program and another for those of type Macro:
Drive:/CdrWork/your_LSH_database_acount_name/Domain_name_(all_existing_domains)/Application_Area_name/Program_definition_name/Program_versionPrograms/source code files.
Drive:/CdrWork/your_LSH_database_acount_name/Domain_name_(all_existing_domains)/Application_Area_name/Program_definition_name/Program_version/Macros/source code files.
When you are ready, go to the Program instance in Oracle LSH, upload the SAS source code and upload any target SAS data sets you have created as Table Descriptors.
Note:
It is possible to work on the same Program in different modes at different times. However, if you work first in Disconnected mode, so that the system downloads data to your personal computer, and then change to Connected mode, you may get an error that the source data set already exists. In this case, the system continues to point to the local data set instead of live data in Oracle LSH.To avoid this problem, delete or move the data sets on your personal computer that were downloaded from Oracle LSH.
Note:
Developing SAS code is an option only for customers who purchase SAS separately from Oracle LSH. See "Setting Up Integrated Development Environments (IDEs)" for instructions on how to set up SAS to work with Oracle LSH.Oracle LSH supports three types of SAS Programs and handles each one differently during execution:
SAS Macro Catalogs and SAS Format Catalogs can be referenced by primary or secondary Source Code instances in a SAS Program and are compiled each time the Program is executed, before the primary Source Code is launched.
SAS Program Define an Oracle LSH Program of type SAS Program to hold the source code of a normal SAS program that manipulates data or generates one or more reports. Upload this SAS source code to Oracle LSH as primary source code. In this primary source code you can call SAS macros or formats stored in Oracle LSH Programs of type SAS Macro Catalog or SAS Format Catalog, or stored in the same Program as secondary Source Code of type Macro.
Before you launch the SAS development environment to write source code, you must define a Program's source Table Descriptors and map them to Table instances so that Oracle LSH can download the views or data for you to use.
In a SAS Program you can have two types of source code:
Program. Source Code of type Program is intended to hold the source code that accomplishes the business purpose of the Program: merging or transforming data and/or producing one or more figures, listings, or table reports. You must designate the Source Code that serves this purpose as the primary Source Code so that the system sends it to the SAS engine for execution. The actual SAS source code file contained in the Source Code definition can call other Source Codes of type Program or Macro, or Oracle LSH Programs of type SAS Macro Catalog or SAS Format Catalog.
Macro. You can define a macro specifically for use within a particular Program. These Source Code definitions can be displayed in any order. The system compiles them before each execution and executes them in the order they are called by the primary source code.
See "Writing SAS Primary Source Code".
SAS Macro Catalog an Oracle LSH Program of type SAS Macro Catalog is intended to store a set of macros that are approved for reuse in a variety of SAS Programs. You can group a set of macros with related functions in a single Catalog; for example, demography macros. In a SAS Macro Catalog Program you can have two types of source code:
Macro. A Source Code definition of type Macro to hold the source code for a single SAS macro. Set each macro's Sharable flag to Yes.
Program. One or more Source Code definitions of type Program to test the macros. Set its Sharable flag to No. This Source Code must be listed in the first (primary) position so that Oracle LSH sends it to the SAS engine to test the macros.
SAS Format Catalog an Oracle LSH Program of type SAS Format Catalog is intended to store a set of formats that are approved for reuse in a variety of SAS Programs. You can group a set of formats with related functions in a single Catalog; for example, demography formats. In a SAS Format Catalog Program you can have two types of source code:
Macro. A Source Code definition of type Macro to hold additional source code to support the format building steps. Set each macro's Sharable flag to Yes.
Program. One or more Source Code definitions of type Program to test the formats. Set its Sharable flag to No. This Source Code must be listed in the first (primary) position so that Oracle LSH sends it to the SAS engine to test the formats.
For Source Code of type Macro in any Program type, you must upload the source code, not the compiled binary file. The system compiles the macros defined in a Program before each execution of a Program's primary source code.
Create a Source Code definition of type Program in a SAS Program to hold the source code that accomplishes the business purpose of the Program: merging or transforming data and/or producing one or more figures, listings, or table reports. You must designate the Source Code that serves this purpose as the primary Source Code so that Oracle LSH sends it to the SAS engine for execution. Its source code can call other Source Codes of type Program or Macro contained in the same SAS Program, or Oracle LSH Programs of type SAS Macro Catalog or SAS Format Catalog.
If you plan to launch the SAS development environment from Oracle LSH to write source code, you must first define a Program's source Table Descriptors, map them to Table instances, and install the Program and Table instances so that Oracle LSH can download the views or data for you to use.
Note:
Do not include the stringerror: in any SAS source code. Oracle LSH searches the Program execution log file for the string "Error:" and errors out the Program execution if it finds the string. The source code of the Program is copied into the log file. Therefore if you include "Error:" in your source code, the Program will fail.Subcomponent References in SAS You must refer to the defined subcomponents of the Program in your SAS source code as described in the following sections:
Table Descriptors Oracle LSH Tables and Table Descriptors are compatible with SAS data sets. The Table is equivalent to a data set, and Table Columns are equivalent to a data set's variables.
Syntax. Write to each Table Descriptor defined within the Program as if it were a data set, using the syntax SAS_library_name.SAS_name. You must read from and write to the Table Descriptor, not the Table instance; if the name of the Table Descriptor or its Columns differ from the Table instance's, use the Table Descriptor's.
Target As Dataset. Because Program source code must write to Table Descriptors, and Table Descriptors are views, you should use Proc SQL statements to write to tables in Oracle LSH. However, Oracle LSH provides a feature to allow you to use existing SAS Programs written with data statements. The Target As Dataset attribute is available only in SAS Programs, and only for target Table Descriptors.
If you set this attribute to Yes, Oracle LSH adds a processing step to enable SAS data statements to write to Oracle LSH Table instances. This extra processing step results in slower performance but allows you to use existing programs.
Select No if the Program's source code uses Proc SQL statements to write to tables. This results in optimal performance.
SAS Secondary Source Code Instances In an Oracle LSH SAS Program you can create secondary Source Code instances of four types:
SAS Macro Catalog. To use any of the macros included in Oracle LSH SAS Macro Catalog in a Source Code instance, create an instance of the catalog Source Code in your Program. Immediately before each execution of the Oracle LSH SAS Program the macros are compiled in the SAS work library. You can call them by name from the primary Source Code.
SAS Format Catalog. To use any of the formats included in an Oracle LSH SAS Format Catalog in a Source Code instance, create a Source Code instance of the whole catalog in your Program. Immediately before each execution of the Oracle LSH SAS Program the formats are compiled in the SAS work library. You can call them by name from the primary Source Code.
Macro. You can create a macro especially for use in the same Program where your primary Source Code is located. You can also create an instance of a sharable Source Code definition of type Macro from another SAS Program. Refer to individual Source Codes of type Macro in your primary source code by their name.
Program. You can use another Oracle LSH SAS Source Code of type Program (not an Oracle LSH Program of type SAS Program) as an Include. If the Source Code definition is located in the same Program as your primary Source Code, refer to it by its name. If the Source Code definition is located in a different Program, refer to it by its SAS File Reference Name.
Parameters For every input or output parameter in your SAS primary source code, you must define a Parameter in Oracle LSH and refer to it by its name in your SAS code. See "Defining Parameters".
Planned Outputs You must define a Planned Output to hold each report to be generated by a Program and refer to each one in the source code by its SAS File Reference Name. Oracle LSH automatically generates a Planned Output for the log file when you create the first Source Code in the Program. See "Defining Planned Outputs".
You can successfully execute a SAS Program through Oracle LSH even if there are unplanned Outputs. If the SAS Program's validation status is Development, the system automatically generates a Planned Output with a file reference name created from the first eight characters of the Output file name in the source code. Ensure that the Output file name in the Source Code is enclosed in double quotations for Oracle LSH to identify.
If the required Output is a SAS data set, Oracle LSH can generate a Planned Output if the SAS Program's validation status is Development. You must define the new SAS data set inside the library named "Target". Oracle LSH then treats the Output data set as the SAS Program's target Table. The system compares the SAS data set filenames with those of the target Table Descriptors and if they match, loads data from the SAS data set into the mapped Table instance.
If the system does not find a target Table Descriptor, it checks out the Program, adds a new target Table Descriptor to it, creates a new Table instance in the Work Area and maps it to the newly created target Table Descriptor before installing the Program and the Table instance. Data is then loaded from the SAS data set into the mapped Table instance. Oracle LSH triggers separate jobs for each of these processes. The system does not support Proc SQL and Insert commands in the generation of unplanned data set Outputs.
Subsequent changes in source data set Table Descriptors must be matched in the target Table manually.
To use any of the macros contained i an Oracle LSH SAS Macro Catalog, you create a Source Code instance in the Program from which you need to call them.
Do the following:
In the Oracle LSH SAS Program where you need to use one or more of the macros in the Catalog, create a Source Code as an instance of an existing definition.
In the Search screen, choose the Domain or Domain and Application Area where the Macro Catalog you need is located, and select the SAS Macro Catalog radio button. If you know the exact name of the Macro Catalog you need, enter it in the Name field.
Click Go. The system returns the Macro Catalog(s) that satisfy the search criteriaâor, if you entered the exact name of a Macro Catalog, returns only that one.
Select a Macro Catalog: select its box in the Select column and click the Select button. The system adds an instance of the Catalog, including all the macros it contains, and returns you to the Source Code screen.
You can now use any of the macros in your Program. At execution they are added to your work library and you can call them by name from the primary source code.
If your SAS code operates on data sets that require SAS formats for the proper expression of their data, you must include the formats in your Oracle LSH SAS Program (see "Defining SAS Programs").
When you create a SAS Program in Oracle LSH that needs to use an Oracle LSH SAS Format Catalog, do the following:
In the Oracle LSH SAS Program, create a secondary Source Code object as an instance of an existing Source Code definition (see "Creating an Instance of an Existing Source Code Definition").
If the Format Catalog includes a Table Descriptor for use as a static reference, select the Static Reference radio button.
In the Search screen, choose the Domain or Domain and Application Area where the Format Catalog is located and select the SAS Format Catalog radio button. If you know the exact name of the Catalog you need, you can enter it.
Press Go. The system displays all the SAS Format Catalogs in the location you specifiedâor, if you supplied the exact name of a Catalog, lists only that Catalog.
Select the Catalog you want by selecting the box next to it in the Select column and click the Select button. The system adds the Catalog as a Source Code instance to your Program and returns you to the Program's Properties screen.
You can now use any of the formats in your Program. At execution they are added to your work library and you can call them by name from the primary source code, for example:.
Oracle LSH includes the Program type SAS macro Catalog especially for the purpose of storing SAS macros that are approved for reuse. You can group logically related macros in each SAS Macro Catalog.
You must upload the source code file, not the compiled binary file. When the Program is submitted for execution, the system compiles its macros before executing the primary source code.
To create a SAS Macro Catalog in Oracle LSH:
Create a Program of type SAS Macro Catalog. Give it a name and description that describe its purpose (see "Creating Source Code").
Note:
You cannot set a Static-reference Source Code as primary. Also, you cannot set a Source Code that points directly to a Program definition as primary.For each SAS macro you want to store in the Catalog, create a Source Code definition of type Macro.
If the macro does not already exist, create it either in SAS or in the Source Code box. If the source code is on a local computer, upload the source code file (not the compiled binary file) from SAS to Oracle LSH.
Set each macro's Sharable flag to Yes.
Note:
You cannot set any macro Source Code instance that points directly to a Program definition as Sharable.(Optional) Add a primary Source Code definition of type SAS Program to test to SAS macros you store in the Catalog. Set its Sharable flag to No. Write the source code and upload if necessary.
Note:
This source code is not compiled when the Program is executed because its Sharable flag is set to No.Test the macros and promote the Catalog to a higher validation status according to your company's policies.
It is possible to create macros that reference other macros contained in a different SAS Macro Catalog in Oracle LSH. In this case, you include as a Source Code instance in your Oracle LSH SAS Program only the SAS Macro Catalog that contains the macro your primary source code references. When the Program is submitted for execution, the system compiles the macro specified in the Program and automatically finds and compiles the macro referenced by the Program's macro. You cannot use more than two layers of macros; a macro referenced by another macro cannot reference yet other macro.
A SAS format translates short data value codes like zero (0) and one (1) to meaningful data values such as Male and Female or Yes and No. Using SAS formats, you can store a minimum amount of data and call the format to correctly display the data in a report. The format itself can consist of source code containing hardcoded values, such as:
Alternatively, the format can consist of a simple call to an Oracle LSH Table instance that contains the relationships between the short stored values and the meaningful display values.
In this case the format refers to a data set, now converted to an Oracle LSH Table called Standard Formats that contains data such as:
Table 5-1 Format Table Example
| Format Name | Stored Code | Display Value |
|---|---|---|
|
$Sex. |
0 |
Male |
|
$Sex. |
1 |
Female |
|
$YesNo. |
0 |
No |
|
$YesNo. |
1 |
Yes |
You can create the Oracle LSH Table in several ways, including:
uploading a format data set from SAS using a Load Set
modifying an Oracle Clinical Discrete Value Group (DVG) table (loaded into Oracle LSH by the Oracle Clinical Global Library adapter)
uploading a table from any integrated external system and modifying as necessary
A SAS format data set contains all the information required to define a format, such as the format name; its starting and ending value; minimum, maximum, and default length; and so on as columns or variables.
To create a SAS Format Catalog in Oracle LSH:
Create an Oracle LSH Program of type SAS Format Catalog. Give it a name and description that describe its purpose.
Note:
You cannot set a Static Reference Source Code as primary. Also, you cannot set a Source Code that points directly to a Program definition as primary.If any of the formats in the Catalog are table-dependent, add the necessary Table Descriptor(s) using the Oracle LSH Table definition that you created for formats as the source Table definition (see "Creating a Table Descriptor").
Map any Table Descriptors to Table instances (see Mapping Table Descriptors to Table Instances).
Add a Source Code definition of type Program that contains the format source code (see "Creating Source Code").
If the format is Table-dependent, the source code must use the Table Descriptor as input and include a SAS Proc format to create the format catalog in SAS.
You can also add Source Codes of type Macro to support the format building steps if necessary.
Set the Source Code's Sharable flag to Yes.
Note:
You cannot set any Source Code instance that points directly to a Program definition as Sharable.Repeat as necessary; you can have multiple format Source Codes in a single Oracle LSH SAS Format Catalog.
Apply. The system saves the Source Code definition(s) and instances in the database and returns you to the Program's main page.
Install the Program in the database (see "Running a Work Area Installation").
You can call a public Oracle LSH API from SAS source code to capture the values of output Parameters in a SAS Program contained in a Report Set or Workflow for the purpose of passing their value during execution to another Program in the same Report Set or Workflow (see "Setting Up Parameter Value Propagation").
If you set up value propagation in a Report Set or Workflow, you must call an API from each SAS Program whose output Parameter values you need to capture. You must call the API once for each Parameter value you need. You may want to add the API call to every SAS Program that contains output Parameters in case you later add the Program to a Report Set or Workflow and want to use the output Parameter value in value propagation. The API package procedure name is: Cdr_Pub_Exe_User_Utils.setOutputParams.
The example below uses a PL/SQL wrapper to call the API. In this way you can call the API multiple times and only connect to the database once from SAS, and only two arguments are required for each output Parameter that you want to send back to Oracle LSH:
pi_vparamName. Enter the Name of the output or input/output Parameter whose value you want to capture.
pi_vparamValue. This procedure parameter receives the value of the Program Parameter you specified as the value of pi_vparamName.
You can use the following code to call the API. Use %sysget (as shown) to get the required values rather than hardcoding the values in the code.
SAS code
--------------------------------------------------------------------------------
Proc SQL;
/*set the job context then send the output value*/
connect to oracle (user=%sysget(CDR_SCHEMA) pass=%sysget(CDR_PASSWD)
path=%sysget(CDR_DB) );
/* pass output parameter back to LSH */
execute(exec my_plsql_package.setOutputParams(
'MyParamName'
,'My Param Value'
)
by oracle ;
PL/SQL code
--------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE my_plsql_package AS
Procedure setOutputParams(
pi_vParamName IN varchar2
,pi_vParamValue IN varchar2
);
END my_plsql_package;
/
CREATE OR REPLACE PACKAGE BODY my_plsql_package AS
Procedure setOutputParams(
pi_vParamName IN varchar2
,pi_vParamValue IN varchar2
) IS
return_status VARCHAR2(10);
msg_count NUMBER;
msg_data VARCHAR2(2000);
BEGIN
Cdr_Pub_Exe_User_Utils.setOutputParams(p_api_version => 1
,p_init_msg_list => Cdr_Pub_Def_Constants.G_FALSE
,p_commit => Cdr_Pub_Def_Constants.G_FALSE
,p_validation_level => Cdr_Pub_Def_Constants.G_VALID_LEVEL_FULL
,x_return_status => return_status
,x_msg_count => msg_count
,x_msg_data => msg_data
,pi_vparamName => pi_vParamName
,pi_vparamValue => pi_vParamValue) ;
IF return_status <> 'S' THEN
RAISE_APPLICATION_ERROR(-20200,'Failed to call Cdr_Pub_Exe_User_Utils.setOutputParams: '||msg_data);
END IF ;
END setOutputParams;
END my_plsql_package;
/
You can use Oracle Reports as an integrated development environment to develop reports in Oracle LSH, launching Oracle Reports from a Program definition. Oracle LSH Programs of type Oracle Reports can have two types of Source Code: Oracle Reports, which are uploaded from Oracle Reports, and PL/SQL, for subroutines; see "Creating a PL/SQL Package Storage Program". (See also: "Setting Up Integrated Development Environments (IDEs)".
Oracle Reports Builder includes the following features:
A query builder with a visual representation of the specification of SQL statements to obtain report data
Wizards that guide you through the report design process
Default report templates and layouts that can be customized to meet your organization's reporting needs
The ability to generate code to customize how reports will run
A Live Previewer that allows you to edit report layouts in WYSIWYG mode
An integrated chart builder that helps you to graphically represent report data
Web publishing tools that dynamically generate web pages based on your corporate data
Other standard report output formats like HTML, PDF, Postscript, and ASCII (to make use of Oracle LSH's Publishing Light features, you must use PDF)
For information on using Oracle Reports Builder, see the Oracle Reports documentation:
Go to Oracle documentation at
Go to the URL for the Oracle Reports Developer Reports Builder manual for Oracle Reports 6i, which is the release included with the Oracle LSH technology stack:
http://download-west.oracle.com/docs/pdf/A73172_01.pdf
Note:
This URL is correct as of the date of publication. If you have trouble with these instructions, try My Oracle Support.This section contains the following topics:
See also: Setting Up Informatica as an IDE
Using Oracle LSH Informatica Programs, you can:
Access Informatica tools from within Oracle LSH to create and edit Informatica mappings and workflows
Execute Informatica workflows from within Oracle LSH, on Oracle LSH data
To define a new Informatica Program, do the following:
After you create the Oracle LSH Informatica Program definition and instance, create Table Descriptors in it and map them. This readies the Program for installation. See "Defining Table Descriptors" for more information on Table Descriptors.
Install the Program and check it out.
Click Launch IDE to start Informatica's PowerCenter Designer. You can create your mappings in the PowerCenter Designer. You can access other Informatica components from the PowerCenter Designer; for example, you can go to the Informatica Workflow Manager from the PowerCenter Designer to create Informatica workflows.
Note:
When you launch Informatica PowerCenter Designer from an Oracle LSH Informatica Program for the first time, you have to configure the Oracle LSH Informatica Repository and connect to it. Consult your Informatica Administrator for more information.See "IDE Launch Settings" for information on data access settings for IDEs. See the appropriate Informatica documentation for information on using Informatica.
Export the mappings and workflows from Informatica when done and upload the resultant XML files into the Source Code of the corresponding Oracle LSH Informatica Program. See "Creating and Synchronizing Source Code".
See "Informatica Integration" for information about what happens behind the scenes when you check out, check in, and launch an Informatica Program in Oracle LSH.
If you want to use your existing Informatica mappings and workflows from the first time you install the Oracle LSH Informatica Program, do the following:
From Informatica, export the mappings and workflows. Informatica generates an XML file for each mapping and each workflow.
In the Oracle LSH Informatica Program, create a Source Code definition and instance and upload the XML files into the Source Code definition. See "Creating a New Source Code Definition and Instance" for instructions.
Install the Oracle LSH Program. Oracle LSH creates a folder in Informatica (in the same format as described above) and imports the mapping and workflow files into this Informatica folder.
For more information on installing Programs in general, see "Installing Program Instances".
An Oracle LSH Informatica Program's Source Code holds the Informatica mapping and workflow files.
You must create a new Source Code definition and instance when you upload Informatica files for the first time. See "Creating a New Source Code Definition and Instance" for instructions.
When you make changes to mappings and workflows in Informatica, you must export the mappings and workflows from Informatica when done and upload the resultant XML files into the Oracle LSH Informatica Program's Source Code.
Note:
Informatica exports the mappings and workflows into separate XML files. When uploading the XMLs into the Oracle LSH Informatica Program's Source Code, upload the mapping XML before the workflow XML. This is because when you launch Informatica from the Oracle LSH Informatica Program, the files are imported into Informatica in the same order in which you uploaded them into the Source Code definition, and Informatica needs the mapping XML first.Oracle LSH Informatica Programs do not use the Primary and Secondary classification for the Source Code objects, unlike other Oracle LSH Programs.
You can also use PL/SQL Source Code in an Oracle LSH Informatica Program by creating a Source Code instance that refers to an installed Oracle LSH PL/SQL Source Code definition. See "Using PL/SQL Source Code in an Oracle LSH Informatica Program".
Note:
Do not edit the XML files from within Oracle LSH.Oracle LSH supports PL/SQL programs for Informatica through statically shared Oracle LSH PL/SQL Source Code. See "Creating and Using Static Reference Source Code".
To use PL/SQL Source Code in an Oracle LSH Informatica Program, do the following:
Create an Oracle LSH PL/SQL Program, create a Source Code definition and instance in this Program, upload or enter valid PL/SQL code in the Source Code definition, and install the Program. See "Defining PL/SQL Programs".
In the Oracle LSH Informatica Program, create a Source Code object as an instance of an existing Source Code definition (see "Creating an Instance of an Existing Source Code Definition".
When you search for the definition source, set Static Reference to Yes.
Note:
You cannot add non-statically shared PL/SQL Source Code in Oracle LSH Informatica Programs.Select the PL/SQL Source Code definition you want to use and click Apply.
If you create a Source Code instance of PL/SQL Source Code as a static reference (referring to the Source Code definition of an installed PL/SQL Program instance) in your Oracle LSH Informatica Program, and this PL/SQL Program needs to read the same source Tables as the Oracle LSH Informatica Program, copy the Oracle LSH Informatica Program and the PL/SQL Program that contains the statically shared Source Code, remove the original shared Source Code from the Oracle LSH Informatica Program and replace it with the Source Code from the copied PL/SQL Program, and map both the Informatica and the PL/SQL Program to new Table Instances.
Oracle LSH Informatica Programs can read Oracle LSH Table instances but cannot write to target Table instances. When you launch Informatica, Oracle LSH creates temporary Table instances to enable you to execute Informatica mappings from within Informatica (as opposed to from Oracle LSH). These temporary Table instances are only available for the current Informatica session. If you make any changes to the structure of these tables through Informatica, you must make the same changes manually in Oracle LSH.
See "Defining Table Descriptors" and"Mapping Table Descriptors to Table Instances".
Oracle LSH Informatica Programs support indexes on Table instances and also allow selective index management in addition to recreating indexes for all the Tables. See "Selective Index Management".
This section contains the following topics:
You must define a corresponding Parameter with the same name and type in the Oracle LSH Informatica Program for each parameter you use for mappings in Informatica. You can pass values to Parameters when executing the Oracle LSH Informatica Program.
The Informatica adapter has the following predefined Parameters:
Bulk Load. Set this Parameter to Yes if you use bulk loading in the Informatica workflow. Oracle LSH supports bulk loading of data only for the staging data processing type. The system drops all indexes on the staging Tables and recreates them after job execution, when you set this Parameter to Yes.
See "Staging Processing" for more information on this data processing type.
Drop and Recreate Index. If set to Yes, the system drops all indexes on all target Table instances before the Oracle LSH Informatica Program is executed, and recreates them after execution. If you do not want to recreate indexes for all the target Table Descriptors, you can call an Oracle LSH API that allows selective index management. See "Selective Index Management".
Recover Workflow. If set to Yes, Oracle LSH recovers a suspended Informatica workflow using the Informatica recover mechanism. If set to No, Oracle LSH aborts a suspended Informatica workflow and restarts it.
WF Name. This is the name of the Informatica workflow that you want Oracle LSH to execute. You must provide this name at the time of submitting the Oracle LSH Informatica Program's Execution Setup. See "Creating, Modifying, and Submitting Execution Setups".
Use the Oracle LSH public API for selective index management to:
Select the target Table instances and the indexes/constraints that you want to recreate.
Control index management at runtime, as opposed to before and after execution (through the Drop and Recreate Indexes Parameter).
The API has the following signature:
CDR_PUB_EXE_RUNTIME.ActOnIndex(Create/Drop:<target_Table_instance_name>:<index/constraint name>)
Call this API from a Stored Procedure Transformation in your Informatica mapping by passing the following values to the Stored Procedure:
Create/Drop. Enter either Create or Drop.
Target Table Instance Name. Enter the Oracle LSH target Table instance name whose index or constraint you want to drop or recreate.
Index/Constraint Name. Enter the index or constraint name.
For example:
CDR_PUB_EXE_RUNTIME.ActOnIndex(Create:T_EMPLOYEE:BMP1)
Note:
Refer to the Oracle Life Sciences Data Hub Application Programming Interface Guide for more information on Oracle LSH public APIs.No Planned Outputs are required for Informatica Programs. The system allows you to create them but they have no effect.
This section contains information on the following:
Oracle LSH first creates a folder in Informatica when you install the Program for the first time and subsequently for each check out of the Program. The Informatica folder that Oracle LSH creates when you first install the Program is useful only if you already have Informatica mappings and workflow that you want to deploy. For all subsequent interactions with Informatica, Oracle LSH uses the Informatica folder that it creates at the time of checking out the Program.
Informatica Folder Format When you install the Oracle LSH Informatica Program for the first time, Oracle LSH creates an empty folder in Informatica with a name in this format:
LSHProg_<Oracle LSH Program ID>_<Oracle LSH Program Version>
For example, for an Oracle LSH Program with the ID Prg098765, the corresponding folder created in Informatica's PowerCenter Designer is: LSHProg_Prg098765_1.
See "Informatica Security Configuration" for information on creating Informatica mappings and workflows in the Informatica folder.
This section contains the following topics:
When you check out an installed Oracle LSH Informatica Program, the following takes place:
Oracle LSH creates another empty folder in Informatica with a name in the same format as at the time of installation with the version number incremented by one:
LSHProg_<Oracle LSH Program ID>_<Oracle LSH Program Version>
Informatica associates this folder with a security group with the same name as the folder, replacing the prefix Folder with Group. For example, for the Oracle LSH Program with the ID Prg098765, the security group in Informatica is: Group_Prg098765_1.
The Informatica admin user LSHAdmin owns this Informatica security group.
Note:
Each version of the Oracle LSH Informatica Program instance results in a new folder in Informatica. You have access to only the latest Informatica folder.Also note that although Informatica supports versioning, the Oracle LSH Informatica Repository is nonversioned. You must access versioning information for Oracle LSH Informatica Programs from within Oracle LSH: go to the Actions drop-down list on the Program Properties screen and select View Version History.
Click Launch IDE to start Informatica's PowerCenter Designer.
When you check in an Oracle LSH Informatica Program, the corresponding Informatica folder is locked for write access. However, all users continue to have read access to the Informatica folder. The user who checks out the Program from Oracle LSH will get write access to the Informatica folder.
When you launch the PowerCenter Designer from an Oracle LSH Informatica Program:
Oracle LSH adds each user who checks out the Oracle LSH Informatica Program to the Informatica security group. This user gets read/write privileges to the Informatica folder. Note that when a user checks out an Oracle LSH Informatica Program and launches Informatica, the user's read/write privileges from all other Informatica folders are taken away. This is because a user can work on only one Informatica folder at a time even if the user has privileges on other folders.
You can edit mappings and workflows in Informatica only if you launch Informatica from an Oracle LSH Informatica Program that you checked out. You have read-only privileges in Informatica if:
Someone else has the Oracle LSH Informatica Program checked out
The Oracle LSH Informatica Program is checked in
This section contains the following topics:
See also: Setting Up Integrated Development Environments (IDEs)
An Oracle LSH BI Publisher Program lets you use data from Oracle LSH Tables to create reports using Oracle BI Publisher. You can run the Oracle LSH BI Publisher Program from within Oracle LSH. The system generates a report in the desired output format(s) for Oracle LSH Consumers.
This section includes information on the process of integration between Oracle LSH and Oracle BI Publisher:
Oracle LSH Creates Planned Outputs and Predefined Parameters. Oracle LSH automatically adds to an Oracle LSH BI Publisher Program definition a Planned Output for each report output format that Oracle BI Publisher supports, and two predefined runtime Parameters.
See "Setting Oracle BI Publisher Program Parameters" and "About Oracle BI Publisher Program Planned Outputs".
Prepare the Program. You must create and map Table Descriptors in the Oracle LSH BI Publisher Program and install it to enable launching Oracle BI Publisher.
See "Defining Table Descriptors" and"Installing Program Instances" for instructions.
Check Out the Program. Before you launch Oracle BI Publisher, make sure you check out the Oracle LSH BI Publisher Program, so that Oracle LSH can synchronize the Program with the changes you make in Oracle BI Publisher.
Note:
The Oracle LSH Program is equivalent to an Oracle BI Publisher report.Oracle LSH creates an Oracle BI Publisher report with the same name as the Oracle LSH BI Publisher Program definition and places the report in a folder under My Folders. The name of this folder is in this format:
<Program Definition Name>_<Program's obj_id>_Ver<Program's Version No>
Edit the Oracle BI Publisher report as follows:
Create a new Data Model and select the Data Source. The Data Source has a name in the format LSH_DataSrc_<LSH_application_username>. Select the Data Source that has your Oracle LSH application username in it. Select the Only Use Default Schema checkbox before creating a query. Use the BI Publisher Query Builder to create a query to fetch data from Oracle LSH Tables.
Note:
See "Setting Up Security for Oracle Business Intelligence Publisher" in the Oracle Life Sciences Data Hub System Administrator's Guide or contact your Oracle LSH System Administrator if you cannot find a Data Source name with your application username in it.Create a new layout. Create a report template in any of the formats that Oracle BI Publisher supports and save the template with this report. Set the Output Format to All Formats to enable Oracle LSH to support all report formats when running the Oracle LSH BI Publisher Program.
Save the report and exit Oracle BI Publisher. After you exit Oracle BI Publisher, check in the Oracle LSH BI Publisher Program in Oracle LSH. Oracle LSH creates a Source Code definition and instance and uploads the zipped BI Publisher report into the Source Code. See "About Oracle BI Publisher Program Source Code".
Note:
Refer to the (BI Publisher Administrator's and Developer's Guide and the BI Publisher Report Designer's Guide) for complete details. You can browse through the documentation online and download what you need from the Oracle Technology Network. Use this hyperlink to go to the list of available documentation for the Oracle Business Intelligence Suite Enterprise Edition (version 10.1.3.4)http://download.oracle.com/docs/cd/E10415_01/doc/nav/portal_booklist.htmCreate an Execution Setup for the Oracle LSH BI Publisher Program and run it. See "Creating, Modifying, and Submitting Execution Setups".
When you execute this Program, the system internally calls BI Publisher APIs to create the report as designed in Oracle BI Publisher.
You can see the final report by going to the My Home tab in Oracle LSH and clicking the Oracle LSH BI Publisher Program's Job ID. See "Tracking Job Execution" in the Oracle Life Sciences Data Hub User's Guide.
When you make changes to the Table Descriptors in an Oracle LSH BI Publisher Program:
You must reinstall and check out the Program after making these changes.
When you launch Oracle BI Publisher after these changes, Oracle BI Publisher gets the latest tables per your changes in Oracle LSH, but if you want to change the query, you must do that in Oracle BI Publisher.
When you return to Oracle LSH after saving the report in Oracle BI Publisher, you must check in the Oracle LSH BI Publisher Program.
Do not change anything else in the Oracle LSH BI Publisher Program from within Oracle LSH.
Note:
Do not change the Oracle LSH BI Publisher Program Source Code in Oracle LSH. The system creates and updates the Source Code automatically.After you save the Oracle BI Publisher report and exit Oracle BI Publisher (that you launched from Oracle LSH for the first time), you must check in the Oracle LSH BI Publisher Program. The system creates a Source Code definition and instance and uploads the zipped report file into the Source Code.
After every Oracle BI Publisher launch, the system automatically updates this zipped report file when you check the Oracle LSH BI Publisher Program in.
Note:
Oracle LSH marks the latest zipped report file in the Oracle LSH BI Publisher Program's Source Code as Primary. The system disregards any other report files that may be present in the Source Code definition, and uses only this Primary Source Code.See "About Source Code" for details on Oracle LSH Source Code.
BI Publisher supports many output formats. When you create an Oracle LSH Program of the BI Publisher adapter type, the system automatically adds a Planned Output for each supported output format to the Oracle LSH BI Publisher Program definition. This makes it possible to select any of the supported output formats when running the Oracle LSH BI Publisher Program.
Note:
Make sure that for each Planned Output, Error if Generated and Error if Not Generated are both set to False. See "Defining Planned Outputs" for more information on Planned Outputs.Do not change anything else in the system-generated Planned Outputs.
Oracle LSH BI Publisher Programs include the following types of Parameters:
An Oracle LSH BI Publisher Program has the following predefined runtime Parameters:
BIP Report Output Format. You may set a default output format from the list of values for this Parameter.
You can reset the output format at the time of submitting the execution setup. See "Creating, Modifying, and Submitting Execution Setups".
BIP Template. This refers to the layout template that you attach to a BI Publisher report. You must type out the layout template's name that you defined in BI Publisher. The template's name does not contain extension names. Do not change anything else in this Parameter.
You can change the name at the time of submitting the execution setup. See "Creating, Modifying, and Submitting Execution Setups".
Note:
Do not change any other properties of these Parameters except the default values, or the Program's execution will fail.For each user-defined parameter you create in Oracle BI Publisher, create a Parameter in Oracle LSH with the same name but of VARCHAR2 data type.
You can install a Program instance directly from its Properties screen, using the Install button, or in its Work Area (see "Installing a Work Area and Its Objects"). If you are working with an integrated development environment (IDE) you must install the Program instance in order to see source data in the IDE.
When you install a Program instance using the Install button on its Properties screen:
The system checks in the Program instance and definition, and also the Table instances in the current Work Area to which the instance is mapped.
The system checks if the Program is installable. If not, the system performs Automatic Mapping by Name on any unmapped target Table Descriptors. If the Program is still not installable and there are still unmapped target Table Descriptors, the system creates Table instances in the current Work Area from the target Table Descriptors and maps them.
The system attempts to install the Program instance and its source and target Table instances in the current Work Area. The system displays a success or error message. If the installation fails, the error message displays the name of any objects that were not installable.
Note:
If any of the Table instances or the Program definition is not installable, the system cannot install the Program instance. See Appendix A, "Installation Requirements for Each Object Type" for the reasons these objects may not be installable.Log File To see the log file for the installation, you must go to the Work Area Installation screen, as follows:
Click the Applications tab. The main Application Development screen opens.
Click the name of the Work Area you are working in. The Work Area screen opens.
From the Actions drop-down list, select Installation History.
Click Go. The system displays the Installation History screen with the log files in chronological order.
Click the View Log link for the most recent installation attempt or for the date and time that you ran the install process. The system displays the log file.
For information on installation and on reading the log file, see "Installing a Work Area and Its Objects".
This section contains the following topics:
You can work on Oracle LSH Programs from an integrated development environment (IDE) which connects to the Oracle database hosting Oracle LSH and retrieves data required for the Program.
To view data as you are developing an Oracle LSH Program in an IDE, do the following before you launch the IDE:
Define and map the source Table Descriptors you need.
Install the Program instance; see "Installing Program Instances".
Specify the Launch Settings for blinding and data currency. See "Setting the Blind Break Value" and "Setting the Shared Snapshot Label Value".
Default launch settings are determined by the Data Currency and Blind Break values in the default Execution Setup and your privileges. For example, if the Blind Break setting in the default Execution Setup is Real (Blind Break) but you do not have Blind Break privileges on the Table instances mapped to the Program's source Table Descriptors, your only Blind Break option is Dummy.
Note:
You must have Read Data privileges on the source Table instances to be able to see Dummy data, if you do not have blinding-related privileges.If there is no Execution Setup defined, the default Blind Break value and options are defined by the blinding status of the Table instances and your privileges, and the default Data Currency value is Current. The values you set here apply only during the current session. See "Modifying an Execution Setup and Setting Parameters" for information on setting the Data Currency and Blind Break system Parameters in the Execution Setup.
Note:
Launch Settings do not apply to statically referenced Table instances. A statically referenced Table instance is mapped to a source Table Descriptor of a Program containing a Source Code shared to the Program you are working on in the IDE; see "Creating and Using Static Reference Source Code".Note:
If you generate an output on real blinded or real unblinded data, you need additional privileges to see the output. See "Blinding-Related Security Privileges" in the Oracle Life Sciences Data Hub Implementation Guide for more information.This setting is relevant only when one or more source Table instances either currently or formerly contained blinded data (whose Blinding Flag is set to Yes). Special privileges are required to view real blinded or real unblinded data in these Table instances. You must have these special privileges on all such Tables, in order to see real data in any of them.
Note:
You must have Read Data privileges in order to see any data at all.The following choices are available depending both on the state of the data and on your security privileges:
Not Applicable. If none of the data has ever been blinded, the only option available is Not Applicable. No special privileges are required.
Dummy. This is the only option available to you if you do not have blinding-related privileges for blinded Tables. You can also see this option if you have blinding-related privileges. In that case, you can select this option to work with dummy (not real) data in the IDE.
Real (Blind Break). If any of the data is currently blinded, and you have the required privileges, you can select this option to view real data in the IDE, according to your company's policies.
Note:
Blind Breaks are not allowed in SAS Connected Mode With Work Area Data. Therefore, if you select Real (Blind Break) in SAS Connected Mode With Work Area Data, you cannot see any data in SAS. See "Connecting to SAS".Real (Unblinded). If a blinded Table instance has now been unblinded, you can see real data for the Table instance, provided you have the required privileges. If there are more than one such Table instances, you need the required privileges for all of them to be able to use this option.
If all the relevant Table instances share one or more snapshot labels, those snapshot labels appear in this drop-down list and you can select one. In addition, you normally have the option to view the current data. Your options may be limited by the settings in the default Execution Setup.
You can apply snapshot labels to all the Table instances that a Program reads from or writes to, when you submit its Execution Setup; see "Data Currency". You can also apply snapshot labels in the Work Area; see "Adding, Removing, or Moving a Snapshot Label".
For more information on what snapshots are, see "Data Snapshots".
This section contains the following topics:
If you have the necessary privileges, you can modify a Program either through an instance of it in a Work Area or directly in the definition in its Domain or Application Area. In most cases it makes sense to work through an instance in a Work Area for the following reasons:
In order to use or test changes to the definition you must create and install an instance of it.
If you work through an instance, the system automatically repoints the instance to the new version of the definition.
However, if you need to change properties of the definition, you must work directly in the definition in its Domain or Work Area.
Whether you work in an instance or directly in the definition, when you check in the new version of the definition you have the opportunity to upgrade instances of the original definition to the new version; see "Upgrading Object Instances to a New Definition Version".
On the Program instance's Properties screen, click Update to enter changes. Oracle LSH creates a new version of the instance you are working on and applies your changes to it when you click Apply. Click Cancel to discard your changes and the new version.
You can modify some properties through the Actions drop-down list; see "Using the Actions Drop-Down List" for further information.
Note:
You must reinstall the Program for the changes to take effect.You can modify the following:
Name See "Naming Objects" for further information.
Description See "Creating and Using Object Descriptions" for further information.
Definition Source This field applies to the instance only. It specifies the Program definition to which this Program instance points. It generally does not make sense to change the source definition for the following reasons:
Changing the definition may result in a new set of Table Descriptors, Source Code, Parameters, and Planned Outputs.
Any new Table Descriptors are not mapped.
The Program's status changes to Non Installable.
If you want to change to a new version of the same definition, use the Upgrade Instance option from the Actions drop-down list.
You can go to a Program definition's Properties screen in one of the following ways:
From the Program's Properties screen: Click the hyperlink of the Program definition that appears in the Definition field. See "Definition".
From the Domain or Application Area where you created the definition: Click Manage Definitions to view all the definitions in that Domain or Application Area. Click the definition name.
Once on the Program definition screen, click Update to enter changes. Oracle LSH creates a new version of the definition. You can change the following properties:
Name See "Naming Objects" for further information.
Description See "Creating and Using Object Descriptions" for further information.
You can modify some properties through the Actions drop-down list; see "Using the Actions Drop-Down List" for further information.
Table Descriptors belong to the Program definition, but Table Descriptor mappings belong to the Program instance. You must check out the definition to add, remove, or update Table Descriptors, but not to map, unmap, or remap Table Descriptors.
If you need to change a Table Descriptor's columns, you must update the Table Descriptor's definition source either to a different Table definition that meets your needs, or to a new version of the same Table definition, after modifying the Table definition. If you do not have the necessary privileges to modify the source Table definition, you can probably copy the original Table definition, paste it into the current Application Area, modify it as necessary, and use it as the new definition source.
In the Program instance you can map the Table Descriptor to a different Table instance. See "Mapping Table Descriptors to Table Instances" for further information.
Source Codes belong to the Program definition. You must check out the definition to add, remove, or update Source Codes.
If a Source Code object has a value in its Shared From column, it is an instance of a Source Code definition in another Program. You can modify only a few of its properties; see "Source Code Instance".
If a Source Code object does not have a value in its Shared From column, it's definition was created in this Program. You can modify all its properties here; see "Source Code Definition".
Source Code Instance You can modify the Source Code name, description, definition source, order, SAS file reference name (if a SAS Program), instance subtype and instance classifications. You can upgrade to a different version of the Source Code definition; see "Upgrading Source Code And Undoing Source Code Upgrades".
If you have the necessary privileges, you can go to the Program definition that contains the Source Code definition (it is listed in the Shared From column) in the Definitions subtab and modify it there, creating a new version. You must then change the definition source for the Source Code instance in this Program to the new version.
Source Code Definition If the Source Code definition was created in this Program (in which case there is no entry in the Shared From column) the Source Code definition is located in this Program definition, and you can modify it here.
You can edit the actual source code, either in the Editor box or by modifying the file in its development environment (such as SAS) and then uploading it again. You can also change the other Source Code definition properties: File Type, Sharable, Subtype, and classifications.
When you save your changes to a sharable Source Code definition, you have the option to find all instances of the original sharable Source Code and decide whether or not to update them to the new version of the Source Code. See "Upgrading One or More Instances from the Definition" for further information.
Parameters belong to the Program definition. You must check out the definition to add, remove, or update Parameters. See "Defining Parameters" for information.
You can also change some Parameter values and settings in Execution Setups. Select Execution Setups from the Actions drop-down list in the Program instance in the Work Area. See "Creating, Modifying, and Submitting Execution Setups".
Planned Outputs belong to the Program definition. You must check out the definition to add, remove, or update Planned Outputs. See "Defining Planned Outputs" for further information.
You can change Planned Outputs' classifications, which affect the classifications of the actual outputs. See "Classifying Outputs" for further information.
This section contains the following topics:
See also:
To use either Oracle SQL Developer or SQL*Plus to edit and compile your Oracle LSH PL/SQL Programs, do the following on your local PC:
Get the CD-ROM that contains the files cdrconfig.xml and cdrclient.exe from your system administrator and insert it into your PC. InstallShield automatically runs setup.exe, that loads cdrconfig.xml and cdrclient.exe to a location you specify on your local computer.
Ensure that cdrconfig.xml has the correct directory path for the Oracle SQL Developer or SQL*Plus executable.
Launching Oracle SQL Developer
When you click Launch IDE from an Oracle LSH PL/SQL Program, the following takes place:
If your Oracle LSH PL/SQL Program contains a Source Code instance with a PL/SQL package in it, then Oracle LSH compiles your PL/SQL package.
Note:
If there are any bugs in your PL/SQL package, Oracle SQL Developer fails to launch and an error message related to the bug appears on the Oracle LSH screen.Oracle LSH launches Oracle SQL Developer. Connect to the Oracle LSH database using your database user credentials.
Note:
If you cannot find an Oracle LSH database connection to which you can connect, you may have to set up the connection. Contact your System Administrator for more information.Table instances mapped to your Program's source Table Descriptors appear as synonyms in Oracle SQL Developer; for example, if the Oracle Name of a source Table descriptor is DEMOG then there will be a synonym by the name DEMOG in Oracle SQL Developer.
Table instances mapped to your Program's target Table Descriptors appear as empty tables in Oracle SQL Developer.
You can edit, compile, and execute your PL/SQL package in Oracle SQL Developer and these tables reflect the results of your data manipulation. However, the data is not written to Oracle LSH Table instances.
Note:
If you do not want to lose changes you made to the PL/SQL package in Oracle SQL Developer, you must upload your package back into the Oracle LSH PL/SQL Program's Source Code instance before you exit Oracle SQL Developer. See "Modifying Source Code" for instructions.Relaunching Oracle SQL Developer from within Oracle LSH If you relaunch Oracle SQL Developer from within Oracle LSH:
Oracle LSH overwrites the PL/SQL package in Oracle SQL Developer with the PL/SQL package contained in the Source Code instance of the Oracle LSH PL/SQL Program.
Oracle LSH overwrites any tables that exist in your database schema with the same name as Oracle LSH Tables mapped to the Oracle LSH PL/SQL Program.
Relaunching Oracle SQL Developer from outside Oracle LSH If you log in to Oracle SQL Developer from outside Oracle LSH, you do not get access to the source tables but you can edit the PL/SQL package.
To use SAS as an integrated development environment (IDE), do the following on your local PC:
Get the CD-ROM that contains the files cdrconfig.xml and cdrclient.exe from your system administrator and insert it into your PC. InstallShield automatically runs setup.exe, that loads cdrconfig.xml and cdrclient.exe to a location you specify on your local computer.
Install SAS on your PC in the location specified by your system administrator. The location must match the directory path specified in cdrconfig.xml.
Ensure that cdrconfig.xml has the correct directory path for the SAS executable.
Set the user preference for the SAS connection mode. See "Connecting to SAS".
Install any software required to support the preferred connection mode "Connecting to SAS".
To use Informatica IDE for creating mappings and workflows from within Oracle LSH, do the following on your local PC:
Get the CD-ROM that contains the files cdrconfig.xml and cdrclient.exe from your system administrator and insert it into your PC. InstallShield automatically runs setup.exe, that loads cdrconfig.xml and cdrclient.exe to Drive:\Program Files\Oracle\CDR.
Install the Informatica client on your PC in the location specified by your system administrator. The location must match the directory path specified in cdrconfig.xml.
Ensure that cdrconfig.xml has the correct directory path for the Informatica executable.
Create a system Environment Variable in Windows with the name INFA_DOMAINS_FILE and set its value to the full path of the domains.infa file; for example:
INFA_DOMAINS_FILE=drive:\Informatica\PowerCenter8.1.1\domains.infa
Set up a user Data Source Name (DSN) named LSHModel for your Oracle LSH database account. Oracle LSH imports source and target Tables from your database account into the Informatica folder using this DSN.
Consult Microsoft Windows online help for instructions on setting up ODBC Data Source Names.
|
 Copyright © 2006, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|