O Apex

Tuesday, November 21, 2017

Oracle 12c and Interactive Grid Oracle Apex

To make Interactive Grid work smoothly using auto generate sequence feature of Oracle 12c, remove the Primary Key column or the column on which you have auto generate sequence from SQL query in Interactive Grid.
That's it!

To know how to generate Auto Sequence, click on below link
http://mythass.blogspot.my/2017/11/auto-generate-sequence-in-oracle-12c.html

I hope the above is useful to you.

Auto Generate Sequence in Oracle 12c

Oracle 12c - No need to create sequences.

Use the below highlighted syntax to auto generate Sequences in Oracle 12c.

CREATE TABLE ABC (
abc_id NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
name VARCHAR2(50)
)
ALTER TABLE ABC ADD CONSTRAINT abc_id_pk PRIMARY KEY ( abc_id );

Note: In case you are passing the value for primary key manually, Oracle won't generate the sequence for that transaction and will start from the same number where it left last time irrespective of manual insertion.

Tuesday, May 30, 2017

How to split comma separated string in new row using CONNECT BY and REGEXP?

SELECT REGEXP_SUBSTR('APPLE,BOB,CARS', '[^,]+', 1, LEVEL) FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT('APPLE,BOB,CARS', '[^,]+', 1)

select * from
(select 'GIOVANNI COSTELLO' writer, 'RODRIGUEZ' author from dual) tt
where exists
(select * from (
select trim(REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL)) as c1
from
(
select 'ARMIN RODRIGUEZ, GIOVANNI COSTELLO, RUBEN RODRIGUEZ ALARCON, RUEDIGER SKOCZOWSKY, XAVIER NAIDOO' as str
from dual) CONNECT BY LEVEL <= REGEXP_COUNT(str, '[^,]+', 1)
) where c1 = tt.writer);

select regexp_substr('abc:xyz:abc:pqr:xyz','[^:]+',1, level) str from dual

connect by level <= regexp_count('abc:xyz:abc:pqr:xyz','[^:]+', 1)

Wednesday, May 10, 2017

Get Data of Particular Month in Oracle

This is the easiest way of getting data of particular month

Select * from table where extract(MONTH from column_name ) = 4; -- April

Column_name should be of date type or timestamp.

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