19 Jan 2017

Using Refcursor Getting Details

CREATE ONE FORM WITH ONE NON-DATABASE BLOCK HAVE ONE TEXT ITEM, AND ONE PUSH BUTTON.
CREATE SCOTT.EMP DATA BLOCK IN TABULAR STYLE ON SAME FORM.
DEFINE THE FOLLOWING
IN PROGRAM UNIT
CREATE PACKAGE
PACKAGE PKG_REF_CURSOR IS
  TYPE USER_CURSOR IS REF CURSOR;
END;

CREATE THE FOLLOWING PROCEDURE
PROCEDURE GET_DEPT_DETL (P_DEPTNO   IN NUMBER,
                        io_cursor  OUT PKG_REF_CURSOR.user_CURSOR) IS
 V_CURSOR PKG_REF_CURSOR.user_CURSOR;
BEGIN
 OPEN V_CURSOR FOR SELECT * FROM SCOTT.EMP
                    WHERE DEPTNO=P_DEPTNO;
IO_CURSOR := V_CURSOR;
EXCEPTION
                WHEN OTHERS THEN
                RAISE FORM_TRIGGER_FAILURE;
END;

ON PUSH BUTTON
DECLARE
                ROW_CNT NUMBER := 0;
                MY_CURSOR PKG_REF_CURSOR.USER_CURSOR;
BEGIN
--PROCEDURE GETTING ROWS
GET_DEPT_DETL(:TI_DEPTNO,MY_CURSOR);
GO_BLOCK('EMP');
                LOOP
                  ROW_CNT :=ROW_CNT+1;
                  FETCH MY_CURSOR
                 INTO :EMPNO,:ENAME,:JOB,:MGR,:HIREDATE,:SAL,:COMM,:DEPTNO;   
                  NEXT_RECORD;
                  EXIT WHEN MY_CURSOR%NOTFOUND;
                END LOOP;
END;

Run the Form.
ref_cursor.JPG


17 Jan 2017

Using Reference Cursor For Creating Record Group

CREATE THE FORM WITH ONE TEXT ITEM AND ONE LIST ITEM. 
@LIST ITEM ELEMENT_LIST SET TO BE NULL.

IN PROGRAM UINT CREATE THE FOLLOWING.

PACKAGE pkg_ref_cursor IS
  TYPE user_CURSOR IS REF CURSOR;
END;

PROCEDURE GET_DEPT_JOBS(P_DEPTNO   IN NUMBER,
                        io_cursor  OUT PKG_REF_CURSOR.user_CURSOR) IS
 V_CURSOR PKG_REF_CURSOR.user_CURSOR;
BEGIN
 OPEN V_CURSOR FOR SELECT DISTINCT INITCAP(JOB) JOBS, JOB FROM SCOTT.EMP
                    WHERE DEPTNO=P_DEPTNO;
IO_CURSOR := V_CURSOR;
EXCEPTION
                WHEN OTHERS THEN
                RAISE FORM_TRIGGER_FAILURE;
END;

CREATE ONE PUSH BUTTON AND APPLY FOLLOWING CODE
DECLARE
  RG_ID    RECORDGROUP;             RG_NAME VARCHAR2(20) := 'RG_DEPT';
  GC_ID1  GROUPCOLUMN;             GC_ID2                 GROUPCOLUMN;
  ROW_CNT NUMBER := 0;             MY_CURSOR PKG_REF_CURSOR.USER_CURSOR;
  V_JOBS VARCHAR2(10);              V_JOB  VARCHAR2(10);
BEGIN
--PROCEDURE GETTING ROWS
GET_DEPT_JOBS(:TI_DEPTNO,MY_CURSOR);

RG_ID := FIND_GROUP(RG_NAME);

IF NOT ID_NULL(RG_ID) THEN     
    DELETE_GROUP(RG_ID);               
END IF;

RG_ID := CREATE_GROUP(RG_NAME);

IF ID_NULL(RG_ID) THEN
   MESSAGE('ERR: CREATING RECORD GROUP '||RG_NAME);
   RAISE FORM_TRIGGER_FAILURE;               
END IF;

GC_ID1 := ADD_GROUP_COLUMN(RG_ID, 'HEAD', CHAR_COLUMN,20);
GC_ID2 := ADD_GROUP_COLUMN(RG_ID, 'JOB',CHAR_COLUMN,05);

LOOP
  ROW_CNT :=ROW_CNT+1;
  ADD_GROUP_ROW(RG_ID, END_OF_GROUP);    
  FETCH MY_CURSOR INTO V_JOBS, V_JOB;         
  EXIT WHEN MY_CURSOR%NOTFOUND;
  SET_GROUP_CHAR_CELL(GC_ID1, ROW_CNT, V_JOBS);   
  SET_GROUP_CHAR_CELL(GC_ID2, ROW_CNT, V_JOB);
END LOOP;

POPULATE_LIST('JOB_LIST', RG_ID);
END;

Dynamic List Items

CREATE THE FORM WITH TWO LIST ITEMS. IN ELEMENTS LIST PROPERTY SET TO BE NULL.
CREATE ONE PUSH BUTTON , HAVE FOLLOWING CODE.
DECLARE
                RG_ID    RECORDGROUP;                               RG_NAME VARCHAR2(20) := 'RG_DEPT';
                GC_ID1                 GROUPCOLUMN;                              GC_ID2                 GROUPCOLUMN;
                ROW_CNT NUMBER := 0;
BEGIN
                IF :DEPT_LIST IS NULL THEN
                BEGIN  
/* FIRST CHECK WHETHER THE RECORD GROUP ALREADY EXISTS.
IF YES, DELETE IT. */
                RG_ID := FIND_GROUP(RG_NAME);
                IF NOT ID_NULL(RG_ID) THEN
                                DELETE_GROUP(RG_ID);
                END IF;
/* CREATE THE RECORD GROUP TO DERIVE A HANDLE OR INTERNAL ID */
                RG_ID := CREATE_GROUP(RG_NAME);
                                IF ID_NULL(RG_ID) THEN
                                                MESSAGE('ERR: CREATING RECORD GROUP '||RG_NAME);
                                                RAISE FORM_TRIGGER_FAILURE;
                                END IF;
/* ADD TWO GROUP COLUMNS, DEPTNO AND DNAME */
                GC_ID1 := ADD_GROUP_COLUMN(RG_ID, 'DNAME', CHAR_COLUMN,20);
                GC_ID2 := ADD_GROUP_COLUMN(RG_ID, 'DEPTNO',CHAR_COLUMN,05);
/* CREATE ROWS AND POPULATE THEM IN A LOOP. */
                FOR I IN (SELECT DNAME,DEPTNO FROM SCOTT.DEPT) LOOP
                                ROW_CNT :=ROW_CNT+1;
                                ADD_GROUP_ROW(RG_ID, END_OF_GROUP);
                                SET_GROUP_CHAR_CELL(GC_ID1, ROW_CNT, I.DNAME);
                                SET_GROUP_CHAR_CELL(GC_ID2, ROW_CNT, TO_CHAR(I.DEPTNO));
                END LOOP;
                POPULATE_LIST('DEPT_LIST', RG_ID);
                END;

                ELSIF :DEPT_LIST IS NOT NULL THEN

                BEGIN  
                RG_NAME := 'JOB_GROUP';         
                RG_ID := FIND_GROUP(RG_NAME);
                IF NOT ID_NULL(RG_ID) THEN
                                DELETE_GROUP(RG_ID);
                END IF;
/* CREATE THE RECORD GROUP TO DERIVE A HANDLE OR INTERNAL ID */
                RG_ID := CREATE_GROUP(RG_NAME);
                                IF ID_NULL(RG_ID) THEN
                                                MESSAGE('ERR: CREATING RECORD GROUP '||RG_NAME);
                                                RAISE FORM_TRIGGER_FAILURE;
                                END IF;
/* ADD TWO GROUP COLUMNS, DEPTNO AND DNAME */
                GC_ID1 := ADD_GROUP_COLUMN(RG_ID, 'DESIGNATION', CHAR_COLUMN,20);
                GC_ID2 := ADD_GROUP_COLUMN(RG_ID, 'JOB',CHAR_COLUMN,05);
/* CREATE ROWS AND POPULATE THEM IN A LOOP. */
                FOR I IN (SELECT INITCAP(JOB) DESIG,JOB FROM SCOTT.EMP
                          WHERE DEPTNO=:DEPT_LIST) LOOP
                                ROW_CNT :=ROW_CNT+1;
                                ADD_GROUP_ROW(RG_ID, END_OF_GROUP);
                                SET_GROUP_CHAR_CELL(GC_ID1, ROW_CNT, I.DESIG);
                                SET_GROUP_CHAR_CELL(GC_ID2, ROW_CNT, I.JOB);
                END LOOP;
                POPULATE_LIST('JOB_LIST', RG_ID);
                END;
                END IF;
END;
                

12 Jan 2017

ROW_NUMBER()


Another Query😤

Here is another Query Which are using Row_Number in With Clause..
WITH GET_INCOME_TAX AS
( SELECT S.EMPNO, CASE WHEN S.JOB IN ('CLERK','SALESMAN')    THEN TRUNC(((NVL2(S.SAL+S.COMM,S.SAL+S.COMM,S.SAL)*10.5)/100)/360*30,0)
                       WHEN S.JOB IN ('MANAGER','PRESIDENT') THEN TRUNC(((NVL2(S.SAL+S.COMM,S.SAL+S.COMM,S.SAL)*18.5)/100)/360*30,0)
                     ELSE TRUNC(((NVL2(S.SAL+S.COMM,S.SAL+S.COMM,S.SAL)*10)/100)/360*30,0) END INCOME_TAX
  FROM SCOTT.EMP S
),
 DEPTS AS 
 (SELECT D.DEPTNO,D.DNAME,D.LOC, SUM(E.SAL) DEPT_SAL,TRUNC( AVG(E.SAL),2) DEPT_AVG
                  FROM SCOTT.DEPT D,
                            SCOTT.EMP E
                  WHERE E.DEPTNO(+)=D.DEPTNO
                  GROUP BY D.DEPTNO,D.DNAME, D.LOC
 )       
SELECT ROW_NUMBER() OVER(ORDER BY E.DEPTNO,E.SAL DESC ) SNO,
       E.EMPNO,
       E.ENAME,
       E.JOB,
       E.HIREDATE,
       E.SAL,
       GR.GRADE,
       E.COMM,
       NVL2(E.SAL+E.COMM,E.SAL+E.COMM,E.SAL) NETS,
       GIT.INCOME_TAX,
       DS.DEPTNO,
       DS.DNAME,
       DS.LOC,
       DS.DEPT_SAL,
       DS.DEPT_AVG
FROM SCOTT.EMP E, GET_INCOME_TAX GIT,DEPTS DS, SCOTT.SALGRADE GR
WHERE E.Empno = GIT.EMPNO
    AND E.DEPTNO = DS.DEPTNO
    AND E.SAL BETWEEN GR.LOSAL AND GR.HISAL
    

Queries--

Using of REGEXP_SUBSTR

SELECT REGEXP_SUBSTR('SMITH,ALLEN,WARD,JONES','[^,]+', 1, LEVEL) REG_EXMP
FROM DUAL
CONNECT BY REGEXP_SUBSTR('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, LEVEL) IS NOT NULL;

REG_EXMP
SMITH
ALLEN
WARD

JONES

USING WITH

WITH GET_INCOME_TAX AS
( SELECT S.EMPNO, CASE WHEN S.JOB IN ('CLERK','SALESMAN')    THEN TRUNC(((NVL2(S.SAL+S.COMM,S.SAL+S.COMM,S.SAL)*1.5)/100)/360*30,0)
                       WHEN S.JOB IN ('MANAGER','PRESIDENT') THEN TRUNC(((NVL2(S.SAL+S.COMM,S.SAL+S.COMM,S.SAL)*8.5)/100)/360*30,0)
                     ELSE TRUNC(((NVL2(S.SAL+S.COMM,S.SAL+S.COMM,S.SAL)*10)/100)/360*30,0) END INCOME_TAX
  FROM SCOTT.EMP S
),
 DEPTS AS (SELECT D.DEPTNO,D.DNAME, SUM(E.SAL) DEPT_SAL,TRUNC( AVG(E.SAL),2) DEPT_AVG 
                  FROM SCOTT.DEPT D,
                            SCOTT.EMP E
                  WHERE E.DEPTNO(+)=D.DEPTNO
                  GROUP BY D.DEPTNO,D.DNAME)  
SELECT E.EMPNO,
       E.ENAME,
       E.JOB,
       E.HIREDATE,
       E.SAL,
       E.COMM, 
       NVL2(E.SAL+E.COMM,E.SAL+E.COMM,E.SAL) NETS,  
       GIT.INCOME_TAX,
       DS.DEPTNO,
       DS.DNAME,
       DS.DEPT_SAL,
       DS.DEPT_AVG
FROM SCOTT.EMP E, GET_INCOME_TAX GIT,DEPTS DS
WHERE E.Empno = GIT.EMPNO
    AND E.DEPTNO = DS.DEPTNO;

Result vary According to the condition, i just call for empno 7839.

Create, Edit, Or Delete A Post @ BLOG

Create, Edit, Or Delete A Post
You can write, edit, or delete posts and drafts at any time.
Write a new post
1.      Sign in to Blogger.
2.      Next to the title of the blog, click New Post.
3.      Create the post.
4.      To save your post without publishing it, click Save. To publish your post, click Publish.
Edit a published post
1.      Sign in to Blogger.
2.      Under the name of the blog, click Posts.
3.      Point to the post you want to edit.
4.      Click Edit.
5.      Make your changes.
6.      Optional: To see how your post will look, click Preview.
7.      To publish your changes, click Update. To save your post as a draft, click Revert to draft.
Edit a draft post
1.      Sign in to Blogger.
2.      Under the name of the blog, click Posts.
3.      Point to the post you want to edit.
4.      Click Edit.
5.      Make your changes.
6.      Optional: To see how your post will look, click Preview.
7.      To save your changes, click Save. To publish your post, click Publish.
Delete a post
1.      Sign in to Blogger.
2.      On the dashboard, click the blog.
3.      Point to the post you want to delete.
4.      Click Delete.
To delete multiple posts, select the posts to delete and click Delete Delete.
Note:
·         Once you delete a post, you won't be able to get it back.
·         If you shared your post in other places, like Google+, you'll need to delete it in those places separately.
Change settings on your posts
Add labels to your posts, schedule when your posts will be published, and choose how HTML works by changing these settings.
Categorize a post with labels
You can categorize your posts by adding keywords:
1.      Sign in to Blogger.
2.      Under the blog, click Posts.
3.      Select the posts you want to label.
4.      In the top left corner, click Label Label:
·         To add or remove a label, select the label in the list.
·         To make a new label, click New label.
Note: You can have a maximum of 5,000 labels per blog and 20 per post.
Schedule a post
1.      Sign in to Blogger.
2.      Under the name of the blog, click Posts.
3.      Click the title of the post.
4.      On the right, click Post settingsand thenSchedule.
5.      Choose a time and date to publish your post.
6.      Click Done.
To set your time zone:
1.      In the main dashboard, in the left menu, click Settingsand thenLanguage and formatting.
2.      Under “Formatting,” choose the time zone you want.
3.      In the top right corner, click Save settings.
Choose how HTML and line breaks are handled in your posts
1.      Sign in to Blogger.
2.      Under the name of the blog, click Posts.
3.      Click the title of the post you want to edit.
4.      On the right side, click Post settingsand thenOptions.
5.      Choose how HTML code is displayed when entered in Compose mode:
·         Show HTML literally: your blog shows HTML code as it is typed
·         Interpret typed HTML: your blog shows HTML code as formatted content
6.      Choose how line breaks are handled.
7.      Click Done.
8.      For posts that have already been published, click Update or Revert to draft. For posts that haven’t been published yet, click Publish or Save.
Other ways to customize your posts
Change how your blog looks, change the language, or choose to make posts using email.
Create a post template
Save time by automatically formatting your posts:
1.      Sign in to Blogger.
2.      Click the blog.
3.      In the left menu, click Settingsand thenPosts, comments and sharing.
4.      Under “Posts,” next to Post Template, click Add.
5.      Add your template.
6.      In the top right corner, click Save settings.
Add a "Read more" link
Instead of displaying the entire post, you can just show a summary with a link to “Read More:”
1.      Sign in to Blogger.
2.      Click the blog.
3.      Click the post.
4.      In the composer box, place your cursor where you want to put the “Read More” link.
5.      Click Insert jump break ic_blogger_18dp.png.
Use a different alphabet
You can type words in English and have them appear in another script, such as Arabic or Russian:
1.      Sign in to Blogger.
2.      Choose the blog.
3.      In the left menu, click Settingsand thenLanguage and formatting.
4.      Under “Language,” select Enable transliteration.
5.      Choose your language.
6.      In the top right, click Save settings.
7.      Open a page or post you want to edit.
8.      On the Post editor next to the spell check button, click the Transliteration button.
9.      Type the text you want. As you type, a drop-down list will appear with options in the script you picked. Choose the text you want to appear in the post or on the page.
10.  To change languages, next to the Transliteration button, click the Down arrow Down Arrow. Choose the language you want to use.
Use email to post to your blog
Turn on posting by email
1.      Sign in to Blogger.
2.      Choose the blog.
3.      In the left menu, click Settingsand thenEmail.
4.      Choose whether to publish emailed posts immediately or to save them as drafts and publish them yourself later.
5.      In the top right corner, click Save settings.
Post by email
1.      Create an email with your post title as the subject.
2.      The body of the email will be the post:
·         To mark the end of your post, enter #end.
·         To include an image, attach an image to your email.
3.      Send the email to the email address you created: (username.[secretword]@blogger.com).
Note: Anyone who emails this unique email address will be able to post as you on your blog.
Create posts with Open Live Writer
If you’re using Windows, you can use Open Live Writer to write posts for your blog.
Content to avoid adding to your blog
·         Avoid adding content hosted by someone else without their consent.
·         Avoid using copyrighted materials. Learn more about Blogger’s copyright policy.
·         Avoid adding content that violates our content policy. This includes, but is not limited to, explicit imagery posted without the subject’s consent and hateful, violent, or crude content.