**PL/SQL experts..Help needed.. Macros in SQL? **

[samprok]

kinda vesina code pani chestundi but adi chala hard coding laga undi.. meeku emailna teliste cheppandi..

I have sys acct to the database so I can see and use all users and tables.

Current working code..

SELECT 'Trail1' as Trial, username, lastname, firstname, Decode (deletestate, 1, 'Terminated', 0, 'Not Terminated') as STATUS from Trial1.Table where upper(username) like 'SAM%'

UNION ALL
SELECT 'Trail2' as Trial, username, lastname, firstname, Decode (deletestate, 1, 'Terminated', 0, 'Not Terminated') as STATUS from Trial2.Table where upper(username) like 'SAM%'

UNION ALL
SELECT 'Trial3' as Trial, username, lastname, firstname, Decode (deletestate, 1, 'Terminated', 0, 'Not Terminated') as STATUS from Trial3.Table where upper(username) like 'SAM%'

ilaa 200 users ki select, union all raasukovadam anagarikam laga undi..

But I want something like.. 

Declare TR=Trail1, Trail2, Trail3.... ( where you can declare the macro variable)

Do

(

SELECT 'TR' as Trial, username, lastname, firstname, Decode (deletestate, 1, 'Terminated', 0, 'Not Terminated') as STATUS from TR.Table1 where upper(username) like 'SAM%'

)

any ideas?

I can do this ms-dos, excel vba and vbs but not sure how to do in sql, any help is appreciated..

 

[samprok]

are there no experts?

[Muney]

3 minutes ago, samprok said:

are there no experts?

LTT for the expert to bet the same request ?

[andhrafan85]

56 minutes ago, samprok said:

kinda vesina code pani chestundi but adi chala hard coding laga undi.. meeku emailna teliste cheppandi..

I have sys acct to the database so I can see and use all users and tables.

Current working code..

SELECT 'Trail1' as Trial, username, lastname, firstname, Decode (deletestate, 1, 'Terminated', 0, 'Not Terminated') as STATUS from Trial1.Table where upper(username) like 'SAM%'

UNION ALL
SELECT 'Trail2' as Trial, username, lastname, firstname, Decode (deletestate, 1, 'Terminated', 0, 'Not Terminated') as STATUS from Trial2.Table where upper(username) like 'SAM%'

UNION ALL
SELECT 'Trial3' as Trial, username, lastname, firstname, Decode (deletestate, 1, 'Terminated', 0, 'Not Terminated') as STATUS from Trial3.Table where upper(username) like 'SAM%'

ilaa 200 users ki select, union all raasukovadam anagarikam laga undi..

But I want something like.. 

Declare TR=Trail1, Trail2, Trail3.... ( where you can declare the macro variable)

Do

(

SELECT 'TR' as Trial, username, lastname, firstname, Decode (deletestate, 1, 'Terminated', 0, 'Not Terminated') as STATUS from TR.Table1 where upper(username) like 'SAM%'

)

any ideas?

I can do this ms-dos, excel vba and vbs but not sure how to do in sql, any help is appreciated..

 

You dont know how to send varaiable list of users to sql from shell?

[former]

VBA macro DB connection invoke chesi query execute cheyi.

[former]

or create procedure with input variable and invoke based on procedure call

[samprok]

9 minutes ago, andhrafan85 said:

You dont know how to send varaiable list of users to sql from shell?

bayya I can do everything in one batch files, ade sqlplus lo connect ayyi query esi, output csv lo istundi..

sqlDeveloper lo ela cheyyali ani alochistunna

[samprok]

5 minutes ago, former said:

VBA macro DB connection invoke chesi query execute cheyi.

bayya.. intention was to do everything in sql developer..

[samprok]

5 minutes ago, former said:

or create procedure with input variable and invoke based on procedure call

enlighten me, or google lo em key words use cheyyalo cheppandi..

I tried For Loop, Cursors but naa need i avi paniki raavu..

[former]

2 hours ago, samprok said:

bayya.. intention was to do everything in sql developer..

Try with Bind Variables

SELECT :TR_Input as Trial, username, lastname, firstname, Decode (deletestate, 1, 'Terminated', 0, 'Not Terminated') as STATUS from :TR_Input.Table1 where upper(username) like 'SAM%'

[gunturguys]

You Can try building  Dynamic SQL using all user names 

[andhrafan85]

2 hours ago, samprok said:

bayya I can do everything in one batch files, ade sqlplus lo connect ayyi query esi, output csv lo istundi..

sqlDeveloper lo ela cheyyali ani alochistunna

I know to pass from shell to sql script