Communication between Bind and Substitution variables in SQLPLUS?

Like many others, I have written some SQLPLUS scripts to automatically generate C header files from Oracle table definitions. This was all well and good so long as the referenced table was local -- all I had to do was go through USER_TAB_COLUMNS. Unfortunately, my script was broken when some of our tables were moved to another node. For those tables, I'd have to refer to USER_TAB_COLUMNS_at_wherever. No problem, I thought. To know where 'wherever' is, all I have to do is SELECT from USER_SYNONYMS, and construct my .H file generating statements from the results. Unfortunately it appears that I can't do that in any kind of elegant way. I can only SELECT into bind variables in a PL/SQL block, and then PRINT the bind variables outside of the block. Try as I might, I cannot use them in any other way, such as assigning them to substitution variables. That's a problem, since only substitution variables can be used to construct the USER_TAB_COLUMNS_at_wherever statement needed for the next step of the procedure.

I've worked around this for the moment by (blush) prompting the user with the bind variables and asking them to type exactly what they see right back into substitution variables. You can bet that I've got a disclaimer on that code!

I sincerely hope that this issue is due to my own ignorance! I'd hate to have to leave the code looking like this.

Thanks in advance!

Bill Received on Sun Aug 18 1996 - 00:00:00 CEST