Showing posts with label PL/SQL. Show all posts
Showing posts with label PL/SQL. Show all posts
Friday, July 19, 2024

What do do if SET SERVEROUTPUT ON doesn't work

›
If you have an anonymous piece of pl/sql code and you have turned on serveroutput, like this: set serveroutput on You may need to enable...
Tuesday, August 15, 2023

Simple PL/SQL script to alter parameter in database

›
At my current workplace, we use emcli for mass-updates of database parameters in groups of databases. Below is a simple PL/SQL script that...
Tuesday, August 2, 2022

How to change an Oracle instance parameter based on database version and container type

›
Here is a simple pl/sql script that will set parameters based on version and container type. set serveroutput on DECLARE v_version ...
Thursday, January 11, 2018

How to bulk-grant privileges to a selection of users using PL/SQL

›
This anonymous PL/SQL script will select all the desired users, and then grant SELECT on a number of performance views to these. set serve...

How to check for table partitions using PL/SQL

›
I created the following piece of code a long time ago. It checks if a table is partitioned or not, and list its partition. It takes the ta...
Wednesday, November 8, 2017

How to use ROWTYPE to fetch entire rows for insert into an interim table

›
Here is how I used a PL/SQL record to insert rows from a table into an interim table, using parallel slaves and direct path: alter session ...
Wednesday, January 20, 2016

What is the package SYS.USER_LOCK?

›
Recenty I was migrating a database with a package that wouldn't compile because it couldn't find the package called upon in its code...
Wednesday, May 27, 2015

How to create a stored procedure for partition maintenance, executed by the Oracle Scheduler.

›
I needed to setup a small job that would remove partitions from a table with records older than 35 days. The procedure should be flexible e...
1 comment:
Tuesday, September 2, 2014

How to convert stored outlines to use SQL Plan Baselines

›
If you have been using stored outlines for plan stability in versions prior to Oracle 11g, you should migrate them to use SQL Plan Baseline...
Thursday, July 10, 2014

A convenient way to declare variables in PL/SQL

›
In PL/SQL programming, a convenient (and programmatically safe) way to specify data types for your variables is to let them be inherited fro...
Tuesday, January 28, 2014

How to save a value in a bind variable

›
Useful for tracing or explaining queries that use bind variables: connect scott/tiger var leom char(10); var eod char(10); exec :leom := t...
Tuesday, January 21, 2014

How to use the DBMS_FILE_TRANSFER.PUT_FILE procedure

›
BEGIN SYS.DBMS_FILE_TRANSFER.PUT_FILE( source_directory_object => 'DPUMP', source_file_name => 'm...
Friday, January 10, 2014

How to use SYS_CONTEXT to display user information

›
Oracle provides a built-in namespace called USERENV, which describes the current session. The function SYS_CONTEXT can be used to return ...

How to retreive information about CPUs on a Solaris server

›
For analytic purposes when dealing with installations, system validations, database design and performance tuning, you will often need to kn...
Thursday, October 31, 2013

How to add a sleep statement to your pl/sql programs

›
Humans will often need some time to react to the output from your scripts. To briefly "pause" the output from a pl/sql script, add...
Wednesday, October 30, 2013

How to print the predefined error messages in the error number in pl/sql

›
dbms_output.put_line(sqlerrm(sqlcode));