现在是这样一条语句:
begin
v_sql := 'select ';
for c1 IN c_sql(p_owner,p_table) loop
v_sql := v_sql || c1.column_query || '||'',''||';
end loop;
return substr(v_sql,1,length(v_sql)-7)||' from '||upper(p_table) ||' where '|| '&&p_agumet' ||'; end;
其中return后面这句出了问题 '&&p_agumet' 这是个参数,是where条件。现在运行时出错,提示:ORA-01756: 引号内的字符串没有正确结束。
我现在要得到的正确结果是:select decode(VEHICLE_ID,null,'null',''''||VEHICLE_ID||'''') from CLW_YW_TEMP_T
where TERMINAL_ID='202IIIA601110602330'求帮助,不胜感激
begin
v_sql := 'select ';
for c1 IN c_sql(p_owner,p_table) loop
v_sql := v_sql || c1.column_query || '||'',''||';
end loop;
return substr(v_sql,1,length(v_sql)-7)||' from '||upper(p_table) ||' where '|| '&&p_agumet' ||'; end;
其中return后面这句出了问题 '&&p_agumet' 这是个参数,是where条件。现在运行时出错,提示:ORA-01756: 引号内的字符串没有正确结束。
我现在要得到的正确结果是:select decode(VEHICLE_ID,null,'null',''''||VEHICLE_ID||'''') from CLW_YW_TEMP_T
where TERMINAL_ID='202IIIA601110602330'求帮助,不胜感激
return substr(v_sql,1,length(v_sql)-7)||' from '||upper(p_table) ||' where '|| '&&p_agumet' ||';||'这里代表拼接上了一个没结束的字符串,要么去掉||',要么变成||''
where TERMINAL_ID='202IIIA601110602330'你这句的四个引号明显不正确
return substr(v_sql,1,length(v_sql)-7)||' from '|| p_table ||' partition '||'(SEC_DATA_20110602) '|| 'where '|| 'terminal_id='202IIIA601110321013'';
*
第 41 行出现错误:
ORA-06550: 第 41 行, 第 136 列:
PLS-00103: 出现符号 "202"在需要下列之一时:
* & = - + ; < / > at in is
mod remainder not rem <an exponent (**)> <> or != or ~= >= <=
<> and or like LIKE2_ LIKE4_ LIKEC_ between || member
因为我传的参数也就是'&&p_agumet'的值为TERMINAL_ID='202IIIA601110602330',本身中间就含有一对''了
如下:
set termout on pagesize 0 linesize 5000
set feedback off verify off wrap off echo off showmode offprompt Specify Table Onwer
prompt &&p_owner
prompt Specify Table Name
prompt &&p_table
prompt Specify patition(表分区)
prompt &&p_patition
prompt Specify conditions(where条件)prompt &&p_conditions
prompt Specify Report Path
prompt &&file_pathcreate table toms_ins_sql (sql_text VARCHAR2(4000),line# number) tablespace users;declare
v_col_list varchar2(4000);
v_val_list varchar2(4000);
v_get_colval varchar2(4000);
v_cur number;
v_ret number;
v_row number;
v_line varchar2(32765);
v_seq number;
function get_cols(p_owner varchar2,p_table varchar2) return varchar2 IS
v_cols varchar2(4000);
begin
v_cols := null;
for c in (select column_name
from dba_tab_columns
where owner=upper(p_owner) and
table_name=upper(p_table)
order by column_id ) loop
v_cols := v_cols ||c.column_name ||',';
end loop;
return substr(v_cols,1,length(v_cols)-1);
end; function get_val(p_owner in varchar2,p_table in varchar2,p_patition in varchar2) return varchar2 IS
v_sql varchar2(4000);
terminal_id varchar2(4000);
cursor c_sql (c_owner varchar2,c_table varchar2) IS
SELECT 'decode('||column_name||',null,''null'','||
decode(data_type,'VARCHAR2','''''''''||'||column_name||'||'''''''''||')',
'CHAR','''''''''||'||column_name ||'||'''''''''||')',
'DATE','''to_date('''''''||'||to_char('||column_name||','||''''||'yyyymmddhh24miss'||''''||')'||'||''''''''||'',''||''''''''||'''||'yyyymmddhh24miss'||'''||''''''''||'')'')',column_name||')') column_query
from dba_tab_columns
where owner=upper(c_owner) and
table_name=upper(c_table)
order by column_id;
begin
v_sql := 'select ';
for c1 IN c_sql(p_owner,p_table) loop
v_sql := v_sql || c1.column_query || '||'',''||';
end loop;
return substr(v_sql,1,length(v_sql)-7)||' from '|| p_table ||' partition '||'(&&p_patition) '|| 'where '|| '&&p_conditions';
end;
begin
v_col_list :=get_cols('&&p_owner','&&p_table');
v_get_colval :=get_val ('&&p_owner','&&p_table','&&p_patition','&&p_conditions');
v_cur :=dbms_sql.open_cursor;
v_row :=0;
v_seq :=4;
select count(*) into v_row
from dba_tables
where owner=upper('&&p_owner') and
table_name=upper('&&p_table');
if v_row=0 then
raise_application_error(-20001,'table or view :&&p_owner'||'.'||'&&p_table'||' does not exist');
return;
else
v_row:=0;
end if;
insert into toms_ins_sql values('prompt thomas zhang genins.sql utility import file',0);
insert into toms_ins_sql values('prompt Created on '||sysdate||' by '||user,1);
insert into toms_ins_sql values('set feedback off define off',2);
insert into toms_ins_sql values('prompt Loading '||upper('&&p_table')||'...',3);
DBMS_SQL.PARSE(v_cur, v_get_colval, DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(v_cur, 1, v_val_list, 32767);
v_ret := DBMS_SQL.EXECUTE(v_cur);
loop
IF DBMS_SQL.FETCH_ROWS(v_cur)>0 THEN
DBMS_SQL.COLUMN_VALUE(v_cur, 1, v_val_list);
v_line:='insert into '||upper('&&p_table')||' ('||v_col_list||')'||' values ('||v_val_list||');';
insert into toms_ins_sql values(v_line,v_seq);
v_row:=v_row+1;
v_seq:=v_seq+1;
else
DBMS_SQL.CLOSE_CURSOR(v_cur);
exit;
end if;
end loop;
insert into toms_ins_sql values('commit;',v_seq);
v_seq:=v_seq+1;
insert into toms_ins_sql values('prompt '||to_char(v_row)||' records loaded',v_seq);
v_seq:=v_seq+1;
insert into toms_ins_sql values('set feedback on',v_seq);
v_seq:=v_seq+1;
insert into toms_ins_sql values('set define on',v_seq);
v_seq:=v_seq+1;
insert into toms_ins_sql values('prompt Done.',v_seq);
commit;
end;
/ set heading off;
column report_name new_value report_name noprint;
select decode('&&file_path'||'/','/','','&&file_path'||'/')||'&&p_table'||'.sql' report_name from dual;
promptspool &report_name;
set pagesize 0 feedback off verify off heading off echo off trimspool on
select sql_text from toms_ins_sql order by line#;
spool offundefine p_owner p_table file_path
set pagesize 14 linesize 80 feedback on verify on heading on echo on wrap on trimspool off
输入 p_table 的值:CLW_YW_SEC_DATA_T
输入 p_patition 的值:SEC_DATA_20110602
输入 p_conditions 的值:terminal_id='202IIIA601110321013'
问题出在这terminal_id='202IIIA601110321013',这个p_conditions外面需要用单引号,而本身它里面就包含了一组单引号
输入 p_owner 的值: clw
clw
Specify Table Name
输入 p_table 的值: CLW_YW_SEC_DATA_T
CLW_YW_SEC_DATA_T
Specify patition
SEC_DATA_20110602
Specify conditions
terminal_id='202IIIA601110321013'
现在估计就是Specify conditions
terminal_id='202IIIA601110321013'的问题,因为本身这里面就包含了一组单引号