SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Request for assistance with $COLUMNS(...)$+$OBJECT(...)$

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Request for assistance with $COLUMNS(...)$+$OBJECT(...)$
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Request for assistance with $COLUMNS(...)$+$OBJECT(...)$ Reply with quote
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.
Tue Dec 19, 2017 11:52 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
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
Wed Dec 20, 2017 4:09 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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.
Wed Dec 20, 2017 10:17 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
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.
Wed Dec 20, 2017 1:15 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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?
Wed Dec 20, 2017 6:22 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
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.
Wed Dec 20, 2017 7:44 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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.
Fri Dec 22, 2017 11:04 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
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>>"
Fri Dec 22, 2017 11:50 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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).
Fri Dec 22, 2017 8:58 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.