Table Types for Physical Tables

The Table Type drop-down list in the General tab of the Physical Table dialog box allows you to specify the physical table object type. Table 12 provides a description of the available object types.

Table 12. Table Type Descriptions for Physical Tables

Table Type	Description
Physical Table	Specifies that the physical table object represents a physical table.
Stored Proc	Specifies that the physical table object is a stored procedure. When you select this option, you type the stored procedure in the text box. Requests for this table will call the stored procedure. For stored procedures that are database specific, select the Use database specific SQL check box. At run time, if a stored procedure has been defined, the stored procedure will be executed; otherwise, the default configuration will be executed. NOTE:  Stored procedures using an Oracle database do not return result sets. For more information, refer to Using Stored Procedures with an Oracle Database. For information about stored procedures and alias tables, see About Physical Alias Tables.
Select	Specifies that the physical table object is a Select statement. When you select this option, you type the select statement in the text field and you need to manually create the table columns. The column names must match the ones specified in the Select statement. Column aliases are required for advanced SQL functions, such as aggregates and case statements. Requests for this table will execute the Select statement. For Select statements that are database specific, select the Use database specific SQL check box. At run time, if a Select statement has been defined, the Select statement will be executed; otherwise, the default configuration will be executed.

Using Stored Procedures with an Oracle Database

Stored Procedures within Oracle do not return result sets. Therefore they cannot be initiated from within Oracle BI. You need to rewrite the procedure as an Oracle function, use it in a select statement in the Administration Tool initialization block, and then associate it with the appropriate Oracle BI session variables in the Session Variables dialog box.

The function uses the GET_ROLES function and takes a user Id as a parameter and returns a semi-colon delimited list of group names.

The following is an example of an initialization SQL string using the GET_ROLES function that is associated with the USER, GROUP, DISPLAYNAME variables:

select user_id, get_roles(user_id), first_name || ' ' || last_name

from csx_security_table

where user_id = ':USER' and password = ':PASSWORD'