How to assemble dynamic Oracle SQL statements

Oracle dynamic SQL statements are easy to use, but the assembly process is too annoying. Especially when the date field is involved in the Assembly Statement, the to_char must be added during the Assembly to convert it to a character first.

Oracle dynamic SQL statements are easy to use, but the assembly process is too annoying. Especially when the date field is involved in the Assembly Statement, the to_char must be added during the Assembly to convert it to a character first.

Oracle dynamic SQL statements are easy to use, but the assembly process is too annoying. Especially when a date field is involved in the Assembly Statement, to_char must be added during the Assembly and converted to a character. In SQL, to_date must be converted to the date type and compared with the original field.

For example, an SQL statement:

Select '= and (t. created> = to_date (''' | to_char (sysdate, 'yyyy-mm-dd') | ''', ''yyyy-mm-dd '') AND t. created <to_date (''' | to_char (sysdate + 1, 'yyyy-mm-dd') | ''', ''yyyy-mm-dd '')) 'from dual;

It converts sysdate to a string and then converts the string to date in the generated SQL statement.

The assembled results are as follows:

=========== And (t. created> = to_date ('2017-11-08 ', 'yyyy-mm-dd') AND t. created <to_date ('2017-11-09 ', 'yyyy-mm-dd '))

The string 2012-11-08 is generated using to_char (sysdate, 'yyyy-mm-dd'). Each single quotation mark involved in the statement must be written as two single quotation marks to escape.

Although the assembly process is annoying, as long as you master three points, you should be able to assemble usable SQL statements.

1. Determine the target first. Make sure that the assembled SQL statement is what it looks like, and then configure the dynamic SQL statement.

2. When assembling SQL statements, all connected objects must be of the varchar2 type. This type of object starts with a single quotation mark and ends with a single quotation mark. The number is automatically converted, but date needs to be converted by using the to_char function.

3. If there are quotation marks, it is written as two single quotation marks.

For example, 'I am a SQL developer ''' | v_name | ''' in China. telephone is' | v_number | '.'

V_name is character-type, so it must be enclosed by single quotation marks.

This type of conversion is annoying, but there is a new function starting from 10 Gb, which can be annoying. It is q'[ xxxxx] '.

Example:

Select Q' [I'm a SQL developer ']' | to_char (sysdate, 'yyyy') | Q' ['in China. telephone is] '| 1990 | '. 'from dual;

The result is as follows:

Result code

I'm a SQL developer '000000' in China. telephone is 2012.

I'm can use a single quotation mark in Q.
To_char (sysdate, 'yyyy') is converted to 2012, which must be enclosed by single quotation marks. Therefore, a single quotation mark is added at the end of q'[ XXX.
In this way, we do not need to use ''' to represent a single quotation mark.

In short, to master these three points, you should be able to assemble usable SQL statements. If you use the BIND variable input and output, you need to use the into using keyword.

Set serveroutput on;

Declare
Incoming date: = sysdate-10;
Outgoing int;
Begin
Execute immediate 'select COUNT (*) FROM user_objects where created>: incoming' into outgoing using incoming;
Dbms_output.put_line ('count is: '| outgoing );
End;
/