Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL in SP
Dynamic SQL in SP [message #293584] Mon, 14 January 2008 03:26 Go to next message
710201srp1
Messages: 13
Registered: January 2008
Junior Member
Users may enter one of more parameters as input into a SELECT, so I need to build up the SQL statement dynamically in a stored procedure, e.g.

<snip>
CREATE OR REPLACE procedure sp_loanagreement(loannum in varchar2, Batch2K in varchar2, BatchID in varchar2) AS

sSql varchar2(500);
iSeq number;

Begin

sSQL := 'select * from loanagreementandgeneraldocstbl where ';
iSeq := 0;

if loannum is not null then
sSQL := ' loanagreementnumber = ' || loannum || ';';
iSeq := 1;
end if;

if Batch2K is not null then
if iSeq = 1 then
sSql := sSql || ' AND ';
end if;
sSQL := sSql || ' loanagreementnumber = ' || Batch2K || ';';
iSeq := 1;
end if;

if BatchID is not null then
if iSeq = 1 then
sSql := sSql || ' AND ';
end if;
sSQL := sSql || ' loanagreementnumber = ' || BatchID || ';';
iSeq := 1;
end if;



end;
</snip>

I don't know how to execute sSQL and pass the results (sometimes 1000's of rows) back to the calling application.

Any ideas?

Ta,

S
Re: Dynamic SQL in SP [message #293590 is a reply to message #293584] Mon, 14 January 2008 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.
2/ Always post your Oracle version (4 decimals).
3/ Remove all ';'
4/ It depends on what you need to do, see "execute immediate" and "for loop cursor"
5/ REPEAT one hundred times: "this procedure will lead to bad performances and security problems because of SQL injection"

Oracle is NOT Sybase or SQL Server, you don't program it the same way.

Regards
Michel

[Updated on: Mon, 14 January 2008 03:38]

Report message to a moderator

Re: Dynamic SQL in SP [message #293594 is a reply to message #293590] Mon, 14 January 2008 03:47 Go to previous messageGo to next message
710201srp1
Messages: 13
Registered: January 2008
Junior Member
So if using a for loop cursor will perform badly and have security implications, what would be the best way to do it?

S
Re: Dynamic SQL in SP [message #293603 is a reply to message #293594] Mon, 14 January 2008 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A single query.

Regards
Michel
Re: Dynamic SQL in SP [message #293793 is a reply to message #293594] Tue, 15 January 2008 01:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
710201srp1 wrote on Mon, 14 January 2008 10:47
So if using a for loop cursor will perform badly and have security implications, what would be the best way to do it?

S

The security issues are unrelated to the fact if you use a loop or a single statement. Google for sql injection to see where the security issues are.
Re: Dynamic SQL in SP [message #293799 is a reply to message #293584] Tue, 15 January 2008 02:19 Go to previous messageGo to next message
710201srp1
Messages: 13
Registered: January 2008
Junior Member
I know what SQL injection is.

All I need to know is how to execute the SQL and return the results.

Suggestions like use 'a single query' are a waste of your time and mine.

S
Re: Dynamic SQL in SP [message #293807 is a reply to message #293799] Tue, 15 January 2008 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Suggestions like use 'a single query' are a waste of your time and mine.

Why?

Regards
Michel
Re: Dynamic SQL in SP [message #293810 is a reply to message #293807] Tue, 15 January 2008 02:37 Go to previous messageGo to next message
710201srp1
Messages: 13
Registered: January 2008
Junior Member
Because I can't do it in a single query, like I explained in the original post
Re: Dynamic SQL in SP [message #293812 is a reply to message #293810] Tue, 15 January 2008 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Because I can't do it in a single query

Why?

Regards
Michel
Re: Dynamic SQL in SP [message #293814 is a reply to message #293812] Tue, 15 January 2008 02:53 Go to previous messageGo to next message
710201srp1
Messages: 13
Registered: January 2008
Junior Member
Why don't you explain how to do it in a single query then
Re: Dynamic SQL in SP [message #293817 is a reply to message #293814] Tue, 15 January 2008 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As far I can see (it is hard because it is not formatted), you have only few cases so write few static queries.

Regards
Michel
Re: Dynamic SQL in SP [message #293821 is a reply to message #293817] Tue, 15 January 2008 03:16 Go to previous messageGo to next message
710201srp1
Messages: 13
Registered: January 2008
Junior Member
There can be 1 to 4 input parameters and any combination of them.

1) How would you handle this in the code
2) HOW WOULD YOU EXECUTE THE SQL
3) HOW WOULD YOU RETURN THE RESULTS
Re: Dynamic SQL in SP [message #293828 is a reply to message #293821] Tue, 15 January 2008 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use UPPER CASE.
Be patient as I am with you.
All what you asked is answered in the documentation and I could simply say ./fa/1622/0/.

Here's an example:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2251

Regards
Michel

Re: Dynamic SQL in SP [message #293850 is a reply to message #293799] Tue, 15 January 2008 04:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
710201srp1 wrote on Tue, 15 January 2008 09:19
I know what SQL injection is.

Then why don't you code in a way that doesn't allow for sql-injection?
Always use bind-variables or use static pl/sql queries.

Your query can be rewritten somewhat like this:
select *
from   your_table
where  col1 = nvl(entered_param, col1)
and    col2 = nvl(entered_param2, col2)
etc


That is the easy way. If this doesn't give you any trouble in performance, you're good.
Re: Dynamic SQL in SP [message #293905 is a reply to message #293850] Tue, 15 January 2008 10:16 Go to previous messageGo to next message
710201srp1
Messages: 13
Registered: January 2008
Junior Member
Hi,

Due to the nature of the table, then SQL would have to be something like this

select distinct(l1.id) from mytable l1, mytable l2, mytable l3, mytable l4
where
l1.defid=25806 and l1.defid=l2.defid and l1.defid=l3.defid and l1.defid=l4.defid
and l1.id=l2.id and l1.id=l3.id and l1.id=l4.id
and l1.attrid=2 and l1.valstr= nvl('',l1.valstr)
and l2.attrid=3 and l2.valstr= nvl('',l2.valstr)
and l3.attrid=4 and l3.valstr= nvl('',l3.valstr)
and l4.attrid=5 and l4.valdate= nvl('',l4.valstr)

But it doesn't seem to work with more than two references to the same table.

Any ideas?
Re: Dynamic SQL in SP [message #293906 is a reply to message #293905] Tue, 15 January 2008 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
nvl('',l1.valstr)

What is this silly thing?
Just use "l1.valstr" it is logically equivalent and will be faster.
And "l4.valdate= nvl('',l4.valstr)" seems to be an error or all other conditions are.

You clearly don't understand what you write.

Regards
Michel

Re: Dynamic SQL in SP [message #293909 is a reply to message #293906] Tue, 15 January 2008 10:27 Go to previous messageGo to next message
710201srp1
Messages: 13
Registered: January 2008
Junior Member
select distinct(l1.id) from mytable l1, mytable l2, mytable l3, mytable l4
where
l1.defid=25806 and l1.defid=l2.defid and l1.defid=l3.defid and l1.defid=l4.defid
and l1.id=l2.id and l1.id=l3.id and l1.id=l4.id
and l1.attrid=2 and l1.valstr= nvl(PARM1,l1.valstr)
and l2.attrid=3 and l2.valstr= nvl(PARM2,l2.valstr)
and l3.attrid=4 and l3.valstr= nvl(PARM3,l3.valstr)
and l4.attrid=5 and l4.valdate= nvl(PARM4,l4.valdate)

That better for you?
Re: Dynamic SQL in SP [message #293912 is a reply to message #293909] Tue, 15 January 2008 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For me? Not really. Above all it depends on the rest. But if it is good for you...

Regards
Michel
Re: Dynamic SQL in SP [message #293913 is a reply to message #293912] Tue, 15 January 2008 10:36 Go to previous messageGo to next message
710201srp1
Messages: 13
Registered: January 2008
Junior Member
Michel,

I suggest you stop posting to this thread, you are clearly just wasting yours and my time

Re: Dynamic SQL in SP [message #293915 is a reply to message #293913] Tue, 15 January 2008 10:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Your design s*cks big time.
This is what you get from designing 'generic' tables in which you can store all kinds of attributes..

But this totally collides with your first post... In there you don't address the attrid

[Updated on: Tue, 15 January 2008 10:41]

Report message to a moderator

Re: Dynamic SQL in SP [message #293916 is a reply to message #293915] Tue, 15 January 2008 10:41 Go to previous messageGo to next message
710201srp1
Messages: 13
Registered: January 2008
Junior Member
I think the millions of users of this product would disagree.

f*ckwit
Re: Dynamic SQL in SP [message #293917 is a reply to message #293916] Tue, 15 January 2008 10:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
710201srp1 wrote on Tue, 15 January 2008 17:41
I think the millions of users of this product would disagree.

f*ckwit

hm..
Apparently it's a db-design you didn't create yourself, yet you somehow feel very offended when somebody criticizes.
That's what I call empathy..
Re: Dynamic SQL in SP [message #293918 is a reply to message #293584] Tue, 15 January 2008 10:46 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi 710201srp1,

Please read and follow OraFAQ Forum Guide.
If you are not find any information in this forum then for the good sake don't post it and also don't scold or degrade any one,
because most of the time these members are posting valuable information.

Re: Dynamic SQL in SP [message #293919 is a reply to message #293917] Tue, 15 January 2008 10:49 Go to previous messageGo to next message
710201srp1
Messages: 13
Registered: January 2008
Junior Member
How can someone criticise the design of a table when they nothing about the product or the schema in general??

Seems incredulous to me
Re: Dynamic SQL in SP [message #293921 is a reply to message #293918] Tue, 15 January 2008 10:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
@ramanajv1968

However good your intentions, my view is that we should not discourage people to post. I do agree that the original poster would probably get more help if he would ease up, but he has just as much right to post as anybody else.
There is only a single person that can judge if a post is worth the time spent on it and it is always the poster him/herself.
Re: Dynamic SQL in SP [message #293922 is a reply to message #293919] Tue, 15 January 2008 10:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
710201srp1 wrote on Tue, 15 January 2008 17:49
How can someone criticise the design of a table when they nothing about the product or the schema in general??

Seems incredulous to me

Your example tells me that it stores multiple attributes in a single column. That is a bad design. I didn't say it never occurs, I said it's bad design.
Re: Dynamic SQL in SP [message #293923 is a reply to message #293922] Tue, 15 January 2008 10:58 Go to previous messageGo to next message
710201srp1
Messages: 13
Registered: January 2008
Junior Member
The table has multiple columns, each of which store a different datatype e.g.

valstr
valint
valdate
valreal
vallong

All the above are individual columns.

So one 'record' has multiple rows, depending on what has been defined and hence why I need to join multiple references to the same table e.g. l1. l2. l3. etc
Re: Dynamic SQL in SP [message #293924 is a reply to message #293923] Tue, 15 January 2008 11:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ok, so a functional 'record' would consist of multiple fysical records. Do I understand that correctly?
Will a single functional record always contain the same amount of fysical records then? (in other words, would it be possible to create a view?)
If not, what does the # of records depend on?
Before you start shouting again about this all being obvious: I don't know how good your (PL/)SQL skills are.

[Updated on: Tue, 15 January 2008 11:01]

Report message to a moderator

Re: Dynamic SQL in SP [message #293926 is a reply to message #293924] Tue, 15 January 2008 11:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Also, how do you handle the different kinds of datatype? I only see some varchar2 parameters in your procedure (in the original first post)


Anyway, you probably won't be able to solve this using static sql..
When you decide on creating your statement dynamically, you can execute it using "execute immediate".
Use that in conjunction with INTO and USING.

[Updated on: Tue, 15 January 2008 11:05]

Report message to a moderator

Re: Dynamic SQL in SP [message #293927 is a reply to message #293924] Tue, 15 January 2008 11:09 Go to previous messageGo to next message
710201srp1
Messages: 13
Registered: January 2008
Junior Member
Correct.

Yes, a functional record will always contain the same number of physical records.

How would creating a view help with the null values?

I know that attrid=2 is a varchar and that attrid=5 is a date etc
Re: Dynamic SQL in SP [message #293936 is a reply to message #293927] Tue, 15 January 2008 13:25 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I thought of creating a view to represent the actual functional record, but from your replies I take it that there is no single representation of a single record (based on the "depending what has been defined"), so that probably won't help you.
Previous Topic: Error
Next Topic: umlaut when converting blob to varchar
Goto Forum:
  


Current Time: Sat Dec 10 05:27:47 CST 2016

Total time taken to generate the page: 0.06182 seconds