Error in plsql code

Question

Pleas help me to resolve this error in plsql.

 sqlquery := 'SELECT COUNT(*) FROM V$SESSION WHERE STATUS = 'INACTIVE' AND LAST_CALL_ET > 0';
 EXECUTE IMMEDIATE sqlquery into s_count;

below is the error:

  ERROR at line 57:
  ORA-06550: line 57, column 66:
  PLS-00103: Encountered the symbol "INACTIVE" when expecting one of the following:
  * & = - + ; < / > at in is mod remainder not rem
  <an exponent (**)> <> or != or ~= >= <= <> and or like like2
  like4 likec between || multiset member submultiset
  The symbol "* was inserted before "INACTIVE" to continue.
  ORA-06550: line 79, column 4:
  PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:
  ( begin case declare else elsif end exit for goto if loop mod
  null pragma raise return select update while with <an identifier> <a double-quoted
  ORA-06550: line 81, column 7:
  PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
  end not pragma final instantiable order overriding static member constructor map

Does this really need to be done with an `EXECUTE IMMEDIATE`? The query isn't dynamic, so you should be able to implement this with a standard cursor and an `OPEN`, `FETCH`, `CLOSE` or a `SELECT INTO` — Rob Baillie, Apr 11 '14 at 11:48
This is essentially the same issue than e.g. http://stackoverflow.com/a/22810334/272735 — user272735, Apr 11 '14 at 11:59
This question appears to be off-topic because it is about a simple typo. — Jeffrey Kemp, Apr 11 '14 at 13:47
Also same as https://stackoverflow.com/questions/48172315/wrong-symbol-inside-replace-function-pl-sql-oracle/48172423 — William Robertson, Feb 26 '18 at 15:23

score 11 · Answer 1 · answered Apr 11 '14 at 11:46

The quotes around INACTIVE are splitting the string up and causing the syntax error...

sqlquery := 'SELECT COUNT(*) FROM V$SESSION WHERE STATUS = ''INACTIVE'' AND LAST_CALL_ET > 0';
EXECUTE IMMEDIATE sqlquery into s_count;

Of course, if the statement isn't actually dynamic you don't need the EXECUTE IMMEDIATE and can instead go with:

SELECT COUNT(*)
INTO   s_count
FROM   V$SESSION
WHERE  STATUS = 'INACTIVE'
AND    LAST_CALL_ET > 0';

score 1 · Answer 2 · answered Jul 22 '15 at 04:28

1

set serveroutput on;
declare
   l_status varchar2(30):= 'INACTIVE';
   s_count number:= 0;
sqlquery varchar2(32767) := null;
begin
sqlquery := 'SELECT COUNT(*) FROM V$SESSION WHERE STATUS = :a  AND LAST_CALL_ET > 0';
EXECUTE IMMEDIATE sqlquery into s_count using l_status ;
dbms_output.put_line(s_count);
end;

answered Jul 22 '15 at 04:28

Chandra kishor Gauro

11
2

1

Please explain your answer – Suresh Karia Jul 22 '15 at 10:03
in sqlquery variable , you can see :a is used as binding variable. Later on those value is passed while "EXECUTE IMMEDIATE sqlquery into s_count using l_status ;" by using l_status. It is just similar like preparedStatment in java. – Chandra kishor Gauro Jul 22 '15 at 22:30
it is also safe comparing to traditional string concatenation. – clq Oct 15 '15 at 14:07

score 1 · Answer 3 · edited Nov 02 '15 at 07:37

1

Use the following query:

sqlquery := 'SELECT COUNT(*) FROM V$SESSION WHERE STATUS = ''INACTIVE'' AND LAST_CALL_ET > 0';

edited Nov 02 '15 at 07:37

jherran

3,337
8
37
54

answered Nov 02 '15 at 06:47

Shamim Ahmed

19
2

score 0 · Answer 4 · answered Mar 31 '15 at 12:03

Solution with Execute immediate

set serveroutput on;
declare
   l_status varchar2(30):= '''INACTIVE''';
   s_count number:= 0;
sqlquery varchar2(32767) := null;
begin
sqlquery := 'SELECT COUNT(*) FROM V$SESSION WHERE STATUS = '||l_status||' AND LAST_CALL_ET > 0';
EXECUTE IMMEDIATE sqlquery into s_count;
dbms_output.put_line(s_count);
end;

score 0 · Answer 5 · answered Jun 20 '15 at 11:59

0

EXECUTE IMMEDIATE '
SELECT COUNT(*) 
FROM V$SESSION 
WHERE STATUS = ''INACTIVE'' AND LAST_CALL_ET > 0' 
INTO s_count;

answered Jun 20 '15 at 11:59

touchchandra

1,506
5
21
37

score 0 · Answer 6 · answered Oct 22 '15 at 09:53

0

Please change the query as sqlquery := 'SELECT COUNT(*) FROM V$SESSION WHERE STATUS = '''INACTIVE''' AND LAST_CALL_ET > 0'; EXECUTE IMMEDIATE sqlquery into s_count;

answered Oct 22 '15 at 09:53

Arun nath

81
1
4

score 0 · Answer 7 · answered Mar 09 '16 at 21:30

Bind variables like Chandra said!

One way I like to assign the SQL:

declare
  vSQL varchar2( 100 ) ;
begin
  vSQL := q'[select 'done' from dual ]' ;
  dbms_output.put_line( vSQL ) ;
end ;

so you'd do:

sqlquery := q'[SELECT COUNT(*) FROM V$SESSION WHERE STATUS = :status AND LAST_CALL_ET > 0]';

Chakib Arrama · Answer 8 · 2017-02-09T17:15:25.623

0

use :

sqlquery := 'SELECT COUNT(*) FROM V$SESSION WHERE STATUS = ''INACTIVE'' AND LAST_CALL_ET > 0';

not :

sqlquery := 'SELECT COUNT(*) FROM V$SESSION WHERE STATUS = 'INACTIVE' AND LAST_CALL_ET > 0';

edited Feb 09 '17 at 17:15

answered Feb 09 '17 at 16:59

Chakib Arrama

93
3
12

score 0 · Answer 9 · answered Aug 12 '18 at 16:08

use this code will work perfectly

set serveroutput on
    DECLARE
    sqlquery    varchar2(1000);
    INACTIVE    varchar2(100):='INACTIVE';
    s_count     number;
    begin
    sqlquery := 'SELECT COUNT(*) FROM V$SESSION WHERE STATUS = '''||INACTIVE||''' AND LAST_CALL_ET > 0';
    EXECUTE IMMEDIATE sqlquery into s_count;
    DBMS_OUTPUT.PUT_LINE(s_count);
    end;
    /

score 0 · Answer 10 · answered Jul 24 '19 at 15:00

Another way to do this:

            set serveroutput on;
            DECLARE
                sqlquery VARCHAR2(4000);
                s_count number;
            begin
                sqlquery := 'SELECT COUNT(*) FROM V$SESSION WHERE STATUS = '||chr(39)||'INACTIVE'||chr(39)||' AND LAST_CALL_ET > 0';
                EXECUTE IMMEDIATE sqlquery into s_count;
                dbms_output.put_line('s_count--'||s_count);
            end;
            /

Error in plsql code

10 Answers10