Home » SQL & PL/SQL » SQL & PL/SQL » Package variables and execution Error [merged 2 by jd] (Oracle 11g)
| Package variables and execution Error [merged 2 by jd] [message #596953] |
Fri, 27 September 2013 15:02  |
 |
plsqldev
Messages: 5
Registered: September 2013
Location: MD
|
Junior Member |
|
|
I have a package with pipelined function in it. using the below stmt to execute the function in Oracle 11g.
select * FROM table(Test_PKG.Test_PIPELINED ('A','--N/A--','1/1/2010','1/1/2011'));
Throwing an error: invalid month error. Can any one please let me know where I am doing wrong.
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, PERIOD 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, PERIOD 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 Product 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;
|
|
|
|
| Variables in Pipelined function [message #596955 is a reply to message #596953] |
Fri, 27 September 2013 15:03  |
|
plsqldev
Messages: 5
Registered: September 2013
Location: MD
|
Junior Member |
|
|
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 declare 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, PERIOD 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, PERIOD 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 Product 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;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Package variables and execution Error [merged 2 by jd] [message #596982 is a reply to message #596953] |
Sat, 28 September 2013 08:42 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
plsqldev wrote on Sat, 28 September 2013 01:32
select * FROM table(Test_PKG.Test_PIPELINED ('A','--N/A--','1/1/2010','1/1/2011'));
Throwing an error: invalid month error.
1. Are you using SQL*Plus or any third party tool?
2. Please execute this in SQL*Plus and paste the output -
select sysdate from dual;
This is to check the NLS_DATE_FORMAT which is being used.
Regards,
Lalit
|
|
|
|
| Re: Package variables and execution Error [merged 2 by jd] [message #596985 is a reply to message #596977] |
Sat, 28 September 2013 10:00 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
plsqldev wrote on Sat, 28 September 2013 15:11When you look at my package function script, function has begin statement after the select statement.
I need to declare variable before select statement and
Only you stated that there is no BEGIN keyword. So, as you finally found it, just declare that variable before that cursor (it is also before BEGIN keyword, is not it?).
plsqldev wrote on Sat, 28 September 2013 15:11and use that variable in the from clause as table name
Fortuinately, you cannot to do it in the static cursor declaration. As already suggested by Michel, you have to use dynamic SQL.
It is described in PL/SQL Language Reference, which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/
Here is chapter for 11gR2: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#CACDDACH
Concentrate on the section "OPEN FOR, FETCH, and CLOSE Statements".
And do not forget the following one (Repeated Placeholder Names in Dynamic SQL Statements) to properly bind the input parameters.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 00:47:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
