Posts tagged with “pl/sql”

Quoting string literals in PL/SQL

We sometimes need to include a single quote in an SQL statement, I believe you already know the normal way, use two single quote characters to express one single quote. However, if you are using oracle 10g or a more recent version, the following way could be better:

This is a new feature of 10g that enables us to embed single-quotes in literal strings without having to resort to double, triple or sometimes quadruple quote characters. This is particularly useful for building dynamic SQL statements that contain quoted literals.

The mechanism is invoked with a simple "q" in PL/SQL only. The syntax is q'[...]', where the "[" and "]" characters can be any of the following as long as they do not already appear in the string.

  • !
  • [ ]
  • { }
  • ( )
  • < >

Note that at the time of writing, the quoting mechanism only appears to work with 10g clients/OCI. If used with any software that uses an older Oracle client it fails with ORA-01756: quoted string not properly terminated (confirmed with sqlplus, TOAD and PL/SQL Developer).

If you want samples, see Reference

PL/SQL: Local function definition in a public procedure / function

Occasionally, we will need to define local used functions in a public procedure or a function. Fortunately, PL/SQL supports embedded functions. I spent more than an hour trying to find the correct way to use this feature. Yes. forgive me, I am a little dumb. According to my retries, the best position to put your local functions is sharp before the BEGIN symbol. And here's a sample.

set serveroutput on;
CREATE OR REPLACE PROCEDURE tmp12345(v_full_name IN VARCHAR2) AS
	localv varchar2(100);
	-- local function definitaions start ---
	FUNCTION FETCH_REAL_NAME(v_statement_name IN VARCHAR2) RETURN VARCHAR2
	AS
		pattern VARCHAR2(100) := '^(Dr|Ms|Mx|Mr|Mrs)\s+';
		full_name varchar2(100);
	BEGIN
		-- replace possible multiple spaces to one space
		full_name := TRIM(REGEXP_REPLACE(v_statement_name, '\s+',  ' '));
		IF (REGEXP_LIKE(full_name, pattern, 'i')) THEN
			-- REGEXP_REPLACE(string, pattern [, replacement_string [, start_position [, nth_appearance [, match_parameter ] ] ] ]) 
			RETURN REGEXP_REPLACE(full_name, pattern, '', 1, 0, 'i');
		ELSE
			RETURN full_name;
		END IF;
	END;
	FUNCTION FETCH_MIDDLE_NAME(full_name IN VARCHAR) RETURN VARCHAR AS
		middle_name VARCHAR2(100);
	BEGIN
		middle_name := SUBSTR(full_name, INSTR(full_name, ' ') + 1, INSTR(full_name, ' ', 1, 2) - INSTR(full_name, ' ') - 1);
		IF (LENGTH(middle_name) > 10) THEN
			-- return initial instead
			RETURN SUBSTR(middle_name, 1, 1);
		ELSE
			RETURN middle_name;
		END IF;
	END;
	-- local functions definitaions end ---
BEGIN
	-- main logic here
	localv := v_full_name;
	DBMS_OUTPUT.PUT_LINE(localv);
	DBMS_OUTPUT.PUT_LINE(FETCH_MIDDLE_NAME(FETCH_REAL_NAME(v_full_name)));
END;
/

MySQL IF(EXPRESSION, A, B) equivalent in PL/SQL (Oracle)

In MySQL, we can 

SELECT IF(A IS NULL, "something", A) FROM some_table;

If you want to do a similar thing in PL/SQL, you need to

SELECT (CASE WHEN A IS NULL THEN "something" ELSE A END) FROM some_table;

If you want to get the first non-null value from several fields, you have a better choice than using the CASE keyword.

SELECT COALESCE(A, B) FROM some_table

see more information about the Oracle COALESCE function