Case Sensitive Passwords in Oracle Database 11g Release 1

Case sensetive login password

 ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

Yasser

Lear HTML

 https://www.websiteplanet.com/blog/html-guide-beginners/

Oracle Select all column and one in the begining

Oracle Select all column and one in the begining

example

 select i.BONUS_ITEM_FLAG,i.* 

from SAL_INVOICES_ITEMS i ;

My youtube links

 https://www.youtube.com/channel/UCDlpipY3srYehE9EcqoN_kQ/

Oracle Forms add_list_element

ADD_LIST_ELEMENT built-in

Description

Adds a single element to a list item.

Syntax

PROCEDURE ADD_LIST_ELEMENT (list_name VARCHAR2, list_index, NUMBER list_label VARCHAR2, list_value NUMBER);

PROCEDURE ADD_LIST_ELEMENT (list_id ITEM,

list_index VARCHAR2, list_label VARCHAR2, list_value NUMBER);

Built-in Type  unrestricted procedure

Enter Query Mode  yes

Parameters

list_id   Specifies the unique ID that Form Builder assigns when it creates the list

item.  Use the FIND_ITEM built-in to return the ID to an appropriately

typed variable.  The data type of the ID is ITEM.

list_name         The name you gave to the list item when you created it.  The data type of

                           the name is VARCHAR2.

list_index      (counter)        Specifies the list index value.  The list index is 1 based.

list_label       Specifies the VARCHAR2 string that you want displayed as the label of the

                       list element.

list_value       The actual list element value you intend to add to the list item.

------------------------

ADD_LIST_ELEMENT restrictions

For a base table list with the List Style property set to Poplist or T-list, Form Builder does not allow you to add another values element when the block contains queried or changed records. Doing so causes an error. This situation can occur if you have previously used DELETE_LIST_ELEMENT or CLEAR_LIST to remove the other values element that was specified at design time by the Mapping of Other Values list item property setting.

Note: The block status is QUERY when a block contains queried records. The block status is CHANGED when a block contains records that have been either inserted or updated.

ADD_LIST_ELEMENT Example:

------

PROCEDURE YLISTE IS

cursor c is 

select CODES_ID,       

USER_CODE ||' - '||

DECODE(GET_USER_LANG(:GLOBAL.LANGUAGE_ID), 'P', PRIMARY_NAME, SECONDARY_NAME) ITEM_NAME

 from codes where code_types_id = 583007;

cnt number := 1;

it_id ITEM;

begin

it_id := Find_Item('SAL_INVOICES.DRIVER_ID');

clear_list(it_id);

for i in c loop

add_list_element(it_id,cnt,i.ITEM_NAME,i.CODES_ID);

cnt := cnt + 1;

end loop;

end; 

-----------

Yasser

Oracle With Clause Syntax and Examples :

Oracle With Clause Syntax and Examples :

In this section i would like to explain the syntax as well as examples of With clause in oracle.Before checking the syntax and examples of With clause in oracle let us first check some important bullet points of With Clause :

With Clause in Oracle  is released in Oracle 9i release 2 to improve the performance of complex sql queries.

The clause works like a global temporary tables of oracle which is used to improve the query speed of complex sql queries.

This technique is also called as sub-query factoring as it is used to De-factor the subqueries.

With clause in oracle is not supported by all oracle versions ,the oracle version 9i and beyond versions.

When sub-query needs to be executed multiple times at that time With clause is used.

The name which is assigned to the sub-query is treated as though it was an inline view or table.

The With Clause is useful in Recursive queries as well.

WITH 

  items_costs AS (

    SELECT items_id, SUM(UNIT_COST) items_total

    FROM   STOCK_IN_DOCUMENTS_ITEMS e, STOCK_IN_DOCUMENTS d

    WHERE  e.document_id = d.document_id

         and e.STORES_ID  = d.STORES_ID

    GROUP BY items_id),

  avg_cost AS (

    SELECT SUM(items_total)/COUNT(*) avg

    FROM   items_costs)

SELECT *

FROM   items_costs

WHERE  items_total > (SELECT avg FROM avg_cost)

ORDER BY items_id;

Oracle script for learning schema scott, table emp, dept and salgrade

 EMP, DEPT and SALGRADE Tables Data

--->> EMP Table

CREATE TABLE EMP

       (EMPNO NUMBER(4) NOT NULL,

        ENAME VARCHAR2(10),

        JOB VARCHAR2(9),

        MGR NUMBER(4),

        HIREDATE DATE,

        SAL NUMBER(7, 2),

        COMM NUMBER(7, 2),

        DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (7369, 'SMITH',  'CLERK',     7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);

INSERT INTO EMP VALUES (7499, 'ALLEN',  'SALESMAN',  7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);

INSERT INTO EMP VALUES (7521, 'WARD',   'SALESMAN',  7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);

INSERT INTO EMP VALUES (7566, 'JONES',  'MANAGER',   7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);

INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN',  7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

INSERT INTO EMP VALUES (7698, 'BLAKE',  'MANAGER',   7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);

INSERT INTO EMP VALUES (7782, 'CLARK',  'MANAGER',   7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);

INSERT INTO EMP VALUES (7788, 'SCOTT',  'ANALYST',   7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

INSERT INTO EMP VALUES (7839, 'KING',   'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN',  7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500, NULL, 30);

INSERT INTO EMP VALUES (7876, 'ADAMS',  'CLERK',     7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

INSERT INTO EMP VALUES (7900, 'JAMES',  'CLERK',     7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);

INSERT INTO EMP VALUES (7902, 'FORD',   'ANALYST',   7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);

INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK',     7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

--->> DEPT Table

CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');

INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');

INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

--->> SALGRADE Table

CREATE TABLE SALGRADE (GRADE NUMBER,LOSAL NUMBER,HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1,  700, 1200);

INSERT INTO SALGRADE VALUES (2, 1201, 1400);

INSERT INTO SALGRADE VALUES (3, 1401, 2000);

INSERT INTO SALGRADE VALUES (4, 2001, 3000);

INSERT INTO SALGRADE VALUES (5, 3001, 9999);

COMMIT;