How can I use external variables into EXECUTE IMMEDIATE statements in PL/SQL?

Question

I need to write a query using the EXECUTE IMMEDIATE command to sum two values I calculate inside a nested query. I would like to know if I have to use USING clause or other clauses and how to put the variables I created into the statement. The query is the following:

Edit: this is the new query, there's still a problem in binding variables (Ora-01008: not all variables are bound). Can you help me with this problem?

     EXECUTE IMMEDIATE 'SELECT sum (n_record_trovati_p) FROM (
         SELECT count(*) as n_record_trovati_p FROM od_pv_trading_day_orders partition (' || partition_current_month || ') WHERE DATETIME = :1 AND orderid = :2 AND broker = :3 
           UNION
         SELECT count(*) as n_record_trovati_p FROM od_pv_trading_day_orders partition (' || partition_previous_month || ') WHERE DATETIME = :1 AND orderid = :2 AND broker = :3 
    )' INTO n_record_trovati
    USING d_datetime, n_orderid, cur.broker;


     //old query
     sys_current_date VARCHAR2 (7);
     sys_previous_date VARCHAR2 (7);
     partition_current_month VARCHAR2 (8);
     partition_previous_month VARCHAR2 (8);

     BEGIN
     ...

     SELECT TO_CHAR(ADD_MONTHS(d_datetime,0),'yyyymm') INTO sys_current_date FROM dual;
     SELECT TO_CHAR(ADD_MONTHS(d_datetime,-1),'yyyymm') INTO sys_previous_date FROM dual;

     partition_current_month := 'P'|| sys_current_date;
     partition_previous_month := 'P'|| sys_previous_date;

     EXECUTE IMMEDIATE 'SELECT SUM (found_records) INTO ' || n_record_trovati || ' FROM (
         SELECT COUNT(*) as found_records FROM example_table PARTITION (' || partition_current_month || ') WHERE DATETIME = ' || d_datetime || ' AND orderid = ' || n_orderid || ' AND broker = ' || cur.broker || '
           UNION
         SELECT COUNT(*) as found_records FROM example_table PARTITION (' || partition_previous_month || ') WHERE DATETIME = ' || d_datetime || ' AND orderid = ' || n_orderid || ' AND broker = ' ||cur.broker || '
    )';

    ...
    end

I tried dividing the variable names from the rest of the execute immediate string using || operator but an error pops up. Can you tell me what's wrong with my query and how can I fix it? Thanks

"Missing expression", even though there's no compiling error — Anacarnil
– Anacarnil, Commented Nov 8, 2017 at 8:38

Kaushik Nayak · Accepted Answer · 2017-11-07 18:16:31Z

1

You don't use INTO inside the string. It should be

EXECUTE IMMEDIATE 'SELECT SUM (found_records)  FROM (
         SELECT COUNT(*) as found_records FROM example_table PARTITION (' || partition_current_month || ') WHERE DATETIME = ' || d_datetime || ' AND orderid = ' || n_orderid || ' AND broker = ' || cur.broker || '
           UNION
         SELECT COUNT(*) as found_records FROM example_table PARTITION (' || partition_previous_month || ') WHERE DATETIME = ' || d_datetime || ' AND orderid = ' || n_orderid || ' AND broker = ' ||cur.broker || '
    )' INTO n_record_trovati;

answered Nov 7, 2017 at 18:16

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

1 Comment

Anacarnil Over a year ago

The "missing expression" error is gone, now there's a mini problem regarding brackets, but it seems everything is ok to me. Do I need to edit the string in any other specific way? Thank you for your help anyway, it seems the query is starting to work!

Collectives™ on Stack Overflow

How can I use external variables into EXECUTE IMMEDIATE statements in PL/SQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related