0

I am attempting to use a loop in my Oracle SQL code that will change the name of the schema slightly each time the loop is run. I have tried the SQL code below, creating a NUMBER variable that counts up from 1 to 3 and then concatenating it with the schema name, but I keep receiving a long error message. The schema name should be 'x_rm_mt1_d' the first loop, 'x_rm_mt2_d' the second loop, and 'x_rm_mt3_d' on the final loop. The select statement in the code below is just a small example of what I am trying to do, I didn't think it would be pertinent to include the entire query that I am looking to use the variable for.

    DECLARE
      l_counter NUMBER := 0;
    BEGIN
      LOOP
        l_counter := l_counter + 1;
        IF l_counter > 3 THEN
          EXIT;
        END IF;
      DEFINE StrSchema = 'x_rm_mt' || l_counter || '_d'


    WITH aed AS (



SELECT DISTINCT SSN , STATE FROM (
--Effective Date During Quarter
    SELECT *
    FROM
    (SELECT * FROM ADDRESS_EFF_DATE 
    WHERE STATE = 'VT'
    AND EFF_DATE >= '01-JAN-20'
    AND EFF_DATE < '01-APR-20'
    AND ADDRESS_KEY = '0'
    
    )aed
    WHERE aed.EFF_DATE = (
            SELECT MAX(aed2.EFF_DATE)
                FROM ADDRESS_EFF_DATE aed2
            WHERE aed2.CONTROL_ID = aed.CONTROL_ID
            AND aed2.SSN = aed.SSN)

--Effective Date Prior to Quarter
    UNION

    SELECT *
    FROM
    (SELECT *
    FROM Strschema.ADDRESS_EFF_DATE
    WHERE STATE = 'VT'
    AND EFF_DATE < '01-JAN-20'
    AND ADDRESS_KEY = '0') aed
    
    WHERE aed.EFF_DATE = (
        SELECT MAX(aed1.EFF_DATE) 
        FROM Strschema.ADDRESS_EFF_DATE aed1 
        WHERE aed.ssn=aed1.ssn 
        AND aed.EFF_DATE < '01-JAN-20' 
        AND aed1.EFF_DATE < '01-JAN-20')) )        

,

--Select all records from Employee Eff Date Where Latest Hire Date is before 4/1/20
--AND Term Date is Null or Term Date is Greater than 1/1/20 eed AS (
       SELECT *
    FROM
    (SELECT *
        FROM Strschema.EMPLOYEE_EFF_DATE eed
        WHERE eed.CONTROL_ID = 'SMLMKT'
        AND eed.EFF_DATE = (
            SELECT MAX(eed1.EFF_DATE) 
            FROM Strschema.EMPLOYEE_EFF_DATE eed1
            WHERE eed.SSN = eed1.SSN 
            AND eed1.CONTROL_ID = 'SMLMKT') 
     ) eed3      
    WHERE eed3.LATEST_HIRE_DATE <= '04-APR-20'
    AND (eed3.LAST_TERM_DATE > '01-JAN-20'
    OR eed3.LAST_TERM_DATE is NULL) ) ,
     ebe AS (   
    SELECT *
    FROM Strschema.emp_ben_elects ebe
    WHERE ebe.control_id = 'SMLMKT'
    AND ebe.BENEFIT_ID = 'MEDICAL' 
    
    AND ebe.life_event_date = (
        SELECT MAX(x.life_event_date) 
        FROM Strschema.employee_life_events x
        WHERE x.ssn = ebe.ssn
        AND x.control_id = 'SMLMKT' 
        AND ebe.p_company_id_i = x.p_company_id_i 
        AND x.life_event_status = 'C')
    
    AND ebe.le_seq_no = (
        SELECT MAX(x.le_seq_no) 
        FROM Strschema.employee_life_events x 
        WHERE x.ssn = ebe.ssn
        AND x.control_id = 'SMLMKT' 
        AND ebe.p_company_id_i = x.p_company_id_i 
        AND x.life_event_status = 'C'
        AND ebe.life_event_date = x.life_event_date)
    )
    
    

SELECT  cmp.NAME as "Client Name" , cssn.REAL_SSN as "Employee SSN"
--, aed.SSN as "FAKE SSN REMOVE" , eed.LAST_NAME as "Last Name" , eed.FIRST_NAME as "First Name" , aed.STATE as "Resident State" , eed.LATEST_HIRE_DATE as "Hire Date" , pi.DESCR1 as "Plan Name" , ebe.OPTION_ID as "Tier" , ebe.BENEFIT_EFF_DATE as "Coverage Start Date" , eed.LAST_TERM_DATE as "Coverage End Date"
--, eed.LAST_TERM_DATE

FROM eed  INNER JOIN aed ON eed.SSN = aed.SSN

LEFT JOIN ebe ON eed.SSN = ebe.SSN

JOIN Strschema.COMP_SSN cssn ON eed.SSN = cssn.SSN

LEFT JOIN Strschema.PLAN_INFO pi ON ebe.PLAN_ID = pi.PLAN_ID AND ebe.BENEFIT_ID = pi.BENEFIT_ID 

JOIN Strschema.COMPANY cmp ON eed.CURRENT_CO = cmp.COMPANY_ID      

      END LOOP;
          END;
3
  • define is a client (SQL Developer) command, not part of PL/SQL, and you'll need to use dynamic SQL if the schema isn't known until runtime. But what will you do with the selected data? You need to select into something (a collection if you're getting multiple rows) or use a cursor. That will affect how the dynamic SQL is written and executed. If you actually have three fixed schema names, which just happen to have a number in, why not use static SQL and union the three queries together - just because your realy query is too long to repeat? Commented Oct 5, 2020 at 22:16
  • Thank you for your response. I am new to using loops in SQL and have more of a background in VBA. Coming from a VBA base I am used to using loops as much as possible. I could just use three union queries but I am using this as a learning moment for a situation down the road where it would be more efficient to use a loop. I'm actually not familiar with what is meant by dynamic SQL. Could you maybe give an example of what you mean? Commented Oct 6, 2020 at 5:21
  • Also @alex, I just updated the query to explain better what I am trying to accomplish. Commented Oct 6, 2020 at 5:31

1 Answer 1

2

DECLARE the variable and then set its value in the loop:

DECLARE
  TYPE employee_array IS TABLE OF EMPLOYEE%ROWTYPE;
     -- Assuming the tables all have the same type.

  l_StrSchema VARCHAR2(30);
  t_employees employee_array;
BEGIN
  FOR l_counter IN 1 .. 3 LOOP
    l_StrSchema := 'x_rm_mt' || l_counter || '_d';
  
    DBMS_OUTPUT.PUT_LINE( 'Loop ' || l_counter );
    
    EXECUTE IMMEDIATE 'SELECT * FROM ' || l_Strschema || '.EMPLOYEE'
                      || ' WHERE ROWNUM < 1000'
      BULK COLLECT INTO t_employees;
    
    FOR i IN 1 .. t_employees.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE( t_employees(i).name );
    END LOOP;
  END LOOP;
END;
/

You also need to use dynamic SQL if you are dynamically setting the table's schema and will need to collect the rows into a data structure as you are working in PL/SQL (and not in the SQL scope). You can also use a FOR loop.

db<>fiddle

Sign up to request clarification or add additional context in comments.

5 Comments

Thank you for your response, I have updated the SQL code above to better show what I am trying to accomplish. I am not sure what is meant by dynamic SQL or by PL/SQL vs the SQL scope. Any further clarification/explanation would be extremely helpful or a link to wher eI can find some good info on the subject. Thank you!
@Chuck0185 PL/SQL is Oracle's procedural language and it is different from SQL. A DECLARE ... BEGIN ... END; block is PL/SQL and a SELECT statement is SQL and they will be executed by different engines within the database and if you go from processing a PL/SQL block to a nested SQL statement then the database has to switch engines that it is using to process the statement and pass data to and from those engine (and this context switch is a relatively expensive operation to perform). PL/SQL statements work on processing variables whereas SELECT statements will return results sets. ....
... To handle a result set returned from an SQL statement in the PL/SQL scope you need to put the result set into a variable. This could be a cursor or it could be a collection (similar to an array) or, if the result set returns a single row, the column values could be assigned directly into variables but PL/SQL will raise an error if you are using a SELECT SQL statement and are not putting it into a variable.
Dynamic SQL is where you build the SQL query up as a string and then use EXECUTE IMMEDIATE to execute that string as an SQL statement. There are certain things that you cannot do in SQL, such as providing schema or table names from a variable and this is where you require dynamic SQL as you build the query as a string and concatenate the variable into the string (as I did in the line starting EXECUTE IMMEDIATE).
Thank you very much, I will be reading more into this but it sounds like I have a ways to go to master PL/SQL!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.