RE: Pl/SQL Dynamic SQL questions

From: David Barbour <dbarbour_at_connectsouth.com>
Date: Wed, 07 Feb 2001 09:07:34 -0800
Message-ID: <F001.002ADC60.20010207081047@fatcity.com>

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)

        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
dbarbour_at_connectsouth.com

-----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
susan_at_filefrenzy.com
919.760.1167

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Susan E Teague
INET: Susan_at_fileFRENZY.com

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: ListGuru_at_fatcity.com (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: dbarbour_at_connectsouth.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------