PL/SQL Not Required
Naveed Islam, August 25, 2017 - 10:16 am UTC
Hi,
Thank you for response along with a complete example.
Actually we need to handle dynamic table name in select statement only not in PL/SQL Script.
Please support.
August 25, 2017 - 10:36 am UTC
The principle's the same. Whatever your calling environment, pass a parameter with the table suffix. Sanitize the input and create your SQL string. Then execute it.
Test Extract SQL
Naveed Islam, August 25, 2017 - 10:41 am UTC
Hi, please see if the following will work sameway?
V_SQL VARCHAR2(1000);
V_SQL ='SELECT TICKET_ID
,SOURCE_ID
,SUB_STR_INFO3
FROM BILL.DDI_TICKET_10_10'||TO_CHAR(SYSDATE-1,'YYMM')
||' WHERE trunc(DEALED_DATE) <='|| TRUNC(SYSDATE-1);
EXECUTE IMMEDIATE V_SQL;
August 29, 2017 - 10:04 am UTC
Same way as what?
That will generate SQL appending the year and month in YYMM format to the table name. Then execute it.
WHERE trunc(DEALED_DATE) <='|| TRUNC(SYSDATE-1);
You don't need to concatenate the date! In fact, this is a bad idea as each day this will become a new query. So the database has to do more parsing.
Put the call to trunc(sysdate) inside your SQL string:
' WHERE trunc(DEALED_DATE) <= TRUNC(SYSDATE-1)';
Test Extract SQL
Naveed Islam, August 29, 2017 - 10:17 am UTC
Hi,
Actually the problem is with the table name. The table name changes every month and i don't want someone change it manually with the change of each month.
So i want dynamic select to extract data where name of the table needs to be dynamically changed.
August 29, 2017 - 10:30 am UTC
So you want a query that append the current date in yymm format to the end of the table name?
If so, your statement looks like what you need.
Test Extract SQL
Naveed Islam, August 29, 2017 - 11:24 am UTC
Exactly, we only need to change the table name each month by using dynamic SQL. Please guide.
Thanks.
Test Extract SQL
Naveed Islam, August 29, 2017 - 11:25 am UTC
But this would not be a PLSQL script rather we need to handle this in select statement of extraction script.
Please support.
August 29, 2017 - 4:14 pm UTC
What precisely is this "extraction script"? What language are you using to write it?
So what is this 'Extraction script'
Paul, August 29, 2017 - 1:03 pm UTC
And why can it not call a PL/SQL block?
What is it extracting To?
August 29, 2017 - 4:16 pm UTC
Indeed, we can't really help without more background...
Test Extract SQL
Naveed Islam, August 30, 2017 - 6:14 am UTC
Hello, We are extracting data from oracle and writing to text file using following select statement:
The table name postfix 1708 is YYMM that changes every month.
We need to get this postfix dynamically in the same select/extract script.
SELECT TICKET_ID
,SOURCE_ID
,SERV_ID
,to_char(COLLECTED_DATE,'YYYY-MM-DD HH24:MI:SS')
,to_char(DEALED_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM BILL.DDI_TICKET_10_101708 --- Need to handle the table name dynamically, Next month 1708 will become 1709.
WHERE trunc(DEALED_DATE) =trunc(SYSDATE-1)
August 30, 2017 - 10:48 am UTC
"We are extracting data from oracle"
HOW? What technology are you using to do this? Which programming language is the script written in?
Dynamic From Claue
Atul, September 24, 2021 - 2:46 pm UTC
I have same issue.
select e.emp_name, d.dept_name
from emp_jan_2021 e -- emp_feb_2021 or emp_mar_2021
, dept d
where e.dept_no = d.dept_no;
This is required in SQL alone. This SQL is to be used in Discoverer Report. So no PLSQL.
The table in the real example are from Oracle APPS. Table names like XLA_GLT_%.
September 24, 2021 - 3:33 pm UTC
This SQL is to be used in Discoverer Report. So no PLSQL.
I'm not familiar with the workings of Discoverer, so I'm not sure exactly how you'd do this.
I'm sure you can call PL/SQL functions in it though. So you could probably create a (pipelined) table function that accepts the table name as a parameter, constructs the SQL statement, and returns the result.
I have similar requirement to query from Tableau
Meghna, January 25, 2023 - 3:59 pm UTC
We have 3 region tables and based on the region selected need to dynamically change the source tables in SQL.
Something like below query where the output of the subquery to set the table name would have helped.
select * from (
select case 'AMER'
when 'AMER' then 'Table1'
when 'EMEAR' then 'Table2'
when 'APJC' then 'Table3'
else
'Tab1'
end case from dual);
January 26, 2023 - 1:27 pm UTC
I'm unsure what you're trying to do here - please clarify with a complete example
Sachin Singh, July 31, 2023 - 8:28 pm UTC
I don't know what is so difficult to understand here guys. It is a simple problem and I am also looking for a similar solution. If i find something, I will post.
August 04, 2023 - 5:44 am UTC
OK, if I'm following what you are asking, then using the previous example
select * from (
select case 'AMER'
when 'AMER' then 'Table1'
when 'EMEAR' then 'Table2'
when 'APJC' then 'Table3'
else
'Tab1'
end case from dual);
would be written as
select * from table1 where :param = 'AMER'
union all
select * from table2 where :param = 'EMEAR'
union all
select * from table3 where :param = 'APJC'