Re: Is this query done easier?
From: Jimbo1 <jamestheboarder_at_googlemail.com>
Date: Wed, 18 Feb 2009 17:42:59 -0800 (PST)
Message-ID: <f9311fd2-b08b-4b43-8a66-8d86a0bcbb33_at_l1g2000yqj.googlegroups.com>
On Feb 18, 4:43 pm, Mtek <m..._at_mtekusa.com> wrote:
> On Feb 17, 5:31 pm, Jimbo1 <jamestheboar..._at_googlemail.com> wrote:
>
>
>
> > On Feb 17, 5:10 pm, Mtek <m..._at_mtekusa.com> wrote:
>
> > > On Feb 17, 11:09 am, Mtek <m..._at_mtekusa.com> wrote:
>
> > > > We have this query:
>
> > > > SELECT ticker, category_name, fund_name, class_rank, total_return_1yr,
> > > > expense_ratio, market_value_avg, mgr_start_date
> > > > FROM (SELECT pr.ticker, pr.category_name, mf.fund_name,
> > > > fc.class_rank, pr.total_return_1yr, fm.expense_ratio,
> > > > market_value_avg, mgr_start_date, ROW_NUMBER()
> > > > OVER (PARTITION BY category_name ORDER BY class_rank) cnt
> > > > FROM mutual_fund mf, fund_classes fc, performance_rank pr,
> > > > fund_master fm
> > > > WHERE pr.category_name = 'Foreign'
> > > > AND pr.ticker = mf.ticker
> > > > AND mf.ticker = fc.ticker
> > > > AND mf.m_fund_id = fm.fund_id)
> > > > WHERE cnt <= 4
> > > > ORDER BY class_rank, total_return_1yr DESC, expense_ratio,
> > > > mgr_start_date DESC;
>
> > > > What I need to do is add the information from the data also passed
> > > > in. something like this:
>
> > > > SELECT ticker, category_name, fund_name, class_rank, total_return_1yr,
> > > > expense_ratio, market_value_avg, mgr_start_date
> > > > FROM (SELECT pr.ticker, pr.category_name, mf.fund_name,
> > > > fc.class_rank, pr.total_return_1yr, fm.expense_ratio,
> > > > market_value_avg, mgr_start_date, ROW_NUMBER()
> > > > OVER (PARTITION BY category_name ORDER BY class_rank) cnt
> > > > FROM mutual_fund mf, fund_classes fc, performance_rank pr,
> > > > fund_master fm
> > > > WHERE pr.category_name = 'Foreign'
> > > > AND pr.ticker = mf.ticker
> > > > AND mf.ticker = fc.ticker
> > > > AND mf.m_fund_id = fm.fund_id)
> > > > WHERE cnt <= 4
> > > > UNION
> > > > SELECT 'PRMSX', category_name, fund_name, class_rank,
> > > > total_return_1yr, expense_ratio, market_value_avg, mgr_start_date
> > > > FROM (SELECT pr.ticker, pr.category_name, mf.fund_name,
> > > > fc.class_rank, pr.total_return_1yr, fm.expense_ratio,
> > > > market_value_avg, mgr_start_date, ROW_NUMBER()
> > > > OVER (PARTITION BY category_name ORDER BY class_rank) cnt
> > > > FROM mutual_fund mf, fund_classes fc, performance_rank pr,
> > > > fund_master fm
> > > > WHERE pr.category_name = 'Foreign'
> > > > AND pr.ticker = mf.ticker
> > > > AND mf.ticker = fc.ticker
> > > > AND mf.m_fund_id = fm.fund_id)
> > > > WHERE cnt <= 1
> > > > ORDER BY class_rank, total_return_1yr DESC, expense_ratio,
> > > > mgr_start_date DESC;
>
> > > > Is there an easier way to do this without the UNION? Something
> > > > shorter? It is the ORDER BY that seems to cause issues. It removes
> > > > the passed in data because of the values in the query. We want 4
> > > > records, PLUS the record passed in.....
>
> > > > Possible in a shorter query? We're still playing right as we post
> > > > this.
>
> > > > Regards,
>
> > > > Jim
>
> > > Sorry, just noticed. The category name, 'Foreign', is actually a
> > > parameter passed in: ''' || p_desc ||| '''
>
> > > Thanks!
>
> > In a nutshell, if I've read your query correctly, yes there is:
>
> > SELECT CASE
> > WHEN fn.cnt <= 1 THEN 'PRMSX'
> > ELSE fn.cntfn.ticker
> > END AS ticker,
> > fn.category_name,
> > fn.fund_name,
> > fn.class_rank,
> > fn.total_return_1yr,
> > fn.expense_ratio,
> > fn.market_value_avg,
> > mgr_start_date
> > FROM ( SELECT pr.ticker,
> > pr.category_name,
> > mf.fund_name,
> > fc.class_rank,
> > pr.total_return_1yr,
> > fm.expense_ratio,
> > market_value_avg,
> > mgr_start_date,
> > row_number() over(PARTITION BY category_name ORDER BY
> > class_rank) cnt
> > FROM mutual_fund mf,
> > fund_classes fc,
> > performance_rank pr,
> > fund_master fm
> > WHERE pr.category_name = :p_desc
> > AND pr.ticker = mf.ticker
> > AND mf.ticker = fc.ticker
> > AND mf.m_fund_id = fm.fund_id
> > ) fn
> > WHERE cnt <= 4
> > ORDER BY class_rank,
> > total_return_1yr DESC,
> > expense_ratio,
> > mgr_start_date DESC;
>
> > Just one thing though, and that is you said you were passing in the
> > p_desc description in a manner that implied you're building this query
> > dynamically via string concatenation, i.e. ''' || p_desc ||| '''
>
> > If you ARE doing that, that is extremely bad practice. If the value of
> > "p_desc" changes a lot, then Oracle will have to hard-parse the query
> > every time the p_desc value changes. If it changes often, that could
> > start to have a negative impact upon the performance of your database.
>
> > Additionally, assembling dynamically executed queries by string
> > concatenation will potentially leave you vulnerable to SQL Injection
> > attacks.
>
> > If my assumption is correct, you need to take a look at how to use
> > bind variables (such as :p_desc) in your dynamic SQL statement, and
> > then bind the actual value to the parameter at runtime. See:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2...
>
> > Also, if you're using the above query in a PL/SQL program, is there
> > any reason why it shouldn't be static?
>
> I thought that bind variables meant you were using the EXECUTE
> IMMEDIATE? This procedure ends with: OPEN v_line FOR v_query. From
> there our PHP scripts read the record sets.
>
> Can I still use bind variables there?
Date: Wed, 18 Feb 2009 17:42:59 -0800 (PST)
Message-ID: <f9311fd2-b08b-4b43-8a66-8d86a0bcbb33_at_l1g2000yqj.googlegroups.com>
On Feb 18, 4:43 pm, Mtek <m..._at_mtekusa.com> wrote:
> On Feb 17, 5:31 pm, Jimbo1 <jamestheboar..._at_googlemail.com> wrote:
>
>
>
> > On Feb 17, 5:10 pm, Mtek <m..._at_mtekusa.com> wrote:
>
> > > On Feb 17, 11:09 am, Mtek <m..._at_mtekusa.com> wrote:
>
> > > > We have this query:
>
> > > > SELECT ticker, category_name, fund_name, class_rank, total_return_1yr,
> > > > expense_ratio, market_value_avg, mgr_start_date
> > > > FROM (SELECT pr.ticker, pr.category_name, mf.fund_name,
> > > > fc.class_rank, pr.total_return_1yr, fm.expense_ratio,
> > > > market_value_avg, mgr_start_date, ROW_NUMBER()
> > > > OVER (PARTITION BY category_name ORDER BY class_rank) cnt
> > > > FROM mutual_fund mf, fund_classes fc, performance_rank pr,
> > > > fund_master fm
> > > > WHERE pr.category_name = 'Foreign'
> > > > AND pr.ticker = mf.ticker
> > > > AND mf.ticker = fc.ticker
> > > > AND mf.m_fund_id = fm.fund_id)
> > > > WHERE cnt <= 4
> > > > ORDER BY class_rank, total_return_1yr DESC, expense_ratio,
> > > > mgr_start_date DESC;
>
> > > > What I need to do is add the information from the data also passed
> > > > in. something like this:
>
> > > > SELECT ticker, category_name, fund_name, class_rank, total_return_1yr,
> > > > expense_ratio, market_value_avg, mgr_start_date
> > > > FROM (SELECT pr.ticker, pr.category_name, mf.fund_name,
> > > > fc.class_rank, pr.total_return_1yr, fm.expense_ratio,
> > > > market_value_avg, mgr_start_date, ROW_NUMBER()
> > > > OVER (PARTITION BY category_name ORDER BY class_rank) cnt
> > > > FROM mutual_fund mf, fund_classes fc, performance_rank pr,
> > > > fund_master fm
> > > > WHERE pr.category_name = 'Foreign'
> > > > AND pr.ticker = mf.ticker
> > > > AND mf.ticker = fc.ticker
> > > > AND mf.m_fund_id = fm.fund_id)
> > > > WHERE cnt <= 4
> > > > UNION
> > > > SELECT 'PRMSX', category_name, fund_name, class_rank,
> > > > total_return_1yr, expense_ratio, market_value_avg, mgr_start_date
> > > > FROM (SELECT pr.ticker, pr.category_name, mf.fund_name,
> > > > fc.class_rank, pr.total_return_1yr, fm.expense_ratio,
> > > > market_value_avg, mgr_start_date, ROW_NUMBER()
> > > > OVER (PARTITION BY category_name ORDER BY class_rank) cnt
> > > > FROM mutual_fund mf, fund_classes fc, performance_rank pr,
> > > > fund_master fm
> > > > WHERE pr.category_name = 'Foreign'
> > > > AND pr.ticker = mf.ticker
> > > > AND mf.ticker = fc.ticker
> > > > AND mf.m_fund_id = fm.fund_id)
> > > > WHERE cnt <= 1
> > > > ORDER BY class_rank, total_return_1yr DESC, expense_ratio,
> > > > mgr_start_date DESC;
>
> > > > Is there an easier way to do this without the UNION? Something
> > > > shorter? It is the ORDER BY that seems to cause issues. It removes
> > > > the passed in data because of the values in the query. We want 4
> > > > records, PLUS the record passed in.....
>
> > > > Possible in a shorter query? We're still playing right as we post
> > > > this.
>
> > > > Regards,
>
> > > > Jim
>
> > > Sorry, just noticed. The category name, 'Foreign', is actually a
> > > parameter passed in: ''' || p_desc ||| '''
>
> > > Thanks!
>
> > In a nutshell, if I've read your query correctly, yes there is:
>
> > SELECT CASE
> > WHEN fn.cnt <= 1 THEN 'PRMSX'
> > ELSE fn.cntfn.ticker
> > END AS ticker,
> > fn.category_name,
> > fn.fund_name,
> > fn.class_rank,
> > fn.total_return_1yr,
> > fn.expense_ratio,
> > fn.market_value_avg,
> > mgr_start_date
> > FROM ( SELECT pr.ticker,
> > pr.category_name,
> > mf.fund_name,
> > fc.class_rank,
> > pr.total_return_1yr,
> > fm.expense_ratio,
> > market_value_avg,
> > mgr_start_date,
> > row_number() over(PARTITION BY category_name ORDER BY
> > class_rank) cnt
> > FROM mutual_fund mf,
> > fund_classes fc,
> > performance_rank pr,
> > fund_master fm
> > WHERE pr.category_name = :p_desc
> > AND pr.ticker = mf.ticker
> > AND mf.ticker = fc.ticker
> > AND mf.m_fund_id = fm.fund_id
> > ) fn
> > WHERE cnt <= 4
> > ORDER BY class_rank,
> > total_return_1yr DESC,
> > expense_ratio,
> > mgr_start_date DESC;
>
> > Just one thing though, and that is you said you were passing in the
> > p_desc description in a manner that implied you're building this query
> > dynamically via string concatenation, i.e. ''' || p_desc ||| '''
>
> > If you ARE doing that, that is extremely bad practice. If the value of
> > "p_desc" changes a lot, then Oracle will have to hard-parse the query
> > every time the p_desc value changes. If it changes often, that could
> > start to have a negative impact upon the performance of your database.
>
> > Additionally, assembling dynamically executed queries by string
> > concatenation will potentially leave you vulnerable to SQL Injection
> > attacks.
>
> > If my assumption is correct, you need to take a look at how to use
> > bind variables (such as :p_desc) in your dynamic SQL statement, and
> > then bind the actual value to the parameter at runtime. See:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2...
>
> > Also, if you're using the above query in a PL/SQL program, is there
> > any reason why it shouldn't be static?
>
> I thought that bind variables meant you were using the EXECUTE
> IMMEDIATE? This procedure ends with: OPEN v_line FOR v_query. From
> there our PHP scripts read the record sets.
>
> Can I still use bind variables there?
Hi there,
> I thought that bind variables meant you were using the EXECUTE IMMEDIATE?
Only if you're developing in PL/SQL. If not, you should still use them. I'd not familiar with PHP, but one thing I'm certain of is that Oracle support PHP very well, and bind variables are an instrisic part of how the database works. If you don't use them, you could negatively impact performance through excessive hard-parsing of your query (Every time it's run with a different parameter, it will have to be hardparsed if that version of the query is not stored in the shared pool memory area). I am sure that Oracle will provide a variable bindingrelated API for you to use with PHP. I would totally recommend getting on this OTN forum and asking some questions about this: http://forums.oracle.com/forums/forum.jspa?forumID=178
To begin with. if you search that forum for posts related to bind variables, you will get some results back. ;)
Finally, if you're using that query in a PHP web page (I guess that you are from what you've said), and you add variable values to it via string concatenation, your PHP app will provide a possible gateway for a SQL injection attack on your Oracle database. Received on Wed Feb 18 2009 - 19:42:59 CST