Clear Oracle Schema all object (Be careful)

SET SERVEROUTPUT ON

DECLARE

  l_count    NUMBER;

  l_cascade  VARCHAR2(20);

BEGIN

  << dependency_failure_loop >>

  FOR i IN 1 .. 5 LOOP

    EXIT dependency_failure_loop WHEN l_count = 0;

    l_count := 0;

    

    FOR cur_rec IN (SELECT object_name, object_type 

                    FROM   user_objects) LOOP

      BEGIN

        l_count := l_count + 1;

        l_cascade := NULL;

        IF cur_rec.object_type = 'TABLE' THEN

          l_cascade := ' CASCADE CONSTRAINTS';

        END IF;

        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"' || l_cascade;

      EXCEPTION

        WHEN OTHERS THEN

          NULL;

      END;

    END LOOP;

    -- Comment out the following line if you are pre-10g, or want to preserve the recyclebin contents. 

    EXECUTE IMMEDIATE 'PURGE RECYCLEBIN';

    DBMS_OUTPUT.put_line('Pass: ' || i || '  Drops: ' || l_count);

  END LOOP;

END;

/

You are not authorized to view this application, either because you have not been granted access, or your account has been locked. Please contact the application administrator.

Before 

Solution 

Total employee count in years wise in oracle sql

    SELECT COUNT (*),

          COUNT (DECODE (TO_CHAR (hire_date, 'RRRR'), '1980', employee_id)) "1980",

         COUNT (DECODE (TO_CHAR (hire_date, 'RRRR'), '1981', employee_id)) "1981",

         COUNT (DECODE (TO_CHAR (hire_date, 'RRRR'), '1982', employee_id)) "1982",

         COUNT (DECODE (TO_CHAR (hire_date, 'RRRR'), '1983', employee_id)) "1983"

    FROM employees

  COUNT(*)       1980       1981       1982       1983

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

       107          0          0          0          0

    SELECT COUNT (*),

          COUNT (DECODE (TO_CHAR (hire_date, 'RR'), '02', employee_id)) "02",

          COUNT (DECODE (TO_CHAR (hire_date, 'RR'), '03', employee_id)) "03",

          COUNT (DECODE (TO_CHAR (hire_date, 'RR'), '04', employee_id)) "04",

         COUNT (DECODE (TO_CHAR (hire_date, 'RR'), '05', employee_id)) "05"

    FROM employees

  COUNT(*)         02         03         04         05

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

       107          7          6         10         29

Conditionally Fiscal year in oracle PL/SQL Function

 create or replace FUNCTION       get_fin_yr ( p_date date ) return varchar2

is 

v varchar2(100);

begin

null;

if to_char(p_date,'mm') between 7 and 12 then 

v:= to_char( p_date,'rrrr') ||'-'|| substr(  to_number( to_char( p_date,'rrrr'))  +1,-2) ;

else 

v:= ( to_number( to_char( p_date,'rrrr')) -1 )||   '-'|| substr(  to_number( to_char( p_date,'rrrr'))  ,-2) ;

end if;

return v;

end;

Conditionally date in oracle PL/SQL Function

create or replace FUNCTION f_get_start_date ( p_date date ) return date

is

v date;

begin

null;

if to_char(P_DATE,'dd') between 16 and 31 then

SELECT (TRUNC(TO_DATE(P_DATE,'DD-MON-RR'),'MON')+15) INTO V FROM DUAL;

else

SELECT ADD_MONTHS(TRUNC(TO_DATE(P_DATE,'DD-MON-RR'),'MON')+15,-1) INTO V FROM DUAL;

end if;

return v;

end;

//////////////

create or replace FUNCTION       f_get_end_date ( p_date date ) return date

is 

v date;

begin

null;

if  to_char(p_date,'dd') between 01 and 15 then 

 SELECT (TRUNC(TO_DATE(P_DATE,'DD-MON-RR'),'MON')+15)-1 INTO V FROM DUAL;

 

else 

 SELECT (ADD_MONTHS(TRUNC(TO_DATE(P_DATE,'DD-MON-RR'),'MON')+14,+1)) INTO V FROM DUAL;

end if;

return v;

end;

select devapp.test_get_start_date('30-JUN-21') dd, devapp.test_get_end_date('30-JUN-21') ddd from dual;

Download Oracle Link

Download Oracle WebLogic Server

https://www.oracle.com/middleware/technologies/weblogic-server-installers-downloads.html

Display running total as 0 when null value - Crystal Reports

Tick ‘Convert Database NULL Values to Default’ and click ‘OK‘ to save the changes.

Convert Oracle Form to Apex Using frmf2xml Utility

Below are the steps are given to perform this task.

1. Convert Oracle Form to XML using frmf2xml.bat

Run the frmf2xml.bat by providing your FMB file with its location from the Oracle form’s instance home directory as shown below:

CD C:OracleMiddlewareasinst_2bin

frmf2xml.bat d:temptestab.fmb

It will convert and save the XML file into the same location of your form.

2. Create an Oracle Apex Workspace.
Run Oracle Apex and create a workspace with the same Oracle Forms application schema.

3. Create a migration project in the workspace you created. App Builder > Migrations
Log in to the workspace and create the migration project and import the XML you created.

4. Create the Oracle Apex Application and Associate with the Migration Project.
Create a new application using in Application Builder and then go to App Builder > Migrations and edit your project and associate the project with your new Apex application.

Select current APEX version and user schema

-- Select current APEX user over all_users/dba_usersselect max(u1.username) as current_apex_user
from all_users u1
where u1.username like 'APEX%'
and REGEXP_LIKE (substr(u1.username,'6',1), '^[0-9]*$');

-- CURRENT_APEX_USER
---------------------
-- APEX_200200

-- Select current version over dba_registry
select comp_name, version from dba_registry
where comp_name = 'Oracle Application Express';

-- COMP_NAME VERSION_NO
-------------------------------------------
-- Oracle Application Express 20.2.0.00.20

-- Select current version over apex_release
select * from apex_release;

-- VERSION_NO API_COMPATIBILITY PATCH_APPLIED
----------------------------------------------------
-- 20.2.0.00.20 2020.10.01 APPLIED

Sumaya Akter Assignment

1. What do you know about Bangabandhu Sheikh Mujibur Rahman?



2. When and where was he giving the historic speech of 7 march

3. Which features of the speech do you appreciate most ? why?

4. What is the significance of the historic 7 march speech

5. Can you compare this speech with others famous speeches in history you know about?

6. Write a paragraph on "The Historic 7 March Speech"



a) What do you know about Bangabandhu Sheikh Mujibur Rahman?



Sheikh Mujibur Rahman (Bengali: শেখ মুজিবুর রহমান; 17 March 1920 – 15 August 1975), shortened as Sheikh Mujib or just Mujib, was a Bangladeshi politician and statesman. He is called the "Father of the Nation" in Bangladesh. He served as the first President of Bangladesh and later as the Prime Minister of Bangladesh from 17 April 1971 until his assassination on 15 August 1975. He is considered to be the driving force behind the independence of Bangladesh. He is popularly dubbed with the title of "Bangabandhu" (Bôngobondhu "Friend of Bengal") by the people of Bangladesh.

b) When and where was he giving the historic speech of 7 march

The 7 March Speech of Bangabandhu was a speech given by Sheikh Mujibur Rahman, the founding father of Bangladesh on 7 March 1971 at the Ramna Race Course in Dhaka to a gathering of over 10 lac people



c) Which features of the speech do you appreciate most ? why?

The 7 March Speech of Bangabandhu was a speech given by Sheikh Mujibur Rahman, the founding father of Bangladesh on 7 March 1971 at the Ramna Race Course in Dhaka to a gathering of over 10 lac people. It was delivered during a period of escalating tensions between East Pakistan and the powerful political and military establishment of West Pakistan, this was filmed by the actor. In the speech, Rahman proclaimed: "This time the struggle is for our freedom. This time the struggle is for our independence." He announced a civil disobedience movement in the province, calling for "every house to turn into a fortress". The speech inspired the Bengali people to prepare for a war of independence amid widespread reports of armed mobilisation by West Pakistan. The Bangladesh Liberation War began 18 days later when the Pakistan Army launched Operation Searchlight against Bengali civilians, intelligentsia, students, politicians, and armed personnel. On 30 October 2017, UNESCO added the speech in the Memory of the World Register as a documentary heritage.

d) What is the significance of the historic 7 march speech

This time the struggle is for our independence." He announced a civil disobedience movement in the province, calling for "every house to turn into a fortress". The speech inspired the Bengali people to prepare for a war of independence amid widespread reports of armed



Write a paragraph on "The Historic 7 March Speech"

The Speech of 7th March

The historic 7th March’s speech of Bangabandhu Sheikh Mujibur Rahman is the main inspiration of our Liberation war. It bears a great importance in our national life. This fiery speech is a part of the world’s documentary heritage. Sheikh Mujibur Rahman delivered this speech on 7th March, 1971 in the then Race Course Ground (present Suhrawardy Uddyan) in Dhaka. In this speech, Bangabandhu declared, “This struggle is for our freedom, this struggle is for our independence.” Following this speech, people from all walks of life got prepared to take part in the Liberation war. The speech of 7th March has inspired about two millions of Bengalee people. The speech has touched the soul of every Bangladeshi people. Even after 1971, this speech has encouraged all other movements in Bangladesh. In this speech, he requested the people to convert every house into a fort. He declared the people to take up weapons against the enemy. In his speech he described about cruel activities of West Pakistani rulers. The importance of the 7th March speech is immeasurable. The speech of 7th March was an eye-opening declaration for the nation that we should stand together and free our country from enemy. Now it's a matter of great pride for us that as UNESCO has recognized the speech and the whole world will be able to know about our liberation war.

ora-00838: specified value of memory_target is too small

SQL> startup
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3072M



Make your calculation 3072*1024*1024 = 3221225472

create pfile='F:\oracle192\app\OracleHomeUser2\admin\orcl\pfile\init.ora' from spfile;

edit file and put the above number in memory_target.

Create spfile from pfile='F:\oracle192\app\OracleHomeUser2\admin\orcl\pfile\init.ora' ;

Startup ;




LIVE

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 10 09:39:44 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Enter user-name: sys as sysdba

Enter password:

Connected to an idle instance.

SQL> startup

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3216M

ORA-01078: failure in processing system parameters

SQL> startup

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3216M

ORA-01078: failure in processing system parameters

SQL> create pfile='F:\oracle192\app\OracleHomeUser2\admin\orcl\pfile\init.ora' from spfile;

File created.

SQL> create spfile from pfile='F:\oracle192\app\OracleHomeUser2\admin\orcl\pfile\init.ora';

File created.

SQL> startup

ORACLE instance started.

Total System Global Area 3372218944 bytes

Fixed Size 9062976 bytes

Variable Size 1371537408 bytes

Database Buffers 1983905792 bytes

Redo Buffers 7712768 bytes

Database mounted.

Database opened.

SQL> conn sys as sysdba

Enter password:

Connected.

SQL>

How to Install Oracle Apex 20.2

# install Oracle apex 20.2

# User unlock

# ords configure