Windows Batch Scripting: Date format manipulation

Just a quick note on date manipulation.

So what does it mean when you see the following in a script:

set /a y=%date:~-4%
set /a d=%date:~07,2%
set /a m=%date:~-10,2%

easy as pie. First you need to know the date format you will be using:

echo %date%
Tue 10/15/2013

Now, here's what our code above means.
Set /a y - Of course, you set the value for y for year.
%date:~-4% - you call on the date as with the echo command earlier. But only display the value starting from -4 up to the last character. The negative value indicates that you should count from the last character of the date value. from here, -4 lands on the number 2. It will then display from that number up to the last which is 2013.

Set /a d - d is for day.
%date:~07,2% - Similar from the above, it calls the date and counts from the positive 7th character. The number 2 then indicates that you should read 2 characters starting from character 7. From our example, the value is 15.

set /a m - m for month, obviously :)
%date:~-10,2% - lastly, as another example, you count from the last digit up to the 10th character. Display 2 digits from the 10th character which is 10.

The example above shows that we can use either a negative or positive values in setting the date value on a variable.

Windows Batch Script Date format: Set day of the year

Unfortunately there is no direct way to do so. You would need to perform some computation after parsing the date. Below is the script you can use:

@echo off
setlocal disabledelayedexpansion

set /a y=%date:~-4%
set /a m=%date:~-07,2%
set /a d=%date:~-10,2%

set /a w= %d% +(!(%y% %% 4)-!(%y% %% 100)+!(%y% %% 400))*(!((%m%-3)^&16))
set /a w=(%w%+(%m%-1)*30+2*(!((%m%-7)^&16))-1+((65611044^>^>(2*%m%))^&3))
echo %w%

One thing to note about using SET /A command is that it treats its values as an octal notation. To easily explain this, below is an excerpt from one of my searches in google (forgot where, if you know, please comment below so I can credit appropriately.)

“SET /A has a very unfortunate feature in that any number that has a leading 0 is treated as octal notation. So a value of 08 or 09 is not a valid number for SET /A, and will give the error message that you cited. To get the correct value you must represent the MINS as 8 or 9 (no leading 0). Note that 00 through 07 work fine because both octal and decimal interpretation yield the same value.”

Here is the octal and decimal values:

Of course, you may simply remove the /A when you only want to get the values for y, m and d. But when using the 08 and 09 value in another date computation (eg. computing for the value of w above), they will still be interpreted as octal because of the SET /A used. To remedy the above issue, you can use:

Before:

set /a m=%date:~-07,2%

set /a d=%date:~-10,2%

Echoing  %m% - %d%  gives you 08 and 09.

After:

REM use the following to remove leading ZERO values:

set /a m=10000%date:~-07,2% %% 10000

set /a d=10000%date:~-10,2% %% 10000

Echoing  %m% - %d%  gives you 8 and 9.

Create DBLINK for Oracle Database

Easy as pie. Use this template:

create public database link <DB Link name> connect to <User account> identified by <Password> using '<Database SID>';

From Oracle's format in creating dblinks:

CREATE [ SHARED ] [ PUBLIC ] DATABASE LINK dblink
  [ CONNECT TO
    { CURRENT_USER
    | user IDENTIFIED BY password
      [ dblink_authentication ]
    }
  | dblink_authentication
  ]
  [ USING 'connect_string' ]

Or this one:

CREATE PUBLIC DATABASE LINK <INSTANCE><.WORLD> CONNECT TO <USER> IDENTIFIED BY <PASSWORD OF USER TO BE USED> USING '<CONNECT STRING usually INSTANCE>';


To verify your DB link, use:

select distinct db_link, created from dba_db_links where db_link='DB LINK NAME';

Common error when using your dblink is getting the ORA-02069 message. As per the following link:

http://www.oracle-developer.com/oracle-post/81/ORA_02069:_global_names_parameter_must_be_set_to_TRUE

ORA-02069: global_names parameter must be set to TRUE

If you get this error while trying to access a table over the database link.
This error happens when the database parameter global_names is set to TRUE.
When this is the case, the database link need to have the same name as the global name of the remote database (the one you are trying to connect to).

You can find out if global_names is on by using:
SQL> show parameter global_names

Find out the global name of the remote database. Log into the remote database and use:

SQL> select * from global_name;

GLOBAL_NAME
-------------------------------------------------------------------
> DB1.ORACLE-DEVELOPER.COM

Create the database link in the Oracle database appropriately:

SQL> create database link DB1 connect to scott identified by tiger using 'DB1'

Altering the password used by your DBLINK? Here are the steps:

1. Drop databae link:
    drop public database link <DBLINK name>;
    commit;

2. Recreate dblink:
    use any of the commands above.

I've even tried using the TNSNAMES entry - just because, although I am not sure if this has any negative impact. I'm thinking no since using the DB name will also use this entry. One drawback I can think of is the cluttered result of your query when selecting from dba_db_links:

Example -

$tnsping factfinder

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = fact-finder.blogspot.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = fact-finder.blogspot.com)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = factfinder) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))))
OK (30 msec)

The command to create the DBLINK is:

create public database link factfinder connect to <user> identified by <password> using '((DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = fact-finder.blogspot.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = fact-finder.blogspot.com)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = factfinder) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))))';

How is your PeopleSoft?

Like most peoplesoft practitioners, I am also aware of the seemingly dwindling opportunities we have when it comes to finding new jobs. Of course I'm from a country where we have only a few companies that has peoplesoft, and most of them are BPO/support work, so I do not really have that much visibility on the market.

So I want to know, if your a Peoplesoft practitioner, on what Peopletools and application version are you now? Are you looking to upgrade your version or are you thinking about moving to another platform?

Renaming Message Queues for Integration Broker in PeopleTools 8.5x

Here's a quick list of steps in renaming your Message Queues for Integration broker. The same steps can be performed when recreating the message queue, simply rename the original one to a another name for backup.

1. Stop/Inactivate the IB Domain.
2. Stop/Pause the queue in queue status page.

3. Archive / Delete all IB messages for this particular queue
        - This is important since it renaming the message queue is not allowed when there are messages under this queue. You may archive or delete all done and cancelled messages under this queue. To archive, use the delivered archiving process for Integration Broker. To delete the queues, use the delivered PSAPMSPURGELIVE.DMS file and append a "where queuename='your queue name'. Now, not all tables have a queuename field and I currently do not have a list of its content, but a sureway of checking is to replace all "delete from" with "select count(*) from" to let you know which fields will be used.

4. Go to service Operation and look for the service operation that this queue is associated with. Normally it has the same name with your queue name. dis-associate this queue name from the service operation by changing the value (including the version) to any other on your list. remember that your domain is currently disabled and we'll replace this with the correct one later on. save

5. Proceed in renaming the queue. Navigate to service administration and rename or delete your queue. Make sure you take a screenshot of it first if your planning to delete and re-create your message queue.
6. Recreate your message queue in the queue page. Note that you need to associate your queue to a service operation for the fields to be created. save
7. Go back to the service operation earlier and rename the queue you changed in step 4. save
8. Go back to queues list and update your queue to make it look as it was before. save

9. Start/Run the new queue in queue status page and make sure the old queue is paused.
10. Start/Activate your IB domain.

PeopleSoft Integration Broker Changes on newer PeopleTools

I know I said I'll be writing about Oracle VMs and PS templates, but I found this on my drafts folder and thought I should probably move it out here first. It's an old post that never got published, soo.. this is probably irrelevant now too since most people are using PeopleTools versions that are on 8.49 or higher, but just in case some one out there needs Integration Broker changes between 8.47 (and older) and 8.48 (and later), here you go:


Integration Broker's architecture had a major face lift when PeopleTools 8.48 came out. The following lists just some of the changes.

I will be adding more details to this list probably after the long holidays (Holy Week in my country).. I'm just so lazy lately. For now, here's a nifty little table that lists the changes, coutesy of a PDF file from peopletechgroup.com.
(EDIT 1 - Kim 9/2012: This was written at around 4/2012, never gotten around to doing this, guess I'm lazy always, LOL) 

Here's the link to the PDF file.

Just to add, one of the worst changes Oracle made wasa by replacing PUBID with Transaction ID. I'll write more about that sometime this week as well...maybe.
(EDIT 2 - Kim 9/2012: I have this on my drafts folder as well, I'll be posting that some other time. Oh the promises! :))

Oracle VM and PeopleSoft Templates

So.. lately i've been busy with Oracle VM and making PeopleSoft templates work in Virtual box. it's crazy. Being a tech guy, I prefer creating PS Environments manually. It seems less complicated. It IS less complicated.

I remember seeing this cartoon a while back which shows exactly what Oracle is doing with the PS Template.

Anyway after much fumbling, we finally managed to create an existing PS Environment with matching OEM. We used OVM version 2.2, I'm now fumbling my way to creating another PS environment with OVM version 3.1 (which is completely different from 2.2). Hopefully i can list the steps here so I won't have to go through all the fumbling again when I need to use OVM in the future.