Oracle stored proc problem

3 Message(s) by 2 Author(s) originally posted in cfml database access

From: dongzky

Date: Monday, October 22, 2007

HI, I have created a stored proc in oracle similar below: CREATE OR REPLACE PACKAGE PACK_REFCURSOR_NHM_TRANSACTION AS
TYPE TRANS_TableRows IS REF CURSOR
RETURN NHM_TRANSACTION%ROWTYPE;
PROC

<language> The {job control language} used in the {Pick}
	{operating system}.

	["Exploring the Pick Operating System", J.E. Sisk et al,
	Hayden 1986].

	(1998-10-13)

EDURE REFCUR_NHM_TRANSACTION (
IN_sTMPACC IN string,
IN_sITEM_TYPES IN string,
OUT_TRANS OUT TRANS_TableRows);
END PACK_REFCURSOR_NHM_TRANSACTION ;

/
-- the above works fine
--but this one below has the error

1. A discrepancy between a computed, observed, or measured
	value or condition and the true, specified, or theoretically
	correct value or condition.

	2. <programming> A mental mistake made by a programmer that
	may result in a program {fault}.

	3. (verb) What a program does when it stops as result of a
	programming error.

	(2000-03-28)

when
CREATE OR REPLACE PACKAGE BODY PACK_REFCURSOR_NHM_TRANSACTION AS
PROCEDURE REFCUR_NHM_TRANSACTION (
IN_sTMPACC IN string,
IN_sITEM_TYPES IN string,
OUT_TRANS OUT TRANS_TableRows) IS
BEGIN

OPEN OUT_TRANS FOR
SELECT T.TRANSACTION_ID, T.TRANSACTION_TYPE, T.TRANSACTION_DATE,
T.TRANSACTION_TIME,
T.ITEM_CODE, 0 OE_DETAIL_ID,
T.QUANTITY, T.LIST_PRICE, T.DISCOUNT, T.SALE_PRICE, T.GST_AMOUNT,
'I'INSURER_TYPE
FROM NHM_TRANSACTION T
WHERE DISPENSED_FLG = 'Y'
AND T.ACCOUNT_ID = IN_sTMPACC
AND T.TRANSACTION_TYPE IN ('01','02', '09')
AND NVL(T.STATUS,'F') != 'C'
AND T.BILLED_FLG = 'N'
AND T.ITEM_TYPE IN (IN_sITEM_TYPES)
AND T.BILL_ITEM_FLG = 'Y'
UNION ALL
SELECT T.OE_TRANSACTION_ID TRANSACTION_ID, TRANSACTION_TYPE,
T.TRANSACTION_DATE,
T.TRANSACTION_TIME, NVL(T.ITEM_CODE,T.PACKAGE_CODE) ITEM_CODE,
T.OE_DETAIL_ID,
T.QUANTITY, T.LIST_PRICE, T.DISCOUNT, T.SALE_PRICE, T.GST_AMOUNT, 'S'
INSURER_TYPE
FROM NHC_OE_TRANSACTION T, NHC_MASTER_ITEM M, NHM_PACKAGE P
WHERE T.ACCOUNT_ID = IN_sTMPACC
AND T.ITEM_CODE = M.ITEM_CODE(+)
AND T.PACKAGE_CODE = P.PACKAGE_CODE(+)
AND (DECODE(T.ITEM_CODE,NULL,P.BILL_OPTION,M.BILL_OPTION) in ('P','O','F'))
AND NVL(T.STATUS,'F') != 'C'
AND T.BILLED_FLG = 'N'
AND ((T.TRANSACTION_TYPE = '09' AND STATUS IN ('P','R'))
OR T.TRANSACTION_TYPE IN ('01','02','11','12', '10'))
AND T.BILL_ITEM_FLG = 'Y'
AND T.ITEM_TYPE IN (IN_sITEM_TYPES) ;

END REFCUR_NHM_TRANSACTION ;
END PACK_REFCURSOR_NHM_TRANSACTION ;

--this gives error: PLS-00382: expression

<programming> Any piece of program code in a {high-level
	language} which, when (if) its execution terminates, returns a
	value.  In most programming languages, expressions consist of
	constants, variables, operators, functions, and {parentheses}.
	The operators and functions may be built-in or user defined.
	Languages differ on how expressions of different {types} may
	be combined - with some combination of explicit {casts} and
	implicit {coercions}.

	The {syntax} of expressions generally follows conventional
	mathematical notation, though some languages such as {Lisp} or
	{Forth} have their own idiosyncratic syntax.

	(2001-05-14)

is of wrong type

<theory, programming> (Or "data type") A set of values from
	which a {variable}, {constant}, {function}, or other
	{expression} may take its value.  A type is a classification
	of data that tells the {compiler} or {interpreter} how the
	programmer intends to use it.  For example, the process and
	result of adding two variables differs greatly according to
	whether they are integers, floating point numbers, or strings.

	Types supported by most programming languages include
	{integers} (usually limited to some range so they will fit in
	one {word} of storage), {Booleans}, {floating point numbers},
	and characters.  {Strings} are also common, and are
	represented as {lists} of characters in some languages.

	If s and t are types, then so is s -> t, the type of
	{functions} from s to t; that is, give them a term of type s,
	functions of type s -> t will return a term of type t.

	Some types are {primitive} - built-in to the language, with no
	visible internal structure - e.g. Boolean; others are
	composite - constructed from one or more other types (of
	either kind) - e.g. {lists}, {arrays}, {structures}, {unions}.
	{Object-oriented programming} extends this with {classes}
	which encapsulate both the structure of a type and the
	operations that can be performed on it.

	Some languages provide {strong typing}, others allow {implicit
	type conversion} and/or {explicit type conversion}.

	(2003-12-22)

but when I tried to change the query

1. <database, information science> A user's (or {agent}'s)
	request for information, generally as a formal request to a
	{database} or {search engine}.

	{SQL} is the most common {database query language}.

	2. <character> {question mark}.

	(1997-04-09)

similar to this:

OPEN OUT_TRANS FOR
SELECT T.*
FROM NHM_TRANSACTION T
WHERE DISPENSED_FLG = 'Y'
AND T.ACCOUNT_ID = IN_sTMPACC
AND T.TRANSACTION_TYPE IN ('01','02', '09')
AND NVL(T.STATUS,'F') != 'C'
AND T.BILLED_FLG = 'N'
AND T.ITEM_TYPE IN (IN_sITEM_TYPES)
AND T.BILL_ITEM_FLG = 'Y' ;

--that worked fine. what I found out is the proc does not seem to work with
complicated queries. instead it works only in something similar to SELECT T.*?
and not when individual fields are extracted? I can not understand why? can
anyone help me with this> thanks.

From: paross1

Date: Monday, October 22, 2007

I think what is hapenning is that you are declaring the reference

1. <programming> An {address}, from the point of view of a
	programming language.  A pointer may be typed, with its {type}
	indicating the type of data to which it points.

	The terms "pointer" and "reference" are generally
	interchangable although particular programming languages often
	differentiate these two in subtle ways.  For example, {Perl}
	always calls them references, never pointers.  Conversely, in
	C, "pointer" is used, although "a reference" is often used to
	denote the concept that a pointer implements.

	{Anthony Hoare} once said:

	Pointers are like jumps, leading wildly from one part of the
	data structure to another.  Their introduction into high-level
	languages has been a step backward from which we may never
	recover.

	[C.A.R.Hoare "Hints on Programming Language Design", 1973,
	Prentice-Hall collection of essays and papers by Tony Hoare].

	2. <operating system> (Or "mouse pointer") An {icon}, usually
	a small arrow, that moves on the screen in response to
	movement of a {pointing device}, typically a {mouse}.  The
	pointer shows the user which object on the screen will be
	selected etc. when a mouse button is clicked.

	(1999-07-07)

cursor

1. <hardware> A visually distinct mark on a display indicating
	where newly typed text will be inserted.  The cursor moves as
	text is typed and, in most modern editors, can be moved around
	within a document by the user to change the insertion point.

	2. <database> In {SQL}, a named control structure used by an
	{application program} to point to a row of data.  The position
	of the {row} is within a {table} or {view}, and the cursor is
	used interactively so select rows from columns.

	(1996-12-27)

in
your package spec as a "strong" type as a rowtype of table

<database> A collection of {records} in a {relational database}.

	(1997-06-04)

NHM_TRANSACTION.
However, in your OPEN FOR SELECT statement, you are selecting columns that do
not exist in the the NHM_TRANSACTION table, such as 0 OE_DETAIL_ID,
'I'INSURER_TYPE, etc.

You'd be much better off to declare your reference cursor as a "weak" type
so that your OPEN FOR SELECT statement actually defines the return structure:

TYPE TRANS_TableRows
IS REF CURSOR;

In other words, leave off the RETURN NHM_TRANSACTION%ROWTYPE in your ref
cursor declaration, and you'd probably be OK (assuming, of course, that all
of the columns selected in your UNION statements are like data

<data, data processing, jargon> /day't*/ (Or "raw data")
	Numbers, {characters}, {images}, or other method of recording,
	in a form which can be assessed by a human or (especially)
	input into a {computer}, stored and {processed} there, or
	transmitted on some {digital channel}.  Computers nearly
	always represent data in {binary}.

	Data on its own has no meaning, only when interpreted by some
	kind of {data processing system} does it take on meaning and
	become {information}.

	People or computers can find patterns in data to perceive
	information, and information can be used to enhance
	{knowledge}.  Since knowledge is prerequisite to wisdom, we
	always want more data and information.  But, as modern
	societies verge on {information overload}, we especially need
	better ways to find patterns.

	1234567.89 is data.

	"Your bank balance has jumped 8087% to $1234567.89" is
	information.

	"Nobody owes me that much money" is knowledge.

	"I'd better talk to the bank before I spend it, because of
	what has happened to other people" is wisdom.

	(1999-04-30)

types).

Phil

From: dongzky

Date: Tuesday, October 23, 2007

@xxxxxxxxxxx : it worked. thanks for the help. I really appreciate it.

Next Message: Trouble with MSSQL query