This discussion is archived
3 Replies Latest reply: Dec 10, 2012 5:58 AM by aetl RSS

ODI 10G getPAckage()

aetl Explorer
Currently Being Moderated
Hi ,

I want to write a procedure like this:

DECLARE
CURSOR C IS
SELECT table_name FROM ALL_TABLES WHERE table_name LIKE 'TMP_<%=odiRef.getPackage("PACKAGE_NAME")%> %' AND owner='STG';
R C%ROWTYPE;
BEGIN
OPEN C;
LOOP
FETCH C INTO R;
EXIT WHEN C%NOTFOUND;
BEGIN
STG.DROP_TABLE(R.TABLE_NAME);
EXCEPTION WHEN OTHERS THEN
NULL;
END;
END LOOP;
CLOSE C;
END;

But I guess it desn't exists odiRef.getPAckage("PACKAGE_NAME") method in odi 10g.How can ı get packagename ?


Thx a lot
  • 1. Re: ODI 10G getPAckage()
    Bhabani Ranjan Guru
    Currently Being Moderated
    What is your ODI Version ?
  • 2. Re: ODI 10G getPAckage()
    A. Drieux Pro
    Currently Being Moderated
    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
    FROM
    odi_work.SNP_PACKAGE Pack
    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)
  • 3. Re: ODI 10G getPAckage()
    aetl Explorer
    Currently Being Moderated
    Thanks.I resolved this problem.I used odiRef.getSession("SESS_NAME")

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points