CrystalProgDev
Programmer
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;
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;