Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL and Loop question
Dynamic SQL and Loop question [message #218736] Fri, 09 February 2007 12:54 Go to next message
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 Go to previous messageGo to next message
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 #218766 is a reply to message #218736] Fri, 09 February 2007 22:47 Go to previous messageGo to next message
pallen
Messages: 5
Registered: February 2007
Junior Member
Thanks for the suggestion. I'll give that a try. I didnt even think of the alter session - I can use that in some of my other procedures.

Thanks!
Re: Dynamic SQL and Loop question [message #219062 is a reply to message #218736] Mon, 12 February 2007 15:44 Go to previous messageGo to next message
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 #219065 is a reply to message #218736] Mon, 12 February 2007 16:12 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I'm curious, and may be missing something, but wouldn't you want to bind here as well? How many records are returned by that outer query?
Re: Dynamic SQL and Loop question [message #219069 is a reply to message #218736] Mon, 12 February 2007 16:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Help needed in building sql query
Next Topic: Dynamic query with different where parameters
Goto Forum:
  


Current Time: Fri Dec 09 01:54:41 CST 2016

Total time taken to generate the page: 0.09092 seconds