I want to write a procedure like this:
CURSOR C IS
SELECT table_name FROM ALL_TABLES WHERE table_name LIKE 'TMP_<%=odiRef.getPackage("PACKAGE_NAME")%> %' AND owner='STG';
FETCH C INTO R;
EXIT WHEN C%NOTFOUND;
EXCEPTION WHEN OTHERS THEN
But I guess it desn't exists odiRef.getPAckage("PACKAGE_NAME") method in odi 10g.How can ı get packagename ?
Thx a lot
If I understand, you've created an ODI procedure. And you have inserted this procedure as a step of an ODI package.
And you want to retrieve the name of the package that contains this procedure.
Is that right ?
If so, you can find the name of the package that use this procedure by using a query on the work repository.
Here's the query :
select pack.pack_name as PACKAGE
inner join odi_work.SNP_STEP step on step.i_package = pack.i_package
inner join odi_work.snp_trt proc on proc.i_trt = etape.i_trt
WHERE proc.trt_name='enter the name of your procedure'
You can write this query on the "SQL on source" tab of your procedure. And use the result on the "SQL on target" tab.
In "target tab", you can write something like that :
SELECT table_name FROM ALL_TABLES WHERE table_name LIKE 'TMP_:PACKAGE%> %' AND owner='STG';
If you don't want to enter the name of procedure manually, I assume you can also retrieve it with "<%=odiRef.getStep("STEP_NAME")%>" method. But your step must have the same name than your procedure (this is the default behaviour)