| Dynamic SQL and Loop question [message #218736] |
Fri, 09 February 2007 12:54  |
pallen
Messages: 5
Registered: February 2007
|
Junior Member |
|
|
I am trying to use the loop below, but I want to use a variable declared earlier V_USER instead of hard-coding the schema, pallen, in the FOR statement at the top. Any ideas?
BEGIN
FOR V_REC IN (SELECT lid, cnt FROM pallen.REG_CUST_BY_LID_TEMP)
LOOP
EXECUTE IMMEDIATE
'INSERT INTO '||V_USER||'.RS_COUNTS_RANDOMS_TRIM
SELECT * FROM
(SELECT a.*
FROM '||V_USER||'.RS_COUNTS_RANDOMS a
WHERE a.lid = '||V_REC.lid||'
ORDER BY dbms_random.random)
WHERE rownum <= '||V_REC.cnt;
COMMIT;
END LOOP;
END;
|
|
|
|
| Re: Dynamic SQL and Loop question [message #218753 is a reply to message #218736] |
Fri, 09 February 2007 15:55  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You could possibly look at the 'alter session set current_schema' command and set the schema to v_user (using an execute immediate dynamic SQL call).
That would let you avoid the NDS within the loop itself since you could then use static SQL with no owner prefix.
Alternatively, you would need to use a dynamic cursor instead of a cursor FOR loop;
type rc is ref cursor;
v_rc rc;
...
open v_rc for 'select ... from ' || v_user || '.reg_cust_by_lid_temp';
loop
fetch v_rc into ...;
exit when v_rc%notfound;
execute immediate 'insert ...';
end loop;
close v_rc; |
|
|
|
|
|
| Re: Dynamic SQL and Loop question [message #219062 is a reply to message #218736] |
Mon, 12 February 2007 15:44 |
pallen
Messages: 5
Registered: February 2007
|
Junior Member |
|
|
Thanks again Todd, the Dynamic Cursor worked beautifully - its running properly now from any schema. I suspected that was the route I needed to go, but I havent used them before and was unsure of how to go about it.
It seemed to ignore the alter session parameter when I put it into the procedure, but I think the Dynamic Cursor is a better way to go anyway.
Thanks for the Help!
|
|
|
|
|
|
| Re: Dynamic SQL and Loop question [message #219069 is a reply to message #218736] |
Mon, 12 February 2007 16:32 |
pallen
Messages: 5
Registered: February 2007
|
Junior Member |
|
|
Perhaps, but I am new to PL\SQL and Binding is on my to-do list of things to learn.
What I am doing here, is taking a table of customers and counting how many go with each location and storing the results in the table reg_cust_by_lid_temp. I then want to pull an equal number of random records per location from larger table of households. The loop reads the count for each location and then adds the same number of random rows to the table.
So to answer the question, "how many rows are returned?", it would depend on the input table - how many locations are represented in that table, and how many customers are associated with each location.
|
|
|
|
| Re: Dynamic SQL and Loop question [message #219171 is a reply to message #218736] |
Tue, 13 February 2007 08:27 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Ok, this probably isn't very helpful, but I'm not fully understanding what you are trying to do yet with your algorithm / goal.
But, my gut is telling me there is a better way to do it..whether that be via a single query, rather than a temp table, or via using some of oracle's built in statistical functions. I'm not a true stats guy, but I'd suggest scanning through the list of sql functions in the sql reference for your version and seeing if anything pops out as applicable. There is also a sample clause in the from statement that may or may not help you that you should investigate.
And lastly, in oracle you often don't need temporary tables, or even cursor loops, because you can simply do it as a single query with inline views (a subquery in the from clause), examples of which are everywhere, but the basic idea is:
select a.location
from
(
select location, count(*) loc_count
from customers
group by location
) a
where a.loc_count > 5;
|
|
|
|
| Re: Dynamic SQL and Loop question [message #219179 is a reply to message #218736] |
Tue, 13 February 2007 09:09 |
pallen
Messages: 5
Registered: February 2007
|
Junior Member |
|
|
I see where you are going. I considered using partition or another analytic funtion, but I couldnt find a way to do that. It's complicated because I am getting the numbers to pull from one table and then going to another much larger table to pull an equal number of randoms. Also, it has to work for any input table I give it.
I agree it could possibly be done in one elegant query, but it was easier for me to get my head around it with a procedural structure.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
