Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: dynamic sql - output actual string that includes all values from bind variables...

Re: dynamic sql - output actual string that includes all values from bind variables...

From: centurionoftheseed <rick.memmer_at_gmail.com>
Date: 3 Mar 2006 12:50:39 -0800
Message-ID: <1141419039.818899.107630@i39g2000cwa.googlegroups.com>


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;
Received on Fri Mar 03 2006 - 14:50:39 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US