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 Go to next message
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 Go to previous messageGo to next message
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 execution Error [message #596956 is a reply to message #596953] Fri, 27 September 2013 15:04 Go to previous messageGo to next message
joy_division
Messages: 4532
Registered: February 2005
Location: East Coast USA
Senior Member
That is because '1/1/2010' is a string, not a date. You have to change your input parameter type and convert the string to a date.
icon4.gif  Re: Variables in Pipelined function [message #596962 is a reply to message #596955] Fri, 27 September 2013 15:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Unformatted this is unreadable.
Anyway, if table name is variable then you have to use dynamic SQL.

Re: Package variables and execution Error [merged 2 by jd] [message #596963 is a reply to message #596953] Fri, 27 September 2013 15:53 Go to previous messageGo to next message
plsqldev
Messages: 5
Registered: September 2013
Location: MD
Junior Member
In order to Generate dynamic SQL, I am thinking of using a variable. But I am not sure where to create and set the variable in that function.
Can you please let me know where to define variable in that function.
Re: Package variables and execution Error [merged 2 by jd] [message #596965 is a reply to message #596963] Fri, 27 September 2013 17:10 Go to previous messageGo to next message
BlackSwan
Messages: 22927
Registered: January 2009
Senior Member
plsqldev wrote on Fri, 27 September 2013 13:53
In order to Generate dynamic SQL, I am thinking of using a variable. But I am not sure where to create and set the variable in that function.
Can you please let me know where to define variable in that function.



variable is declared before the BEGIN statement.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

which includes properly formatting any & all code posted here
Re: Package variables and execution Error [merged 2 by jd] [message #596969 is a reply to message #596965] Fri, 27 September 2013 22:32 Go to previous messageGo to next message
plsqldev
Messages: 5
Registered: September 2013
Location: MD
Junior Member
But this is pipelined function. There is no begin statement in this.
icon4.gif  Re: Package variables and execution Error [merged 2 by jd] [message #596970 is a reply to message #596969] Sat, 28 September 2013 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A pipelined function still must have a BEGIN statement.
The 2 differences with a non-pipelined function is:
1/ There is no need of a RETURN statement (since version 10.1)
2/ There are PIPE ROW statements (not allowed in other functions)

Re: Package variables and execution Error [merged 2 by jd] [message #596977 is a reply to message #596970] Sat, 28 September 2013 08:11 Go to previous messageGo to next message
plsqldev
Messages: 5
Registered: September 2013
Location: MD
Junior Member
When you look at my package function script, function has begin statement after the select statement.
I need to declare variable before select statement and use that variable in the from clause as table name
Re: Package variables and execution Error [merged 2 by jd] [message #596979 is a reply to message #596977] Sat, 28 September 2013 08:18 Go to previous messageGo to next message
BlackSwan
Messages: 22927
Registered: January 2009
Senior Member
since we don't have your tables or data, we can not run, test, or debug posted code.

Please see response from joy_division for most likely cause of reported error.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

which includes properly formatting any & all code posted here
Re: Package variables and execution Error [merged 2 by jd] [message #596982 is a reply to message #596953] Sat, 28 September 2013 08:42 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2458
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 Go to previous messageGo to next message
flyboy
Messages: 1776
Registered: November 2006
Senior Member
plsqldev wrote on Sat, 28 September 2013 15:11
When 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:11
and 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.
Re: Package variables and execution Error [merged 2 by jd] [message #596992 is a reply to message #596969] Sat, 28 September 2013 18:17 Go to previous message
BlackSwan
Messages: 22927
Registered: January 2009
Senior Member
plsqldev wrote on Fri, 27 September 2013 20:32
But this is pipelined function. There is no begin statement in this.


please post reproducible test case that shows above is true.
Previous Topic: Selecting the correct record from the table
Next Topic: Combining rows in data file based on values in control file
Goto Forum:
  


Current Time: Fri Oct 31 14:29:52 CDT 2014

Total time taken to generate the page: 2.27287 seconds