very urgent-sql script

[urchin556]

hi friends,
i have a script which is as follows:

clear screen ;
prompt |
prompt |Current Set of Books ID:&&sob
prompt |Current Organization ID:&&org
prompt |Current Business ID :&&bus
prompt |
prompt | 1 - (AOL) - AOL Insert Scripts
prompt |
prompt | 2 - (GL) - Oracle General Ledger prompt |
prompt | 3 - (AP) - Oracle Accounts Payable prompt |
prompt | 4 - (AR) - Oracle Accounts prompt |
prompt | 5 - (FA) - Oracle Fixed Assets
prompt |
prompt | 6 - (PO) - Oracle Purchasing
prompt |
prompt | 7 - (INV) - Oracle Inventory
prompt |
prompt | 8 - (OE) - Oracle Order Entry
prompt |
prompt | 9 - (HR) - Oracle Human Resource
prompt |
prompt | 10 - (BOM) - Oracle Bill of Material
prompt |
prompt | 11 - Run All Module Insert Scripts
prompt |
prompt | 12 - Exit
accept choice num prompt '| Enter your Choice: '

define xchoice = choice

column script_name noprint new_value script_name_var

select decode(&choice,1,'c:\sit\aol\aol_scripts.txt',
2,'c:\sit\gl\gl_scripts.txt',
3,'C:\sit\ap\ap_scripts.txt',
4,'c:\sit\ar\ar_scripts.txt',
5,'c:\sit\fa\fa_scripts.txt',
6,'c:\sit\po\po_scripts.txt',
7,'c:\sit\inv\inv_scripts.txt',
8,'c:\sit\oe\oe_scripts.txt',
9,'c:\sit\hr\hr_scripts.txt',
10,'c:\sit\bom\bom_scripts.txt',
11,'c:\sit\all_scripts.txt',
12,'c:\sit\exit_scripts.txt',
'c:\sit\badscript.txt') script_name
from dual ;

@&script_name_var

there are three substitute variables SOB,BUS and ORG which are being used in the all the scripts.
now the problem is i have to run the above script from an anonymous block .
the block is as follows

eclare
scr varchar2(150);
cursor c1 is SELECT substr(ORG.set_of_books_id,1,6) sobid,
substr(GSOB.name,1,20) "SOB Name",
substr(ORG.operating_unit,1,6) orgcode,
-- substr(ORG.organization_name,1,20) "Org Name",
-- substr(ORG.organization_code,1,6) " OrgCode",
substr(pbg.name,1,20) bname,
substr(pbg.business_group_id, 1, 6) bgid
FROM org_organization_definitions ORG,
gl_sets_of_books GSOB,
per_business_groups pbg
WHERE ORG.set_of_books_id = GSOB.set_of_books_id and rownum<=5;

begin

dbms_output.put_line(scr);
for i in c1
loop
---------------------------------------------------
the script shoould be called with in the loop and the values of sbstitute vriable should be assigned by the vales of the cursor columns.
I cant make it stored procedure coz it calls so many scripts inside and m not allowed to touch the inner scripts.
I have control on just these 2 scripts.
IS there any workaround??Guys its very urgent...plz help

 

[sem]

You may spool this query to command file and execute it, e.g.:

spool cmd.sql
select '@script '||empno||' '||deptno from emp;
spool off
@cmd.sql


Regards, Dima

http://seminihin.narod.ru

 

[urchin556]

hi Friend,
I didnt get it.How will i call the script within the loop with substitution variables having values from columns values.It would be more helpful if u give the example of the above script.
Thanks and Regards,
Hemant

 

[sem]

To provide a solution I need a bit better description of your task. How do SOB,BUS and ORG relate to sobid, "SOB Name", " OrgCode" etc?

Regards, Dima

http://seminihin.narod.ru

 

[urchin556]

hi friend,
Actually the cursor will give me the value of sobid,orgid and busid which i have to assigned to each substitution variable i.e sob,bus and org respectively.
so if the cursor returns 10 rows..10 times the value should be pass to substitution variables and script should be called.Thanks

 

[sem]

I don't see any orgid or busid in your script. Could you provide a query returning sobid,orgid and busid?

Regards, Dima

http://seminihin.narod.ru

 

[urchin556]

cursor c1 is SELECT substr(ORG.set_of_books_id,1,6) sobid,
substr(GSOB.name,1,20) "SOB Name",
substr(ORG.operating_unit,1,6) orgid,
-- substr(ORG.organization_name,1,20) "Org Name",
-- substr(ORG.organization_code,1,6) " OrgCode",
substr(pbg.name,1,20) bname,
substr(pbg.business_group_id, 1, 6) busid
FROM org_organization_definitions ORG,
gl_sets_of_books GSOB,
per_business_groups pbg
WHERE ORG.set_of_books_id = GSOB.set_of_books_id and rownum<=5;

This cursor will give me sobid,orgid and busid and then value of these coulmns should be assigned to substitution variables sob,bus and org and call the script .
Thanks

 

[sem]

Assuming your main script is called script

set serverout off
set feedback off
set verify off
set pages 0
set lines 1000
set trimspool on
spool cmd.sql
select '@script '||
substr(ORG.set_of_books_id,1,6)||' '||
substr(ORG.operating_unit,1,6)||' '||
substr(pbg.business_group_id, 1, 6)
FROM   org_organization_definitions ORG,
           gl_sets_of_books GSOB,
        per_business_groups pbg
WHERE  ORG.set_of_books_id = GSOB.set_of_books_id and rownum<=5
spool off
@cmd

Store the above into a file and call it from sql*plus. Be careful to use correct order of parameters.

Regards, Dima

http://seminihin.narod.ru