12C

https://www.oracle.com/database/index.html

18C NEW IN

http://blog.enmotech.com/2017/12/17/15-oracle-18c-new-features-for-oracle-data-guard-whats-news/

Run 10g Report on Oracle Forms

Declare



         repid REPORT_OBJECT;
         v_rep VARCHAR2(1000);
         rep_status VARCHAR2(20);
         PL_ID PARAMLIST;
         pr_nm varchar2(20):='PNM';

         --rwserver server=SHOWJOBS EXCECUTE IT ON RUN
       
         
          v_rptserver varchar2(10):=:global.RWS1; ----- Change 1
          v_host varchar2(20):=:global.IP1; ---- Change 2


BEGIN



         PL_ID := GET_PARAMETER_LIST(pr_nm);
     
         IF ID_NULL(PL_ID) = FALSE THEN
          DESTROY_PARAMETER_LIST(PL_ID);
          PL_ID := CREATE_PARAMETER_LIST(pr_nm);   
       IF ID_NULL(PL_ID) THEN 
        MESSAGE('ERROR CREATING PARAMETER LIST '||pr_nm);
          RAISE FORM_TRIGGER_FAILURE;   
       END IF;
       ELSE PL_ID := CREATE_PARAMETER_LIST(pr_nm);
       
       END IF;
   
   
         --add parameter with separate statement for each parameter
     


        repid := find_report_object('COST');
     
     

     
         SET_REPORT_OBJECT_PROPERTY(repid,REPORT_EXECUTION_MODE,BATCH);
         SET_REPORT_OBJECT_PROPERTY(repid,REPORT_COMM_MODE,SYNCHRONOUS);
         SET_REPORT_OBJECT_PROPERTY(repid,REPORT_DESTYPE,CACHE);
     
     
         SET_REPORT_OBJECT_PROPERTY(repid,REPORT_DESFORMAT,'PDF');                              --pdf/html
        -- SET_report_object_property(repid,REPORT_DESFORMAT,:BLOCK3.REP_FORMAT);                      ------all format
         SET_REPORT_OBJECT_PROPERTY(repid,REPORT_SERVER,v_rptserver);
       
       
         v_rep := RUN_REPORT_OBJECT(repid,PL_ID);
         rep_status := REPORT_OBJECT_STATUS(v_rep);
     
     
    IF rep_status = 'FINISHED' THEN

       WEB.SHOW_DOCUMENT('http://'||v_host||':8889/reports/rwservlet/getjobid' ||substr(v_rep,instr(v_rep,'_',-1)+1)||'?'||'server='||v_rptserver,'_blank');

    ELSE
   
   
   
       message('Error when running report');
     
     
    END IF;
END;

how to center the windows oracle forms 10g

This code work 10g 11g 12c for Center the windows

Declare

v_window varchar2(1000):='WINDOW1';
v_width number(4);
v_height number(4);
vw number(4);
vh number(4);

BEGIN
SET_WINDOW_PROPERTY(FORMS_MDI_WINDOW,WINDOW_STATE,MAXIMIZE);
SET_WINDOW_PROPERTY('WINDOW1',WINDOW_STATE,MAXIMIZE);
set_window_property(forms_mdi_window,title,'KAYUMITBD LIMITED HUMAN RESOURCE MANAGEMENT SYSTEM (HRMS)');
vw:=gET_application_PROPERTY(DISPLAY_WIDTH)-50;   ---change your wise
vh :=gET_application_PROPERTY(DISPLAY_HEIGHT)-140;  ---change your wise

v_width := Get_Window_Property(v_WINDOW, WIDTH);
v_height:= Get_Window_Property(v_WINDOW, HEIGHT);

Set_Window_Property(v_WINDOW, POSITION, (vw-v_width)/2, (vh-v_height)/2) ;

--replace_menu(:global.path||'HRM10g\Personal\FORMS\PERSONAL_MENU.MMX');




END;

Displaying Icon on Button in Oracle Forms 12c

1.
Star following services 
StarWeblogic admin Server
StarWeblogic Server WLS_Forms 


2.
 First create folder icons & paste      .gif --image  files below
C:\oracle\Middleware\Oracle_Home\forms\java\icons

3.
create icons.jar file 
open cmd from windows+R command

Microsoft Windows [Version 10.0.10586]
(c) 2016 Microsoft Corporation. All rights reserved.

C:\Users\Administrator>cd C:\oracle\Middleware\Oracle_Home\forms\java\icons

C:\oracle\Middleware\Oracle_Home\forms\java\icons>C:\oracle\Middleware\Oracle_Home\oracle_common\jdk\bin\jar.exe -cvf icons.jar *.gif
added manifest
adding: act.gif(in = 1375) (out= 1380)(deflated 0%)
adding: clear.gif(in = 1206) (out= 1033)(deflated 14%)
adding: copy.gif(in = 998) (out= 1003)(deflated 0%)
adding: cquery.gif(in = 1558) (out= 1563)(deflated 0%)
adding: cut.gif(in = 1041) (out= 756)(deflated 27%)
adding: delete.gif(in = 504) (out= 482)(deflated 4%)
adding: delete11.gif(in = 1430) (out= 1435)(deflated 0%)
adding: esearch.gif(in = 931) (out= 763)(deflated 18%)
adding: exitn.gif(in = 1316) (out= 1321)(deflated 0%)
adding: insert.gif(in = 1578) (out= 1583)(deflated 0%)
adding: login.gif(in = 1006) (out= 722)(deflated 28%)
adding: next.gif(in = 1060) (out= 1065)(deflated 0%)
adding: paste.gif(in = 1154) (out= 1132)(deflated 1%)
adding: pdf.gif(in = 1836) (out= 1841)(deflated 0%)
adding: previous.gif(in = 1064) (out= 1069)(deflated 0%)
adding: print.gif(in = 1719) (out= 1724)(deflated 0%)
adding: print555.gif(in = 16777) (out= 3083)(deflated 81%)
adding: saven.gif(in = 1375) (out= 1380)(deflated 0%)
adding: search.gif(in = 1251) (out= 1050)(deflated 16%)
adding: staricon.gif(in = 897) (out= 576)(deflated 35%)

C:\oracle\Middleware\Oracle_Home\forms\java\icons>



icons.jar file created see your folder

4.
change formsweb.cfg file

C:\oracle\Middleware\Oracle_Home\user_projects\domains\fr\config\fmwconfig\servers\WLS_FORMS\applications\formsapp_12.2.1\config\formsweb.cfg

# Forms applet parameter
codebase=/forms/java
# Forms applet parameter

imageBase=codebase

# Forms applet archive setting for other clients (Java Plugin, Appletviewer, etc)

archive=frmall.jar,icons.jar

baseHTMLjpi=webutiljpi.htm

archive=frmall.jar,icons.jar


C:\Oracle\Middleware\Oracle_Home\user_projects\domains\fr\config\fmwconfig\servers\WLS_FORMS\applications\formsapp_12.2.1\config\oracle\forms\registry

#default.icons.iconpath=
default.icons.iconpath=icons/
default.icons.iconextension=gif

5.
C:\oracle\Middleware\Oracle_Home\user_projects\domains\fr\config\fmwconfig\servers\WLS_FORMS\applications\formsapp_12.2.1\config
CLASSPATH=;C:\oracle\Middleware\Oracle_Home\forms\java\icons.jar

6. 
copy icons.jar to paste java C:\oracle\Middleware\Oracle_Home\forms\java

7.
StarWeblogic Server WLS_Forms


8.
button 
Set forms file iconic file name yes
set iconic filename ...your file image_name.gif 

9.
Run forms it is successfully display image 

Import dmp file in oracle 12c database

SQL> conn starerp/starerp@orcldb
Connected.
SQL>
SQL>
SQL>
SQL>
SQL> host imp

Import: Release 12.2.0.1.0 - Production on Mon Jan 8 14:51:57 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Username: starerp
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Import data only (yes/no): no >

Import file: EXPDAT.DMP > E:\12c_DMP\STARERP-08-01-2018.dmp

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V12.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
IMP-00403:

Warning: This import generated a separate SQL file "import_sys" which contains DDL that failed due to a privilege issue.

List contents of import file only (yes/no): no >

Ignore create error due to object existence (yes/no): no >

Import grants (yes/no): yes >

Import table data (yes/no): yes >

Import entire export file (yes/no): no > y

IMP-00001: respond with either yes, no, RETURN or '.' to quit
Import entire export file (yes/no): no >
Username: starerp

Enter table(T) or partition(T:P) names. Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done:

. importing STARERP's objects into STARERP
. importing STARERP's objects into STARERP
. . importing table             "STRERP_USER_INFO"          2 rows imported
. . importing table             "STRERP_MENU_INFO"         25 rows imported
. . importing table          "STRERP_REPSERVER12C"          1 rows imported
. . importing table           "STRERP_USER_MODULE"         37 rows imported
IMP-00041: Warning: object created with compilation warnings
 "CREATE TRIGGER "STARERP"."ENCRYPTED_DATA_BIUR_TRG" "
 "BEFORE INSERT OR UPDATE of user_password ON STRERP_USER_INFO"
 "FOR EACH ROW"
 "DECLARE"
 "BEGIN"
 "  :new.user_password := toolkit.encrypt(:new.user_password);"
 "END;"
About to enable constraints...
Import terminated successfully with warnings.

Table Export in oracle 12c Database

conn strerp/strerp@kayum1

SQL> host exp

Export: Release 12.2.0.1.0 - Production on Mon Jan 8 14:49:53 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.


Username: strerp/strerp@kaium

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Enter array fetch buffer size: 4096 >

Export file: EXPDAT.DMP > E:\12c_DMP\STARERP-08-01-2018.dmp

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > strerp_user_info

. . exporting table               STRERP_USER_INFO          2 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > strerp_menu_info

. . exporting table               STRERP_MENU_INFO         25 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > strerp_repserver12c

. . exporting table            STRERP_REPSERVER12C          1 rows exported
EXP-00091: Exporting questionable statistics.
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > strerp_user_menu

EXP-00011: STARERP.STRERP_USER_MENU does not exist
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > strerp_user_module

. . exporting table             STRERP_USER_MODULE         37 rows exported
EXP-00091: Exporting questionable statistics.
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

Export terminated successfully with warnings.

Database Roles

select * from session_roles;

ROLE
------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
EXECUTE_CATALOG_ROLE
HS_ADMIN_EXECUTE_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS

ROLE
------------------------------
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDB_SET_INVOKER
OLAP_XS_ADMIN
OLAP_DBA

19 rows selected.

Drop Structure

Column Drop (be Carefull) 

alter table table_name drop column col_name1;  --one column table

/

alter table table_name drop(col_name1, col_name2)  --more columns in table 

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

alter table APPLPATH12c drop column FOLDER;

/

alter table APPLPATH12c drop (IP3,IP2);  

Column Name Format a20

column MENU_PATH  Format a20
column MENU_DESC  Format a20

Multiple column is single row (alpha_examall)

create or replace function alpha_examall (p_st_dt date,p_end_dt date, P_div_cd varchar2,P_dept_cd varchar2, p_desg_cd varchar2)
return varchar2
as
cursor c1 is
select distinct EXAM_INFO.exam_name
from emp_mast,EMP_EDU,EXAM_INFO
where emp_mast.emp_id=EMP_EDU.emp_id
and EMP_EDU.EXAM_CD=EXAM_INFO.EXAM_CD
and emp_mast.div_cd=P_div_cd
and emp_mast.dept_cd=P_dept_cd
and emp_mast.desg_cd=P_desg_cd
and emp_mast.flag='R'
and emp_mast.conf_fg<>'4'
and to_date(emp_mast.resig_dt) between p_st_dt and p_end_dt;

v_ret  varchar2(200) := '';

begin
for r1 in c1 loop
v_ret := v_ret||r1.exam_name||', ';
end loop;
return v_ret;
exception
when others then
return 'err';
end;