3 Replies Latest reply: Dec 10, 2012 7:58 AM by aetl RSS

    ODI 10G getPAckage()

    aetl
      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
          What is your ODI Version ?
          • 2. Re: ODI 10G getPAckage()
            A. Drieux
            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
              Thanks.I resolved this problem.I used odiRef.getSession("SESS_NAME")