Oracle PL/SQL: Making All Tables in a Schema Read-Only

This article covers preventing casual users from writing data to some tables within an Oracle Schema. This routine will prevent the user writing data to ALL tables, but you can then just remove the constraints from the tables that you wish the users to still write data to (i.e. tables that audit the user logging in).

Recently we wanted to migrate users from an old system to a new one giving them access to both systems at the same time but making the old system "read only". It's possible, in Oracle, to set either the whole Database or a complete Table Space but we discovered that when the users connected an audit record was created. We didn't want to change the code in the existing system but wanted the guarantee the users could not update any old data. The script we came up with is:

declare

  cursor c_Tables is

    select owner, table_name from all_tables where owner = '';

begin

  for v_Table in c_Tables loop

    dbms_output.put_line(v_Table.owner || '.' || v_Table.Table_Name);

    begin

      execute immediate 'ALTER TABLE ' || v_Table.owner || '.' ||

                        v_Table.Table_Name || ' ADD CONSTRAINT ' ||

                        substr(v_Table.Table_Name, 1, 27) ||

                        '_RO check(1=1) disable validate';

    exception

      when others then

        dbms_output.put_line(v_Table.owner || '.' || v_Table.Table_Name ||

                             sqlerrm);

    end;

  end loop;

end;

This script adds a constraint to each table (called _RO, for example the MASTER_BATCH_REC_HDR table would have a constraint called MASTER_BATCH_REC_HDR_RO. Of course if you use the full 30 characters for lots of your table names you might run into a problem if the first 27 characters are the same!

This constraint can never be evaluated so every insert/update will fail but select statements will still run.

Of course the problem with audit records is that you still have to insert them so after a few tries running the application (and reading the error messages) we worked out which tables to drop the existing constraint from in order to allow the audit records to be inserted but no more.

 

Noetix: Generating a (XU2) Column-Addition Script

In order to use the code below you need to replace the values for v_ViewLabel and v_ColumnLabel in the block with values that are relevant to the column you are trying to add. This script uses the standard DBMS_OUTPUT package to output the script - depending on the nature of the column you're copying this could be quite big.

If you want to include new tables in your column addition script then set the v_IncludeWhere clause to Y (otherwise only the new column script will be generated).

The script is below;

declare
  v_ViewLabel    n_view_column_templates.view_label%TYPE := 'GMF_Order_Details_Base'; -- Must be direct value from table, case sensitive!
  v_ColumnLabel  n_view_column_templates.column_label%TYPE := 'Location';
  v_IncludeWhere varchar2(1) := 'N'; -- Must be either Y or N
  v_ColumnCount  number := 1;

  v_HeaderSQL varchar2(4000);
  v_DataSQL   varchar2(4000);
  v_SQL       varchar2(4000);
  v_result    varchar2(4000);

  procedure AddToHeader(v_Text in varchar) as
  begin
    if length(v_HeaderSQL) > 100 then
      dbms_output.put_line(v_headerSQL);
      v_headerSQL := '  ';
    end if;
    v_headerSQL := v_headerSQL || v_Text;
  end;
begin
  dbms_output.put_line('@utlspon ' || lower(v_ViewLabel) || '_' ||
                       lower(v_ColumnLabel) || '_xu2');
  dbms_output.put_line('');
  dbms_output.put_line('COLUMN max_col NEW_VALUE max_col_position');
  dbms_output.put_line('');
  dbms_output.put_line('SELECT MAX(column_position) max_col');
  dbms_output.put_line('FROM   n_view_column_templates');
  dbms_output.put_line('WHERE  view_label = ''' || v_ViewLabel || ''';');
  dbms_output.put_line('');

  if (v_IncludeWhere = 'Y') then
    dbms_output.put_line('COLUMN max_where NEW_VALUE max_where_position');
    dbms_output.put_line('');
    dbms_output.put_line('SELECT MAX(where_clause_position) max_where');
    dbms_output.put_line('FROM   n_view_where_templates');
    dbms_output.put_line('WHERE  view_label = ''' || v_ViewLabel || ''';');
    dbms_output.put_line('');
    dbms_output.put_line('COLUMN max_from NEW_VALUE max_from_position');
    dbms_output.put_line('');
    dbms_output.put_line('SELECT MAX(from_clause_position) max_from');
    dbms_output.put_line('FROM   n_view_table_templates');
    dbms_output.put_line('WHERE  view_label = ''' || v_ViewLabel || ''';');
    dbms_output.put_line('');
 
    for v_Data in (select n.*, n.rowid
                     from n_view_table_templates n
                    where n.view_label = v_ViewLabel
                      and n.table_alias in
                          (select n.table_alias
                             from n_view_column_templates n
                            where n.view_label = v_ViewLabel
                              and n.column_label = v_ColumnLabel)) loop
      v_HeaderSQL := '  ';
      v_DataSQL   := '';
      dbms_output.put_line('INSERT INTO n_view_table_templates(');
      for v_Column in (select atc.column_name,
                              atc.data_type,
                              atc.column_id,
                              (select max(atc2.column_id)
                                 from all_tab_columns atc2
                                where atc2.owner = USER
                                  AND atc2.column_name not in
                                      ('INCLUDE_FLAG',
                                       'GEN_SEARCH_BY_COL_FLAG')
                                  and atc2.table_name = atc.table_name) as max_column_id
                         from all_tab_columns atc
                        where atc.owner = USER
                          and atc.table_name = 'N_VIEW_TABLE_TEMPLATES'
                          AND atc.column_name not in
                              ('INCLUDE_FLAG', 'GEN_SEARCH_BY_COL_FLAG')
                        order by atc.column_id) loop
        v_SQL := 'SELECT T.' || V_Column.Column_name ||
                 ' FROM N_VIEW_TABLE_TEMPLATES T WHERE T.ROWID = ''' ||
                 v_Data.rowid || '''';
        EXECUTE IMMEDIATE v_SQL
          into v_result;
        if instr(v_Result, '''') > 0 then
          v_Result := Replace(v_result, '''', '''''');
        end if;
        if v_Column.Column_Name = 'FROM_CLAUSE_POSITION' then
          v_result := '(&max_from_position + ' || to_char(v_ColumnCount) || ')';
        end if;
        if v_Column.Column_Name = 'LAST_UPDATE_DATE' then
          v_result := SYSDATE;
        end if;
     
        if (v_result is not null) then
          if (v_Column.Column_Id = v_Column.Max_Column_Id) then
            AddToheader(lower(V_Column.Column_name) || ')');
            v_DataSQL := v_DataSQL || case
                           when v_Column.Data_Type = 'VARCHAR2' then
                            '  ''' || v_result || ''') -- ' ||
                            lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'NUMBER' then
                            '  ' || v_result || ') -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'DATE' then
                            '  TO_DATE(''' || v_result || ''')) -- ' ||
                            lower(V_Column.Column_name)
                           else
                            '** ERROR **' || v_Result
                         end;
          else
            AddToHeader(lower(V_Column.Column_name) || ', ');
            v_DataSQL := v_DataSQL || case
                           when v_Column.Data_Type = 'VARCHAR2' then
                            '  ''' || v_result || ''', -- ' ||
                            lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'NUMBER' then
                            '  ' || v_result || ', -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'DATE' then
                            '  TO_DATE(''' || v_result || '''), -- ' ||
                            lower(V_Column.Column_name)
                           else
                            '** ERROR **' || v_Result
                         end || chr(13);
          end if;
        end if;
      end loop;
      dbms_output.put_line(v_headerSQL);
      dbms_output.put_line('VALUES(');
      dbms_output.put_line(v_DataSQL);
      dbms_output.put_line(';');
      v_ColumnCount := v_ColumnCount + 1;
      dbms_output.put_line('');
    end loop;
 
    v_ColumnCount := 1;
    for v_Data in (select n.*, n.rowid
                     from n_view_where_templates n
                    where n.view_label = v_ViewLabel
                      and n.where_clause like
                          '%' ||
                          (select q.table_alias
                             from n_view_column_templates q
                            where q.view_label = v_ViewLabel
                              and q.column_label = v_ColumnLabel
                              and q.QUERY_POSITION  = n.QUERY_POSITION) || '%') loop
      v_HeaderSQL := '  ';
      v_DataSQL   := '';
      dbms_output.put_line('INSERT INTO n_view_where_templates(');
      for v_Column in (select atc.column_name,
                              atc.data_type,
                              atc.column_id,
                              (select max(atc2.column_id)
                                 from all_tab_columns atc2
                                where atc2.owner = USER
                                  AND atc2.column_name not in
                                      ('INCLUDE_FLAG')
                                  and atc2.table_name = atc.table_name) as max_column_id
                         from all_tab_columns atc
                        where atc.owner = USER
                          and atc.table_name = 'N_VIEW_WHERE_TEMPLATES'
                          AND atc.column_name not in ('INCLUDE_FLAG')
                        order by atc.column_id) loop
        v_SQL := 'SELECT T.' || V_Column.Column_name ||
                 ' FROM N_VIEW_WHERE_TEMPLATES T WHERE T.ROWID = ''' ||
                 v_Data.rowid || '''';
        EXECUTE IMMEDIATE v_SQL
          into v_result;
        if instr(v_Result, '''') > 0 then
          v_Result := Replace(v_result, '''', '''''');
        end if;
        if v_Column.Column_Name = 'WHERE_CLAUSE_POSITION' then
          v_result := '(&max_where_position + ' || to_char(v_ColumnCount) || ')';
        end if;
        if v_Column.Column_Name = 'LAST_UPDATE_DATE' then
          v_result := SYSDATE;
        end if;
     
        if (v_result is not null) then
          if (v_Column.Column_Id = v_Column.Max_Column_Id) then
            AddToheader(lower(V_Column.Column_name) || ')');
            v_DataSQL := v_DataSQL || case
                           when v_Column.Data_Type = 'VARCHAR2' then
                            '  ''' || v_result || ''') -- ' ||
                            lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'NUMBER' then
                            '  ' || v_result || ') -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'DATE' then
                            '  TO_DATE(''' || v_result || ''')) -- ' ||
                            lower(V_Column.Column_name)
                           else
                            '** ERROR **' || v_Result
                         end;
          else
            AddToHeader(lower(V_Column.Column_name) || ', ');
            v_DataSQL := v_DataSQL || case
                           when v_Column.Data_Type = 'VARCHAR2' then
                            '  ''' || v_result || ''', -- ' ||
                            lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'NUMBER' then
                            '  ' || v_result || ', -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'DATE' then
                            '  TO_DATE(''' || v_result || '''), -- ' ||
                            lower(V_Column.Column_name)
                           else
                            '** ERROR **' || v_Result
                         end || chr(13);
          end if;
        end if;
      end loop;
      dbms_output.put_line(v_headerSQL);
      dbms_output.put_line('VALUES(');
      dbms_output.put_line(v_DataSQL);
      dbms_output.put_line(';');
      v_ColumnCount := v_ColumnCount + 1;
      dbms_output.put_line('');
    end loop;
  end if;
  v_ColumnCount := 1;
  for v_Data in (select n.*, n.rowid
                   from n_view_column_templates n
                  where n.view_label = v_ViewLabel
                    and lower(n.column_label) = lower(v_ColumnLabel)) loop
    v_HeaderSQL := '  ';
    v_DataSQL   := '';
    dbms_output.put_line('INSERT INTO n_view_column_templates(');
    for v_Column in (select atc.column_name,
                            atc.data_type,
                            atc.column_id,
                            (select max(atc2.column_id)
                               from all_tab_columns atc2
                              where atc2.owner = USER
                                AND atc2.column_name not in ('INCLUDE_FLAG')
                                and atc2.table_name = atc.table_name) as max_column_id
                       from all_tab_columns atc
                      where atc.owner = USER
                        and atc.table_name = 'N_VIEW_COLUMN_TEMPLATES'
                        AND atc.column_name not in ('INCLUDE_FLAG')
                      order by atc.column_id) loop
      v_SQL := 'SELECT T.' || V_Column.Column_name ||
               ' FROM N_VIEW_COLUMN_TEMPLATES T WHERE T.ROWID = ''' ||
               v_Data.rowid || '''';
      EXECUTE IMMEDIATE v_SQL
        into v_result;
      if instr(v_Result, '''') > 0 then
        v_Result := Replace(v_result, '''', '''''');
      end if;
      if v_Column.Column_Name = 'COLUMN_POSITION' then
        v_result := '(&max_from_position + ' || to_char(v_ColumnCount) || ')';
      end if;
      if v_Column.Column_Name = 'LAST_UPDATE_DATE' then
        v_result := SYSDATE;
      end if;
   
      if (v_result is not null) then
        if (v_Column.Column_Id = v_Column.Max_Column_Id) then
          AddToheader(lower(V_Column.Column_name) || ')');
          v_DataSQL := v_DataSQL || case
                         when v_Column.Data_Type = 'VARCHAR2' then
                          '  ''' || v_result || ''') -- ' ||
                          lower(V_Column.Column_name)
                         when v_Column.Data_Type = 'NUMBER' then
                          '  ' || v_result || ') -- ' || lower(V_Column.Column_name)
                         when v_Column.Data_Type = 'DATE' then
                          '  TO_DATE(''' || v_result || ''')) -- ' ||
                          lower(V_Column.Column_name)
                         else
                          '** ERROR **' || v_Result
                       end;
        else
          AddToHeader(lower(V_Column.Column_name) || ', ');
          v_DataSQL := v_DataSQL || case
                         when v_Column.Data_Type = 'VARCHAR2' then
                          '  ''' || v_result || ''', -- ' ||
                          lower(V_Column.Column_name)
                         when v_Column.Data_Type = 'NUMBER' then
                          '  ' || v_result || ', -- ' || lower(V_Column.Column_name)
                         when v_Column.Data_Type = 'DATE' then
                          '  TO_DATE(''' || v_result || '''), -- ' ||
                          lower(V_Column.Column_name)
                         else
                          '** ERROR **' || v_Result
                       end || chr(13);
        end if;
      end if;
    end loop;
    dbms_output.put_line(v_headerSQL);
    dbms_output.put_line('VALUES(');
    dbms_output.put_line(v_DataSQL);
    dbms_output.put_line(';');
 
    v_ColumnCount := v_ColumnCount + 1;
    dbms_output.put_line('');
  end loop;

  dbms_output.put_line('COMMIT;');
  dbms_output.put_line('');
  dbms_output.put_line('@utlspoff');

end;

Refreshing AppV Application List

Sometimes new/ updated software is deployed during a business day and you want to have access immediately without rebooting. This purpose of this blog post is to show end-users the simple steps needed to refresh their applications.

The instructions given in this blog post are aimed at end-users.

Locate the "Show Hidden Icons" button at the bottom right of your task bar;

Left-click the icon to show all the "hidden" icons.

Next locate the "Microsoft Application Virtualization Desktop Client" icon, this is typically box-like and yellow;

Right-click this icon;

Select "Refresh Applications".

The refresh will typically take a few seconds but any new or updated applications will then appear under your start menu.

Noetix: Removing Obsolete Columns at R12

This blog post includes a script-generating piece of PL/SQL that will write a series of scripts for you that will remove all the obsolete columns from your Noetix Views following a R12 upgrade.

For example if you look at the Finance views for Payables then you will see the column "Set_Of_Books_Name". Oracle have replaced sets of books in R12 so this column is no longer relevant. Noetix, rather than removing the column, have changed it so that rather than displaying data it just displays the results of a TO_CHAR(NULL) function call - i.e. Nothing.

If you speak to Noetix they will tell you that this allows your code to work across versions *however* in our experience of an R12 upgrade all this allowed was code that needed re-pointing to the new structures to *appear* to work. In the specific case of significant change like this experience has shown me that it's better to have everything collapsing in a big heap than appearing to work when it doesn't!

The following SQL detects the new "obsolete" columns at R12;

select n.view_label, n.column_label, n.query_position
  from n_view_column_templates n
 where n.column_expression like '%(NULL)%'
   and n.include_flag = 'Y'
   and n.product_version like '12%'
   and not exists (select 1
          from n_view_column_templates t
         where t.column_expression not like '%(NULL)%'
           and t.include_flag = 'Y'
           and t.product_version like '12%'
           and t.view_label = n.view_label
           and t.column_label = n.column_label)
 order by n.view_label, n.column_label, n.query_position

When you run it it will give you some idea of the extent of your problem (which will obviously be more significant the more you use oracle - for us this query returned move than 5,000 rows).

For every record returned by this query the script will generate output. In most cases there is a single query for each of the columns so you will see something like;

@utlspon ap_checks_set_of_books_name_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8',
       last_update_date = TO_DATE('17-NOV-2011'),
       last_updated_by = 'A Pellew'
 WHERE UPPER(view_label) = UPPER('AP_Checks')
   AND UPPER(column_label) = UPPER('Set_Of_Books_Name')
   AND QUERY_POSITION = 1
   AND PRODUCT_VERSION LIKE '12%'
;
 
COMMIT;
 
@utlspoff
----------------------------------------

In this case this is updating the view template AP_Checks, and moving the product_version of the "Set_Of_Books_Name" column back to version 8 - this will prevent it being picked up during a regenerate.

In the case of multiple queries the script will generate something similar to;

@utlspon ap_invoice_distributions_posted_amount_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8',
       last_update_date = TO_DATE('17-NOV-2011'),
       last_updated_by = 'A Pellew'
 WHERE UPPER(view_label) = UPPER('AP_Invoice_Distributions')
   AND UPPER(column_label) = UPPER('Posted_Amount')
   AND QUERY_POSITION = 4
   AND PRODUCT_VERSION LIKE '12%'
;
UPDATE n_view_column_templates
   SET product_version = '8',
       last_update_date = TO_DATE('17-NOV-2011'),
       last_updated_by = 'A Pellew'
 WHERE UPPER(view_label) = UPPER('AP_Invoice_Distributions')
   AND UPPER(column_label) = UPPER('Posted_Amount')
   AND QUERY_POSITION = 5
   AND PRODUCT_VERSION LIKE '12%'
;
 
COMMIT;
 
@utlspoff
----------------------------------------

This is removing the column "Posted_Amount" from the "AP_Invoice_Distributions" template where it appears in the 4th and 5th query positions.

The script is given below;

declare
  v_OldViewLabel   n_view_column_templates.view_label%TYPE := '@';
  v_OldColumnLabel n_view_column_templates.column_label%TYPE := '@';

  v_LastUpdateDate n_view_column_templates.last_update_date%TYPE := SYSDATE;
  v_LastUpdatedBy  n_view_column_templates.last_updated_by%TYPE := 'A Pellew';
begin
  for v_Data in (select n.view_label, n.column_label, n.query_position
                   from n_view_column_templates n
                  where n.column_expression like '%(NULL)%'
                    and n.include_flag = 'Y'
                    and n.product_version like '12%'
                    and not exists
                  (select 1
                           from n_view_column_templates t
                          where t.column_expression not like '%(NULL)%'
                            and t.include_flag = 'Y'
                            and t.product_version like '12%'
                            and t.view_label = n.view_label
                            and t.column_label = n.column_label)
                  order by n.view_label, n.column_label, n.query_position) loop
    if v_Data.view_label <> v_OldViewLabel or
       v_Data.column_label <> v_OldColumnLabel then
      if v_OldViewLabel <> '@' then
        dbms_output.put_line(' ');
        dbms_output.put_line('COMMIT; ');
        dbms_output.put_line(' ');
        dbms_output.put_line('@utlspoff ');
        dbms_output.put_line(LPAD('-', 40, '-'));
      end if;
      dbms_output.put_line('@utlspon ' || lower(v_Data.view_label) || '_' ||
                           lower(v_Data.column_label) || '_upd_xu2 ');
      v_OldViewLabel   := v_Data.view_label;
      v_OldColumnLabel := v_Data.column_label;
      dbms_output.put_line(' ');
    end if;
    dbms_output.put_line('UPDATE n_view_column_templates ');
    dbms_output.put_line('   SET product_version = ''8'', ');
    dbms_output.put_line('       last_update_date = TO_DATE(''' ||
                         TO_CHAR(v_LastUpdateDate, 'DD-MON-YYYY') ||
                         '''), ');
    dbms_output.put_line('       last_updated_by = ''' || v_LastUpdatedBy ||
                         ''' ');
    dbms_output.put_line(' WHERE UPPER(view_label) = UPPER(''' ||
                         v_Data.view_label || ''') ');
    dbms_output.put_line('   AND UPPER(column_label) = UPPER(''' ||
                         v_Data.column_label || ''') ');
    dbms_output.put_line('   AND QUERY_POSITION = ' ||
                         TO_CHAR(v_Data.Query_Position));
    dbms_output.put_line('   AND PRODUCT_VERSION LIKE ''12%'' ');
    dbms_output.put_line(';');
  end loop;
  dbms_output.put_line(' ');
  dbms_output.put_line('COMMIT; ');
  dbms_output.put_line(' ');
  dbms_output.put_line('@utlspoff ');
  dbms_output.put_line(LPAD('-', 40, '-'));
end;

You should change "A Pellew" at the top to be your own name!

NOTE: Two files are generated in error (due to the base data from Noetix not following their own standards - or at least not following any standards they tell developers to follow!). The two files (in our installation, there might be others in yours) are;

ar_std_rcpt_dist_sla_gl_je_line_item_number_upd_xu2.sql (Receivables)
fa_adjustments_sla_gl_je_acct$_upd_xu2.sql (Fixed Assets)

When you run your regenerate after adding all the files errors quickly show up. Just stop using files which prevent your regenerate from working (did that need saying?!). If you consider that we added almost 500 files finding 2 with errors is a pretty good error rate - imagine trying to write them all by hand.

SSRS: Displaying Values in a "Allow Multiple Values" Parameter as a String

This post provides two simple formulas; the first to provide something you could use in a page header (a summary of the selected values) and the other to provide a complete list of the selected values - something you could use on the final page of a report.

Let's assume you have a fairly simple dataset that contains the years 1980 to 2011. You have a parameter that uses this dataset as the source for it's "Available Values" list and allows the user to multi-select years.

Now to be helpful to your users you echo back to them the parameters they used to run the report in the reports output (always a good idea; gives helpdesk something to work with when the user wants to report a problem!).

The following block of code will convert the list of the users selected years into a English string that can be displayed;

=IIF(
  Count(Fields!KEY.Value, "LIST_YEARS") =
        Parameters!YEARLIST.Count, "All",
    IIF(
       Parameters!YEARLIST.Count = 1,
       Join(Parameters!YEARLIST.Label, ""),
       IIF(
         Parameters!YEARLIST.Count > 10,
         "Multiple",
         Replace(
           Left(
             Join(
               Parameters!YEARLIST.Label, "@@@@@"),
             InStrRev(
               Join(
                 Parameters!YEARLIST.Label,
                   "@@@@@"),
               "@@@@@") - IIF( Parameters!YEARLIST.Count < 2, 0, 1)),
           "@@@@@",
           ", ") + ", or " +
           Parameters!YEARLIST.Label(
             Parameters!YEARLIST.Count-1))))

This assumes your parameter is called YEARLIST and the dataset you are using for lookups is called LIST_YEARS and has the key field KEY.

The output follows a few simple rules; If the user has selected all records in the drop down display "All", if they have selected more than 10 items show "Multiple", otherwise display the items comma-separated and between the final two items replace the comma with ", or ".

Assuming you just want the list of all values (for the report footer) then you can use;

=IIF(Parameters!YEARLIST.Count = 1, Parameters!YEARLIST.Label(0), Replace(
    Left(
        Join(
            Parameters!YEARLIST.Label, "@@@@@"),
            InStrRev(
                Join(
                    Parameters!YEARLIST.Label,
                    "@@@@@"),
                "@@@@@") -
            IIF( Parameters!YEARLIST.Count < 2, 0, 1)),
    "@@@@@",
    ", ") +
    ", or " +
    Parameters!YEARLIST.Label( Parameters!YEARLIST.Count-1))

This will provide you with a complete list of the years selected.