Oracle ProC INSERT INTO VALUES ( (select ...) )

Question

Running Pro*C on Oracle 10g.

I am looking to do a subquery within an insert statement values clause. This sql query is fully valid and runs within TOAD with no problems, but Pro*C fails to parse the query.

EXEC SQL INSERT INTO TARGET_ATTACHMENT 
      (
          TARGET_ID
          FILENAME
      ) 
      VALUES ( 
         :targetID,
         ( SELECT CREATED_FLAG from TARGET t where t.TARGET_ID = :targetID ) || '.tif'
      )

If I remove:

( SELECT (CREATED_FLAG || DISPLAY_ID) from TARGET t where t.TARGET_ID = :targetID ) ||**".

The Pro*C compiler works and everything compiles and runs as expected.

If I DO NOT remove: The Pro*C compiler throws a syntax error.

1>Syntax error at line 128, column 12, file        d:\SVN\...\TA.pc:
1>Error at line 128, column 12 in file d:\SVN\...
1>...\TA.pc
1>                ( select CREATED_FLAG from target t where t.TARGET_ID = :targetID )
1>...........1
1>PCC-S-02201, Encountered the symbol "CREATED_FLAG" when expecting one of the fol
1>lowing:
1>   ( ) * + - / . @ | at, day, hour, minute, month, second, year,

This is a problem, as I expect Pro*C to be able to compile subquerys within a values caluse:

ie.

INSERT into table1 (col1) values ( (select t2.singleCol from table2 t2 where t2.priKey = :priKey) )

Is this expected behaviour of Pro*C? or Should it support subqueries within the values clause?

score 1 · Accepted Answer · answered Oct 03 '12 at 15:07

1

Possibly change the subquery to:

( SELECT CREATED_FLAG || '.tif' from TARGET t where t.TARGET_ID = :targetID )

I dont think I have ever seen something appended to a subquery the way you were attempting.

answered Oct 03 '12 at 15:07

John D

2,307
17
28

score 1 · Answer 2 · answered Aug 11 '13 at 09:52

The amount of SQL the Pro*C preprocessor is able to parse in static SQL statements is quite limited. For example it can't even parse explicit inner joiner/outer left join etc. notation.

As a workaround you can just prepare a dynamic SQL statement and execute it - even if your SQL statement is not really dynamic.

score 0 · Answer 3 · answered Oct 03 '12 at 15:25

0

The code you have posted is logically identical to this:

INSERT INTO TARGET_ATTACHMENT       
  ( TARGET_ID ,  FILENAME )      
 select    :targetID, CREATED_FLAG|| '.tif' 
 from TARGET t 
where t.TARGET_ID = :targetID )

Is there a particular reason why you need to use scalar cursors in a VALUES clause?

answered Oct 03 '12 at 15:25

APC

144,005
19
170
281

I am only showing a simplified query to show just what broke (after I added in the subquery). There are actually 20+ fields being inserted in the TARGET_ATTACHMENT table, only one of them needs information from the TARGET table. – user297500 Oct 03 '12 at 18:41

Oracle ProC INSERT INTO VALUES ( (select ...) )

3 Answers3

Linked