CODEWITHSUNDEEP
oracle-databaseplsqlexecute-immediate
Anacarnil
Anacarnil

Reputation: 55

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

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

Upvotes: 0

Views: 1387

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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;

Upvotes: 1

Related Questions