Variable in a package with pipelined function

[CrystalProgDev]

I have a package with a pipelined function with 3 parameters. Based on Val parameter I need to get the table name.
for example if user select A then the table name that I should use is p_A, if user selects R the table name should be p_R.

I am thinking of creating a variable and set the variable value to tablename and use that variable in FROM clause as below.

DECLARE Table_Nm VARCHAR(10);
SET Table_Nm := 'p_' + Val;

But I am not sure where to decalre and set the value to variable. Below is the package that I have created. Can any one please let me know how to implement this in the below package.

CREATE TYPE WBS_ROW IS OBJECT
(
Product VARCHAR2 (100),
DESC VARCHAR (1000),
RecDATE DATE);

CREATE TYPE WBS_TAB IS TABLE OF WBS_ROW;

CREATE OR REPLACE PACKAGE "test_PKG"
AS
FUNCTION Test_PIPELINED (Val VARCHAR2,
STARTDATE DATE,
ENDDATE DATE)
RETURN WBS_TAB
PIPELINED;
END Test_PKG;

CREATE OR REPLACE PACKAGE BODY "Test_PKG"
AS
FUNCTION Test_PIPELINED (Val VARCHAR2,
STARTDATE DATE,
ENDDATE DATE)
RETURN WBS_TAB
PIPELINED
IS
PRAGMA AUTONOMOUS_TRANSACTION;

CURSOR WBS_CUR
IS
SELECT P.Product AS "Product",
P.VDESC AS "DESC",
TO_CHAR (P.DATE2_ADD, 'MM/DD/RRRR') AS "RecDATE"
FROM [highlight #FCE94F]Table_NM[/highlight] p
WHERE ( (PERIOD = 'ALL')
OR ( P.RecDATE >=
DECODE (
PERIOD,
'DAY', TRUNC (SYSDATE, 'DD') - 1,
'WEEK', TRUNC (SYSDATE, 'DD') - 7,
'MONTH', ADD_MONTHS (
TRUNC (SYSDATE, 'MM'),
-1),
'QUARTER', ADD_MONTHS (
TRUNC (SYSDATE, 'Q'),
-3),
'YEAR', ADD_MONTHS (
TRUNC (SYSDATE, 'YYYY'),
-12),
'--N/A--', TO_DATE (STARTDATE,
'MM/DD/YYYY'))
AND P.RecDATE <
DECODE (
PERIOD,
'DAY', TRUNC (SYSDATE, 'DD'),
'WEEK', TRUNC (SYSDATE, 'DD'),
'MONTH', TRUNC (SYSDATE, 'MM'),
'QUARTER', TRUNC (SYSDATE, 'Q'),
'YEAR', TRUNC (SYSDATE, 'YYYY'),
'--N/A--', TO_DATE (ENDDATE,
'MM/DD/YYYY')
+ 1)));
BEGIN
FOR RS_ROW IN WBS_CUR
LOOP
PIPE ROW (WBS_ROW (RS_ROW.Product,
RS_ROW.DESC,
RS_ROW.RecDATE));
END LOOP;

RETURN;
END;
END;

 

[Thargy]

crystal,

I believe that your approach to this in not the best.
Whilst it is possible to use dynamic table names in queries, it's often extremely hard to debug anything.

Can you give an over view of what you're trying to achieve - an English language narrative of what you're trying to implement?

As an opening suggestion, create a query, one for each possible table, and run that query with the table name in it.
That way you can trace your program execution much more easily.



Regards

T

 

[CrystalProgDev]

we have 3 tables p_A,p_B,p_C. when user selects product category type A, then we need to get the data from p_A. if the user selects product category type B, then we need to get the data from p_B. user selects product category type C, then we need to get the data from p_C. Val is the parameter that holds the product category type (A or B or C).

we wanted to use pipelined function in order to create universe. we are using BI 4 Information design tool. It will not allow us to create universe using stored procedures. so we came up with pipelined function, which will allow us to create universe.

the problem is I am not able to declare a variable before SQL to come up with table name based on product category type selection. if I am able to declare a variable and set the table name to that variable as below, will solve the issue.

DECLARE Table_Nm VARCHAR(10);
SET Table_Nm := 'p_' + Val;

Can you please suggest the best way to implement this?

 

[Thargy]

Crystal,

I don't know anything about business objects universes, but since a function is a type of stored procedure, I don't understand the restriction.
If you're receiving parameters in a stored procedure, then the universe must presumably be able to respond to the stored procedure - correct?

Is it possible to provide a list of what sort of oracle structures the universe can work with? Is it possible for me to download a trial version of the product, so that I can see what sort of problems you're facing?



Regards

T

 

[CrystalProgDev]

I am very new to both business objects and oracle 11g.
All the seniors in the team recommended using pipelined function.
We already have pipelined functions. Based on existing functions I came up with the above package and function in it. But as per the report logic as I mentioned I need to generate dynamic SQL. I just wanted to declare variables and use variable to generate SQL. Please let me know how to create variables in that function.



Thank you in advance

 

[fredericofonseca]

For what you trying to achieve use as many cursors/if statements as needed within your function.

small example using your code as base and the HR schema and tables.

Also note that I removed the " (double quotes) from your quote - that is considered bad practice as it creates a case sensitive name

CREATE TYPE WBS_ROW
IS
   OBJECT (tablename VARCHAR2 (100), rec_count DECIMAL (6));

CREATE TYPE WBS_TAB IS TABLE OF WBS_ROW;

CREATE OR REPLACE PACKAGE test_PKG
AS
   FUNCTION Test_PIPELINED (Val VARCHAR2)
      RETURN WBS_TAB
      PIPELINED;
END Test_PKG;

CREATE OR REPLACE PACKAGE BODY Test_PKG
AS
   FUNCTION Test_PIPELINED (Val VARCHAR2)
      RETURN WBS_TAB
      PIPELINED
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;

      CURSOR WBS_CUR1
      IS
         SELECT   'HR' AS table_name, COUNT ( * ) AS record_count
           FROM   hr.countries;

      CURSOR WBS_CUR2
      IS
         SELECT   'departments' AS table_name, COUNT ( * ) AS record_count
           FROM   hr.departments;

      CURSOR WBS_CUR3
      IS
         SELECT   'JOBS' AS table_name, COUNT ( * ) AS record_count
           FROM   hr.JOBS;

      CURSOR WBS_CUR4
      IS
         SELECT   'LOCATIONS' AS table_name, COUNT ( * ) AS record_count
           FROM   hr.LOCATIONS;
   BEGIN
      IF val = 'HR'
      THEN
         FOR RS_ROW IN WBS_CUR1
         LOOP
            PIPE ROW (WBS_ROW (RS_ROW.table_name, RS_ROW.record_count));
         END LOOP;
      END IF;

      IF val = 'DEP'
      THEN
         FOR RS_ROW IN WBS_CUR2
         LOOP
            PIPE ROW (WBS_ROW (RS_ROW.table_name, RS_ROW.record_count));
         END LOOP;
      END IF;

      IF val = 'JOB'
      THEN
         FOR RS_ROW IN WBS_CUR3
         LOOP
            PIPE ROW (WBS_ROW (RS_ROW.table_name, RS_ROW.record_count));
         END LOOP;
      END IF;

      IF val = 'LOC'
      THEN
         FOR RS_ROW IN WBS_CUR4
         LOOP
            PIPE ROW (WBS_ROW (RS_ROW.table_name, RS_ROW.record_count));
         END LOOP;
      END IF;

      RETURN;
   END;
END;


SELECT * from table(HR.TEST_PKG.TEST_PIPELINED('HR'))
UNION ALL
SELECT * from table(HR.TEST_PKG.TEST_PIPELINED('DEP'))
UNION ALL
SELECT * from table(HR.TEST_PKG.TEST_PIPELINED('LOC'))
UNION ALL
SELECT * from table(HR.TEST_PKG.TEST_PIPELINED('JOB'));

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[CrystalProgDev]

Yes, it works. is there any way we can run one cursor based on val parameter value? in this case...it will execute all the cursors and get the data and then filters the data right... my lead to performance issues. Am I correct?

 

[CrystalProgDev]

I found a solution for this. I figured out how to create variables in functions and wanted to use them in cursor.But not sure how to use them in cursors. When I exucte pakage throwing an error ([Warning] ORA-24344: success with compilation error
30/18 PL/SQL: ORA-00942: table or view does not exist
29/12 PL/SQL: SQL Statement ignored
35/32 PLS-00364: loop index variable 'RS_ROW' use is invalid
35/13 PL/SQL: Statement ignored
(30: 0): Warning: compiled but with compilation errors).


Can you please take a look and let me know the solution. below is the sql statement.

CREATE TYPE WBS_ROW
IS
OBJECT (Product VARCHAR2 (100), DESC VARCHAR (1000), RecDATE DATE);

CREATE TYPE WBS_TAB IS TABLE OF WBS_ROW;

CREATE OR REPLACE PACKAGE test_PKG
AS
FUNCTION Test_PIPELINED (Val VARCHAR2)
RETURN WBS_TAB
PIPELINED;
END Test_PKG;

CREATE OR REPLACE PACKAGE BODY Test_PKG
AS
FUNCTION Test_PIPELINED (Val VARCHAR2)
RETURN WBS_TAB
PIPELINED
IS
TABLE_NM VARCHAR2 (25) := 'P_' + SUBSTR (val, 2, 1); // table name based on Val parameter value

SELECTSTMT VARCHAR2 (2500) := 'P.PART AS PART,'
+ 'P.VDESC AS VDESC,'
+ 'P.UM AS UM;
FROMCLAUSE VARCHAR2(2500) := TABLE_NM +'AS P';

PRAGMA AUTONOMOUS_TRANSACTION;

CURSOR WBS_CUR
IS
SELECT SELECTSTMT
FROM FROMCLAUSE ;

BEGIN
FOR RS_ROW IN WBS_CUR
LOOP
PIPE ROW (WBS_ROW (RS_ROW.Product, RS_ROW.DESC, RS_ROW.RecDATE));
END LOOP;
RETURN;
END;
END;

 

[fredericofonseca]

no - each one of the following will execute the function and open a single cursor each time, and fetch from that cursor alone based on the value supplied to the function which was what you desired

this one opens cursor 1 - e.g. the if statement code associated with val = 'HR'
SELECT * from table(HR.TEST_PKG.TEST_PIPELINED('HR'))

this one opens cursor 2 - e.g. the if statement code associated with val = 'DEP'
SELECT * from table(HR.TEST_PKG.TEST_PIPELINED('DEP'))

this one opens cursor 3 - e.g. the if statement code associated with val = 'JOB'
SELECT * from table(HR.TEST_PKG.TEST_PIPELINED('JOB'));

this one opens cursor 4 - e.g. the if statement code associated with val = 'LOC'
SELECT * from table(HR.TEST_PKG.TEST_PIPELINED('LOC')) - opens cursor




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[CrystalProgDev]

Instead of having too many cursors, can't we have variables for select, from clauses. and use those variables in main sql.

SELECTSTMT VARCHAR2 (2500) := 'P.PART AS PART,'
+ 'P.VDESC AS VDESC,'
+ 'P.UM AS UM;

[COLOR=]FROMCLAUSE[/color] VARCHAR2(2500) := TABLE_NM +'AS P';

PRAGMA AUTONOMOUS_TRANSACTION;

CURSOR WBS_CUR
IS
SELECT SELECTSTMT
FROM FROMCLAUSE ;

BEGIN
FOR RS_ROW IN WBS_CUR
LOOP
PIPE ROW (WBS_ROW (RS_ROW.Product, RS_ROW.DESC, RS_ROW.RecDATE));
END LOOP;
RETURN;