CODEWITHSUNDEEP
sqlcoracle-databaseembedded-sql
Dax
Dax

Reputation: 470

Does anyone know why my execute immediate doesn't complie in PRO*C?

Can anyone figure out what is my mistake in the following PRO*C code? I cannot compile this:

int v1 = 5096;
int v2 = 8110;
int v3 = 8111;
int v4 = -1;
char stmt[6000];

strcpy(stmt, " MERGE INTO LDX_STYLE_MOVEMENTS ssd  USING (SELECT :1 pk from dual) ssd_pk   ON (ssd.style_movements_pk = ssd_pk.pk)  WHEN NOT MATCHED THEN insert (style_movements_pk, style, from_subclass, to_subclass, reclassified_date, change_type_fk) values(LDX_STYLE_MOVEMENTS_SEQ.nextval , null, :2, :3, null, :4 )");   

EXEC SQL execute immediate  :stmt using :v1, :v2, :v3, :v4;

Upvotes: 0

Views: 1259

Answers (1)

Alex Poole
Alex Poole

Reputation: 191560

EXECUTE IMMEDIATE (dynamic SQL method 1) doesn't support the USING clause.

You can prepare and execute it instead (using dynamic SQL method 2):

EXEC SQL PREPARE ora_stmt FROM :stmt;
EXEC SQL EXECUTE stmt USING :v1, :v2, :v3, :v4;

It doesn't look you really need to do this dynamically at all, but if you choose to do that then you need to use the appropriate method.

More details from the documentation:

Method 1 parses, then immediately executes the SQL statement using the EXECUTE IMMEDIATE command. The command is followed by a character string (host variable or literal) containing the SQL statement to be executed, which cannot be a query.

The syntax of the EXECUTE IMMEDIATE statement follows:

EXEC SQL EXECUTE IMMEDIATE { :host_string | string_literal };

...

With Method 2, the SQL statement can contain placeholders for input host variables and indicator variables...

The syntax of the PREPARE statement follows:

EXEC SQL PREPARE statement_name 
    FROM { :host_string | string_literal };

PREPARE parses the SQL statement and gives it a name.

The statement_name is an identifier used by the precompiler, not a host or program variable, and should not be declared in the Declare Section. It simply designates the PREPAREd statement you want to EXECUTE.

The syntax of the EXECUTE statement is

EXEC SQL EXECUTE statement_name [USING host_variable_list];

where host_variable_list stands for the following syntax:

:host_variable1[:indicator1] [, host_variable2[:indicator2], ...]

EXECUTE executes the parsed SQL statement, using the values supplied for each input host variable.

Upvotes: 2

Related Questions