Home » SQL & PL/SQL » SQL & PL/SQL » Difference between two declaration.
Difference between two declaration. [message #594526] Thu, 29 August 2013 09:44 Go to next message
VIP2013
Messages: 84
Registered: June 2013
Member
i am using 11.2.0.3 version of oracle, and when i am executing below piec of code and expecting similar eesults

Below variable is declare at package level as below.

TYPE mycur IS REF CURSOR;

now mentioned below is my procedure inside the package. My question is, i want to understand the difference between below two declaration of the same procedure. as because declaration:1 giving me more than one records for same pk1, where as declaration:2 giving me the correct result i.e. one records as output of the return cursor.

Declaration:1

PROCEDURE p1 (pk1 IN NUMBER,
ret_cursor OUT mycur)
AS

BEGIN
open ret_cursor for SELECT *
FROM a ,
b ,
WHERE a.id = b.id
AND (a.c1 = b.c1
OR a.c2 = b.c2)
AND a.c3 = pk1;

END;
/

Declaration : 2

PROCEDURE p1 (pk1 IN NUMBER,
ret_cursor OUT mycur)
AS
V_QUERY VARCHAR2(4000);

BEGIN
V_QUERY :=
'SELECT *
FROM a ,
b ,
WHERE a.id = b.id
AND (a.c1 = b.c1
OR a.c2 = b.c2)
AND a.c3 = ' || pk1;

OPEN ret_cursor FOR V_QUERY;
END;
/

Re: Difference between two declaration. [message #594527 is a reply to message #594526] Thu, 29 August 2013 09:50 Go to previous messageGo to next message
BlackSwan
Messages: 22728
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

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


since you repeatedly choose to ignore Posting Guidelines, I chose to not provide any additional assistance
Re: Difference between two declaration. [message #594528 is a reply to message #594527] Thu, 29 August 2013 09:54 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
You know how to use code tags, so why didn't you?

I can't see any obvious reason for the problem, you'll need to give a full example, including tables, data, the code used to fetch the ref cursor and the results you are getting.
Re: Difference between two declaration. [message #594534 is a reply to message #594528] Thu, 29 August 2013 12:11 Go to previous messageGo to next message
VIP2013
Messages: 84
Registered: June 2013
Member
actually my mistake, just got the reason behind the issue. i had posted the name of the table/variable by modifying them and are different from the actual one.
So the issue was naming convention given by the developers, the input variable name was exactly similar as that of column name, that is why it was evaluating to something like 'and (1=1)'.

Name of the input variable was 'c3' same as column name of 'a' causing issue.

a.c3 = c3
Re: Difference between two declaration. [message #594563 is a reply to message #594534] Fri, 30 August 2013 03:01 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's why most coders name variables and parameters with a prefix to indicate that's what they are.
Re: Difference between two declaration. [message #594578 is a reply to message #594563] Fri, 30 August 2013 03:36 Go to previous message
pablolee
Messages: 2615
Registered: May 2007
Location: Scotland
Senior Member
cookiemonster wrote on Fri, 30 August 2013 09:01
That's why most coders name variables and parameters with a prefix to indicate that's what they are.

Heh, there's a heated 'discussion' going on on OTN just now about that, with a couple of people stating that the use of prefixes to indicate scope is "Stupid". They make some fairly compelling arguments, but I still think I'll stick with the prefix method personally.
Previous Topic: PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
Next Topic: query help (if, else)
Goto Forum:
  


Current Time: Tue Sep 02 17:58:27 CDT 2014

Total time taken to generate the page: 0.16742 seconds