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
– Rob Baillie, Commented Apr 11, 2014 at 11:48
This is essentially the same issue than e.g. stackoverflow.com/a/22810334/272735 — user272735
– user272735, Commented Apr 11, 2014 at 11:59
This question appears to be off-topic because it is about a simple typo. — Jeffrey Kemp
– Jeffrey Kemp, Commented Apr 11, 2014 at 13:47

Rob Baillie · Accepted Answer · 2014-04-11 11:46:11Z

11

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';

answered Apr 11, 2014 at 11:46

Rob Baillie

3,4702 gold badges24 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chandra kishor Gauro · Accepted Answer · 2015-07-22 04:28:32Z

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, 2015 at 4:28

Chandra kishor Gauro

112 bronze badges

3 Comments

Suresh Karia Over a year ago

Please explain your answer

Chandra kishor Gauro Over a year ago

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.

clq Over a year ago

it is also safe comparing to traditional string concatenation.

jherran · Accepted Answer · 2015-11-02 07:37:59Z

1

Use the following query:

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

edited Nov 2, 2015 at 7:37

jherran

3,3878 gold badges40 silver badges54 bronze badges

answered Nov 2, 2015 at 6:47

Shamim Ahmed

192 bronze badges

Comments

Vedran Turković · Accepted Answer · 2015-03-31 12:03:07Z

0

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;

answered Mar 31, 2015 at 12:03

Vedran Turković

235 bronze badges

Comments

touchchandra · Accepted Answer · 2015-06-20 11:59:16Z

0

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

answered Jun 20, 2015 at 11:59

touchchandra

1,5866 gold badges21 silver badges40 bronze badges

Comments

Arun nath · Accepted Answer · 2015-10-22 09:53:12Z

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, 2015 at 9:53

Arun nath

811 silver badge4 bronze badges

Comments

JustinKaisse · Accepted Answer · 2016-03-09 21:30:17Z

0

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]';

answered Mar 9, 2016 at 21:30

JustinKaisse

295 bronze badges

Comments

Chakib Arrama · Accepted Answer · 2017-02-09 17:15:25Z

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 9, 2017 at 17:15

answered Feb 9, 2017 at 16:59

Chakib Arrama

1084 silver badges12 bronze badges

Comments

Sakib Mulla · Accepted Answer · 2018-08-12 16:08:30Z

0

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;
    /

answered Aug 12, 2018 at 16:08

Sakib Mulla

839 bronze badges

Comments

sudhirkondle · Accepted Answer · 2019-07-24 15:00:59Z

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;
            /

Collectives™ on Stack Overflow

Error in plsql code

10 Answers 10

Comments

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

Comments

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Linked

Related