O Apex: 2016

Friday, July 8, 2016

How to show Post Text dynamically in Text Field in Oracle APEX and in HTML on Focus using jQuery and CSS?

In Oracle Apex-

Click below link to see demo-
https://apex.oracle.com/pls/apex/f?p=S_MYDEMO:POSTXT:&APP_SESSION.:::::

Create page items as text field P3_TEXT1, then create dynamic action
as Get Focus > Items > name
Create True action > Set Value
give some value e.g., *
select affected element as jquery selector > .t-Form-itemText--post
Create True action > Execute JavaScript Code
$(".apex-item-text").focus(function (){
$(".t-Form-itemText--post").css('display','none');
$(this).next(".t-Form-itemText--post").css('display','inline').show();
$(this).next(".t-Form-itemText--post").animate({ color: "rgb( 150, 10, 160 )"},'slow');
});

It's Done.

HTML Example:

<html>

<head>

<style>

#sp {display:none;color:red;}

</style>

$("input").focus(function (){

$(this).next("#sp").css('display','inline').blink(2000);

});

</script>

</head>

<body>

<p>Field 1<input type="text" /><span id="sp"> mandatory</span></p>

<p>Field 2<input type="text" /><span id="sp"> mandatory</span></p>

</body>

</html>

Thursday, July 7, 2016

How to send mail in UNIX Shell Script?

#!/bin/bash
RETVAL=`<<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT 'shivam' FROM dual;
EXIT;
EOF`
if [ -z "$RETVAL" ]; then
echo "No rows returned from database" | mailx -s "TEXT...... is corrupted" shivam.kumar91@gmail.com
exit 0
else
echo $RETVAL
fi

How to Create Exception in PL/SQL?

First Declare your exception name in Declaration section-
my_excp EXCEPTION

Then need to Raise your defined exception inside Begin section-
RAISE my_excp;

And last, Catch it in Exception section and and link the exception to a user-defined error number-
WHEN my_excp THEN
....

Below is the example of how to do-

DECLARE

my_excp EXCEPTION;

v_int1 NUMBER := 0;

BEGIN

IF v_int1 <> 3 THEN

RAISE my_excp;

END IF;

EXCEPTION

WHEN my_excp THEN

raise_application_error (-20009,'this is my exception');

END;

*** The Error number must be between -20000 and -20999

Note: DBMS_OUTPUT.PUT_LINE('test'); -- this will not work with raise_application_error

How to change or toggle image onClick ?

Take two different images to toggle.
we can achieve this functionality by using javascript or by jQuery.

HTML-

<img src="images/arrow-up.png" onclick="hide_f(this)" style="display:none;" class="class_1" id="minus_2" />

<img src="images/arrow-down.png" onclick="show_f(this)" class="class_1" id="plus_2" />

Javascript-

Using function names-

function show_f(ele) {

document.getElementById("minus_2").style.display = "block";
document.getElementById("plus_2").style.display = "none";
}

function hide_f(elem) {
var id2 = elem.id;
document.getElementById("minus_2").style.display = "none";
document.getElementById("plus_2").style.display = "block";
}

jQuery-

Using jQuery functions-

jQuery(function ($) {
        $(document).ready(function(){
   $("#plus_2, #minus_2").click(function(){
    $("#minus_2, #plus_2").toggle();
   });
 });
});

Tuesday, July 5, 2016

How to find the value before or after Comma "," in Oracle sql?

using REGEXP_SUBSTR we can find out values before or after comma.

Here I am extracting the value before comma based on occurrence. The below code searches for a comma, return one or more occurrences of non-comma characters

SELECT REGEXP_SUBSTR ( 'KUMAR,SHIVAM,MYTHASS' , '[^,]+' , 1, 1) FROM DUAL;

The above code will extract the first occurrence of non-comma character.

If you want to extract the second occurrence of non-comma character, you need to find the second appearance-

SELECT REGEXP_SUBSTR ( 'KUMAR,SHIVAM,MYTHASS' , '[^,]+' , 1, 2) FROM DUAL;

Same way you can find Nth appearance of non-comma character.

Wednesday, June 29, 2016

UNIX Commands

Shell Commands
=============

ls *.tar.gz*|wc -l # Count

cp -r FILE_NAME/DIRECTORY_PATH # Copy e.g., " cp -r Scripts /c/my_dir/my_proj "

cp -r * FILE_NAME/DIRECTORY_PATH # Copy_all e.g., " cp -r * /c/my_dir/my_proj "

rm *.gz # Remove files

rm -rf DIR_NAME # Remove DIR with sub-contents

cd ../AU    # go directly to AU Folder

ls -d */ OR ls -ltr -d */     # list only directories

find . -maxdepth 1 -type f       # list only files in current directory

find . -maxdepth 1 -type f > shivam.txt # print output in a file

find . -type d -name "HK_SIT" # search for a directory, ".(dot)" means current dir

ls -d HK_*       # show all dir's starting with HK_

tar -xvf filename       # untar files

$?       # returns the status of the last finished command.          Status 0 tells you that everything finished w/o error.

if [ $val -eq $? ]       # $val extract the value that is hold by the val

grep 'abc_xyz' test.txt |cut -d, -f2 # search for the string "abc_xyz" inside          test.txt & fetch 2nd field as ouput i.e, f2

tar -tvf file_name |grep '.xcl' | wc -l # get count without untar

:1,$d       # ':' colon command (moves the cursor                to the bottom), The 1,$ is an indication of which          lines the following command (d) should work on.

:4,$-2d       # leaving only the first 3 and last 2 lines, deleting the rest.

:%d       # delete all text from file

grep 'SG/AU/SIT' *.*       # search string in all files of current directory

echo 'MY_PASSWORD_2016'|base64 # ENCRYPT

echo 'Q1NBX0NUTF9JE1Cg' |base64 -d # DECRYPT

mailx -s "[$value_1|$value_2|$value_3] "TEXT......" $v_grp_name is corrupted" $GEM_XCL_EMAIL <<EOM

How to get more than 4000 character like LISTAGG?

In case we want to list more than 3999 characters in one column-

SELECT TBL1.COL1,
SUBSTR(XMLCAST(XMLAGG(XMLELEMENT(E,' | ' || TBL1.COL2)
ORDER BY TBL1.COL3) AS CLOB),
4) AS COL2
FROM
(
SELECT DISTINCT COL1, COL2
FROM TABLE_1 TB1,
TABLE_2 TB2
WHERE TB1.COL = TB2.COL
) TBL1
WHERE ...
GROUP BY TBL1.COL1

Friday, April 15, 2016

How to generate HTML output from UNIX?

To generate HTML output from UNIX-

printf "<html>\n<head>\n<title>MyDOC - Shivam</title>\n<style>#p1{color:red;}#p2{color:orange;}#p3{color:#cccc00;}</style></head>\n<body>\n<span id="p1">HIGH</span><br />\n<span id="p2">MEDIUM</span><br />\n<span id="p3">LOW</span><br />\n</body></html>" > /cs/csaapp/CSA_APAC_SIT/DataLoad/GEM/SG/AU_SIT/shivam_test/myHTML.html