How to use declared variable in query

Question

I want to get count of records in specific partition.
The returned value will be used later in the flow.

DECLARE  
  pt VARCHAR2(20);
  countr NUMBER;
BEGIN
  pt:='61';
   DBMS_OUTPUT.PUT_LINE(pt);

 SELECT COUNT(*) INTO countr FROM T1 PARTITION ( pt) ;

END;

But getting error, it doesn't replace 'pt' with the assigned value.

You would have to use dynamic SQL to specify a partition name at run time (or a table name, or column name, etc.). But how is the table partitioned - why not filter on the partition range/interval instead? And '61' doesn't look like a valid partition name, as it doesn't meet the object naming rules. — Alex Poole
– Alex Poole, Commented Oct 24, 2018 at 12:07
61 is not real name, i wrote from my head... I need to see if partition has data if not, to exit. — yuris
– yuris, Commented Oct 24, 2018 at 12:08

Alex Poole · Accepted Answer · 2018-10-24 12:16:29Z

1

You can't use variables to specify object names (tables, columns, partition etc.) in static SQL.

You need to use dynamic SQL, e.g. with a non-quoted partition name:

DECLARE  
  pt all_tab_partitions.partition_name%TYPE;
  countr NUMBER;
BEGIN
  pt:='p61';
  DBMS_OUTPUT.PUT_LINE(pt);

  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM T1 PARTITION (' || pt || ')'
  INTO countr ;
END;
/

If you have quoted identifiers for the partition names you need to include the quotes in the statement, and make sure the case of the partition name matches exactly:

DECLARE  
  pt all_tab_partitions.partition_name%TYPE;
  countr NUMBER;
BEGIN
  pt:='61';
  DBMS_OUTPUT.PUT_LINE(pt);

  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM T1 PARTITION ("' || pt || '")'
  INTO countr ;
END;
/

answered Oct 24, 2018 at 12:16

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

Collectives™ on Stack Overflow

How to use declared variable in query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related