Home » SQL & PL/SQL » SQL & PL/SQL » Cursor
Cursor [message #294573] Fri, 18 January 2008 00:59 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

I have a stored proc where a select statement is inserted into a variable sSql, the from clause in another variable sfrom and the where clause in another variable swhere.

This is then concatenated as this sSql = sSql | sfrom | swhere and hence we get the entire select statement in sSql.

This statement is run at the end of the stored proc as

open p_cursor for sSql
return (p_cursor)

Now, I wish to add a query to select the number of records from this select statement i.e. I want to do this

select count(1) | sfrom | swhere and store this in a variable.

Now how do I execute this query without using a cursor?

Please help me out.

Thanks
Re: Cursor [message #294577 is a reply to message #294573] Fri, 18 January 2008 01:06 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I hope this program is for testing pupose . Any way you can do using Execute Immedate if the total sql query is fragmented .
Just ggogle and try .

Thumbs Up
Rajuvan.
Re: Cursor [message #294578 is a reply to message #294577] Fri, 18 January 2008 01:10 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
So, I can use execute immediate ('select count (1) ' | sfrom | shwere 'into myvar')

myvar is the name of the variable in which I want to store the count.

will this statement work?
Re: Cursor [message #294579 is a reply to message #294573] Fri, 18 January 2008 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know why you need that but never do it in a real program.
Search for "SQL injection" to know why.
Also this can lead to bad performances, non scalable application, overloaded cpu and so on.

Regards
Michel
Re: Cursor [message #294584 is a reply to message #294579] Fri, 18 January 2008 01:22 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi Michel,

I will explain the situation,

I have a search UI page which calls a stored proc for searching through some tables.

The stored proc query that selects the row set and sends it back looks like this.

sCountSQL :='SELECT COUNT(1) as NO_OF_RECORDS';
sSQL := 'select * from (select temp_count.NO_OF_RECORDS,temp.* from ( ';
sSQL := sSQL ||'SELECT tba.cold, tba.cole, tbc.colk, tbc.colh';
sFromSQL := ' FROM tba, tbc';
sWhere := 'WHERE tba.cola = tbc.colc';

After the above and a series of if conditions to modify the sql
the query below happens
sSQL := sSQL ||' )temp,('|| sCountSQL || ' '|| sFromSQL || ' ' || sWhere||')temp_count);

So, u can see that all the rows and the count are moved into temp and temp_count temporary tables.

When the user wishes to see all the records at one time, the temp segment overflows and the db goes down.

I guess this can be removed by not using a temp table. So, what I am trying to do now is separating the count of the number of records and the records and not using any temporary tables.

Could you advise me on this? I am new to PL/SQL.

This code was written by a DBA who has left my organisation.

Thanks so much
Re: Cursor [message #294586 is a reply to message #294584] Fri, 18 January 2008 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So, u can see that all the rows and the count are moved into temp and temp_count temporary tables.

I can't see anything as the code is not formatted.
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.

There is no variable part in your query why don't you statically write it?

Regards
Michel

Re: Cursor [message #294590 is a reply to message #294573] Fri, 18 January 2008 01:35 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
sCountSQL :='SELECT COUNT(1) as NO_OF_RECORDS';

sSQL := 'select * from (select temp_count.NO_OF_RECORDS,temp.* from ( ';

sSQL := sSQL ||'SELECT tba.cold, tba.cole, tbc.colk, tbc.colh';
sFromSQL := ' FROM tba, tbc';

sWhere := 'WHERE tba.cola = tbc.colc';


After the above, a series of if conditions to modify the sql
the query below happens

sSQL := sSQL ||' )temp,('|| sCountSQL || ' '|| sFromSQL || ' ' || sWhere||')temp_count);


So, u can see that all the rows and the count are moved into temp and temp_count temporary tables.
Re: Cursor [message #294593 is a reply to message #294590] Fri, 18 January 2008 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All is static, use static query not dynamic one.
In the end, what is the query?

And what is its purpose?
And what is the business need?

Also they are other comments on the query and procedure, if you'll post it sometime.

Regards
Michel

[Updated on: Fri, 18 January 2008 01:42]

Report message to a moderator

Re: Cursor [message #294595 is a reply to message #294593] Fri, 18 January 2008 01:41 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
I cannot use a static query as there are several conditions which I have not specified according to which the query gets modified.

This is the final query.

sSQL := sSQL ||' )temp,('|| sCountSQL || ' '|| sFromSQL || ' ' || sWhere||')temp_count);


Re: Cursor [message #294614 is a reply to message #294573] Fri, 18 January 2008 03:36 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Anyway ,

Now sSQL becomes (As per your Posting)

Quote:
select * from (select temp_count.NO_OF_RECORDS,temp.* from ( )temp,(SELECT COUNT(1) as NO_OF_RECORDS FROM tba, tbc WHERE tba.cola = tbc.colc)temp_count)


How will Oracle understand this ?

Thumbs Up
Rajuvan



Re: Cursor [message #294619 is a reply to message #294595] Fri, 18 January 2008 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I cannot use a static query as there are several conditions which I have not specified according to which the query gets modified.

If you don't post the actual code we can't help.

Quote:
This is the final query.

If I put this in SQL*Plus I bet I will have a syntax error.
This is not a query.

You still don't answer my questions.
I give up, good luck.

@rajavu1, this is why I asked for the final query. Wink

Regards
Michel
Re: Cursor [message #294626 is a reply to message #294619] Fri, 18 January 2008 04:10 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Resolved.

Thank you for your help
Re: Cursor [message #294628 is a reply to message #294573] Fri, 18 January 2008 04:14 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Fine . Congratutlations Smile

But it is widely accepted practiced to post the answer/query if somebody resolved their issue Smile


Thumbs Up
Rajuvan
Previous Topic: create a file that contains the output
Next Topic: Need Design Idea to Store 1 - 1 - n relationship
Goto Forum:
  


Current Time: Sat Dec 10 20:53:00 CST 2016

Total time taken to generate the page: 0.08092 seconds