There is a standard way of trapping any SQL or database errors in Pro*C.This is done using EXEC SQL WHENEVER SQLERROR DO ; just ensure that the above statement goes before any sql statements whenever an error is encountered , the control goes to the function function_name in the body of the function_name , just…
1. EXEC SQL COMMIT WORK RELEASE; (When you want to commit transactions and disconnect;similar to EXIT in SQL*PLUS) 2. EXEC SQL ROLLBACK WORK RELEASE; (When you want to rollback transactions and disconnect;similar to QUIT in SQL*PLUS)
select * from v$session_longops where sid=100;
exec dbms_output.enable(10000); set lines 130 pages 1000 set serveroutput on size 1000000 declare cursor c1 is select substr(name,1,61) complete_filename, bytes/(1024) KB, substr(name, instr(name, ‘/’, -1)+1) only_filename from v$datafile order by file#; threshold_kb number(10) := 94000000 ; size_counter number := 0; mountpoint_counter number := 1; mountpoint_prefix varchar2(10) := ‘/800t’; common_mountpoint_prefix varchar2(15) := ‘/oradata/800T/’; target_filename varchar2(61); begin…
ALTER SEQUENCE CS.SESSION_ACTIVE_SEQ_ID CACHE 2000; exec sys.DBMS_SHARED_POOL.KEEP(‘CS.SESSION_ACTIVE_SEQ_ID ‘, ‘Q’) =>> To pin the sequence using the DMBS_SHARED_POOL.KEEP procedure (IF NEEDED) Potential drawbacks Setting a cache size larger then 0 can result in the loss of sequence values if the system is shutdown abruptly. When the system fails, the values that were preserved in memory are…
11g Environment Setting: ======================= —————————————————————– ==>cat .profile . /oracle/app/oracle/set_env_var_dba PS1=” `hostname`*${ORACLE_SID}-${PWD} ==>” EDITOR=vi; export EDITOR set -o vi stty erase PATH=$PATH:. dba() { cd /oracle/app/oracle/scripts/MAINT/DBA/bin } ———————————————————————————— ==>cat /oracle/app/oracle/set_env_var_dba # This File Sets all the Oracle Environment Variables and is called from # Each Users’s . profile file. # # Please Don’t change these settings…
export INSTANCE_NAME=`echo $ORACLE_SID | tr ‘[:upper:]’ ‘[:lower:]’` export ORA_BDUMP=$ORACLE_HOME/admin/”$ORACLE_SID”_`hostname`/diag/rdbms/$INSTANCE_NAME/$ORACLE_SID/trace
A common misconception is that the ‘audit any procedure’ is a privileges auditing option associated with the privilege ‘alter any procedure’ and thus does NOT cover the alteration of a procedure that is owned by the user altering it. Note that AUDIT PROCEDURE covers: CREATE FUNCTION CREATE LIBRARY CREATE PACKAGE CREATE PACKAGE BODY CREATE PROCEDURE…
EXTPROC Scope and Application A list of the essential components for executing an external procedure is included below, along with a brief description of the role of each. Database: Responsible for storing any information required to locate and execute an external procedure (e.g. the location of shared library, number and types of arguments, etc.) Initiates…
column currdate new_value pvmdt noprint select ‘pvm_comp_’ || to_char(SYSDATE, ‘DDMONRRRR_HH24MI’) || ‘.sql’ currdate from v$instance; spool &pvmdt set lines 120 pages 2000 set head off set feedback off prompt set pages 5000 lines 120 prompt select ‘prompt ‘||a.owner||’.’||a.object_name||’ – ‘||a.object_type|| chr(10)|| prompt ‘alter ‘|| prompt decode(a.object_type,’PACKAGE BODY’,’PACKAGE’,object_type) || ‘ “‘|| prompt a.owner||’”.’||a.object_name||’ compile ‘|| prompt…
Read More “run this before doing any dbchange pvm_pre_change.sql” »
