Hemant's Oracle DBA Blog

I am an Oracle Database Specialist in Singapore. Please note that this site uses cookies.

05 September, 2016

Index of Posts

My friend, Ravi Muthupalani has created an Index of my Blog Posts.

Hemant's Oracle DBA Blog

As on 03-Oct-16

No comments:

Email This BlogThis!Share to X Share to Facebook Share to Pinterest

23 July, 2016

CODE : Persistent Variables via PL/SQL Package and DBMS_APPLICATION_INFO

I am now introducing some code samples in my blog. I won't restrict myself to SQL but will also include PL/SQL (C ? Bourne/Korn Shell ? what else ?)

This is the first of such samples.

Here I demonstrate using a PL/SQL Package to define persistent variables and then using them with DBMS_APPLICATION_INFO. This demo consists of only 2 variables being used by 1 session. But we could have a number of variables in this Package and invoked by multiple client sessions in the real workd.

I first :

SQL> grant create procedure to hr;

Grant succeeded.

SQL>

Then, in the HR schema, I setup a Package to define variables that can persist throughout a session. Public Variables defined in a Package, once invoked, persist throughout the session that invoked them.

create or replace package
define_my_variables
authid definer
is
  my_application varchar2(25) := 'Human Resources';
  my_base_schema varchar2(25) := 'HR';
end;
/

grant execute on define_my_variables to hemant;
grant select on employees to hemant;

As HEMANT, I then execute :

SQL> connect hemant/hemant  
Connected.
SQL> execute dbms_application_info.set_module(-
> module_name=>HR.define_my_variables.my_application,-
> action_name=>NULL);

PL/SQL procedure successfully completed.

SQL>

As SYSTEM, the DBA can monitor HEMANT

QL> show user
USER is "SYSTEM"
SQL> select sid, serial#, to_char(logon_time,'DD-MON HH24:MI:SS') Logon_At, module, action
  2  from v$session
  3  where username = 'HEMANT'
  4  order by 1
  5  /

       SID    SERIAL# LOGON_AT                 MODULE
---------- ---------- ------------------------ ----------------------------------------------------------------
ACTION
----------------------------------------------------------------
         1     63450 23-JUL 23:24:03           Human Resources



SQL>

Then, HEMANT intends to run a query on the EMPLOYEES Table.

SQL> execute dbms_application_info.set_action(-
> action_name=>'Query EMP');

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.employees where job_id like '%PROG%'
  2  /

  COUNT(*)
----------
         5

SQL>

SYSTEM can see what he is doing with

SQL> l
  1  select sid, serial#, to_char(logon_time,'DD-MON HH24:MI:SS') Logon_At, module, action
  2  from v$session
  3  where username = 'HEMANT'
  4* order by 1
SQL> /

       SID    SERIAL# LOGON_AT                 MODULE
---------- ---------- ------------------------ ----------------------------------------------------------------
ACTION
----------------------------------------------------------------
         1      63450 23-JUL 23:24:03          Human Resources
Query EMP


SQL>

Returning, to the HEMANT login, I can see :

SQL> show user
USER is "HEMANT"
SQL> execute dbms_output.put_line(-
> 'I am running ' || hr.define_my_variables.my_application || '  against ' || hr.define_my_variables.my_base_schema);
I am running Human Resources  against HR

PL/SQL procedure successfully completed.

SQL>

So, I have demonstrated :
1. Using a PLSQL Package Specification (without the need for a Package Body) to define variables that are visible to another session.

2. The possibility of using this across schemas. HR could be my "master schema" that setups all variables and HEMANT is one of many "client" schemas (or users) that use these variables..

3. The variables defined will persist throughout the client session once they are invoked.

4. Using DBMS_APPLICATION_INFO to call these variables and setup client information.

Note : SYSTEM can also trace HEMANT's session using DBMS_MONITOR as demonstrated in Trace Files -- 2 : Generating SQL Traces (another session)

.
.
.

No comments:

Email This BlogThis!Share to X Share to Facebook Share to Pinterest

11 July, 2016

Loading SQL*Plus HELP into the Database

Oracle provides scripts to load the HELP command for SQL*Plus.

See $ORACLE_HOME/sqlplus/admin/help

The schema to use is SYSTEM, not SYS.

I demonstrate
(a) How to load SQLPlus Help into the database
(b) How to customise the Help (e.g. add new commands)

[oracle@ora11204 help]$ cd $ORACLE_HOME/sqlplus/admin/help
[oracle@ora11204 help]$ ls -l
total 84
-rwxrwxrwx. 1 oracle oracle   265 Feb 17  2003 helpbld.sql
-rwxrwxrwx. 1 oracle oracle   366 Jan  4  2011 helpdrop.sql
-rwxrwxrwx. 1 oracle oracle 71817 Aug 17  2012 helpus.sql
-rwxrwxrwx. 1 oracle oracle  2154 Jan  4  2011 hlpbld.sql
[oracle@ora11204 help]$ sqlplus -S system/oracle @helpbld.sql `pwd` helpus.sql
...
...
...
View created.


58 rows created.


Commit complete.


PL/SQL procedure successfully completed.

[oracle@ora11204 help]$

The 'pwd` (note the back-quote character, not the single quote character) is a way of specifying the current directory in Unix and Linux shells. This specifies where the help datafile is located. helpus.sql is the help data in English (US-English).

The scripts create a table called "HELP" in the SYSTEM schema. SQL*Plus's "HELP" command then uses this table.

Examples :

SQL> connect hemant/hemant
Connected.
SQL> help

 HELP
 ----

 Accesses this command line help system. Enter HELP INDEX or ? INDEX
 for a list of topics.

 You can view SQL*Plus resources at
     http://www.oracle.com/technology/documentation/

 HELP|? [topic]


SQL> 
SQL> help set  

 SET
 ---

 Sets a system variable to alter the SQL*Plus environment settings
 for your current session. For example, to:
     -   set the display width for data
     -   customize HTML formatting
     -   enable or disable printing of column headings
     -   set the number of lines per page

 SET system_variable value

 where system_variable and value represent one of the following clauses:

   APPI[NFO]{OFF|ON|text}                   NEWP[AGE] {1|n|NONE}
   ARRAY[SIZE] {15|n}                       NULL text
   AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}      NUMF[ORMAT] format
   AUTOP[RINT] {OFF|ON}                     NUM[WIDTH] {10|n}
   AUTORECOVERY {OFF|ON}                    PAGES[IZE] {14|n}
   AUTOT[RACE] {OFF|ON|TRACE[ONLY]}         PAU[SE] {OFF|ON|text}
     [EXP[LAIN]] [STAT[ISTICS]]             RECSEP {WR[APPED]|EA[CH]|OFF}
   BLO[CKTERMINATOR] {.|c|ON|OFF}           RECSEPCHAR {_|c}
   CMDS[EP] {;|c|OFF|ON}                    SERVEROUT[PUT] {ON|OFF}
   COLSEP {_|text}                            [SIZE {n | UNLIMITED}]
   CON[CAT] {.|c|ON|OFF}                      [FOR[MAT]  {WRA[PPED] |
   COPYC[OMMIT] {0|n}                          WOR[D_WRAPPED] |
   COPYTYPECHECK {ON|OFF}                      TRU[NCATED]}]
   DEF[INE] {&|c|ON|OFF}                    SHIFT[INOUT] {VIS[IBLE] |
   DESCRIBE [DEPTH {1|n|ALL}]                 INV[ISIBLE]}
     [LINENUM {OFF|ON}] [INDENT {OFF|ON}]   SHOW[MODE] {OFF|ON}
   ECHO {OFF|ON}                            SQLBL[ANKLINES] {OFF|ON}
   EDITF[ILE] file_name[.ext]               SQLC[ASE] {MIX[ED] |
   EMB[EDDED] {OFF|ON}                        LO[WER] | UP[PER]}
   ERRORL[OGGING] {ON|OFF}                  SQLCO[NTINUE] {> | text}
     [TABLE [schema.]tablename]             SQLN[UMBER] {ON|OFF}
     [TRUNCATE] [IDENTIFIER identifier]     SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
   ESC[APE] {\|c|OFF|ON}                    SQLPRE[FIX] {#|c}
   ESCCHAR {@|?|%|$|OFF}                    SQLP[ROMPT] {SQL>|text}
   EXITC[OMMIT] {ON|OFF}                    SQLT[ERMINATOR] {;|c|ON|OFF}
   FEED[BACK] {6|n|ON|OFF}                  SUF[FIX] {SQL|text}
   FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}  TAB {ON|OFF}
   FLU[SH] {ON|OFF}                         TERM[OUT] {ON|OFF}
   HEA[DING] {ON|OFF}                       TI[ME] {OFF|ON}
   HEADS[EP] {||c|ON|OFF}                   TIMI[NG] {OFF|ON}
   INSTANCE [instance_path|LOCAL]           TRIM[OUT] {ON|OFF}
   LIN[ESIZE] {80|n}                        TRIMS[POOL] {OFF|ON}
   LOBOF[FSET] {1|n}                        UND[ERLINE] {-|c|ON|OFF}
   LOGSOURCE [pathname]                     VER[IFY] {ON|OFF}
   LONG {80|n}                              WRA[P] {ON|OFF}
   LONGC[HUNKSIZE] {80|n}                   XQUERY {BASEURI text|
   MARK[UP] HTML [OFF|ON]                     ORDERING{UNORDERED|
     [HEAD text] [BODY text] [TABLE text]              ORDERED|DEFAULT}|
     [ENTMAP {ON|OFF}]                        NODE{BYVALUE|BYREFERENCE|
     [SPOOL {OFF|ON}]                              DEFAULT}|
     [PRE[FORMAT] {OFF|ON}]                   CONTEXT text}


SQL> 
SQL> help show

 SHOW
 ----

 Shows the value of a SQL*Plus system variable, or the current
 SQL*Plus environment. SHOW SGA requires a DBA privileged login.

 SHO[W] option

 where option represents one of the following terms or clauses:
     system_variable
     ALL
     BTI[TLE]
     ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER
        | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
     LNO
     PARAMETERS [parameter_name]
     PNO
     RECYC[LEBIN] [original_name]
     REL[EASE]
     REPF[OOTER]
     REPH[EADER]
     SGA
     SPOO[L]
     SPPARAMETERS [parameter_name]
     SQLCODE
     TTI[TLE]
     USER


SQL> 
SQL> help connect

 CONNECT
 -------

 Connects a given username to the Oracle Database. When you run a
 CONNECT command, the site profile, glogin.sql, and the user profile,
 login.sql, are processed in that order. CONNECT does not reprompt
 for username or password if the initial connection does not succeed.

 CONN[ECT] [{logon|/|proxy} [AS {SYSOPER|SYSDBA|SYSASM}] [edition=value]]

 where logon has the following syntax:
     username[/password][@connect_identifier]

 where proxy has the syntax:
     proxyuser[username][/password][@connect_identifier]
 NOTE: Brackets around username in proxy are required syntax


SQL>

Remember ! These are SQL*Plus commands, not SQL Language commands. So you won't see help about CREATE or ALTER or SELECT and other such commands.

Since, it uses a plain-text file (helpus.sql in this case) to load the help information, it is possible to extend this.

For example, I copy helpus.sql as helpcustom.sql and add these lines into the scrip file :

INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 2, 'This Hemant''s Test Database');
INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 3, 'A Playground database');
INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 4, 'Running 11.2.0.4 on Linux');

INSERT INTO SYSTEM.HELP VALUES ('OWNERINFO', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('OWNERINFO', 2, 'Test Database owned by Hemant');
INSERT INTO SYSTEM.HELP VALUES ('CONTENTS', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('CONTENTS', 2, 'Various Experiments by Hemant');

INSERT INTO SYSTEM.HELP VALUES ('WHO IS HEMANT', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('WHO IS HEMANT', 2, 'Hemant K Chitale');
INSERT INTO SYSTEM.HELP VALUES ('WHO IS HEMANT', 3, 'https://hemantoracledba.blogspot.com');

COMMIT;

and then I run the command :

sqlplus -S system/oracle @helpbld.sql `pwd` helpcustom.sql

And view the results :

SQL> connect hemant/hemant
Connected.
SQL> help dbinfo

This Hemant's Test Database
A Playground database
Running 11.2.0.4 on Linux

SQL> help ownerinfo

Test Database owned by Hemant

SQL> help who is hemant

Hemant K Chitale
https://hemantoracledba.blogspot.com

SQL>         
SQL> help startup

 STARTUP
 -------

 Starts an Oracle instance with several options, including mounting,
 and opening a database.

 STARTUP options | upgrade_options

 where options has the following syntax:
    [FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] |
    [ OPEN [open_options] [dbname] ] |
    NOMOUNT ]

 where open_options has the following syntax:
    READ {ONLY | WRITE [RECOVER]} | RECOVER

 and where upgrade_options has the following syntax:
    [PFILE=filename] {UPGRADE | DOWNGRADE} [QUIET]


SQL> help shutdown

 SHUTDOWN
 --------

 Shuts down a currently running Oracle Database instance, optionally
 closing and dismounting a database.

 SHUTDOWN [ABORT|IMMEDIATE|NORMAL|TRANSACTIONAL [LOCAL]]


SQL>

And, so, the SQL*Plus HELP command can be customised !

.
.
.

No comments:

Email This BlogThis!Share to X Share to Facebook Share to Pinterest

Newer Posts Older Posts Home

Subscribe to: Posts (Atom)

Follow This Blog

Follow This Blog On follow.it

Search this Blog

Custom Search

My SQL Server DBA Blog

GitHub Site

My GitHub site -- Presentations and some code

YouTube Videos

YouTube Videos for beginners to Oracle and SQL Server

Blog Archive

▼ 2026 (5)
- ▼ August (1)
  - Tracing a Power BI DirectQuery Refresh in Oracle
- ► May (2)
- ► February (2)

► 2025 (18)
- ► December (1)
- ► November (2)
- ► October (1)
- ► September (1)
- ► August (2)
- ► July (2)
- ► June (2)
- ► May (1)
- ► March (2)
- ► February (1)
- ► January (3)

► 2024 (23)
- ► December (2)
- ► November (1)
- ► September (2)
- ► July (2)
- ► June (2)
- ► May (5)
- ► April (1)
- ► March (3)
- ► February (4)
- ► January (1)

► 2023 (5)
- ► December (1)
- ► October (1)
- ► September (1)
- ► August (1)
- ► January (1)

► 2022 (22)
- ► October (1)
- ► September (1)
- ► August (4)
- ► June (1)
- ► May (2)
- ► April (2)
- ► March (4)
- ► February (1)
- ► January (6)

► 2021 (43)
- ► December (3)
- ► November (4)
- ► October (4)
- ► September (2)
- ► August (3)
- ► July (4)
- ► June (2)
- ► May (10)
- ► April (4)
- ► March (1)
- ► February (2)
- ► January (4)

► 2020 (44)
- ► December (2)
- ► November (4)
- ► October (5)
- ► September (5)
- ► August (8)
- ► July (5)
- ► June (4)
- ► May (4)
- ► March (3)
- ► February (3)
- ► January (1)

► 2019 (22)
- ► November (1)
- ► October (4)
- ► September (5)
- ► August (4)
- ► May (2)
- ► April (2)
- ► March (3)
- ► January (1)

► 2018 (30)
- ► December (3)
- ► November (4)
- ► October (1)
- ► September (4)
- ► August (7)
- ► July (1)
- ► June (3)
- ► May (2)
- ► April (2)
- ► March (2)
- ► January (1)

► 2017 (36)
- ► December (2)
- ► November (2)
- ► September (3)
- ► August (1)
- ► July (4)
- ► June (3)
- ► May (3)
- ► April (6)
- ► March (6)
- ► February (1)
- ► January (5)

► 2016 (68)
- ► December (6)
- ► November (6)
- ► October (5)
- ► September (4)
- ► July (3)
- ► June (7)
- ► May (9)
- ► April (14)
- ► March (7)
- ► February (5)
- ► January (2)

► 2015 (53)
- ► December (6)
- ► November (6)
- ► October (4)
- ► September (5)
- ► August (3)
- ► July (6)
- ► June (3)
- ► May (4)
- ► April (2)
- ► March (6)
- ► February (6)
- ► January (2)

► 2014 (40)
- ► December (2)
- ► November (2)
- ► October (3)
- ► September (4)
- ► August (3)
- ► July (5)
- ► June (3)
- ► May (3)
- ► April (2)
- ► March (4)
- ► February (6)
- ► January (3)

► 2013 (44)
- ► December (3)
- ► November (3)
- ► October (2)
- ► September (2)
- ► August (7)
- ► July (6)
- ► June (7)
- ► May (2)
- ► April (4)
- ► March (3)
- ► February (2)
- ► January (3)

► 2012 (50)
- ► December (1)
- ► November (1)
- ► October (1)
- ► September (3)
- ► August (2)
- ► July (4)
- ► June (5)
- ► May (8)
- ► April (6)
- ► March (7)
- ► February (5)
- ► January (7)

► 2011 (99)
- ► December (5)
- ► November (12)
- ► October (7)
- ► September (9)
- ► August (7)
- ► July (11)
- ► June (12)
- ► May (8)
- ► April (6)
- ► March (6)
- ► February (7)
- ► January (9)

► 2010 (59)
- ► December (2)
- ► November (4)
- ► October (4)
- ► September (4)
- ► August (6)
- ► July (5)
- ► June (3)
- ► May (5)
- ► April (7)
- ► March (6)
- ► February (9)
- ► January (4)

► 2009 (48)
- ► November (3)
- ► October (3)
- ► September (5)
- ► August (3)
- ► July (5)
- ► June (2)
- ► May (8)
- ► April (4)
- ► March (6)
- ► February (7)
- ► January (2)

► 2008 (62)
- ► November (7)
- ► October (3)
- ► September (1)
- ► August (8)
- ► July (5)
- ► June (6)
- ► May (9)
- ► April (10)
- ► March (6)
- ► February (3)
- ► January (4)

► 2007 (48)
- ► December (2)
- ► November (1)
- ► October (2)
- ► September (2)
- ► August (6)
- ► July (3)
- ► June (4)
- ► May (7)
- ► April (5)
- ► March (6)
- ► February (3)
- ► January (7)

► 2006 (3)
- ► December (3)

Blog Roll

Mike Dietriche

Transportable Tablespaces fails with ORA-01647 – TABLESPACE IS READ-ONLY
1 day ago
The ORACLE-BASE Blog

Oracle Support : An Update
2 days ago
Oracle Scratchpad. Jonathan Lewis

Savepoint Funny
1 month ago
Oracle related stuff. Randolf Geist

Aggregated by orafaq.com

This blog is being aggregated by orafaq.com

OCP 12c

Oracle Database Certified Professional 12c