Oracle Developer Courses

start Oracle developer private course
SQL
PL/SQL
FORMS
REPORTS

1 months

price: 500$

email: yasser.hassan@yandex.com

Oracle Developer Course

Private courses for Oracle developer start from 3000 Egyptian pound

Learn Oracle developer

Soon we will start Oracle developer course,
especial prices
email me on : yasser.hassan@yandex.com

Learn Oracle developer

Learn Oracle developer with me

Oracle Forms, create folder

HOST('md c:\somefolder\newfolder');

Learn Oracle Developer

email me: Yasser.hassan@yandex.com

Learn Oracle Developer with me

Oracle Developer

Oracle SQL Developer is a free integrated development environment that simplifies the development and management of Oracle Database in both traditional and Cloud deployments. SQL Developer offers complete end-to-end development of your PL/SQL applications, a worksheet for running queries and scripts, a DBA console for managing the database, a reports interface, a complete data modeling solution, and a migration platform for moving your 3rd party databases to Oracle.

Learn Oracle Developer

email me for private course
Yasser.hassan@yandex.com

Learn Oracle with me

Email me for private Course
Yasser.hassan@yandex.com

Learn Oracle with me

REF CURSOR

A REF Cursor is a datatype that holds a  cursor value in the same way that a varchar2 variable will hold a string value.
A REF Cursor can be opened on the server and passed to the client as a unit rather than fetching one row at a time. One can use a Ref Cursor as target of an assignment, and it can be passed as parameter to other program units. Ref Cursors are opened with an OPEN FOR statement. In most other ways they behave similar to normal cursors.

History

This feature was introduced with PL v2.3 (Oracle 7.3).

Example

Create a function that opens a cursor and returns a reference to it:

CREATE OR REPLACE FUNCTION f RETURN SYS_REFCURSOR
AS
  c SYS_REFCURSOR;
BEGIN
  OPEN c FOR select * from dual;
  RETURN c;
END;
/

Call the above function and fetch all rows from the cursor it returns:
set serveroutput on
DECLARE
 c SYS_REFCURSOR;
v VARCHAR2(1);
BEGIN
c := f(); -- Get ref cursor from function
 LOOP FETCH c into v;
EXIT WHEN c%NOTFOUND;
dbms_output.put_line('Value from cursor: '||v);
END LOOP;
END;
/

Yasser

Learn Oracle

Your way to learn Oracle Developer

Oracle forms get WEEK DAYS

PROCEDURE GET_YWEEKDAYS(P_WORK_DATE IN DATE,
                          P_DAY_NAME  IN OUT VARCHAR2) 
IS
BEGIN
 DECLARE
  TXT NUMBER(1);
 BEGIN
   TXT := TO_NUMBER(TO_CHAR(P_WORK_DATE,'D'));
if :global.direction =1 then
   if     TXT = 7 
   then 
      P_DAY_NAME := 'ÇáÓÈÊ';
   elsif  TXT = 1 
   then 
      P_DAY_NAME := 'ÇáÇÍÏ';
   elsif  TXT = 2
   then 
      P_DAY_NAME := 'ÇáÇËäíä';
   elsif  TXT = 3
   then 
      P_DAY_NAME := 'ÇáËáÇËÇÁ';
   elsif  TXT = 4
   then 
      P_DAY_NAME := 'ÇáÇÑÈÚÇÁ';
   elsif  TXT = 5
   then 
      P_DAY_NAME := 'ÇáÎãíÓ';
   elsif  TXT = 6
   then 
      P_DAY_NAME := 'ÇáÌãÚå';  
   end if;
else
   if     TXT = 7 
   then 
      P_DAY_NAME := 'Saturday';
   elsif  TXT = 1 
   then 
      P_DAY_NAME := 'Sunday';
   elsif  TXT = 2
   then 
      P_DAY_NAME := 'Monday';
   elsif  TXT = 3
   then 
      P_DAY_NAME := 'Tuesday';
   elsif  TXT = 4
   then 
      P_DAY_NAME := 'Wednesday';
   elsif  TXT = 5
   then 
      P_DAY_NAME := 'Thursday';
   elsif  TXT = 6
   then 
      P_DAY_NAME := 'Friday';  
   end if;

end if;

Oracle Developer private courses

Oracle developer full course 500$

Oracle forms, Parameter form

1. create a parameter list
2. add the parameter to the param
3. call_form using this parameter list:

DECLARE
    fParams ParamList := null;
BEGIN 
  fParams := Get_Parameter_List('SOME_NAME'); 
  IF (not Id_Null(fParams)) THEN 
    destroy_parameter_list(fList);
  END IF;

  fParams := Create_Parameter_List('SOME_NAME');
  Add_Parameter(fParams, 'PARAM_NAME', TEXT_PARAMETER, 'PARAM_VALUE'); 
  OPEN_FORM('Form_Name', ACTIVATE, SESSION, NO_SHARE_LIBRARY_DATA, fParams);
END;

Oracle forms : Call a form

DECLARE

     param_list_id  ParamList;   -- Define an object of type paramlist

BEGIN

     param_list_id := GET_PARAMETER_LIST(‘my_parameter');

     -- Test if the parameter list already exists.

     IF NOT ID_NULL(param_list_id) THEN

          DESTROY_PARAMETER_LIST(param_list_id);

     END IF;

     param_list_id := CREATE_PARAMETER_LIST('my_parameter');

     ADD_PARAMETER(param_list_id, 'p_customer_id', TEXT_PARAMETER, :ID);

     CALL_FORM('c:\portfolio', NO_HIDE, DO_REPLACE, NO_QUERY_ONLY, param_list_id);

END;

Run report from form

PROCEDURE Run_Emp_Report IS
pl_id ParamList;
BEGIN
/*
** Check to see if the ’tmpdata’ parameter list exists.
*/
pl_id := Get_Parameter_List(’tmpdata’);
/*
** If it does, then delete it before we create it again in
** case it contains parameters that are not useful for our
** purposes here.
*/
IF NOT Id_Null(pl_id) THEN
Destroy_Parameter_List( pl_id );
END IF;
/*
** Create the ’tmpdata’ parameter list afresh.
*/
pl_id := Create_Parameter_List(’tmpdata’);
/*
** Add a data parameter to this parameter list that will
** establish the relationship between the named query
** ’EMP_QUERY’ in the report, and the record group named
** ’EMP_RECS’ in the form.
*/
Add_Parameter(pl_id,’EMP_QUERY’,DATA_PARAMETER,’EMP_RECS’);
/*
**Pass a Parameter into PARAMFORM so that a parameter dialog
will not appear
**for the parameters being passing in.
*/
Add_Parameter(pl_id, ’PARAMFORM’, TEXT_PARAMETER, ’NO’);
/*
** Run the report synchronously, passing the parameter list
*/
Run_Product(REPORTS, ’empreport’, SYNCHRONOUS, RUNTIME,
FILESYSTEM, pl_id, NULL);
END;

PROCEDURE Run_Emp_Report IS
pl_id ParamList;
BEGIN
/*
** Check to see if the ’tmpdata’ parameter list exists.
*/
pl_id := Get_Parameter_List(’tmpdata’);
/*
** If it does, then delete it before we create it again in
** case it contains parameters that are not useful for our
** purposes here.
*/
IF NOT Id_Null(pl_id) THEN
Destroy_Parameter_List( pl_id );
END IF;
/*
** Create the ’tmpdata’ parameter list afresh.
*/
pl_id := Create_Parameter_List(’tmpdata’);
/*
** Add a data parameter to this parameter list that will
** establish the relationship between the named query
** ’EMP_QUERY’ in the report, and the record group named
** ’EMP_RECS’ in the form.
*/
Add_Parameter(pl_id,’EMP_QUERY’,DATA_PARAMETER,’EMP_RECS’);
/*
**Pass a Parameter into PARAMFORM so that a parameter dialog
will not appear
**for the parameters being passing in.
*/
Add_Parameter(pl_id, ’PARAMFORM’, TEXT_PARAMETER, ’NO’);
/*
** Run the report synchronously, passing the parameter list
*/
Run_Product(REPORTS, ’empreport’, SYNCHRONOUS, RUNTIME,
FILESYSTEM, pl_id, NULL);
END; - See more at: http://www.foxinfotech.in/2013/03/running-reports-in-oracle-forms-6i.html#sthash.4Bkehzpn.dpuf

PROCEDURE Run_Emp_Report IS
pl_id ParamList;
BEGIN
/*
** Check to see if the ’tmpdata’ parameter list exists.
*/
pl_id := Get_Parameter_List(’tmpdata’);
/*
** If it does, then delete it before we create it again in
** case it contains parameters that are not useful for our
** purposes here.
*/
IF NOT Id_Null(pl_id) THEN
Destroy_Parameter_List( pl_id );
END IF;
/*
** Create the ’tmpdata’ parameter list afresh.
*/
pl_id := Create_Parameter_List(’tmpdata’);
/*
** Add a data parameter to this parameter list that will
** establish the relationship between the named query
** ’EMP_QUERY’ in the report, and the record group named
** ’EMP_RECS’ in the form.
*/
Add_Parameter(pl_id,’EMP_QUERY’,DATA_PARAMETER,’EMP_RECS’);
/*
**Pass a Parameter into PARAMFORM so that a parameter dialog
will not appear
**for the parameters being passing in.
*/
Add_Parameter(pl_id, ’PARAMFORM’, TEXT_PARAMETER, ’NO’);
/*
** Run the report synchronously, passing the parameter list
*/
Run_Product(REPORTS, ’empreport’, SYNCHRONOUS, RUNTIME,
FILESYSTEM, pl_id, NULL);
END; - See more at: http://www.foxinfotech.in/2013/03/running-reports-in-oracle-forms-6i.html#sthash.4Bkehzpn.dpuf

PROCEDURE Run_Emp_Report IS
pl_id ParamList;
BEGIN
/*
** Check to see if the ’tmpdata’ parameter list exists.
*/
pl_id := Get_Parameter_List(’tmpdata’);
/*
** If it does, then delete it before we create it again in
** case it contains parameters that are not useful for our
** purposes here.
*/
IF NOT Id_Null(pl_id) THEN
Destroy_Parameter_List( pl_id );
END IF;
/*
** Create the ’tmpdata’ parameter list afresh.
*/
pl_id := Create_Parameter_List(’tmpdata’);
/*
** Add a data parameter to this parameter list that will
** establish the relationship between the named query
** ’EMP_QUERY’ in the report, and the record group named
** ’EMP_RECS’ in the form.
*/
Add_Parameter(pl_id,’EMP_QUERY’,DATA_PARAMETER,’EMP_RECS’);
/*
**Pass a Parameter into PARAMFORM so that a parameter dialog
will not appear
**for the parameters being passing in.
*/
Add_Parameter(pl_id, ’PARAMFORM’, TEXT_PARAMETER, ’NO’);
/*
** Run the report synchronously, passing the parameter list
*/
Run_Product(REPORTS, ’empreport’, SYNCHRONOUS, RUNTIME,
FILESYSTEM, pl_id, NULL);
END; - See more at: http://www.foxinfotech.in/2013/03/running-reports-in-oracle-forms-6i.html#sthash.4Bkehzpn.dpuf

PROCEDURE Run_Emp_Report IS
pl_id ParamList;
BEGIN
/*
** Check to see if the ’tmpdata’ parameter list exists.
*/
pl_id := Get_Parameter_List(’tmpdata’);
/*
** If it does, then delete it before we create it again in
** case it contains parameters that are not useful for our
** purposes here.
*/
IF NOT Id_Null(pl_id) THEN
Destroy_Parameter_List( pl_id );
END IF;
/*
** Create the ’tmpdata’ parameter list afresh.
*/
pl_id := Create_Parameter_List(’tmpdata’);
/*
** Add a data parameter to this parameter list that will
** establish the relationship between the named query
** ’EMP_QUERY’ in the report, and the record group named
** ’EMP_RECS’ in the form.
*/
Add_Parameter(pl_id,’EMP_QUERY’,DATA_PARAMETER,’EMP_RECS’);
/*
**Pass a Parameter into PARAMFORM so that a parameter dialog
will not appear
**for the parameters being passing in.
*/
Add_Parameter(pl_id, ’PARAMFORM’, TEXT_PARAMETER, ’NO’);
/*
** Run the report synchronously, passing the parameter list
*/
Run_Product(REPORTS, ’empreport’, SYNCHRONOUS, RUNTIME,
FILESYSTEM, pl_id, NULL);
END; - See more at: http://www.foxinfotech.in/2013/03/running-reports-in-oracle-forms-6i.html#sthash.4Bkehzpn.dpuf

PROCEDURE Run_Emp_Report IS
pl_id ParamList;
BEGIN
/*
** Check to see if the ’tmpdata’ parameter list exists.
*/
pl_id := Get_Parameter_List(’tmpdata’);
/*
** If it does, then delete it before we create it again in
** case it contains parameters that are not useful for our
** purposes here.
*/
IF NOT Id_Null(pl_id) THEN
Destroy_Parameter_List( pl_id );
END IF;
/*
** Create the ’tmpdata’ parameter list afresh.
*/
pl_id := Create_Parameter_List(’tmpdata’);
/*
** Add a data parameter to this parameter list that will
** establish the relationship between the named query
** ’EMP_QUERY’ in the report, and the record group named
** ’EMP_RECS’ in the form.
*/
Add_Parameter(pl_id,’EMP_QUERY’,DATA_PARAMETER,’EMP_RECS’);
/*
**Pass a Parameter into PARAMFORM so that a parameter dialog
will not appear
**for the parameters being passing in.
*/
Add_Parameter(pl_id, ’PARAMFORM’, TEXT_PARAMETER, ’NO’);
/*
** Run the report synchronously, passing the parameter list
*/
Run_Product(REPORTS, ’empreport’, SYNCHRONOUS, RUNTIME,
FILESYSTEM, pl_id, NULL);
END; - See more at: http://www.foxinfotech.in/2013/03/running-reports-in-oracle-forms-6i.html#sthash.4Bkehzpn.dpuf

learn oracle plsql

Learn oracle plsql
120$

learn oracle sql

Learn oracle SQL 100$

Oracle Example for Run_product

DECLARE
  pl_id paramlist;
  filename varchar2(200);
 
BEGIN
 
  pl_id := get_parameter_list('pl_name');
  if not id_null(pl_id) then
      destroy_parameter_list(pl_id);
  end if;

  pl_id := create_parameter_list('pl_name');
  add_parameter(pl_id,'wcaller_form',text_parameter,:EMP_NO2);
  add_parameter(pl_id,'paramform',text_parameter,'no');
  ADD_PARAMETER(pl_id,'DESTYPE',TEXT_PARAMETER,'FILE');
  ADD_PARAMETER(pl_id,'DESFORMAT',TEXT_PARAMETER,'RTF');
  --ADD_PARAMETER(pl_id,'DESNAME',TEXT_PARAMETER,return_path||'\REPORT_NAME.RTF'||i.emp);
  filename := return_path||'called_reprot.rep';
  run_product(REPORTS,filename,SYNCHRONOUS,RUNTIME,FILESYSTEM,pl_id,NULL);
 
  message(' emp no = '||:EMP_NO2);
  --

 
END;

ORacle report generate to RTF

ADD_PARAMETER(pl_id,'DESTYPE',TEXT_PARAMETER,'FILE');
ADD_PARAMETER(pl_id,'DESFORMAT',TEXT_PARAMETER,'RTF');
ADD_PARAMETER(pl_id,'DESNAME',TEXT_PARAMETER,'FILE_PATH\REPORT_NAME.RTF');

Oracle when tables created

full detials about your database tables from dba_objects

SELECT created
  FROM dba_objects
 WHERE object_name = <<your table name>>
   AND owner = <<owner of the table>>
   AND object_type = 'TABLE'