Susan,
Dynamic SQL can get pretty interesting, depending on the values of your
programmer defined records. Obviously your code compiles, but when you add
the variable, funny things can happen. When you break out of the routine,
what line is showing as being the hangup? If it's your bigSQL, try
commenting out various parts of the code and use something like
dbms_output.put_line to see what your variables and clauses look like.
Here's a snippet of something similar I wrote. It selects from different
tables using different where clauses depending on user input from the
browser:
CREATE OR REPLACE PROCEDURE leadsResults(
leadFunction varchar2,
customer varchar2,
leads_phone_npa varchar2,
leads_phone_nxx varchar2,
leads_phone_suffix varchar2,
clli varchar2,
market varchar2,
status varchar2)
AS
v_id owa_cookie.cookie;
fromClause varchar2(20);
whereClause varchar2(250);
TYPE RefCurTyp IS REF CURSOR;
listCur RefCurTyp;
BEGIN
v_id:=OWA_COOKIE.GET('employee');
IF leadFunction = 'workingLeads' THEN
v_lead_type:= NULL;
fromClause:='leads';
IF clli IS NOT NULL
AND customer IS NOT NULL
AND status IS NOT NULL THEN
whereClause:= 'WHERE ld_clli = ' ||''''|| clli ||''''||
'AND UPPER(ld_bus_name) like UPPER('
||''''||'\%' || customer || '\%'||''''||')
AND ld_term_code = ' ||''''||status||''''||
'AND to_number(ld_emp_id) = '
||v_id.vals(1)||
'ORDER BY ld_bus_name';
END IF;
END IF;
OPEN listCur for
'SELECT ld_bus_name, ld_term_code, ld_timestamp,
ld_sic_name, ld_bus_phone,
ld_bus_addr, ld_bus_city, ld_bus_state, ld_bus_zip,
ld_notes, ld_duns_num from '
|| fromClause || ' ' || whereClause;
Hope this helps.
David A. Barbour
Oracle DBA - ConnectSouth
512-681-9438
[EMAIL PROTECTED]
-----Original Message-----
Sent: Tuesday, February 06, 2001 3:18 PM
To: Multiple recipients of list ORACLE-L
Hello all,
Okay, so I may be making this problem more difficult than it appears, but
I'm having a helluva time using ref cursors with dynamic sql in a function.
Anybody done this? Basically I'm trying to construct the select, from and
where clauses via variables; so for instance, I test for acctType (passed in
as a parameter) like so:
if (acctType = 0) then
selectClause := 'select etc.';
elsif (accType = 1) then
selectClause := 'select etc.';
etc. etc.
Then I have one big sqlStmt variable where I concat the select, from, where
clause.
I open the sqlStmt, fetch into a programmer defined record and try and
return this cursor. Problem is sql*plus locks up each time I try to run it.
I never get anything back which leads me to believe that something is
terribly wrong with my approach. I've used an approach outlined in the
Oracle documentation...
Thanks for you help! Hope this makes sense :)
SE Teague
Aresenal Digital Solutions
Data Services
[EMAIL PROTECTED]
919.760.1167
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Susan E Teague
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: David Barbour
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
