Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: dynamic sql - output actual string that includes all values from bind variables...
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,da.COMPANYID(+)
: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 =
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
COMMIT;Received on Fri Mar 03 2006 - 14:50:39 CST