这是存储过程里面的一段代码,在11g里面能编译通过,但是在10g里面说expression is wrong type。
哪位遇到过这问题,帮我解决一下,谢谢。。。
- SQL code
TSQL := 'insert into modelmanage(modelid,modelname,xmlversion,tableversion,tabletype,xmlpath,tablepath,modeltable,dotpath,updatetime) values (''' ||v_mbid||''','''||v_mbname||''','''||v_mbxmlver||''','''||v_mbver||''','''||v_mbtype||''','''||xml||''','''||sheet ||''','''||v_mbtable||''','''||dotFilePath||''',sysdate)'; [color=#FF0000]EXECUTE IMMEDIATE TSQL;[/color]
------解决方案--------------------
可否给出完整的代码,便于调试
------解决方案--------------------
看不出什么问题,我手里有个例子,可以参考
oracle EXECUTE IMMEDIATE
/*
--用system用户执行
begin
EXECUTE IMMEDIATE 'GRANT create table TO plsql';
EXECUTE IMMEDIATE 'GRANT create sequence TO plsql';
end;
--用plsql用户执行
-- Set the SQL*PLUS environment for the script.
SET SERVEROUTPUT ON SIZE 1000000
SET ECHO OFF
SET FEEDBACK OFF
SET LINESIZE 90
SET PAGESIZE 0
/
-- Create a Varray of a one character string.
CREATE OR REPLACE TYPE varchar2_table1 IS
VARRAY(100) OF VARCHAR2(1);
/
-- Create a Varray of number.
CREATE OR REPLACE TYPE card_number_varray IS
VARRAY(100) OF NUMBER;
/
-- Create a Varray of twenty character string.
CREATE OR REPLACE TYPE card_name_varray IS
VARRAY(100) OF VARCHAR2(2000);
/
-- Create a Varray of thirty character string.
CREATE OR REPLACE TYPE card_suit_varray IS
VARRAY(100) OF VARCHAR2(2000);
/
-- Put package in log.
SELECT 'CREATE OR REPLACE PACKAGE nds_tutorial' FROM dual;
-- Create package specification.
CREATE OR REPLACE PACKAGE nds_tutorial AS
-- Define formatting variables.
dline VARCHAR2(80) :=
'============================================================';
sline VARCHAR2(80) :=
'------------------------';
-- Procedure creates a sequence using concatenation.
PROCEDURE create_sequence
( sequence_name IN VARCHAR2);
-- Procedure creates a table using concatenation.
PROCEDURE create_table
( table_name IN VARCHAR2
, table_definition IN VARCHAR2);
-- Procedure drops a sequence using concatenation.
PROCEDURE drop_sequence
( sequence_name IN VARCHAR2);
-- Procedure drops table using concatenation.
PROCEDURE drop_table
( table_name IN VARCHAR2);
-- Procedure encapsulates a PL/SQL block SELECT-INTO.
PROCEDURE increment_sequence
( sequence_name IN VARCHAR2
, sequence_value IN OUT NUMBER);
-- Procedure demonstrates a DML without bind variables.
PROCEDURE insert_into_table
( table_name IN VARCHAR2
, table_column_value1 IN NUMBER
, table_column_value2 IN VARCHAR2
, table_column_value3 IN VARCHAR2);
-- Procedure demonstrates a DML with ordered bind variables.
PROCEDURE inserts_into_table
( table_name IN VARCHAR2
, table_column_value1 IN NUMBER
, table_column_value2 IN VARCHAR2
, table_column_value3 IN VARCHAR2);
-- Procedure demonstrates multiple row DQL.
PROCEDURE multiple_row_return;
-- Procedure demonstrates multiple row DQL.
PROCEDURE multiple_row_return
( table_name VARCHAR2
, column_name1 VARCHAR2
, column_name2 VARCHAR2
, column_name3 VARCHAR2 );
-- Procedure demonstrates single row DQL.
PROCEDURE single_row_return;
-- Procedure demonstrates single row DQL.
PROCEDURE single_row_return
( table_name VARCHAR2
, column_name1 VARCHAR2
, column_name2 VARCHAR2
, column_name3 VARCHAR2 );
END nds_tutorial;
------解决方案--------------------
你的好像是单引号的问题!
------解决方案--------------------
execute immediate 字符串表达式
10g中的字符串表达式不允许clob类型
可以将其声明为varchar2,在pl/sql中其最大长度可达32767