Postgresql Function. Procedural script

Postgresql can be use with procedural call also ..

example as below.

CREATE FUNCTION update_aqsense_id (v_oldid varchar, v_newid varchar, vlayer varchar,geortd_object_type varchar)
returns setof varchar AS $$
DECLARE
nismos RECORD;
nismos_id integer;
nismos_aq varchar;
nismos_aqformat varchar;
nismos_sendonconnect varchar;
nismos_sendonhide varchar;
nismos_sendondisplay varchar;
station_id integer NOT NULL DEFAULT 0;
new_aq varchar;
new_aqformat varchar;
new_sendonconnect varchar;
new_sendonhide varchar;
new_sendondisplay varchar;
-- this function internal variable
status varchar;
BEGIN
IF geortd_object_type = 'label' THEN
FOR nismos IN select label_id,aqsense_id,aqsense_format,sendonconnect,sendondisplay,sendonhide from geortd_label where label_name ILIKE vlayer ORDER BY label_id LOOP
-- Temporary set the variable from old one.
nismos_id := nismos.label_id;
nismos_aq := nismos.aqsense_id;
nismos_aqformat := nismos.aqsense_format;
nismos_sendonconnect := nismos.sendonconnect;
nismos_sendondisplay := nismos.sendondisplay;
nismos_sendonhide := nismos.sendonhide;
station_id := station_id +1;
-- Replacing with new product_id
new_aq := regexp_replace (nismos_aq,v_oldid,v_newid);
new_aqformat := regexp_replace (nismos_aqformat,v_oldid,v_newid);
new_sendonconnect := regexp_replace (nismos_sendonconnect,v_oldid,v_newid);
new_sendonhide := regexp_replace (nismos_sendonhide,v_oldid,v_newid);
new_sendondisplay := regexp_replace (nismos_sendondisplay,v_oldid,v_newid);
UPDATE geortd_label SET aqsense_id = new_aq ,aqsense_format = new_aqformat , sendonconnect = new_sendonconnect, sendonhide = new_sendonhide , sendondisplay = new_sendondisplay where label_id = nismos_id;
-- Return the status.
status := 'updated nismos(' || station_id || ' ) at record number : ' || nismos_id || ' \n';
RETURN NEXT status;
END LOOP;
END IF;

-- windindicator updater
IF geortd_object_type = 'windindicator' THEN
FOR nismos IN select windindicator_id,aqsense_id,sendonconnect,sendondisplay,sendonhide from geortd_windindicator where windindicator_name ILIKE vlayer ORDER BY windindicator_id LOOP
-- Temporary set the variable from old one.
nismos_id := nismos.windindicator_id;
nismos_aq := nismos.aqsense_id;
nismos_sendonconnect := nismos.sendonconnect;
nismos_sendondisplay := nismos.sendondisplay;
nismos_sendonhide := nismos.sendonhide;
station_id := station_id +1;
-- Replacing with new product_id
new_aq := regexp_replace (nismos_aq,v_oldid,v_newid);
new_sendonconnect := regexp_replace (nismos_sendonconnect,v_oldid,v_newid);
new_sendonhide := regexp_replace (nismos_sendonhide,v_oldid,v_newid);
new_sendondisplay := regexp_replace (nismos_sendondisplay,v_oldid,v_newid);
-- Update the table
UPDATE geortd_windindicator SET aqsense_id = new_aq , sendonconnect = new_sendonconnect, sendonhide = new_sendonhide , sendondisplay = new_sendondisplay where windindicator_id = nismos_id;
-- Return the status.
status := 'updated nismos(' || station_id || ' ) at record number : ' || nismos_id || ' \n';
RETURN NEXT status;
END LOOP;
END IF;
-- image table updater.
IF geortd_object_type = 'image' THEN
FOR nismos IN select image_id,aqsense_id,sendonconnect,sendondisplay,sendonhide from geortd_image where image_name ILIKE vlayer ORDER BY image_id LOOP
-- Temporary set the variable from old one.
nismos_id := nismos.image_id;
nismos_aq := nismos.aqsense_id;
nismos_sendonconnect := nismos.sendonconnect;
nismos_sendondisplay := nismos.sendondisplay;
nismos_sendonhide := nismos.sendonhide;
station_id := station_id +1;
-- Replacing with new product_id
new_aq := regexp_replace (nismos_aq,v_oldid,v_newid);
new_sendonconnect := regexp_replace (nismos_sendonconnect,v_oldid,v_newid);
new_sendonhide := regexp_replace (nismos_sendonhide,v_oldid,v_newid);
new_sendondisplay := regexp_replace (nismos_sendondisplay,v_oldid,v_newid);
-- Update the table
UPDATE geortd_image SET aqsense_id = new_aq , sendonconnect = new_sendonconnect, sendonhide = new_sendonhide , sendondisplay = new_sendondisplay where image_id = nismos_id;
-- Return the status.
status := 'updated nismos(' || station_id || ' ) at record number : ' || nismos_id || ' \n';
RETURN NEXT status;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql;

to use.. just need one liner each time..


SELECT * from update_aqsense_id ('1050','105050','LabelRainHourly%','label');

how nice..

Related Post

6 Responses

  1. namran says:

    the following item also appear..

    ORA-00257:
    archiver error. Connect internal only, until freed.

    Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

    can either increase the size with the above said post.

    or ..

    DELETE archivelog ALL device TYPE disk;

    in rman frees your archive log from unneeded data and fixes this problem,

    until it’s full again.

    Reply
  2. namran says:

    some RMAN detail can be obtained from here.

    http://www.oracle-base.com/articles/11g/RmanEnhancements_11gR1.php

    and for the flash_recovery_area
    further reading ..

    http://www.ucertify.com/article/the-flash-recovery-area.html

    Reply
  3. namran says:

    Setting up the flash recovery area using SQL commands:

    Following are the two-initialization parameters that should be defined in order to set up the flash recovery area:

    * DB_RECOVERY_FILE_DEST_SIZE
    * DB_RECOVERY_FILE_DEST

    Reason to define the above mentioned parameters:
    The DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST are defined to make the flash recovery area usable without shutting down and restarting the database instance.

    Note: DB_RECOVERY_FILE_DEST_SIZE is defined before DB_RECOVERY_FILE_DEST in order to define the size of the flash recovery area.
    The size of the flash recovery area should be large enough to hold a copy of all data files, all incremental backups, online redo logs, archived redo log not yet backed up on tape, control files, and control file auto backups. The following table shows the various estimated sizes for all the database elements in the Flash Recovery area:

    Configuring DB_RECOVERY_FILE_DEST_SIZE

    ALTER SYSTEM
    SET db_recovery_file_dest_size = 10g scope = BOTH;

    In the above example the size of the flash recovery area is 10GB and the initialization parameter takes effect immediately and stays in effect even after the database restarts.

    The role of the DB_RECOVERY_FILE_DEST parameter is to specify the physical location where all the flash recovery files are to be stored.

    An example of how this parameter specifies the location is given below:

    ALTER SYSTEM
    SET db_ recovery_file_dest =/OFR1? scope = BOTH;

    In the above example OFR1 is the name of the directory that has been specified as the physical location where all the flash recovery files are stored in the flash recovery area.

    Note:

    * If the value specified in the db_recovery_file_dest parameter is cleared then as a result the flash recovery area is disabled.
    * DB_RECOVERY_FILE_DEST_SIZE parameter cannot be cleared up prior to the DB_RECOVERY_FILE_DEST parameter.

    Dynamically Defining the Flash Recovery Area

    ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2G SCOPE=BOTH
    ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = 'C:ORACLERECOVERY_AREA' SCOPE=BOTH

    You must always specify the size parameter before specifying the location parameter.
    Disabling the Current Flash Recovery Area

    ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ''

    Note: even after you disable the flash recovery area, the RMAN will continue to access the files located in the flash recovery area for backup and recovery purposes.

    Default File Location and the Flash Recovery Area
    The initialization parameters DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n determine the location of all OMF files.
    Control Files
    If you haven’t set the CONTROL_FILES parameter, Oracle will create the control files in various default locations, according to the following rules:

    • If you specify the DB_CREATE_ONLINE_LOG_DEST_n parameter, Oracle will create an OMF-based control file in n number of locations, with the first directory holding the primary control file.
    • If you specify the DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST parameters, Oracle will create an OMF based control file in both of these locations.
    • If you just specify the DB_RECOVERY_FILE_DEST parameter, Oracle will create an OMF-based control file in the flash recovery area only.
    • If you omit all three of the initialization parameters, Oracle will create a non-OMF-based control file in the system-specific default location.
    Note: If the database creates an OMF control file, and it is using a server parameter file, then the database sets the CONTROL_FILES initialization parameter in the server parameter file.
    Redo Log Files

    If you omit the LOGFILE clause during database creation, Oracle will create the redo log files according to the same rules as mentioned above.
    Backing Up the Flash Recovery Area
    In order to back up the flash recovery area itself using RMAN, you must set CONFIGURE BACKUP OPTIMIZATION to ON.
    You can back up the flash recovery area only to a tape device using these backup commands:

    BACKUP RECOVERY AREA

    o This command backs up all flash recovery files in the current or previous flash recovery area destinations.
    o It backs up only those files that have never been backed up to tape before.
    o The files that the command will back up include full backups, incremental backups, control file autobackups, archive logs, and datafile copies.

    BACKUP RECOVERY FILES

    This command backs up all the files that the BACKUP RECOVERY AREA command does, but from all areas on your file system, not just from the flash recovery area.

    BACKUP RECOVERY FILE DESTINATION

    Use this command to move disk backups created in the flash recovery area to tape.
    Note: Neither of the two commands, BACKUP RECOVERY AREA or BACKUP RECOVERY FILES, will back up any permanent files or the flashback logs in the flash recovery area.

    Managing the Flash Recovery Area
    Space Management

    If you ever receive the out-of-space warning (85) and critical alerts (97) because of space pressure in you flash recovery area, you have the following options:
    o Consider changing your backup retention and archive log retention policies.
    o Increase the size of the DB_RECOVERY_FILE_DEST_SIZE parameter to allocate more space to your current flash recovery area.
    o Use the BACKUP RECOVERY AREA command in the RMAN to back up the contents of the flash recovery area to a tape device.
    o Use the RMAN to delete unnecessary backup files. The RMAN commands CROSSCHECK and DELETE EXPIRED come in handy during this deletion process.

    Data Dictionary Views

    V$RECOVERY_FILE_DEST
    This view is the main source and contains the following columns:

    SPACE_LIMIT :how much space has been allocated to the flash recovery area

    SPACE_USED :space occupied

    SPACE_RECLAIMABLE :how much space you can reclaim by getting rid of obsolete and redundant files in the flash recovery area.
    NUMBER_OF_FILES :number of files

    V$FLASH_RECOVERY_AREA_USAGE

    This view provides information about the flash recovery area disk space usage. Following is its main columns:

    FILE_TYPE :the type of the file and can have any of the following values: controlfile, onlinelog, archivelog, backuppiece, imagecopy, flashbacklog

    PERCENT_SPACE_USED :This represents the disk space used by the file type, in percentage.

    PERCENT_SPACE_RECLAIMABLE :this represents the percentage of disk space reclaimable from the file type after deleting any obsolete or redundant files, and files backed up to a tertiary device.

    Flash Recovery Area Columns in Other Views

    The Yes/No column IS_RECOVERY_DEST_FILE is added to some dictionary views to indicate whether the file was created in the flash recovery area. It exists in V$CONTROLFILE, V$LOGFILE, V$ARCHIVED_LOG, V$DATAFILE_COPY, V$BACKUP_PIECE.
    Moving the Flash Recovery Area

    ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/app/oracle/new_area' SCOPE=BOTH

    Eventually, Oracle will delete all the transient files from the previous flash recovery area location, when each of them become eligible for deletion. However, if you want to move your current permanent files, transient files, or flashback logs to the new flash recovery area, you can do so by using the standard file-moving procedures.

    Reply
  4. namran says:

    if failed to stop/start for whatever reason
    try :

    shutdown abort;

    startup nomount;

    Reply
  5. ceiling fans with lights and remote says:

    Ceiling fans with lights offers users the option to light up the room
    and gives a decorative look to it. Therefore, environment conscious people often encourage the use of ceiling fans with lights.
    Depending on the homeowner’s choice, they can either have up lights or down lights in them.

    Reply
  6. boobs adult social network adult social network says:

    Heya i am for the first time here. I found this board and I
    find It truly useful & it helped me out much. I hope to present one thing
    again and aid others such as you aided me.

    Reply

Leave a Reply to ahstod Cancel reply

Your email address will not be published. Required fields are marked *