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: Jim Kennedy <jim>
Date: Fri, 3 Mar 2006 16:59:08 -0800
Message-ID: <dZadnWIK1dhFeJXZnZ2dneKdnZydnZ2d@comcast.com>

"centurionoftheseed" <rick.memmer_at_gmail.com> wrote in message news:1141419039.818899.107630_at_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;
>

You don't need to do this in dynamic sql. Just use regular sql. in pl/sql. Then you don't have the problem to begin with. Jim Received on Fri Mar 03 2006 - 18:59:08 CST

Original text of this message

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