Query the view ALL_SOURCE. It has a field called TYPE, which says whether the object is a FUNCTION or a PACKAGE or a PACKAGE BODY or a PROCEDURE.
The field TEXT gives the actual source code for that object.
Example: SELECT TEXT FROM ALL_SOURCE WHERE NAME='FUNCTION_NAME';
Need SQL script to remove dash ('-') from data.
data in field = '5110-1' should be '51101'
Here is the function that can eliminate any string from the given string.
create or replace function DELETE_CHAR( CHAR_TYPE varchar2, STRING_TYPE varchar2 )
return varchar2 as
RETURN_STRING varchar2(1000) := STRING_TYPE;
STRING_LENGTH number := 0;
CHAR_LENGTH number := 0;
CHAR_POSITION number := 0;
begin
select length(STRING_TYPE) into STRING_LENGTH from dual;
select length(CHAR_TYPE) into CHAR_LENGTH from dual;
if STRING_LENGTH > 0 and CHAR_LENGTH > 0 then
select instr(STRING_TYPE, CHAR_TYPE) into CHAR_POSITION from dual;
if CHAR_POSITION = 0 then
return RETURN_STRING;
else
RETURN_STRING := substr(STRING_TYPE, 1, CHAR_POSITION-1)||substr(STRING_TYPE, CHAR_POSITION+CHAR_LENGTH, STRING_LENGTH);
RETURN_STRING := DELETE_CHAR( CHAR_TYPE, RETURN_STRING );
end if;
end if;
return RETURN_STRING;
end;
/
Example:
select delete_char('-', '111-11-1111') from dual;
ANSWER: 111111111
select delete_char('-', SSN) from EMP;
There is an Oracle built in function REPLACE to do the same job.
Ex: Select REPLACE('111-11-1111','-') from dual;
Ans: 111111111
Ex: Select REPLACE('111-11-1111','-', '*') from dual;
Ans: 111*11*1111
