How to assemble dynamic SQL statements in Oracle

Although Oracle's dynamic SQL statements are very convenient to use, the assembly process is too troublesome. Especially when a date field is involved in the Assembly Statement, to_char must be added during the Assembly to convert it into a character first. In SQL, to_date must be used to convert it into a date type and compare it with the original field.

For example, there is 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('2012-11-08','yyyy-mm-dd') AND t.created< to_date('2012-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.

I,First determine the target. Make sure that the assembled SQL statement is what it looks like, and then configure the dynamic SQL statement.

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

III,If there is a quotation mark, 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:

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

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; declareincoming date:=sysdate-10;outgoing int;beginexecute immediate 'select COUNT(*) FROM user_objects where created > :incoming' into outgoing using incoming ;dbms_output.put_line(' count is: ' || outgoing);end;

The advantage of using is that you do not need to convert the date type to varchar type, and then convert it back to the date type.

The SQL code is as follows:

declareincoming date:=sysdate-10;outgoing int;beginexecute immediate 'insert into t_object(a) select COUNT(*) FROM user_objects where created > :incoming' into outgoing using incoming ;dbms_output.put_line(' count is: ' || outgoing);end;

ORA-01007: variables are not in the selection list
ORA-06512: In line 6
 
Tom explained the error as follows: Followup November 24,200 4-7 am Central time zone:
You have to use DBMS_ SQL when the number of outputs is not known until run time.
 
The SQL code is as follows:

Declare v_cursor number; -- Define the cursor v_string varchar2 (2999); v_row number; begin v_string: = 'insert into t_object (a) select COUNT (*) FROM user_objects where created>: incoming '; -- operation statement, where: name Is the variable v_cursor: = dbms_ SQL .open_cursor; -- open the processing cursor dbms_ SQL .parse (v_cursor, v_string, dbms_ SQL .native ); -- explain the statement dbms_ SQL .bind_variable (v_cursor, ': incoming', sysdate-30); -- assign v_row: = dbms_ SQL .execute (v_cursor) to the variable; -- execute the statement dbms_ SQL .close_cursor (v_cursor ); -- close the cursor -- dbms_output.put_line (v_row); commit; exception when others then dbms_ SQL .close_cursor (v_cursor); -- close the cursor rollback; end;