Random Inspiration

How to use "OUTPUT" with Update and Delete (Cascade delete in same table)

2016-07-21T07:55:00.001-07:00

Hi fellas,

Today I'm going to discuss how to do a cascade delete on a record set not stored in two tables (parent table and child table) but in a single table.

Let's imaging we have two tables Set_Folder and Set_Article. The database concept is that the folders in the system will be stored under Set_Folder table and the articles in those folders stored under Set_Article table. Don't be fussy, I'm applying cascade delete for one single table, not parent and child table. When it comes to between parent and child table, it's pretty much simple and straight forward but when it comes to a single table the code will be bit different and complex. Not much complex but not straight forward.

Set_Folder table :-

FolderId FolderName ParentId IsDeleted IsParentDeleted
   1 folder 1 0 0 0
2 folder 2 1 0 0
3 folder 3 1 0 0
4 folder 4 2 0 0
5 folder 5 4 0 0
6 folder 6 0 0 0
7 folder 7 6 0 0
8 folder 8 0 0 0

According to the table structure root folders are the folders which has parentid as 0. All the other folders are sub folders. Therefor folder 1, folder 6 and folder 8 are root folders and all the others are sub folders.
The requirement is to delete an specific root folder with it's sub folders. Let's say user needs to delete folder 1. folder 2, folder 3, folder 4 and folder 5 should be deleted along with it since those are sub folders in folder 1.

To do this we have to use WITH key word along with OUTPUT key word.
This is how we gonna do it.

WITH q AS
(
SELECT Folderid
FROM Set_Folder
WHERE Folderid = 1
UNION ALL
SELECT tc.Folderid
FROM q
JOIN Set_Folder tc
ON tc.parentID = q.Folderid
)
UPDATE Set_Folder
SET IsDeleted = '1'
OUTPUT DELETED.*
WHERE EXISTS
(
SELECT Folderid
INTERSECT
SELECT Folderid
FROM q
)

In here we are doing a self join. I think you all are familiar with DELETED and INSERTED table. Those are like virtual tables and we will discuss about them in a separate section.

After running this query the table data set will be like,

FolderId FolderName ParentId IsDeleted  IsParentDeleted
   1 folder 1 0 1 1
2 folder 2 1 1 1
3 folder 3 1 1 1
4 folder 4 2 1 1
5 folder 5 4 1 1
6 folder 6 0 0 0
7 folder 7 6 0 0
8 folder 8 0 0 0

Let's say these folders have articles in them. The Set_Article table is like this.

ArticleId FolderId ArticleName IsDeleted
1 1 A1 0
2 1 A2 0
3 2 A3 0
4 3 A4 0
5 4 A5 0
6 5 A6 0
7 6 A7 0
8 7 A8 0

Requirement is to delete the root folder 1 and all it's sub folders and all the articles on those tables. Now we know how to delete the folders. Next step is to get the folder ids we deleted and delete all the articles in those folders. We can achieve this with below query.

WITH q AS
(
SELECT Folderid
FROM Set_Folder
WHERE Folderid = @FolderId
UNION ALL
SELECT tc.Folderid
FROM q
JOIN Set_Folder tc
ON tc.parentID = q.Folderid
)

UPDATE Set_Folder
SET IsParentDeleted = 1, IsDeleted = 1
OUTPUT DELETED.Folderid,
inserted.Folderid
INTO @table
WHERE EXISTS
(
SELECT Folderid
INTERSECT
SELECT Folderid
FROM q
)
AND IsDeleted = 0

What we are doing here is deleting the folders and insert those folder ids into a temporary table. Then we can simply get those deleted folder ids from the temporary table and delete relevant articles.

Implement Ranking in MySql

2015-12-31T02:55:00.002-08:00

Hi falks,

Recently i had a task to create a procedure to return certain data set and to fulfill the task as exactly as client wants, need to have rank to the data set. I have data related to the room prices in hotels and i need to get the best valued hotels (Most cheapest hotels regardless of room type). For this scenario i had to implement a rank value to the data set and then get the records which has 1 as the rank value. Mysql doesn't have inbuilt ranking functions like MsSql. Therefore i had to implement a workaround to do that. So this is what i did,

Note :- I used a temporary table to hold the data and then apply the rank to those data set and then select the relevant data. There is a limitation in mysql regarding temporary table. We cannot open a temporary table more than once in a same session. But in my solution I had to do a self join to that temporary table and i got stuck because of it. The solution i used is to have two temporary tables which held exact same data set and then join those two.

SET @sqll = CONCAT('SELECT HotelID, HotelName, Category, Slug, DisplayName, DisplayAddress, Address1, Address2, CityName, Country, RoomTypeID, PackageId, Popularity,
BasicTotal, Discount, Total, ThumbnailPath, PicturePath, PackageName, PromotionTag, x.RANK
FROM (SELECT f.HotelID, f.HotelName, f.Category, f.Slug, f.DisplayName, f.DisplayAddress, f.Address1, f.Address2, f.CityName, f.Country, f.RoomTypeID,
f.PackageId, f.Popularity, f.BasicTotal, f.Discount, f.Total, f.ThumbnailPath, f.PicturePath, f.PackageName, f.PromotionTag,
(SELECT 1 + COUNT(*)
FROM FinalDataSet_2 f1
WHERE f1.HotelId = f.HotelId
AND f1.Total < f.Total) RANK
FROM FinalDataSet_1 AS f) AS x
WHERE x.RANK = 1
GROUP BY HotelID, Total
ORDER BY',@v_Orderby,' LIMIT ',v_offset,', ',v_Limit);

How to update certain node values from set of node values in XML type table column value in sql

2015-06-16T00:51:00.000-07:00

Hi folks,

I have recently got task to create a SQL procedure which could update 2 specific node values of an xml column value in a table. Original column has 7 to 10 node value sets depending on the user type (in my scenario, it's employee type and student type). I'm writing this article is to put all the needed steps to one place because it will make easier for someone to find it in here rather than going through number of pages and links for each and every part.

The xml column value is some what like this,

<attributes>
<attribute>
<code>EMPID</code>
<type>System.Int32</type>
<value>0477348</value>
</attribute>
<attribute>
<code>EMPEID</code>
<type>System.Int32</type>
<value>19245866</value>
</attribute>
<attribute>
<code>EMPBADGEID</code>
<type>System.Int32</type>
<value>15354</value>
</attribute>
<attribute>
<code>EMPCF</code>
<type>CMB</type>
<value>1</value>
</attribute>
<attribute>
<code>ENTITY</code>
<type>CMB</type>
<value>4</value>
</attribute>
<attribute>
<code>JOBTITLE</code>
<type>System.String</type>
<value>Executive Director</value>
</attribute>
<attribute>
<code>DEPARTMENT</code>
<type>CMB</type>
<value>Transportation Parking & Traffic</value>
</attribute>
<attribute>
<code>DEPARTMENTLOCATOIN</code>
<type>CMB</type>
<value>29</value>
</attribute>
<attribute>
<code>MOBILENO</code>
<type>System.String</type>
<value>9097300644</value>
</attribute>
</attributes>

I have to update above green colored values with the given two values according to the given userid. The approach i used is inserting the <code> value, <type> value and <value> value to a temporary table and then update the relevant columns with the given values and then replace the original table value with the updated values in the temporary table.

First step is to insert the node values to separate columns in a temporary table and i found how to do it via web.

DECLARE @tempTable TABLE (
id int identity(1,1) not null,
code VARCHAR(500),
type VARCHAR(500),
value VARCHAR(500))

Since the table column is xml, I had to convert it to a varchar value.

DECLARE @xml XML
SELECT @xml= CAST(userAttributes AS VARCHAR(MAX)) FROM op_tb_trn_user WHERE userid = @userid

Then insert it to a temporary table.

INSERT INTO @tempTable
SELECT
Tbl.Col.value('code[1]', 'varchar(500)'),
Tbl.Col.value('type[1]', 'varchar(500)'),
Tbl.Col.value('value[1]', 'varchar(500)')
FROM @xml.nodes('attributes/attribute') Tbl(Col)

Run a select statement on your temporary table and the output will be something like this,

id code type value
1 EMPID System.Int32 0477348
2 EMPEID System.Int32 19245866
3 EMPBADGEID System.Int32 15354
4 EMPCF CMB 1
5 ENTITY CMB 4
6 JOBTITLE System.String Executive Director
7 DEPARTMENT CMB Transportation Parking & Traffic
8 DEPARTMENTLOCATOIN CMB 29
9 MOBILENO System.String 9097300644

Then you should update the relevant column with the given values.
In my scenario,

UPDATE @tempTable SET value = @newvalue1 WHERE code = 'EMPID'
UPDATE @tempTable SET value = @newvalue2 WHERE code = 'EMPEID'

According to my scenario, i have declared set of variables to hold the values in my temporary table and then create a dynamic sql command with those variables.

DECLARE @type VARCHAR(100)
DECLARE @value VARCHAR(100)
DECLARE @type1 VARCHAR(100)
DECLARE @value1 VARCHAR(100)
DECLARE @type2 VARCHAR(100)
DECLARE @value2 VARCHAR(100)........

SET @empstring = '<attributes><attribute><code>EMPID</code><type>'+@type+'</type><value>'+@value+'</value></attribute><attribute><code>EMPEID</code><type>'+@type1+'</type><value>'+@value1+'</value></attribute><attribute><code>EMPBADGEID</code><type>'+@type2+'</type><value>'+@value2+'</value></attribute><attribute><code>EMPCF</code><type>'+@type3+'</type><value>'+@value3+'</value></attribute><attribute><code>ENTITY</code><type>'+@type4+'</type><value>'+@value4+'</value></attribute><attribute><code>JOBTITLE</code><type>'+@type5+'</type><value>'+@value5+'</value></attribute><attribute><code>DEPARTMENT</code><type>'+@type6+'</type><value>'+@value6+'</value></attribute><attribute><code>DEPARTMENTLOCATOIN</code><type>'+@type7+'</type><value>'+@value7+'</value></attribute><attribute><code>MOBILENO</code><type>'+@type8+'</type><value>'+@value8+'</value></attribute></attributes>'

Then i got another problem while i'm trying to update the original table column values with xml converted value of my dynamic sql because the dynamic sql command has '&' in somewhere in it. '&' is causing problem only because it's an special character in xml. Therefore we have to treat him separately. We have to replace '&' with '&' and then only we can convert this in to xml.

SELECT @initialpart = SUBSTRING(@empstring,1,CHARINDEX('&',@empstring) -1)
SET @initialpart = @initialpart+'&'
SELECT @final = SUBSTRING(@empstring,CHARINDEX('&',@empstring) + 1,len(@empstring))
SET @finalempstring = @initialpart+@final

Then you can update the original value like,

UPDATE op_tb_trn_user SET userAttributes = CAST(@finalempstring AS XML) WHERE userid = @userid

You can proceed with the update statement without the '&' convention statements if there is no '&' in your dynamic command.

Happy coding... :)

How to add additional conditions within a where condition depending on the value got in a where condition

2015-05-18T02:07:00.001-07:00

Hi folks,

I have recently got a task to add additional condition to a particular query. This additional condition should be validate if a particular condition got true. Let's illustrate this as below,

SELECT col1, col2, col3...

FROM table1

WHERE col3 = 'val 1' OR col3 = 'val 2'

AND ........................

Lets say we have to add and additional condition if col3 = val 1. If col3 = val 2 then we dont have to check for this additional condition (lets say for col4).

This is how we can simply do this.

SELECT col1, col2, col3...

FROM table1

WHERE ((col3 = 'val 1' AND col4 = 'val 3') OR col3 = 'val 2')

AND ........................

In the above query, it will check for col4 = 'val 3' when col3 = val 1. If col3 = 'val 2' the additional check will be neglected and the rest is same.

ORA-01791 not a SELECTed expression

2014-07-09T19:57:00.001-07:00

Hi fellas,

ORA-01791 not a SELECTed expression is kind of an untraceable error when i had came across in a first place. Oracle form which has a LOV (List of Values) pops up an error "ORACLE ERROR : Unable to read from List of Values" when click on the LOV button or press CTRL+L key combination.

I haven't come across the exact reason for this but this has something to do with the database changes (Not like package changes or table structure changes but due to db version changes or rack changes) since the relevant query runs fine in all the existing 10g and 11g databases except a particular database which had a major change few dates back.

The relevant query is something like,

SELECT DISTINCT receipt_no, r_amount
FROM receipts
WHERE status = 'Y'
AND paycate_code = 'BL'
AND cust_keyvalue like DECODE(:customer_keyvalue,NULL,'%',:customer_keyvalue)
ORDER BY creation_date DESC;

Problem occurred because of the combination of the DISTINCT keyword with the ORDER BY keyword. To resolve this,

Remove the DISTINCT keyword if there is a trigger available in relevant to eliminate entering duplicate data in to the table.
Select all the columns which are using for ORDER BY clause along with the needed columns (In this scenario, you don't have to remove the DISTINCT keyword).

The query will be like,

SELECT receipt_no, r_amount
FROM receipts
WHERE status = 'Y'
AND paycate_code = 'BL'
AND cust_keyvalue like DECODE(:customer_keyvalue,NULL,'%',:customer_keyvalue)
ORDER BY creation_date DESC;

SELECT DISTINCT receipt_no, r_amount, creation_date
FROM receipts
WHERE status = 'Y'
AND paycate_code = 'BL'
AND cust_keyvalue like DECODE(:customer_keyvalue,NULL,'%',:customer_keyvalue)
ORDER BY creation_date DESC;

Happy coding...:)

How to handle "100501 Non-Oracle exception" in a simple way

2014-02-17T19:17:00.002-08:00

Hi fellas,

I have comacross 100501 Non-Oracle exception in one of my oracle form yesterday. 100501 is the error code of FORM_TRIGGER_FAILURE. In my form, after firing some validation, this non oracle exception message poped up and then gone after click on the ok button on the message box. This message didn't do any harm my form's functionality but do kind of a harm in QC (quality controlling) phase.

the previous developer who modified the form i'm working now used RAISE FORM_TRIGGER_FAILURE in exception handling area. The original code is,

EXCEPTION
WHEN OTHERS THEN
set_application_property(CURSOR_STYLE, 'DEFAULT');
alert_message(110,SQLERRM);
ROLLBACK;
RAISE FORM_TRIGGER_FAILURE;

The relevant exception in my scenario is a FORM_TRIGGER_FAILURE. Therefore the relevant error code is displaying via alert_message. In a scenario like this simply you can comment the relevant code which is responsible of displaying error messages or you could simple write a small coding part like below to handle that scenario separately.

EXCEPTION
WHEN FORM_TRIGGER_FAILURE THEN
set_application_property(CURSOR_STYLE, 'DEFAULT');
ROLLBACK;
RAISE FORM_TRIGGER_FAILURE;

WHEN OTHERS THEN
set_application_property(CURSOR_STYLE, 'DEFAULT');
alert_message(110,SQLERRM);
ROLLBACK;
RAISE FORM_TRIGGER_FAILURE;

Most valuable code is,
WHEN FORM_TRIGGER_FAILURE THEN
RAISE FORM_TRIGGER_FAILURE;

How to validate previous record's properties when user tries to enter new record in to the same data block without entering and saving the previous record and it's properties(data for child blocks)

2013-12-04T00:14:00.000-08:00

Hi fellas,

This is just for new guys to oracle.
I got an oracle form which has 6 data blocks based on 6 separate tables and form has 3 tabs. From first data block to last block, there is a connection between the particular block and it's previous block. Its like second block has a relation with first block, third block has a relation with second block, fourth block has a relation with third block and like wise. Since all these blocks based on database tables when tab pages changes changes will be inserted into the relevant tables.

The form i had has data enter process like below.

First block has the master record. This is only one record per one round.
Second block is tabular one and can have multiple records per round. Record can be a item which should have serial numbers or may be a item which shouldn't have serial numbers. Third block based on this concept.
If the second block has serialized item then the third block should have relevant serial numbers accordingly.
Since the data blocks based on tables when tab pages changed relevant input data will get inserted in to tables.
The problem i had was when user create the first record on the second block, user used serialized item and without entering relevant serial numbers in to third block, he tries to add a second record in to the second block. I have restrict the user in this scenario and navigate him / her to enter serial numbers when tries to enter the second record in the second block without entering serial numbers for the first record.
all the input data will get inserted in to the tables when tab pages changed but wont get committed until press the save button in the end of the process. Therefore i couldn't check for the serial numbers from the database table. What i did for this situation was i maintain a variable to hold the value of :system.cursor_value. When ever user tries to create a new record in the second block ( :system.cursor_value null because user tries to create a new record. ) i navigate to the previous record, checked whether the previous record is a serialized item and if so i checked the third block's values. If the third block doesn't have any data then i just redirect the user to the third block and forced him / her to enter the serial numbers. If the previous record has the relevant serial numbers in the third block or the previous record is a non serialized item then navigate to the second block and create a new record by using create_record built in.
I just put my code here and you can get the idea by going through that. Leave the additional conditions here which are unique to my form and it's functionality. Just concentrate on the codings in red color...

DECLARE

ml_new_cursor_rec NUMBER;
ml_value VARCHAR2(50);

BEGIN

ml_new_cursor_rec := :system.cursor_record;
ml_value := :system.cursor_value;

IF ml_value IS NULL THEN
previous_record;

IF FU_SERIAL_NO_ITEM THEN

IF :stl_adjust_qty != 0 AND :stl_adjust_qty IS NOT NULL AND :SVS_SERIAL_NO IS NULL THEN
err_msg(3620,2);

set_tab_page_property('ADJUSTED_DETAILS',enabled,property_true);
set_tab_page_property('APPROVEL_INFORMATION',enabled,property_true);

IF NVL(:ic_stk_take_lines.stl_adjust_qty,0) > 0 THEN
:IC_STK_VARIANCE_LINES.SVL_VARIANCE_TYPE := 'R';
ELSIF NVL(:ic_stk_take_lines.stl_adjust_qty,0) < 0 THEN
:IC_STK_VARIANCE_LINES.SVL_VARIANCE_TYPE := 'I';
END IF;

:ic_stk_variances.stv_date := sysdate;
:ic_stk_variance_lines.svl_item_code := :ic_stk_take_lines.stl_item_code;
:ic_stk_variance_lines.fc_item_desc := :ic_stk_take_lines.fc_item_desc;
:ic_stk_variance_lines.svl_adjust_qty := NVL(:ic_stk_take_lines.stl_adjust_qty,0);
:ic_stk_variance_lines.svl_rack_no := :ic_stk_take_lines.stl_rack_no;
:ic_stk_variance_lines.svl_bin_no := :ic_stk_take_lines.stl_bin_no;

:FC_SUPPESS_VALIDATE_FLAG := 'Y';
SELECT SYSDATE INTO :IC_STK_VARIANCES.STV_DATE FROM DUAL;
go_item('ic_stk_variance_serials.svs_serial_no');
first_record;

IF :ic_stk_variance_serials.svs_serial_no IS NULL THEN

LOOP

EXIT WHEN :ic_stk_variance_serials.svs_serial_no IS NULL;

DELETE FROM ic_stk_variance_serials
WHERE svs_voucher_no = :ic_stk_variances.stv_voucher_no
AND svs_item_code = :ic_stk_take_lines.stl_item_code
AND svs_serial_no = :ic_stk_variance_serials.svs_serial_no;

EXIT WHEN :system.last_record = 'TRUE';

next_record;

END LOOP;

ELSE
go_block('IC_STK_TAKE_LINES');
create_record;

END IF;

ELSE
go_block('IC_STK_TAKE_LINES');
create_record;

END IF;

ELSE
go_block('IC_STK_TAKE_LINES');
create_record;

END IF;

END IF;

END;

Happy coding.....:)

How to get a selected part of a string separate from the back end of the given string in oracle forms

2013-06-16T22:32:00.000-07:00

Hi fellas,

Last week i got a task to write a sql statement to select a particular part of a string from given string. The part of the string i need to get separate is in the back end of the original string. Actually given string is path where valuable text document stored in the server. File name is included in this path from the back end. To process the file they need to read the full path from the database and get the file name separated from the original path string.
Actual string is like this,
Main_Folder\folder_1\Folder_2\textfile.txt

Every folder is separated by '\' sign and number of folders in string can be different by time to time.Apart from that, the length of the file name can be very. Therefore we cannot predict the length of the original file path and length of the file name as well.My task is to read the original string from back end and cut the string when first '\' sign meets.

To read the string from the back, I had to reverse the given string. We can use REVERSE key word for this. But normally 10g oracle forms doesn't support all the key words in sql. Therefore forms doesn't support for REVERSE as well. Forms doesn't recognize REVERSE as a key word.Therefore we have to use UTL package along with the reverse key word (only for forms).

Lets imagine the original string is like "Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt"
First of all I had to reverse the original string and then i can read it until I can find the first '\' sign. Relevant statement is,
SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) FROM DUAL;

Output will be "txt.eliFtxeT\redlof_buS\2_redloF\1_redlof\redloF_niaM"

Then I had to read the string till I can find the position of the first '\' sign. Relevant statement is,
SELECT INSTR(UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))),'\')-1 FROM DUAL;

Output will be "12".

Therefore now I know that i have to substring the given string from 12th position to get only the file name separated. The relevant statement is,
SELECT SUBSTR((SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) FROM DUAL,0,(SELECT INSTR(UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) ,'\')-1 FROM DUAL)) FROM DUAL;

Output will be "txt.eliFtxeT".

Now I had to reverse the separated file name to get the original file name. For this process I had to use REVERSE statement again for the above mentioned output.

For the whole process, relevant statement is like below.

SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW((SELECT SUBSTR((SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) FROM DUAL,0,(SELECT INSTR(UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW
('Main_Folder\folder_1\Folder_2\Sub_folder\TextFile.txt'))) ,'\')-1 FROM DUAL)) FROM DUAL)))) FROM DUAL;

When we use this in the form, all the SELECT key words and FROM DUAL key words should be left.
Final output of the above mentioned statement will be,
"TextFile.txt".

I used INSTR to calculate the position of the first '\' sing in the reversed string and we must reduce 1 from that position number to get only the file name.

Use SSL in ASP.NET MVC3 Applcation

2013-01-14T07:04:00.001-08:00

Hi fellas,

SSL stands for Secure Socket Layer. Normally we use SSL in our web applications. We use it to secure the message transmission on the internet. This will secure the information we transmit via the URL. Different MVC applications use different methods to apply SSL to web application. In this section you will see how to use SSL with your ASP.NET MVC web application.

First of all we have to add related programming codes to the Web.config file of our web application. You can select the form to add this and if you want you can use this for several forms in your application. Bellow is the related XML code you should use in the Web.config file,

<forms loginUrl="~/Account/LogOn" timeout="2880" requireSSL="true"/>

In above XML code requireSSL="true" is the cording part which will take care of SSL. You can add any related URL to the loginUrl according to your requirement. In above example it's loginUrl="~/Account/LogOn".

After define Web.config file we have to modify the properties of our application project. to do this right click on the project and select properties.

In project properties select web tab and in that web tab the Server properties scroll down to the Servers.

In that properties select Use Local IIS Web Server radio button. After that select Use IIS Express checkbox and Overide applicationroot URL checkbox.

Then go the project properties by double clicking on the project and you will get a window like bellow.

In the properties window set SSL Enable as True. After that copy the given URL in SSL URL and paste it on project URl and field under Overide application root URL checkbox like bellow,

Paste the copied URL like bellow,

After modify the web tab in project properties save the changes and the clean the project, Rebuild and run the project. Now your URL will include the https (SSL).

FRM-30457: Warning: Maximum Length ignored for character-datatype subordinate mirror itme

2013-01-09T22:26:00.005-08:00

Hi fellas,

Sometimes we used Copy Value From Item property and Synchronize With Item property in oracle forms to connect separate items in same form.

Just imagine we have two items named ITEM1 and ITEm2 and we need to use number of properties like Data Type, Maximum Length, Required property , Highest Allowed Value,..etc... of ITEM1 on ITEM2. In these kind of scenario, we can use Copy Value From Item property and Synchronize With Item property to apply them.

For apply this you have to keep both items in the same data type.

FRM-30457: Warning: Maximum Length ignored for character-datatype subordinate mirror item will appear if you assign a value for Maximum Length property in child item (in this example it's ITEM2. ITEM1 is the master item.).

If we assign a value to Copy Value From Item property to some item, we cant assign a value to the Maximum Length property of that item because it refers the master item and will take those properties from that master item. Therefore we have to keep Maximum Length property as 0 for child item.

FRM-30443: Warning: Highest Value Allowed ignored for subordinate mirror item
and
FRM-30444: Warning: Lowest Value Allowed ignored for subordinate mirror item
will be appear if you assign values for Lowest Allowed Value and Highest Allowed Value properties of the child item. You should keep those properties (Lowest Allowed Value / Highest Allowed Value) empty for child item to avoid those above warnings.

Synonyms in PL/SQL

2012-12-17T02:35:00.000-08:00

Hi falks,

In pl/sql synonym refers to schema objects which are created by a particular user to access an objects which didn't belongs to him.Sometimes we have to join certain modules of a product to retrieve data for business purposes. Normally the particular user has to create the synonym for the related object according to the data requirement. The owner of the object should give grants for this user.

Owner can create synonyms and give grants to other users who are using different schema s.

User cannot create a synonym for different schema other than his own schema from different schema other than his own schema.

Example statement of creating synonym from different schema for a certain table. Just imagine that your schema is ABC and other schema which include the table you want is DEF and table is employees. Statement is,

CREATE OR REPLACE PUBLIC SYNONYM synemployees FOR ABC.employees;

User don't have to give any kind of grants to the synonym for his schema. It will assign all the grants automatically when synonym is creates.
But user can give grants to other users (schemas) while creating the synonym.

Anagram words in text file and file write and replace text through java

2012-12-06T21:05:00.001-08:00

Hi all,

Before few days I was having an assignment which need to apply Anagram to the words in a given text file through java. No way of using predefined methods or algorithms for this. Therefore I created simple java program to do this. This might also help you to understand about anagram and some techniques of applying anagram through java.

This is the source code.....

Just imagine that the existing file name is "test"

import java.io.*;
import java.util.StringTokenizer;

public class FileRead {

    static String sentence [];
    static String temp [];
    static int i = 0;
    static String first="";

public static void Read() {

try{

// Read the existing text file

FileInputStream fstream = new FileInputStream("test.txt");
DataInputStream in = new DataInputStream(fstream);
BufferedReader br = new BufferedReader(new InputStreamReader(in));

String strLine;

while ((strLine = br.readLine()) != null) {

     StringTokenizer stk = new StringTokenizer(strLine);
     sentence = new String[stk.countTokens()];
    temp = new String[sentence.length];
    while(stk.hasMoreTokens()) {
    sentence[i] = stk.nextToken();
    i++;

}
for(int x=0;x<sentence.length;x++){

if(x==0){
temp[x]= sentence[(sentence.length-1)];
}
else{
temp[x]= sentence[x-1];

}

}
for(int x=0;x<temp.length;x++){
     first=first+temp[x]+" ";

}
// Print original sentence
System.out.println (strLine);
System.out.println ("\n");
System.out.println ("Apply anagram for the above sentence.");
//Print Anagramed sentence.
System.out.print (first+"\n");

first="";

You can write this new phrase by using Filewritter and Bufferwritter as below mentioned. I'm using new text file to write new phrase......

// Write Anagramed sentence to new text file
File file =new File("test1.txt");
    //if file doesnt exists, then create it
    if(!file.exists()){
    file.createNewFile();
    }
    FileWriter fileWritter = new FileWriter(file.getName(),false);
    BufferedWriter bufferWritter = new BufferedWriter(fileWritter);
    bufferWritter.write(first);

The statement "FileWriter fileWritter = new FileWriter(file.getName(),false);" includes "false" in the end of the statement. We can use true instead of using false. The different of these two is , we can write new word or phrase right after the existing word or phrase in the text file by using "true". When we use "false" it will replace the existing word or phrase by given word or phrase.

How to put existing tab canvas on a newly created content canvas

2012-10-24T21:34:00.003-07:00

Hi fellas,

Before few days i got a oracle form which has a tab canvas to display the items of the form. One particular tab include 2 buttons which have to be common to all three tabs. At that time it only belongs to the first tab of the canvas. Part of my task is to make those to buttons common to all three tabs. Given solution is for this is create a new content canvas and place existing tab canvas on that content and place above mentioned two buttons on the content canvas. Content canvas should be larger than the tab canvas. Other wise those button wont get display on the form.
If you have to create both the content canvas and tab canvas you can simply create a content canvas and can draw a tab canvas on that content canvas. No need to have set any property or anything else for that. But when you have a existing tab canvas, you just cannot create new content canvas and place or set the tab canvas on that. In a situation like this we can use different method to do this.
First create a content canvas and make it larger than the existing tab canvas. Then double click on the new content canvas and click on the VIEW tab of the form builder and select STACKED VIEWS.. After that you will get a window which is showing the existing canvases.

In that window you can select the canvas you need to set on the newly created canvas. Now Our tab canvas is displaying on the content canvas and simply we can place the above mentioned two buttons on the content canvas. Now those two buttons are common to all three tabs. We don't have to set any kind of property for this.

How to extract only number values from given string....

2012-10-02T01:00:00.000-07:00

Hi fellas,

In oracle development, sometime we have to extract only the numbers from the given string. If the given string is has a dynamic length we can use TRIM for this task. But we have select whether it is LTRIM or RTRIM. Just imagine we have a string like '-4646'. We have to extract the numbers without '-'. We can use below statement for that.
select LTRIM('-4646','-') from dual;
If the unwanted part of the string ('-' in the example) is in left side, we can use LTRIM. If it's on the right side, we can use RTRIM.
Just imagine we have a string like '---00045454' and we have to extract only '45454' from the string. Also the length of this string can be change. Its dynamic. Apart from that the number of numbers in the string can be change and the number of '-' s and '0' can be change. In this kind of a situation we can use TRIM like below.
select LTRIM ( LTRIM ( '---00045454' , '-' ) , '0' ) from dual;

You can use both LTRIM and RTRIM like below for some situations.
select RTRIM ( LTRIM ( '----4587000' , '-' ) , '0' ) from dual;

You can use number of times of trims in your statement accordingly. The major thing you should consider is that you should give the characters you want to delete in a proper order. Otherwise it wont give the real output you want. The other thing you should consider about is select the trim you should use according to the places of the characters (RTRIM or LTRIM).

You can use trim within trim in your statements. In those kind of situations,the most inner trim will trim the value accordingly and pass the resulted value to the previous trim. This trim will trim the value accordingly and pass the value to his previous trim and so on and on. You can use this function with REG_EXP_REPLACE simultaneously.
TRIM function is really suitable for the situations like

String has a dynamic length.
Number of unwanted characters can be change by time to time.
Both starting point and ending point includes unwanted characters.

Trim couldn't filter all the characters. But he will do somethings for you when he fit to the situation.

How to put a hyperlink in Oracle forms.....

2012-09-10T00:10:00.000-07:00

Hi fellas,
Hyperlink is something like a URL. we can click on that and it will navigate us to the relevant location where we can have information we need. First of all we need to have relevant window and canvas to display the relevant information for the users. Our hyperlink will navigate the user to this newly created canvas to show the information. You can add any item like text items, display items, tables and etc...
To add new hyper link, first you have to add new Text item on the canvas that you need to display the hyperlink to the user. It's better change it's Visual Attribute Group as "VA_CLICK". You don't have to write anything in the "Prompt" section to display. Because hyperlinks didn't display anything in Prompt section. Apart from that you should set BEVEL = NONE in the property palate of the text item. You should call to the newly created canvas in WHEN-MOUSE-CLICK trigger by using SHOW_VIEW. Apart from that we can use web.Show_Document to show the relevant page to the user. But we can use web.Show_Document from within a form which run from the web.
If you run the form with above modification, still you wont be able to see the hyperlink. But it's there where you placed it and you can see it's functionality by clicking on the area you placed it. To display the hyperlink, you should assign a relevant name for the item within the trigger or program unit where you populate the data to the canvas where you placed your hyperlink. Otherwise it wont get display. eg - :BLK_MAIN.HYPERLINK := Hyper Link(Hyperlink name). Then only you can view this through your form.

How to compare two columns in a same table in a select statement and show a particular result ...

2012-07-26T03:02:00.000-07:00

Hi Oracle fellas,

Some times in oracle developments, we have to compare various kinds of values, table columns and etc.. to give a comfortable out put for the users. In this process, some times we have to give them feedback by showing them some kind of status or a value putting to newly created column which is not in the related database table.
As an example, just imagine we have table name "Employees" and it include Emp_No, Emp_name, Previous_Year_Salary and Current_Year_Salary columns. We have to compare the previous salary with current salary and return a value as M if those two values are equal and NM if not equal. Apart from that we have to display above value in a separate column name "any name" which not include in Employees table. Just image that we are advised to not to add any new column to the related table.
All you have to do is write a simple SELECT statement and put two columns into decode function with two values you have to return accordingly. It's like,
SELECT Emp_No, Emp_name, DECODE(Previous_Year_Salary , Current_Year_Salary, 'M', 'NM')
FROM Employees;
Above statement will return all the fetched records with the relevant comparison and relevant newly added column values. You can put a name to new column like bellow.

SELECT Emp_No, Emp_name, DECODE(Previous_Year_Salary , Current_Year_Salary, 'M', 'NM') as 'any name'
FROM Employees;

Apart from this DECODE function includes lots of formulas we can use. Within DECODE function we can not only compare numbers, but also dates and characters as well.

ORA- 12154: TNS:could not resolve service name in Oracle Formbuilder or Report builder

2012-06-21T00:34:00.001-07:00

Hi Oracle fellas,

You will get above problem when you are using oracle form builder or report builder. This can happen because of various kinds of reasons. It can happen because of the error of the TNS name you are trying to connect. May be the port numbers can be wrong or something like that. You cannot connect through form builder means that form builder cannot see or find your TNSNAMES.ora file. Form builder uses the TNSNAMES.ora file and SQLNET.ora file found under the iDS suite folder - under this folder the NETWORK - ADMIN folder. Sometimes there can be syntax errors like unrelated parentheses in your TNS name. Including these kind of error makes TNS file unusable.

To overcome this problem always maintain your TNSNAMES.ora file without any errors. Keep your .ora files in proper directory.

If you are using directory naming,
Verify that "LDAP" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the oracle net profile(SQLNET.ora).
Verify that the LDAP directory server is up and that it is accessible.
Verify that the net service name or database name used as the connect identifier is configured in the directory.
Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP Dn as the connect identifier.

If you are using easy connect naming,
Verify that "EZconnect" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the oracle net profile(SQLNET.ora).
Make sure the host,port and service name specified are correct.
Try enclosing the connect identifier in quote marks.

Sometimes this happen when your are using particular oracle version in the first time. Reason for this kind of scenario is the problem with SQLNET.ora file. You can see few lines of statements in that .ora file. In the very first moment only first two lines were commented. Therefore you wont be able to connect to needed database via form builder or report builder. Only you have to do is comment all the lines in that file and try to connect to the database you want.

There may be different oracle products installed in your computer. Each of these products contains it's own NETWORK/ADMIN directory which is the location of .ora files. Therefore we can maintain only one TNSNAMES file for all the products in your computer. TNS_ADMIN environmental variable will allow you to do that. Take any of the TNSNAMES.ora file and put it in to a directory (like "D:\0_library") which is easily accessible and let the TNS_ADMIN environment variable point to its directory. Then do as follows,

c:\ temp>set tns_admin
tns_admin = D:\0_library
c:\ temp

Simply log in to your database.....:)

Raise form_trigger_failure... ORACLE...

2012-03-26T01:50:00.001-07:00

Hi Oracle fellows...:)

We all are familiar with the above phrase "Raise Form_Trigger_Failure". This statement is use freeze the program whenever we need. Freeze means stop the whole process of the oracle program.
Normally in Oracle forms we use this statement to freeze the form whenever an error occurred. This statement mostly helpful when you are doing higher level validations like QC validations. We can use this statement when we handle an error like prompt appropriate error message. Otherwise program will prompt an appropriate error message and will try to continue the rest of the process according to the program process and code. This will make program to prompt an Oracle error messages for user and he or she will not be able to understand those messages and will get messed up with the program.

Sometimes this statement wont work properly in WHEN_BUTTON_PRESSED trigger. Sometimes this button wont be able to freeze the form and therefore program will try to proceed with the error and will prompt an oracle error.

This happens sometime when you are using exception handling without proper manner. I mean when trying to prompt out the exception and do nothing to handle it. Actually "Raise Form_Trigger_Failure" is an predefined exception. So in a situation like that, the program will throw you an exception without freezing it.

For these kind of scenario, we can use above statement as below mentioned.

when form_trigger_failure
Raise Form_Trigger_Failure;

Pretty easy stuff. But you have to put these two lines within the exception handling part. It will work in a nice and smooth manner...

2012-03-19T09:16:00.003-07:00

FRM-40735:ON-ERROR trigger raised unhandled exception ORA-06508

2012-03-18T21:29:00.003-07:00

After converting older version of oracle forms to forms 10g using forms Migration Assistant, you are not be able to open the application or log in to the application via web. When access from developer suit 10g, it will run without any problems. One of the problem could be the related ORACLE LIBRARY PATH which attached to it. Some times this can happens when you are changing the library path according to the module or work area you work at that moment.

In older versions of forms the library path is mapped to the client pc and the forms application refer to the mapped drive letter. In the web we couldn't find specific path. It is relative. It will automatically refer the path.

If you have above mentioned problem, you will get message from oracle form builder. It says cannot attache specific libraries to your form source. Sometimes you wont get this message but wont be able to open the form. when you look in to the form source in form builder, you will see that form didn't include any library.

There's few solution available for this matter.

Open the form in Oracle form builder. Click on Attached library and plus sign item on Object Navigator. Locate the need .plls and press YES on the message which ask whether to remove path or not.
Go to the regedit through Command Prompt. Go to forms_path if you are working with 10g2 , otherwise go to forms90_path and enter the related library path to that.
Go to the Oracle folder where installed it in your computer and go to the forms folder if you are using oracle 10g2. Otherwise go to forms90 folder. In that folder, go in to the server folder and you can find ENV file named Default. open it on notepad or wordpad. It includes something like "FORMS_PATH" if you are using 10g2. Otherwise "FORMS90_PATH" give the related library to that path and save it. Now whole forms is under your control.

Now you can open the form and access via web successfully.

Additional....

Don't use long paths for Library folder.
Keep the latest libraries which are in SVN.
Always replace the regedit path and default.env path when you are changing the module or oracle version accordingly.
Oracle 10g1 use FORMS90 AND Oracle 10g2 use FORMS.

How to create a Password Protected folder in Windows 7....

2011-12-29T21:23:00.001-08:00

Hi folks,

We all have various kinds of personal things in our computers. Because of some reasons we want to keep those things personal. But it's hard to do that if we are sharing one computer among few people. Therefore we have to our own folder which has password to protect it from other viewers. Below steps will help you to create a hidden folder and password protected files.

Create a new folder and name it whatever you would like.
Open the folder, right click on a blank area in it, then select New and then select Text Document from the pop up menu.
Open the text file you just created by double clicking on it and copy / paste in the following text

cls
@ECHO OFF
title Folder Private
if EXIST "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}" goto UNLOCK
if NOT EXIST Private goto MDLOCKER
:CONFIRM
echo Are you sure you want to lock the folder(Y/N)
set/p "cho=>"
if %cho%==Y goto LOCK
if %cho%==y goto LOCK
if %cho%==n goto END
if %cho%==N goto END
echo Invalid choice.
goto CONFIRM
:LOCK
ren Private "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}"
attrib +h +s "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}"
echo Folder locked
goto End
:UNLOCK
echo Enter password to unlock folder
set/p "pass=>"
if NOT %pass%== PASSWORD_GOES_HERE goto FAIL
attrib -h -s "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}"
ren "Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}" Private
echo Folder Unlocked successfully
goto End
:FAIL
echo Invalid password
goto end
:MDLOCKER
md Private
echo Private created successfully
goto End
:End

In the above code, replace the key PASSWORD_GOES_HERE with the password you want to use. For example, if your password is 123 then,

if NOT %pass%== 123 goto FAIL

Save your new file in .bat format with a complete file name being locker.bat. To do this, make sure to change the Save as type: to All Files(*.*).
In the folder you created earlier, double click the locker.bat file and there will now be a new folder name Private where you can put anything you want.

Upon existing, double click on the locker.bat file again. It will prompt you to answer weather you want to lock your folder or not. Press Y and the folder will disappear.

In order to retrieve the folder, all you have to do is double click the locker.bat file and enter the password which you set in step 4 and the folder will be appear again and you can access.

Trick to Automatically shutdown PC in Windows

2011-12-21T20:44:00.000-08:00

Hi folks,

Sometimes we were running virus scan, huge file downloads and stuff like that in our computers. those things might take few hours to get it done. Therefore we might not be able to sit in front of the computer until those process get done. In a situation like that, we wont be able to shut the computer after complete those process. therefore it's really helpful if we have some one to do that for us.

There are lot of third party softwares available which can do this for us. these softwares can schedule the shutdown process. By using this method we can shutdown our PC using Windows Scheduler.

Go to Start and type Task Scheduler in search box and press enter.
You will get a new window and click on the Action and then lick on the Create Basic Task.

In the Name field give any name for your task and then click on next button.
Select the delay(Daily, weekly, etc) after which you want to repeat this task and click on the next button.
Now specify the time at which when you want this task tom execute and click on the next button.

Now select Start a Program and then click on the next button.
Browse the program which you want to execute and click on the next button.
For shutdown copy and paste this location.
C:\Windows\system32\shutdown.exe

After this click on the Finish button.

If someone is logged-on your PC at the execution time of the task, he/she is given one minute to save all data which is open before the computer shutdown.

Task scheduler can also be use to schedule other programs.Even Google Chrome uses Task Scheduler to run the Automatic Update.

Change the size of the virtual memory paging file.....

2011-12-01T03:36:00.000-08:00

You must be logged on as a Administrator or a member of Administration group to do this. If your computer is connected to a network, network policy settings may also prevent you of completing this procedure.

Click and open Control Panel.
Click Performance and Maintenance and then click System.
On the Advanced tab, under Virtual Memory, click Change.
Under drive (volume), click the drive that contains the paging file you want to change.
Under Paging file size for selected drive, click Custom size.
Type a new paging file size in megabytes in the Initial size (MB) or Maximum size (MB) box, and then click Set.

If you decrease the size of either initial or maximum page file settings, you must restart your computer to see the effects on those changes. Increases typically do not require a restart.

To have windows choose the best paging file size, click System managed size.

For best performance, do not set the initial size to less than the minimum recommended size under Total paging file size for all drives. The recommended size is equivalent to 1.5 times the amount of RAM on your system.

Using memory in your storage device to speed up your computer....

2011-12-01T00:32:00.000-08:00

Hi folks,

Recently I'm having an issue with my CPU memory. Actually is about RAM capacity. I want to increase my RAM capacity to speed up my computer. ReadyBoost helped me to do this. ReadyBoost can speed up your computer by using storage space on most USB Flash drives and Flash Memory Cards. When you plug a ReadyBoost - compatible storage device to your computer, the Autoplay dialog box offers you the option to speed up your computer by using ReadyBoost. If you select this option, you can choose how much memory on the device to use for this purpose.

When you setup a device to work with ReadyBoost, windows shows you how much space it recommends you allow it to use for optimal performance. For ReadyBoost to effectively speed up your computer, the flash drive or memory card should have at least 1Gb of available space.

You can enable or disable ReadyBoost for a specific flash drive or other removable storage devices.

Here are some tips on what to look for when selecting a USB flash drive or memory card to use with ReadyBoost .

The minimum amount of available space recommended is 1Gb.
For best results, use a storage device with available space of at least double the amount of memory in your computer (RAM).
To work with ReadyBoost, storage device must support USB 2.0 or higher.
For best results, plug the device directly to the computer instead of using external cables or hubs.

If your computer has a hard disk that uses solid-state drive (SSD) technology, you might not seen an option to speed up your computer with ReadyBoost when you plug in a USB flash. You may instead receive a message " ReadyBoost is not enable on this computer because the system disk is fast enough that ReadyBoost is unlikely to provide any additional benefit. " This is because some SSD drivers are so fast they're unlikely to benefit from ReadyBoost.

In some situations, you might not be able to use all of the memory on your device to speed up your computer. For example, some memory devices contain both slow and fast flash memory. But ReadyBoost can only use fast flash memory to speed up computers.

This function is currently available only on WINDOWS 7 operating system.

Change folder or file properties via command prompt....

2011-11-16T21:37:00.000-08:00

Hi folks,
Don't you ever had an issue with viewing hidden files or folders in your personal computer or office computer?
I had this issue in my computer few times, because of virus attack. Sometimes this can happen because of some other reasons like problems in system files.
In my case system didn't allow me to change the properties of particular file in my PC. To overcome this problem i found a simple way which can change file or folder properties via command prompt.
Let's see how can we do that......

Just imagine you have a wmv file name 'Advertisement'.( Hidden file )
File path is C:\Users\udara\Desktop\Personal\Customize\My Advertisement.
First open command prompt.
Type ' C: ' and press Enter.
Then type ' cd Desktop ' and press Enter.
Then type ' cd Customize ' and press Enter.
Type ' My Advertisement ' and then press Enter.
Type ' attrib ' + ' -s ' + ' -h ' + <File name or Folder name> ( In this case 'Advertisement' ) and then press Enter.

You can see your file now in related folder.

You can use this command for a hidden folders also. This command functioning well in WINDOWS XP and also WINDOWS 7 platforms.

To find out more details about these commands, follow
http://www.computerhope.com/attribhl.htm