Oracle’s dbms_utility package contains a lot of neat functionality for the database developer. Today we?re going to look at the analyze_schema procedure. The analyze_schema procedure can be used to generate statistics for Oracle’s cost-based optimizer. The parameters are: Procedure analyze_schema
How to count number of characters in a string
CREATE OR REPLACE FUNCTION count_char ( p_data VARCHAR2 ,p_char VARCHAR2 DEFAULT ‘ ‘ ) RETURN VARCHAR2 IS v_loc NUMBER := 0; v_count NUMBER := 0; v_new_str VARCHAR2(32000); j binary_integer default 1; — BEGIN FOR i IN 1 .. LENGTH (p_data)
How to count the number of occurences in a string
/* This function will return the number of occurences that p_sub_string appears in p_string Unlike instr that only counts the position of a sub-string in a string. */ CREATE OR REPLACE FUNCTION occurs (p_string IN VARCHAR2, p_sub_string IN VARCHAR2) RETURN
How to check for numbers in a varchar column
If you have a table with a VARCHAR2 column in it, that means it may contain any number of values, i.e. numbers or characters, if you want to check that the column only has numbers or you only want to
How to transpose rows to columns in Oracle
The objective of this function is to transpose rows to columns. This RowToCol function is built with invoker-rights AUTHID CURRENT_USER. The function works on tables/views of the user who invokes the function, not on the owner of this function. RowToCol
How to convert a comma string to an insert string
This function will split an input string separated by commas into a string enclosed with single quotes. This is helful in applying conditions in dynamic SQL. dyn_string1 Function: create or replace function dyn_string1(ab varchar2)return varchar2 as t varchar2(2):=’,’; t1 varchar2(2):=””;