EXAMPLE OF CONTINUE IN PLSQL 11G

SQL> DECLARE

  2   V_CNTR NUMBER:=0;

  3   V_CURR_DATE DATE :=SYSDATE;

  4  BEGIN

  5   FOR I IN 1..31 LOOP

  6     CONTINUE WHEN V_CURR_DATE>LAST_DAY(SYSDATE);

  7     &D(V_CURR_DATE);

  8     V_CURR_DATE:=SYSDATE+I;

  9   END LOOP;

 10  END;

 11  /

08-AUG-15

09-AUG-15

10-AUG-15

11-AUG-15

12-AUG-15

13-AUG-15

14-AUG-15

15-AUG-15

16-AUG-15

17-AUG-15

18-AUG-15

19-AUG-15

20-AUG-15

21-AUG-15

22-AUG-15

23-AUG-15

24-AUG-15

25-AUG-15

26-AUG-15

27-AUG-15

28-AUG-15

29-AUG-15

30-AUG-15

31-AUG-15

ABBREVIATION FUNCTION

SQL> CREATE OR REPLACE FUNCTION USER_ABBRIVATION(P_TEXT IN VARCHAR2) RETURN VARCHAR2 IS

  2  V_RET_VAL VARCHAR2(2000):=SUBSTR(P_TEXT,1,1);

  3  V_EXCEPTION EXCEPTION;

  4  V_ERRM   VARCHAR2(1000);

  5  V_WORD   CHAR(1);

  6  BEGIN

  7   IF P_TEXT IS NULL  OR LENGTH(P_TEXT)<=1 THEN

  8    V_ERRM:='TEXT VALUE MUST BE MORE THEN ONE CHARACTER, AND CONTAIN SPACES, BLANK VALUES NOT APPLICABLE HERE..';

  9    RAISE V_EXCEPTION;

 10   ELSE

 11     FOR I IN 1..LENGTH(P_TEXT) LOOP

 12     V_WORD := substr(P_TEXT,I, 1);

 13     IF V_WORD = ' ' THEN

 14        V_RET_VAL := V_RET_VAL||substr(P_TEXT,i+1,1);

 15     END IF;

 16     END LOOP;

 17   END IF;

 18   RETURN V_RET_VAL;

 19  EXCEPTION

 20   WHEN V_EXCEPTION THEN

 21   RAISE_APPLICATION_ERROR(-20191,V_ERRM);

 22  END USER_ABBRIVATION;

 23  /

Function created.

SQL> SELECT USER_ABBRIVATION('&ANY_COMPANY') FROM DUAL;

Enter value for any_company: PAKISTAN INTERNATIONAL AIRLINE

USER_ABBRIVATION('PAKISTANINTERNATIONALAIRLINE')

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

PIA

1 row selected.

SQL> SELECT USER_ABBRIVATION('&ANY_COMPANY') FROM DUAL;

Enter value for any_company: WORLD HEALTH ORGANIZATION

USER_ABBRIVATION('WORLDHEALTHORGANIZATION')

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

WHO

1 row selected.

Adding current OS user to ORA_DBA

Open the Command Prompt with Run as Administrator

c:\windows\system32>cd\

c:\> net user
--Show all the users of current OS

C:\> net localgroup
--Show all the group of current PC OS

C:\>net localgroup ora_dba /Add <OS user name>

--Add the OS username to a localgroup

Adopted From Another BLOG of Halim.
SQL> SHOW USER
USER is "SCOTT"
SQL> CREATE OR REPLACE PROCEDURE PR_Oracle_table_to_csv_or_txt (
  2  p_tname IN VARCHAR2,
  3  p_dir IN VARCHAR2,
  4  p_filename IN VARCHAR2
  5  )
  6  IS
  7  l_output UTL_FILE.file_type;
  8  l_thecursor INTEGER DEFAULT DBMS_SQL.open_cursor;
  9  l_columnvalue VARCHAR2 (4000);
 10  l_status INTEGER;
 11  l_query VARCHAR2 (1000) DEFAULT 'select * from ' || p_tname;
 12  l_colcnt NUMBER := 0;
 13  l_separator VARCHAR2 (1);
 14  l_desctbl DBMS_SQL.desc_tab;
 15  BEGIN
 16  l_output := UTL_FILE.fopen (p_dir, p_filename, 'w');
 17
 18  EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
 19
 20  DBMS_SQL.parse (l_thecursor, l_query, DBMS_SQL.native);
 21  DBMS_SQL.describe_columns (l_thecursor, l_colcnt, l_desctbl);
 22
 23  FOR i IN 1 .. l_colcnt
 24  LOOP
 25  UTL_FILE.put (l_output,
 26  l_separator || '"' || l_desctbl (i).col_name || '"'
 27  );
 28  DBMS_SQL.define_column (l_thecursor, i, l_columnvalue, 4000);
 29  l_separator := ',';
 30  END LOOP;
 31
 32  UTL_FILE.new_line (l_output);
 33  l_status := DBMS_SQL.EXECUTE (l_thecursor);
 34
 35  WHILE (DBMS_SQL.fetch_rows (l_thecursor) > 0)
 36  LOOP
 37  l_separator := '';
 38
 39  FOR i IN 1 .. l_colcnt
 40  LOOP
 41  DBMS_SQL.COLUMN_VALUE (l_thecursor, i, l_columnvalue);
 42  UTL_FILE.put (l_output, l_separator || l_columnvalue);
 43  l_separator := ',';
 44  END LOOP;
 45
 46  UTL_FILE.new_line (l_output);
 47  END LOOP;
 48
 49  DBMS_SQL.close_cursor (l_thecursor);
 50  UTL_FILE.fclose (l_output);
 51
 52  EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy'' ';
 53  EXCEPTION
 54  WHEN OTHERS
 55  THEN
 56  EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy'' ';
 57
 58  RAISE;
 59  END;
 60  /

Procedure created.

SQL> DESC PR_ORACLE_TABLE_TO_CSV_OR_TXT
PROCEDURE PR_ORACLE_TABLE_TO_CSV_OR_TXT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_TNAME                        VARCHAR2                IN
 P_DIR                          VARCHAR2                IN
 P_FILENAME                     VARCHAR2                IN

SQL> CONN SYS/ORACLE AS SYSDBA
Connected.
USER is "SYS"
trimspool ON
linesize 100
pagesize 100
long 4000
verify OFF
FEEDBACK ON for 1 or more rows
SQL> CREATE DIRECTORY DIR_SCOTT AS 'F:\emp_image';

Directory created.

SQL> GRANT READ,WRITE ON DIR_SCOTT TO SCOTT;
GRANT READ,WRITE ON DIR_SCOTT TO SCOTT
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> GRANT READ,WRITE ON DIRECTORY DIR_SCOTT TO SCOTT;

Grant succeeded.

SQL> CONN SCOTT/TIGER
Connected.
USER is "SCOTT"
trimspool ON
linesize 100
pagesize 100
long 4000
verify OFF
FEEDBACK ON for 1 or more rows

SQL> exec PR_ORACLE_TABLE_TO_CSV_OR_TXT( 'emp', 'DIR_SCOTT', 'emp_in_csv.csv' );

PL/SQL procedure successfully completed.

SQL> SPOOL OFF