Home » SQL & PL/SQL » SQL & PL/SQL » Question on methodology (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit)
Question on methodology [message #430239] Mon, 09 November 2009 13:10 Go to next message
TNK101
Messages: 4
Registered: November 2009
Junior Member
I am a PLSQL newbie in need of some design advice and suggestions. My background is OO development with C++, C# and .NET. I'm writing an application in C#/.NET that talks to an Oracle database.

I am using ref cursors to return data to my C# code. This works and I can get the data I want.

However, I'm trying to reduce some duplication of SQL.

I have a situation where I have two procedures that return result sets with identical schemas. The only difference is their where clause.

I've encapsulated the schema in a view and created two procedures that

select *
from viewName
where viewName.x = someVar;


The difference between the procedures is the where clause. This works.

However, my application also needs to be able to run sub queries against the result sets returned by the two queries.

That is, I have a number of sub queries that can be run against the result sets of either of the two procedures.

Conceptually, I have a view in the first layer, 2 procedures that query the view in the second layer and a number of procedures that query the result set of either of the two procedures in the 2nd layer in the third layer.

That is, if this were a 3 layer graph with edges between nodes indicating a query relationship, the 1st and 2nd layer would be fully connected and the 2nd and 3rd layer would be fully connected. The 1st and 3rd layer would not be connected at all.

Ideally, I would have something like this:

procedure layer2Foo (retVal out ref cursor) is
begin
  open retVal
  for
  select *
  from view
  where someWhereClause;
end layer2Foo;

procedure layer2Bar (retVal out ref cursor) is
begin
  open retVal
  for
  select *
  from view
  where someOtherWhereClause;
end layer2Bar;

procedure layer3Sub1 (callType in varchar2, retVal out ref cursor) is
begin
if (callType = 'callLayer2Foo') then
  open retVal
  for
  select *
  from layer2Foo
  where someWhereClause;
elsif (callType = 'callLayer2Bar') then
  open retVal
  for
  select *
  from layer2Bar
  where someWhereClause;
end if;

end layer3Sub1;


I apologize for the pseudo code but it was clearest way to preset my goal. I can't figure out how to query the ref cursor returned by the layer 2 procedures without doing something stupid like fetching the records into a temporary table.

I've tried setting up the queries in the layer 2 procedures as strings and using those but I run into issues when writing the where clauses for the layer 3 procedures.

Here is my current approach using the string substitution method.

create or replace package body POFaxAPI is
        apprInd_Y		constant varchar2(1) := 'Y';
	completeInd_Y		constant varchar2(1) := 'Y';
	poListType_Single	constant varchar2(6) := 'single';
	poListType_All		constant varchar2(3) := 'all';
	qryGetAllPOList		constant varchar2(1024) :=
	'select * ' ||
	'from POList ' ||
	'where POList.fpbpohd_appr_ind = :apprInd_Y and ' ||
	'POList.fpbpohd_complete_ind = :completeInd_Y';

	qryGetSinglePOList	constant varchar2(1024) :=
	'select * ' ||
	'from POList ' ||
	'where POList.fpbpohd_code = :poNum';

	procedure GetAllPOList(poFaxRefCurOut out POFaxDefn.poFaxRefCurType) is
	begin
		open	poFaxRefCurOut
		for
		qryGetAllPOList
		using	apprInd_Y, completeInd_Y;
	end GetAllPOList;

	procedure GetSinglePOList(	poNum in varchar2,
					poFaxRefCurOut out POFaxDefn.poFaxRefCurType) is
	begin
		open	poFaxRefCurOut
		for
		qryGetSinglePOList
		using	poNum;
	end GetSinglePOList;

	procedure GetPOHeader(	callType in varchar2,
				poNum in varchar2,
				poFaxRefCurOut out POFaxDefn.poFaxRefCurType) is
	begin
		if	(callType = poListType_Single)	then
			open	poFaxRefCurOut
			for
			select	*
			from
			qryGetSinglePOList;
--			where	POList.fpbpohd_code = poNum;

		elsif	(callType = poListType_All)	then
			open	poFaxRefCurOut
			for
			select	*
			from
			qryGetAllPOList;
--			where	POList.fpbpohd_code = poNum;
		end if;
	end GetPOHeader;
end POFaxAPI;


Building this on the SQL Plus command line generates the following error:

SQL> show error
Errors for PACKAGE BODY POFAXAPI:

LINE/COL ERROR
-------- -----------------------------------------------
53/4 PL/SQL: SQL Statement ignored
55/4 PL/SQL: ORA-00942: table or view does not exist
61/4 PL/SQL: SQL Statement ignored
63/4 PL/SQL: ORA-00942: table or view does not exist
SQL>

In GetPOHeader, I want to check the fpbpohd_code in the result set of qryGetAllPOList but I don't know how to reference it.

I could probably do something like:

open poFaxRefCurOut
for
select *
from qryGetAllPOList || ' where POList.fpbpohd_code = ' || poNum;


However, coding stuff like this is stupid. It's going to be flakey and a maintenance nightmare. I'm not sure why I can't encapsulate things in procs and process result sets like I could in C++/C#. There is probably a way and I'm hoping someone here could point it out.

I'd appreciate if someone could point me in the right direction in terms of design. I don't want to be duplicating queries if I can help it.

Thanks

[Updated on: Mon, 09 November 2009 13:14]

Report message to a moderator

Re: Question on methodology [message #430271 is a reply to message #430239] Mon, 09 November 2009 20:47 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
A SQL SELECT statement can only select from a table, a view / MV, or a sub-query. There is a fourth option - Table Collection Expression - more on that later.

You cannot SELECT from a REFCURSOR. (Actually it's kind of possible with nasty XML conversions, but don't go there).

You have correctly identified VIEWs as the means of encapsulating SQL. You can have VIEWs over VIEWs which gives you multi-level reuseability, but is somewhat limited as well.

CREATE VIEW b AS
SELECT * FROM a
WHERE some clause

CREATE VIEW c AS
SELECT * FROM b
WHERE (some subquery)


If you don't like that, then the alternative is an order of magnitude more complicated. You need to create a PL/SQL function that returns a SQL OBJECT collection TYPE (defined on the DB, not a PL/SQL collection type) - it may optionally be a PIPELINED function (check the manual). These are called Table Functions. You could also do it with Procedures, but it's clunkier and doesn't permit PIPELINED functions.

You create a Table Function to return the results of one query. You can then use that table function in the FROM clause of another query.

SELECT *
FROM table(my_func(params))
WHERE some clause;


Essentially, you could change your pseudocode to return Nested Table TYPEs rather than REFCURSORS (and change procedures to functions), and you would get the functionality you seek.

Note that performance would be limited by the efficiency of the innermost Table Function. It would return EVERY row, which would then would be filtered by the outer table functions.

Maybe duplication is not so bad after all....

Ross Leishman
Re: Question on methodology [message #430293 is a reply to message #430239] Mon, 09 November 2009 23:38 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
I will ask you not to complicate things.

As you are already using dynamic sql you can generate your sql query at run time using some simple if else conditions.

And with using bind variables you can look into the following link
http://www.oracle.com/technology/oramag/oracle/09-jul/o49asktom.html
Re: Question on methodology [message #430487 is a reply to message #430271] Tue, 10 November 2009 12:07 Go to previous messageGo to next message
TNK101
Messages: 4
Registered: November 2009
Junior Member
rleishman wrote on Mon, 09 November 2009 20:47

Essentially, you could change your pseudocode to return Nested Table TYPEs rather than REFCURSORS (and change procedures to functions), and you would get the functionality you seek.

Note that performance would be limited by the efficiency of the innermost Table Function. It would return EVERY row, which would then would be filtered by the outer table functions.

Maybe duplication is not so bad after all....

Ross Leishman


The issue with duplication is that it doesn't scale well. If the where clauses in the two procedures at level two were 40 lines each and the sub queries at level three were extensive and there were many of them, I would have a huge mess of SQL.

Furthermore, maintenance would be a nightmare.

Dynamic SQL seems like it has the potential for maintenance problems as well but less so than blindly duplicating large pieces of SQL.

Do you have a link to some documentation on properly setting up the nested table types?

I'll do some googling in the meantime.

Trying to apply object oriented design principles to PLSQL is proving to be difficult.
Re: Question on methodology [message #430488 is a reply to message #430487] Tue, 10 November 2009 12:23 Go to previous message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Trying to apply object oriented design principles to PLSQL is proving to be difficult.

From my perspective, trying to use PL/SQL to implement Object Oriented Programming (OOP) paradigm is like trying to mix oil & water.

PL/SQL was created & implemented before OOP was created.
I believe trying to implement OOP inside Oracle RDBMS using PL/SQL is a fools task.

If the goal is to use OOP inside Oracle then use Java instead.
Java does not scale as well as PL/SQL inside the DB.

As a point of reference if OOP inside the DB were such a Good Thing (TM); then Oracle would be doing so itself.
Oracle continues to develop new functionality using vanilla PL/SQL without the "benefits" of OOP.
I suggest you should consider a similar approach.
Previous Topic: Checking Null values in column (merged 2)
Next Topic: Performance of MERGE statement
Goto Forum:
  


Current Time: Sat Dec 03 01:16:02 CST 2016

Total time taken to generate the page: 0.12781 seconds