Request for assistance with $COLUMNS(...)$+$OBJECT(...)$

I've got a table described below:

	Code:
	CREATE TABLE DEMO1.VONAL ( VONAL_ID VARCHAR2 ( 5 ) NOT NULL ENABLE ,DATUM_TOL DATE NOT NULL ENABLE ,VAROS_ID NUMBER ( 10 ,0 ) DEFAULT 0 NOT NULL ENABLE ,VONAL_NEV VARCHAR2 ( 140 ) DEFAULT '' NOT NULL ENABLE ,AKTIV_E NUMBER ( 1 ,0 ) DEFAULT 1 NOT NULL ENABLE ,DATUM_IG DATE );

And I have an expected result code as follows:

	Code:
	DECLARE l_vonal_id vonal.vonal_id%TYPE; l_datum_tol vonal.datum_tol%TYPE; l_varos_id vonal.varos_id%TYPE; l_vonal_nev vonal.vonal_nev%TYPE; l_aktiv_e vonal.aktiv_e%TYPE; l_datum_ig vonal.datum_ig%TYPE;

Now, no matter how many different and complex combinations of $COLUMNS(...)$ + $OBJECT()$ I tried, I was unable to create a working one that would yield the above result. I'm beginning to believe it is impossible. Any help is
appreciated.

Please try this one

	Code:
	DECLARE $$ SELECT 'l_' \|\| lower(column_name \|\| ' ' \|\| table_name \|\| '.' \|\| column_name) \|\| '%TYPE;' FROM sys.all_tab_columns WHERE owner = upper('$OBJECT(ins_schema, table)$') AND table_name = replace(upper('$OBJECT(table)$'), upper('$OBJECT(ins_schema, table)$') \|\| '.', '') ORDER BY column_id $$

Note that table schema is omitted in the declaration. If required, it can be easily added

Yes, I've tried to use a similar script to create the result, though I didn't bother setting the correct cases. The problem with this approach is that the macros allow selecting columns individually, while the scripted workaround does not.

	gemisigo wrote:
	Yes, I've tried to use a similar script to create the result, though I didn't bother setting the correct cases. The problem with this approach is that the macros allow selecting columns individually, while the scripted workaround does not.

Unfortunately mixing $COLUMNS$ macro with other adjacent macros like $COLUMNS(...)$+$OBJECT(...)$ to generate multi-line script is beyond the capabilities of the macro processing engine. $COLUMNS$ and $ARGUMENTS$ macros support multi-line output driven by the table columns or procedure arguments only while other macros don't, and cannot be used in that manner.

Yes, I've run into limitations with each try. I wasn't even able to create

	Code:
	l_vonal_id vonal_id%TYPE; l_datum_tol datum_tol%TYPE;

without using '='.

It's funny how

	Code:
	l_$COLUMNS(vertical)$ = table_name_here.$COLUMNS(vertical)$%TYPE;

works but

	Code:
	l_$COLUMNS(vertical)$ = $OBJECT$.$COLUMNS(vertical)$%TYPE;

does not.

I have to search&replace the rubbish surrounding '=' and remove the comma from the end of each line except the last one but it still beats entering the whole stuff manually or having to drop lines with unwanted column one by one. Incidentally, isn't there a way to omit those commas or to replace them with AND or OR or whatever is needed in the current situation?

that should be changed to

	Code:
	"l_"$COLUMNS(vertical)$" = "$OBJECT$.$COLUMNS(vertical)$"%TYPE;"

Anything that contains spaces or non alphanumerics and _ symbols should be enclosed in double quotes. for "l_" I did that only for consistency.

Yes, I stand corrected. I added a couple of double quotes here and there when I tried a few things. It turned out that sometimes it helps, sometimes it does not, and sometimes (cases involving '=') it makes no difference

#1

	Code:
	l_$COLUMNS(vertical)$" test."$COLUMNS(vertical)$%TYPE

#1 result

	Code:
	l_vonal_id test., l_datum_tol test., l_varos_id test., l_vonal_nev test., l_aktiv_e test., l_datum_ig test.vonal_id%TYPE, datum_tol%TYPE, varos_id%TYPE, vonal_nev%TYPE, aktiv_e%TYPE, datum_ig%TYPE

#2

	Code:
	l_$COLUMNS(vertical)$ = aha.$COLUMNS(vertical)$%TYPE

#2 result

	Code:
	l_vonal_id = aha.vonal_id%TYPE, l_datum_tol = aha.datum_tol%TYPE, l_varos_id = aha.varos_id%TYPE, l_vonal_nev = aha.vonal_nev%TYPE, l_aktiv_e = aha.aktiv_e%TYPE, l_datum_ig = aha.datum_ig%TYPE

This seemed to be promising except for the equal signs.

#3

	Code:
	l_$COLUMNS(vertical)$" = aha."$COLUMNS(vertical)$%TYPE

#3result

	Code:
	l_vonal_id = aha.vonal_id%TYPE, l_datum_tol = aha.datum_tol%TYPE, l_varos_id = aha.varos_id%TYPE, l_vonal_nev = aha.vonal_nev%TYPE, l_aktiv_e = aha.aktiv_e%TYPE, l_datum_ig = aha.datum_ig%TYPE

Same as #2.

#4

	Code:
	l_$COLUMNS(vertical)$ = $OBJECT$.$COLUMNS(vertical)$%TYPE

#4 result

	Code:
	l_vonal_id, l_datum_tol, l_varos_id, l_vonal_nev, l_aktiv_e, l_datum_ig = vonal.vonal_id%TYPE, .datum_tol%TYPE, .varos_id%TYPE, .vonal_nev%TYPE, .aktiv_e%TYPE, .datum_ig%TYPE

Strange, I expected something similar to #2 and #3.

#5
"l_"$COLUMNS(vertical)$" $OBJECT$."$COLUMNS(vertical)$"%TYPE;"

#5 result

	Code:
	l_vonal_id $OBJECT$., l_datum_tol $OBJECT$., l_varos_id $OBJECT$., l_vonal_nev $OBJECT$., l_aktiv_e $OBJECT$., l_datum_ig $OBJECT$.vonal_id%TYPE;, datum_tol%TYPE;, varos_id%TYPE;, vonal_nev%TYPE;, aktiv_e%TYPE;, datum_ig%TYPE;

Er... Huhh?

#6

	Code:
	"l_"$COLUMNS(vertical)$" = $OBJECT$."$COLUMNS(vertical)$"%TYPE;"

#6 result

	Code:
	l_vonal_id = $OBJECT$.vonal_id%TYPE;, l_datum_tol = $OBJECT$.datum_tol%TYPE;, l_varos_id = $OBJECT$.varos_id%TYPE;, l_vonal_nev = $OBJECT$.vonal_nev%TYPE;, l_aktiv_e = $OBJECT$.aktiv_e%TYPE;, l_datum_ig = $OBJECT$.datum_ig%TYPE;

Ohh, I see, double quotes really take things "literally" (pun absolutely intended :).

The best I was able to produce so far was

	Code:
	"l_"$COLUMNS(vertical)$ <<table name here;parameter;varchar;=>>.$COLUMNS(vertical)$"%TYPE;"

This worked sort of expected. I only had to search&replace "<<table name here;parameter;varchar;=>>" with the table name (I have a script for things like that) and remove the commas from the ends of the lines.

Just thinking aloud, if you add $PROMPT$ macro to ask for table name, you can avoid search and replace, as that input can be likely inserted in place of "<<table>>", or perhaps use one of $CURRENT... $ macros to get table name from the script without a need to type it and then the result of that can go in place of "<<table>>"

These were good ideas. Unfortunately, neither of them worked. If I exclude the double quotes, the result is broken. If I include the quotes the macros between them are not evaluated :(

#7

	Code:
	"l_"$COLUMNS(vertical)$ = $CURRENT(param1, names_only)$.$COLUMNS(vertical)$"%TYPE;"

#7 result

	Code:
	l_vonal_id, l_datum_tol, l_varos_id, l_vonal_nev, l_aktiv_e, l_datum_ig = vonal.vonal_id%TYPE;, .datum_tol%TYPE;, .varos_id%TYPE;, .vonal_nev%TYPE;, .aktiv_e%TYPE;, .datum_ig%TYPE;

#8

	Code:
	"l_"$COLUMNS(vertical)$" = $CURRENT(param1, names_only)$."$COLUMNS(vertical)$"%TYPE;"

#8 result

	Code:
	vonal l_vonal_id = $CURRENT(param1, names_only)$.vonal_id%TYPE;, l_datum_tol = $CURRENT(param1, names_only)$.datum_tol%TYPE;, l_varos_id = $CURRENT(param1, names_only)$.varos_id%TYPE;, l_vonal_nev = $CURRENT(param1, names_only)$.vonal_nev%TYPE;, l_aktiv_e = $CURRENT(param1, names_only)$.aktiv_e%TYPE;, l_datum_ig = $CURRENT(param1, names_only)$.datum_ig%TYPE;

Here the name that should be used by the $CURRENT$ macro is not removed either, since the macro is not executed between the double quotes.

#9

	Code:
	$PROMPT(table,Table name?,,Declare table column variables)$ "l_"$COLUMNS(vertical)$ = $table$.$COLUMNS(vertical)$"%TYPE;"

#9 result

	Code:
	l_vonal_id, l_datum_tol, l_varos_id, l_vonal_nev, l_aktiv_e, l_datum_ig = vonal.vonal_id%TYPE;, .datum_tol%TYPE;, .varos_id%TYPE;, .vonal_nev%TYPE;, .aktiv_e%TYPE;, .datum_ig%TYPE;

#10

	Code:
	$PROMPT(table,Table name?,,Declare table column variables)$ "l_"$COLUMNS(vertical)$" = $table$."$COLUMNS(vertical)$"%TYPE;"

#10 result

	Code:
	l_vonal_id = $table$.vonal_id%TYPE;, l_datum_tol = $table$.datum_tol%TYPE;, l_varos_id = $table$.varos_id%TYPE;, l_vonal_nev = $table$.vonal_nev%TYPE;, l_aktiv_e = $table$.aktiv_e%TYPE;, l_datum_ig = $table$.datum_ig%TYPE;

Getting desperate and removing '=' as well:
#11

	Code:
	$PROMPT(table,Table name?,,Declare table column variables)$ "l_"$COLUMNS(vertical)$ $table$.$COLUMNS(vertical)$"%TYPE;"

#11 result

	Code:
	l_vonal_id, l_datum_tol, l_varos_id, l_vonal_nev, l_aktiv_e, l_datum_ig vonal.vonal_id%TYPE;, .datum_tol%TYPE;, .varos_id%TYPE;, .vonal_nev%TYPE;, .aktiv_e%TYPE;, .datum_ig%TYPE;

#12

	Code:
	$PROMPT(table,Table name?,,Declare table column variables)$ "l_"$COLUMNS(vertical)$" $table$."$COLUMNS(vertical)$"%TYPE;"

#12 result

	Code:
	l_vonal_id $table$., l_datum_tol $table$., l_varos_id $table$., l_vonal_nev $table$., l_aktiv_e $table$., l_datum_ig $table$.vonal_id%TYPE;, datum_tol%TYPE;, varos_id%TYPE;, vonal_nev%TYPE;, aktiv_e%TYPE;, datum_ig%TYPE;

The conclusion is that excluding '=' is out of the question, none of the candidates without it came anywhere near the desired result. Including '=' implies a mandatory search&replace to remove it. It's clear that omitting double quotes doesn't work either. Their presence also means that no other macros will get their values as they operate sort of an anti-magic field for macros.

To look at the bright side, I can say that I've learnt quite a bit about how these macros work (or don't work).