这是存储过程里面的一段代码,在11g里面能编译通过,但是在10g里面说expression is wrong type。
哪位遇到过这问题,帮我解决一下,谢谢 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)';
EXECUTE IMMEDIATE TSQL;
哪位遇到过这问题,帮我解决一下,谢谢 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)';
EXECUTE IMMEDIATE TSQL;
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;
-- ==========================================================================
-- This is a debugging and log management technique for capturing the code
-- attempted to be compiled as a specification and then any error messages.
-- You would re these out when your code is production ready and then
-- remove the reing comments when debugging changes to your code.
-- ==========================================================================
SPOOL nds_tutorial_spec.log
list
show errors
SPOOL OFF
-- Put package body in log.
SELECT 'CREATE OR REPLACE PACKAGE BODY nds_tutorial' FROM dual;
-- Create package body.
CREATE OR REPLACE PACKAGE BODY nds_tutorial IS
-- Procedure creates a sequence using concatenation.
PROCEDURE create_sequence
( sequence_name IN VARCHAR2) IS
-- Define local variable.
statement VARCHAR2(2000);
-- Define a local function to ensure sequence does not exist.
FUNCTION verify_not_sequence
( sequence_name_in IN VARCHAR2)
RETURN BOOLEAN IS
-- Defines default return value.
retval BOOLEAN := TRUE;
-- Cursor returns a single row when finding a sequence.
CURSOR find_sequence IS
SELECT null
FROM user_objects
WHERE object_name = sequence_name_in;
BEGIN
-- The for-loop sets the Boolean when a sequence is found.
FOR i IN find_sequence LOOP
retval := FALSE;
END LOOP;
-- Return Boolean state.
RETURN retval;
END verify_not_sequence;
BEGIN
-- If sequence does not exist create it.
IF verify_not_sequence(sequence_name) = TRUE THEN
-- Build dynamic SQL statement.
statement := 'CREATE SEQUENCE '||sequence_name||CHR(10)
|| ' INCREMENT BY 1' ||CHR(10)
|| ' START WITH 1' ||CHR(10)
|| ' CACHE 20' ||CHR(10)
|| ' ORDER';
-- Use NDS to run the statement.
EXECUTE IMMEDIATE statement;
-- Print successful output message.
dbms_output.put_line(
'-> nds_tutorial.create_sequence');
-- Print output break.
dbms_output.put_line(sline);
-- Print sequence created.
dbms_output.put_line(
'Created Sequence <'||sequence_name||'>');
ELSE
-- Print module name output message.
dbms_output.put_line(
'-> nds_tutorial.create_sequence');
-- Print output line break.
dbms_output.put_line(sline);
-- Print output message.
dbms_output.put_line(
'Sequence <'||sequence_name||'> already exists');
END IF;
END create_sequence;
-- Procedure creates a table using concatenation.
PROCEDURE create_table
( table_name IN VARCHAR2
, table_definition IN VARCHAR2) IS
-- Define local native dynamic SQL variables.
statement VARCHAR2(2000);
-- Define a local function to ensure table does not exist.
FUNCTION verify_not_table
( object_name_in IN VARCHAR2)
RETURN BOOLEAN IS
-- Defines default return value.
retval BOOLEAN := TRUE;
-- Cursor returns a single row when finding a table.
CURSOR find_object IS
SELECT null
FROM user_objects
WHERE object_name = object_name_in;
BEGIN
-- The for-loop sets the Boolean when a table is found.
FOR i IN find_object LOOP
retval := FALSE;
END LOOP;
-- Return Boolean state.
RETURN retval;
END verify_not_table;
BEGIN
-- If table does not exist create it.
IF verify_not_table(table_name) = TRUE THEN
-- Build dynamic SQL statement.
statement := 'CREATE TABLE '||table_name||CHR(10)
|| table_definition;
-- Use NDS to run the statement.
EXECUTE IMMEDIATE statement;
-- Print module name message.
dbms_output.put_line(
'-> nds_tutorial.create_table');
-- Print line break.
dbms_output.put_line(sline);
-- Print output message.
dbms_output.put_line('Created Table <'||table_name||'>');
ELSE
-- Print module name message.
dbms_output.put_line(
'-> nds_tutorial.create_table');
-- Print line break.
dbms_output.put_line(sline);
-- Print output message.
dbms_output.put_line(
'Object <'||table_name||'> already exists');
END IF;
END create_table;
-- Procedure drops a sequence using concatenation.
PROCEDURE drop_sequence
( sequence_name IN VARCHAR2) IS
-- Define local variable.
statement VARCHAR2(2000);
-- Define a local function to ensure sequence does not exist.
FUNCTION verify_sequence
( sequence_name_in IN VARCHAR2)
RETURN BOOLEAN IS
-- Defines default return value.
retval BOOLEAN := FALSE;
-- Cursor returns a single row when finding a sequence.
CURSOR find_sequence IS
SELECT null
FROM user_objects
WHERE object_name = sequence_name_in;
BEGIN
-- The for-loop sets the Boolean when a sequence is found.
FOR i IN find_sequence LOOP
retval := TRUE;
END LOOP;
-- Return Boolean state.
RETURN retval;
END verify_sequence;
BEGIN
-- If sequence exists delete it.
IF verify_sequence(sequence_name) = TRUE THEN
-- Build dynamic SQL statement.
statement := 'DROP SEQUENCE '||sequence_name;
-- Use NDS to run the statement.
EXECUTE IMMEDIATE statement;
-- Print module name message.
dbms_output.put_line(
'-> nds_tutorial.drop_sequence');
-- Print line break.
dbms_output.put_line(sline);
-- Print the output message.
dbms_output.put_line(
'Dropped Sequence <'||sequence_name||'>');
ELSE
-- Print module name message.
dbms_output.put_line(
'-> nds_tutorial.drop_sequence');
-- Print line break.
dbms_output.put_line(sline);
-- Print output message.
dbms_output.put_line(
'Sequence <'||sequence_name||'> does not exists');
END IF;
END drop_sequence;
-- Procedure drops a table using concatenation.
PROCEDURE drop_table
( table_name IN VARCHAR2) IS
-- Define local variables.
statement VARCHAR2(2000);
-- Define a local function to ensure table does exist.
FUNCTION verify_table
( object_name_in IN VARCHAR2)
RETURN BOOLEAN IS
-- Defines default return value.
retval BOOLEAN := FALSE;
-- Cursor returns a single row when finding a table.
CURSOR find_object IS
SELECT null
FROM user_objects
WHERE object_name = object_name_in;
BEGIN
-- The for-loop sets the Boolean when a table is found.
FOR i IN find_object LOOP
retval := TRUE;
END LOOP;
-- Return Boolean state.
RETURN retval;
END verify_table;
BEGIN
IF verify_table(table_name) = TRUE THEN
-- Build dynamic SQL statement.
statement := 'DROP TABLE '||table_name;
-- Execute DNS statement.
EXECUTE IMMEDIATE statement;
-- Print method output message.
dbms_output.put_line(
'-> nds_tutorial.drop_table');
-- Print line break.
dbms_output.put_line(sline);
-- Print actual action.
dbms_output.put_line(
'Dropped Table <'||table_name||'>');
ELSE
-- Print failure output message.
dbms_output.put_line(
'-> nds_tutorial.drop_table');
-- Print line break.
dbms_output.put_line(sline);
-- Print output message.
dbms_output.put_line(
'Object <'||table_name||'> does not exist');
END IF;
END drop_table;
PROCEDURE increment_sequence
( sequence_name IN VARCHAR2
, sequence_value IN OUT NUMBER ) IS
-- Define local native dynamic SQL variables.
statement VARCHAR2(2000);
-- Define a local function to ensure sequence does not exist.
FUNCTION verify_sequence
( sequence_name_in IN VARCHAR2)
RETURN BOOLEAN IS
-- Defines default return value.
retval BOOLEAN := FALSE;
-- Cursor returns a single row when finding a sequence.
CURSOR find_sequence IS
SELECT null
FROM user_objects
WHERE object_name = sequence_name_in;
BEGIN
-- The for-loop sets the Boolean when a sequence is found.
FOR i IN find_sequence LOOP
retval := TRUE;
END LOOP;
-- Return Boolean state.
RETURN retval;
END verify_sequence;
BEGIN
-- Check if sequence already exists.
IF verify_sequence(sequence_name) = TRUE THEN
-- Build dynamic SQL statement as anonymous block PL/SQL unit.
statement := 'BEGIN' ||CHR(10)
|| ' SELECT PLSQL.'||sequence_name||'.nextval'||CHR(10)
|| ' INTO :retval' ||CHR(10)
|| ' FROM DUAL;' ||CHR(10)
|| 'END;';
-- Execute dynamic SQL statement.
EXECUTE IMMEDIATE statement
USING OUT sequence_value;
-- Print module name message.
dbms_output.put_line(
'-> nds_tutorial.increment_sequence');
-- Print line break.
dbms_output.put_line(sline);
-- Print output message.
dbms_output.put ('Sequence <'||sequence_name||'> ');
dbms_output.put_line('Value <'||sequence_value||'>');
ELSE
-- Print module name message.
dbms_output.put_line(
'-> dbms_sql_tutorial.increment_sequence');
-- Print line break.
dbms_output.put_line(sline);
-- Print output message.
dbms_output.put_line(
'Sequence <'||sequence_name||'> does not exist');
END IF;
END increment_sequence;
-- 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) IS
-- Define local variables.
statement VARCHAR2(2000);
-- Define a local function to ensure table does exist.
FUNCTION verify_table
( object_name_in IN VARCHAR2)
RETURN BOOLEAN IS
-- Defines default return value.
retval BOOLEAN := FALSE;
-- Cursor returns a single row when finding a table.
CURSOR find_object IS
SELECT null
FROM user_objects
WHERE object_name = object_name_in;
BEGIN
-- The for-loop sets the Boolean when a table is found.
FOR i IN find_object LOOP
retval := TRUE;
END LOOP;
-- Return Boolean state.
RETURN retval;
END verify_table;
BEGIN
-- If table exists insert into it.
IF verify_table(table_name) = TRUE THEN
-- Build dynamic SQL statement.
statement := 'INSERT '
|| 'INTO '||table_name||' '
|| 'VALUES ('
|| ''''||table_column_value1||''','
|| ''''||table_column_value2||''','
|| ''''||table_column_value3||''')';
-- Execute the NDS statement.
EXECUTE IMMEDIATE statement;
-- Commit the records.
commit;
-- Print module name output message.
dbms_output.put_line(
'-> nds_tutorial.insert_into_table');
-- Print line break.
dbms_output.put_line(sline);
-- Print data output.
dbms_output.put_line(
'Value inserted <'||table_column_value1||'>');
dbms_output.put_line(
'Value inserted <'||table_column_value2||'>');
dbms_output.put_line(
'Value inserted <'||table_column_value3||'>');
ELSE
-- Print module name output message.
dbms_output.put_line(
'-> nds_tutorial.insert_into_table');
-- Print line break.
dbms_output.put_line(sline);
-- Print error output message.
dbms_output.put_line(
'Object <'||table_name||'> does not exist');
END IF;
END insert_into_table;
/*
|| ------------------------------------------------------------------
*/
-- 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) IS
-- Define local variables.
statement VARCHAR2(2000);
-- Define a local function to ensure table does exist.
FUNCTION verify_table
( object_name_in IN VARCHAR2)
RETURN BOOLEAN IS
-- Defines default return value.
retval BOOLEAN := FALSE;
-- Cursor returns a single row when finding a table.
CURSOR find_object IS
SELECT null
FROM user_objects
WHERE object_name = object_name_in;
BEGIN
-- The for-loop sets the Boolean when a table is found.
FOR i IN find_object LOOP
retval := TRUE;
END LOOP;
-- Return Boolean state.
RETURN retval;
END verify_table;
BEGIN
-- If table exists insert into it.
IF verify_table(table_name) = TRUE THEN
-- Build dynamic SQL statement.
statement := 'INSERT '
|| 'INTO '||table_name||' '
|| 'VALUES (:col_one, :col_two, :col_three)';
-- Execute the NDS statement.
EXECUTE IMMEDIATE statement
USING table_column_value1
, table_column_value2
, table_column_value3;
-- Commit the records.
commit;
-- Print module name output message.
dbms_output.put_line(
'-> nds_tutorial.insert_into_table');
-- Print line break.
dbms_output.put_line(sline);
-- Print data output.
dbms_output.put_line(
'Value inserted <'||table_column_value1||'>');
dbms_output.put_line(
'Value inserted <'||table_column_value2||'>');
dbms_output.put_line(
'Value inserted <'||table_column_value3||'>');
ELSE
-- Print module name output message.
dbms_output.put_line(
'-> nds_tutorial.insert_into_table');
-- Print line break.
dbms_output.put_line(sline);
-- Print error output message.
dbms_output.put_line(
'Object <'||table_name||'> does not exist');
END IF;
END inserts_into_table;
(
sheetFilePath in varchar2,
xmlFilePath in varchar2,
dotFilePath in varchar2,
xmlContent in clob,
code out varchar2,
description out varchar2
)
AUTHID CURRENT_USER
is
xexp exception;
par xmlparser.Parser;
doc xmldom.DOMDocument;
colNums number;
i_Clobxml clob;
v_xml xmltype;
i_flag varchar2(10);
i_desc varchar2(100);
function InsertData(doc xmldom.DOMDocument, sheet varchar2, xml varchar2)return number
as
v_mbid modelmanage.modelid%type;
v_mbname modelmanage.modelname%type;
v_mbxmlver modelmanage.xmlversion%type;
v_mbver modelmanage.tableversion%type;
v_mbtype modelmanage.tabletype%type;
--v_mbxmlpath modelmanage.xmlpath%type;
--v_mbsheetpath modelmanage.tablepath%type;
v_mbtable modelmanage.modeltable%type;
--ColEml xmldom.DOMNodeList;--XML节点集合
TABLEEml xmldom.DOMNodeList;--列集合
TABNode xmldom.DOMNode;--列节点
ColCEml xmldom.DOMNodeList;--列节点子集合
--ColTEml xmldom.DOMNode;--列节点子节点
v_xml xmltype;
EMLLen NUMBER;
v_num varchar2(10);
TSQL clob;
--v_reason varchar2(200);
--模板域ID嵌套表
v_domainid modeldomain.domianid%TYPE;
--模板域名称
v_domainname modeldomain.domainname%TYPE ;
--模版域描述
v_domiandesc modeldomain.domiandesc%type;
--个性查询时的排序
v_domianorder modeldomain.domianorder%type;
--域类型
v_domiantype modeldomain.domiantype%type;
begin
v_xml := xmltype(xmlContent);
IF v_xml IS NULL
THEN
RAISE_APPLICATION_ERROR(-20000,'调用插入数据函数InsertData时XML数据为空');
END IF;
Tableeml := xmldom.getElementsByTagName(doc, 'id');
Tabnode := xmldom.item(Tableeml, 0);
v_mbid := xmldom.getNodeValue(xmldom.getFirstChild(Tabnode));
Tableeml := xmldom.getElementsByTagName(doc, 'name');
TABNode := xmldom.item(Tableeml, 0);
v_mbname := xmldom.getNodeValue(xmldom.getFirstChild(Tabnode));
Tableeml := xmldom.getElementsByTagName(doc, 'sheetversion');
TABNode := xmldom.item(Tableeml, 0);
v_mbver := xmldom.getNodeValue(xmldom.getFirstChild(Tabnode));
Tableeml := xmldom.getElementsByTagName(doc, 'xmlversion');
TABNode := xmldom.item(Tableeml, 0);
v_mbxmlver := xmldom.getNodeValue(xmldom.getFirstChild(Tabnode));
Tableeml := xmldom.getElementsByTagName(doc, 'sheettype');
TABNode := xmldom.item(Tableeml, 0);
v_mbtype := xmldom.getNodeValue(xmldom.getFirstChild(Tabnode));
-- Tableeml := xmldom.getElementsByTagName(doc, 'table');
--TABNode := xmldom.item(Tableeml, 0);
--Attribute := xmldom.getAttribute(tabnode, 'name');
--v_mbtable := xmldom.getNodeValue(xmldom.getAttributes(tabnode));
select EXTRACTVALUE(v_xml,'sheet/table/@name') into v_mbtable from dual;
-- v_mbtable := 'mb_wjqc';
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)';
--
EXECUTE IMMEDIATE TSQL;
PROCEDURE ADD_SHEET 编译错误错误:PLS-00382: expression is of wrong type
行:96
文本:EXECUTE IMMEDIATE TSQL;错误:PL/SQL: Statement ignored
行:96
文本:EXECUTE IMMEDIATE TSQL;
10g里用varchar2试试。
10g中的字符串表达式不允许clob类型
可以将其声明为varchar2,在pl/sql中其最大长度可达32767