executing `EXECUTE IMMEDIATE ` Oracle Statement Getting Error

0
votes

I am NewBie to Oracle. When I Execute Following Statement 

BEGIN
 EXECUTE IMMEDIATE  'SELECT * FROM DUAL;';
END;
 /

I Got Error as

Error starting at line : 2 in command - BEGIN EXECUTE IMMEDIATE 'SELECT * FROM DUAL;'; END;

Error report - ORA-00911: invalid character ORA-06512: at line 2 00911. 00000 - "invalid character" *Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual. *Action:

3
oracleoracle11goracle-sqldeveloper
Can u please let us know what you are trying to achieve with that statement? U can simply give select * from dual if u want to see some output. – Arun Palanisamy
Try this : BEGIN EXECUTE IMMEDIATE 'SELECT * FROM DUAL'; END; Just remove ';' from the dymanic string. – Stefan Yordanov
@Crazy2crack i am trying to create a dynamic query from here ...oracle-base.com/articles/8i/native-dynamic-sql – Ganesh_Devlekar
@Ganesh_Devlekar - the truncate example in that article is wrong, it should not have the semicolon inside the dynamic string either. The single-row query example is OK, and notice that is has an into clause. The queried data has to go somewhere. – Alex Poole

3 Answers

4
votes

The problem is the ; character in 'SELECT * FROM DUAL;'.

From documentation:

execute_immediate_statement ::=
EXECUTE_IMMEDIATE dynamic_string
 { 
    INTO { define_variable [, define_variable ...] | record_name } 
  | BULK COLLECT INTO { collection_name [, collection_name ...] | :host_array_name } 
 }
   [ USING [ IN | OUT | IN OUT ] bind_argument
   [, [ IN | OUT | IN OUT ] bind_argument] ... ] [ returning_clause ] ;

... where dynamic_string is (emphasis mine):

A string literal, variable, or expression that represents a single SQL statement or a PL/SQL block. It must be of type CHAR or VARCHAR2, not NCHAR or NVARCHAR2.

Since it won't accept multiple statements unless you enclose them in a single PL/SQL block, the ; separator is not expected.

There's a better explanation at Using the EXECUTE IMMEDIATE Statement in PL/SQL:

When constructing a single SQL statement in a dynamic string, do not include a semicolon (;) at the end inside the quotation mark. When constructing a PL/SQL anonymous block, include the semicolon at the end of each PL/SQL statement and at the end of the anonymous block; there will be a semicolon immediately before the end of the string literal, and another following the closing single quotation mark.

4
votes

you can fix the error by removing ; from the dynamic query.

BEGIN
 EXECUTE IMMEDIATE  'SELECT * FROM DUAL';
END;
 /

This query is not going to return any results;

select statement in EXECUTE IMMEDIATE without into clause will be ignored.

Declare
    v_variable number;--some variable
BEGIN
     EXECUTE IMMEDIATE  'SELECT clmn FROM tbl' into v_variable;
END;
/
3
votes

Just remove ';' from the dymanic string.Try this : 

BEGIN 
   EXECUTE IMMEDIATE 'SELECT * FROM DUAL'; 
END;