i newbie oracle. when execute following statement
begin execute immediate 'select * dual;'; end; / i got error as
error starting @ line : 2 in command - begin execute immediate 'select * dual;'; end;
error report - ora-00911: invalid character ora-06512: @ line 2 00911. 00000 - "invalid character" *cause: identifiers may not start ascii character other letters , numbers. $#_ allowed after first character. identifiers enclosed doublequotes may contain character other doublequote. alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns delimiters. other contexts, consult sql language reference manual. *action:
the problem ; character in 'select * dual;'.
from documentation:
execute_immediate_statement ::= execute_immediate dynamic_string { { define_variable [, define_variable ...] | record_name } | bulk collect { collection_name [, collection_name ...] | :host_array_name } } [ using [ in | out | in out ] bind_argument [, [ in | out | in out ] bind_argument] ... ] [ returning_clause ] ; ... dynamic_string (emphasis mine):
a string literal, variable, or expression represents a single sql statement or a pl/sql block. must of type char or varchar2, not nchar or nvarchar2.
since won't accept multiple statements unless enclose them in single pl/sql block, ; separator not expected.
there's better explanation @ using execute immediate statement in pl/sql:
when constructing single sql statement in dynamic string, not include semicolon (;) @ end inside quotation mark. when constructing pl/sql anonymous block, include semicolon @ end of each pl/sql statement , @ end of anonymous block; there semicolon before end of string literal, , following closing single quotation mark.
Comments
Post a Comment