Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!i39g2000cwa.googlegroups.com!not-for-mail
From: "centurionoftheseed" <rick.memmer@gmail.com>
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server
Subject: Re: dynamic sql - output actual string that includes all values from bind variables...
Date: 3 Mar 2006 12:50:39 -0800
Organization: http://groups.google.com
Lines: 92
Message-ID: <1141419039.818899.107630@i39g2000cwa.googlegroups.com>
References: <1141307212.349674.184720@e56g2000cwe.googlegroups.com>
   <1141334277.851311@jetspin.drizzle.com>
   <1141402796.153971.156660@i39g2000cwa.googlegroups.com>
   <1141417490.658355@jetspin.drizzle.com>
NNTP-Posting-Host: 171.159.192.10
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1141419046 10093 127.0.0.1 (3 Mar 2006 20:50:46 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 3 Mar 2006 20:50:46 +0000 (UTC)
In-Reply-To: <1141417490.658355@jetspin.drizzle.com>
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705; .NET CLR 1.1.4322; InfoPath.1),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: i39g2000cwa.googlegroups.com; posting-host=171.159.192.10;
   posting-account=cQk9Ew0AAAD_gjqjWD5cO56YIRYm8rMu
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:125682 comp.databases.oracle.server:262736

Hi, Daniel!

Sorry about the confusion.  I'm going to provide some detail below.
Note:  The procedure already works, and this is just a snapshot of it
to give you an idea of what it's doing.  Using Oracle 8.1.7.  Using
Embarcedero Rapid SQL and DBArtisan (though I'll do it in SQL+ if it
must be done there).

Also - yes, I am looking to see what the binded values that were used,
but I'm not sure how that works in conjunction with the dba_views.  I
mean, once you run a procedure - how would this sql end up in
dba_views?  If that works - great, but I don't see how it would.

Bottom line:  Ideally I would be able to ouput the vchSQLAppend sql
that gets fired based on the bind variables in the USING statement
after the EXECUTE IMMEDIATE.  If I can print the actual sql (including
bind variable values) or insert them into a table (either way is fine),
then that would be great.  As it stands now I can only find out what
the bind variables are by stepping through the code to look at the
individual variables.  Nowhere in the bugging can you view the actual
sql with bind variable values, because it doesn't know them until it
gets fired with the USING.

Thoughts?

Thanks!


intCountSales := 0;
                WHILE intCountSales < 5 LOOP
                    IF intCountSales = 0 THEN
                        numSales := 50000000;
                    ELSIF intCountSales = 1 THEN
                        numSales := 50000000;
                    ELSIF intCountSales = 2 THEN
                        numSales := 70000000;
                    ELSIF intCountSales = 3 THEN
                        numSales := 75000000;
                    ELSE
                        numSales := 100000000;
                    END IF;


                    vchSQLSelectFromWhere :=
                    ' SELECT :1 AS REPORT_TYPE,
                    :2 AS CATEGORY,
                    da.DOCASSIGN,sm.RATING,
                    COUNT(sm.COMPANYID) AS ACTIVITY_COUNT,
                    :3 AS ACTIVITY_MONTH,
                    :4 AS RANGE_FACTOR,
                    :5 AS ACTIVITY_MONTH_NUM,
                    :6 AS ACTIVITY_YEAR,
                    :7 AS SALES,
                    :8 AS SALES_SIGN
                    FROM SMRT_DOCASSIGN da, SMRT_ASSOCIATE sm_assoc,
                    SMRT_COMPANY sm WHERE sm.COMPANYID =
da.COMPANYID(+)

                    AND da.DOCASSIGN = sm_assoc.FULLNAME
                    AND sm.SMARTTYPE = ''Prospect''
                    AND sm_assoc.REPORT_FLAG = 1
                    AND (sm.CURORPASTCLIENT <> ''Current Client'' OR
sm.CURORPASTCLIENT IS NULL)
                    AND sm.RATING IN(:9,:10,:11)';


                    IF intCountSales = 0 THEN
                        vchCriteriaSales := ' AND SALES < :12';
                        vchSalesSign := '<';
                    ELSE
                        vchCriteriaSales := ' AND SALES >= :12 ';
                        vchSalesSign := '>=';
                    END IF;

                    IF vchCategory = 'Not Contacted' THEN
                        strCriteriaCategory := ' AND
CONTACT_FLAG_CUR(DATELASTACT,:13) = 0';
                    ELSE
                        strCriteriaCategory := ' AND LENGTH(:13) >= 0';
                    END IF;

                    vchSQLAppend := vchSQLInsertInto ||
vchSQLSelectFromWhere || strCriteriaCategory || vchCriteriaSales || '
GROUP BY da.DOCASSIGN,sm.RATING';
                    EXECUTE IMMEDIATE vchSQLAppend
                    USING
vchReportType,vchCategory,vchActMonthMON,intRangeFactor,intActMonthNum,i
ntActYear,numSales,vchSalesSign,vchRatingA,vchRatingB,vchRatingC,numSale
s,intRangeFactor;

                    COMMIT;