Pro *C

I have a SQL select statement that may or may not return a value. I need to know how to determine if the query returned nothing, because if that is the case the program needs to jump into an IF statement containing an INSERT SQL block

Don't think there's another way to do it Pro*C but you could could use sqlplus with a command like

Code:

System(sqlplus -s user_name/password @script.sql > output.log 2>&1)

This would allow you to use PL/SQL with IF statements in the script and also to use the EXCEPTION functionality to customise the output of any errors within the script. Apart from being able to determine if an error occured, you could find out what error occured as well and whether or not any records were found/added/updated using select statements and the count(*) function (possibly using a cursor with a SELECT INTO instead of using count(*))

Parsing the output file (which because you can control all output between sqlplus commands and PL/SQL commands should be a simple task) then gives you far more meaningful output from your SQL.

This may be too much for what you want to do, but if you're using Pro*C at all, then this method could be a useful possibility for you to use in future programs if not in this case. Also, despite the fact that I have tried to describe several things you OCULD do, you could keep it as simple as only covering the EXCEPTION OTHER to determine if the script failed and a SELECT count(*) to determine what work (if any) was done.