Essentials of Assembling Dynamic SQL by Oracle

Although Oracle's dynamic SQL statement is really convenient to use, its assembly process is too troublesome. Especially, when the date type field is involved in the assembly statement, to_char should be added to convert it into characters first, and to_date should be used to convert it into date type and compare it with the original field in sql.

For example, there is one 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 into a string, and then converts the string into date in the generated SQL.

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'), and every single quotation mark involved in the statement should be written as two single quotation marks to escape.

Although the assembling process is annoying, as long as you master 3 points, you should be able to assemble a usable SQL statement.

1. Set goals first. Make sure what the assembled SQL should look like, and then configure the dynamic SQL

2. When assembling SQL, all objects connected with connectors should be of type varchar2, which begins with and ends with single quotation marks. Numbers rotate automatically, but date requires us to rotate manually using the to_char function.

3. If there are quotation marks, write them in two single quotation marks.

Such as' I am a SQL developer '' v_name '' in China. telephone is 'v_number'. '

v_name is character type, so assembling it requires single quotation marks.

This conversion is annoying, but there is a new feature from 10g, which can make people not so annoyed. It is q '[xxxxx]'

Examples are as follows:

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

The results are as follows:

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

I 'm just use a single quotation mark in q' [] '.
to_char (sysdate, 'yyyy') is converted to 2012 with single quotation marks before and after. So there is a single quotation mark at the end of q '[xxx'] '.
This makes it unnecessary for us to use '''' to represent a single quotation mark as before.

In short, if you master these three points, you should be able to assemble a usable SQL. The into using keyword is required if binding variables are used for input and output.

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;

The advantage of using using is that you don't have to switch date type to varchar type and then go back to date type.

The SQL code is as follows:

declare
incoming date:=sysdate-10;
outgoing int;
begin
execute 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: Variable is not in the selection list
ORA-06512: In line 6

tom explains this error as follows: Followup November 24, 2004-7am 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 cursors  
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 , Among them :name Is a variable whose value is determined when the statement runs  
v_cursor:=dbms_sql.open_cursor;-- Open the processing cursor  
dbms_sql.parse(v_cursor,v_string,dbms_sql.native);-- Interpretive statement  
dbms_sql.bind_variable(v_cursor,':incoming',sysdate-30); -- Assign a value to a variable  
v_row := dbms_sql.execute(v_cursor);-- Execute 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;