SQL> select 'Test'|| '_' || extract(day from sysdate) ||
2 lpad(extract(month from sysdate), 2, 0)
3 from dual;
'TEST'||'_'||EXTRACT(DAYFROMSY
-----------------------------------------------------
Test_1502
SQL>
I want to create a table with this table name 'Test_1502'
I am using the create command as:
SQL> create table Test || '_' || (select extract(day from sysdate) ||
2 lpad(extract(month from sysdate), 2, 0) from dual) as
3 select * from Emp;
create table Test || '_' || (select extract(day from sysdate) ||
lpad(extract(month from sysdate), 2, 0) from dual) as
select * from Emp
ORA-00922: missing or invalid option
SQL>
i have created a report in dis report i created bind parameters..how to capture these parameters into a table(for this i hav created a new table) means if i pass delivery_id at runtime
i need to insert this value into what ever i hav created table next time if i pass same value i need to update that record where should i write dis code in a report and can any one send script for this.
I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.
Explanation:
In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.
I need to query on this dynamic table and return the result as return result. But i am not able to do it.
Here T_web_loylty_report_table is a type.
CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number) return T_web_loylty_report_table is v_tbl_nm varchar2(50); i_cntry_cd varchar2(20); v_sql_str varchar2(32567); [code]......
We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,
Hierarchy Table Parent Child Report1Table A Table ATable M Table ATable N Table NTable Z
Requirement :
From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this
-ParentChild 1Child 2 Child 3 -Report1Table ATable M -Report1Table ATable N Table Z
Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.
SQL Statements to create hierarchy table:
create table hierarchy (parent varchar2(20), child varchar2(20)); insert into hierarchy values ('Report1','Table A'); insert into hierarchy values ('Report1','Table B'); insert into hierarchy values ('Table A','Table M'); insert into hierarchy values ('Table B','Table N'); insert into hierarchy values ('Report2','Table P'); insert into hierarchy values ('Table M','Table X'); insert into hierarchy values ('Table N','Table Y'); insert into hierarchy values ('Report X','Table Z');
Approached already tried :
1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;
2)Using connect by path function : select * from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path from hierarchy start with parent='Report1' connect by prior child =parent) a where Leaf not in (0);
Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.
I have to create a table which contain history of a main table. like this:
if the main table is ======================== nametypelengthnot null Avarchar5Y Bvarchar5N Cvarchar5N Dvarchar5N ======================== [code]....
I've plan to so this by create a trigger in main_table. my problem is my main table have a lot of fields and I can't write a code to control it 1 by 1 like :
if old.A <> new.a insert into history("A",old.A,new.a) if old.B <> new.B insert into history("B",old.b,new.b) ......
I decided to select column name from the data dictonary using this SQL:
SELECT column_name FROM user_tab_columns WHERE table_name = '<<Table Name>>';
and then do a loop over the resultset and use the column name I've got , like this (its just an idea, may be not a write syntax):
BEGIN ..... FOR i IN 1..:result.COUNT LOOP if ld.colname[i] <> :new.colname[i] INSERT INTO history VALUES ( colname[i], ld.colname[i], :new.colname[i]); END LOOP; END;
but I can't write a "old.colname". I try with " old.'colname' ", " ld.'colname' " but it won't work.how to create a history file like I've describe.
I want to create a new table 'b' from table 'a'.I know the query will be " create table b as select * from a;" But there is some twist in my question. I do not know how many table are there in table 'a' all I know is that there is one column named "timestamp" in table 'a'.
Now I want to create table 'b' with the difference that the column "timestamp" should contain the value "sysdate" instead of the earlier values which were there in table 'a';
Note: I have to do this in single query. I can not first create table 'b' from 'a' and then update the values of column "timestamp" to sysdate.
I am trying to create a table that will increment my ID by one using the following commands:
/*Creates the log needed to increment ID*/ create sequence seq_log; CREATE TABLE MESSAGE_LOG_TABLE ( IDNUMBER(10)NOT NULLPRIMARY KEY,
[code]...
When I run the above my create sequence completes successfully but I get a ORA-00955: name is already used by an existing object error message on the create table. I have dropped all tables and sequences before running my command but I still get the same error message.
After it bombs out it appears that SQL+ want's more information for it begins to give me line numbers as if it is looking for a ";" to end the above command. I have to exit SQL+ and log back in to continue working.
This creates a table webpen with around 54107 rows. What i am want is every time run "select * from webpen" it should run the above query and give the result as per the values in main table M_PENSIONER ,M_PEN_DCRG_WITHHELD.
What i want is it should truncate the existing values and insert the value by running the above mentioned query .
Today I’m trying to make a “table” from a column. The information I’m using looks like:
AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ - K
I need my table have these columns:
Column1 Column2 Column3 AAA B CCC DDD E FFF GGG H III JJJ K
So I began to divide the string into smaller pieces:
SELECT LEVEL reg, REGEXP_SUBSTR('AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ-K','[^|]+', 1, LEVEL) ferroc FROM DUAL CONNECT BY LEVEL <= LENGTH('AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ-K') - LENGTH(REPLACE('AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ–K','|')) + 1; What I get is: >
then it gets " select * from ext_schoolof " BUT when i use procedure, it creates external table but when I try to get " select * from ext_schoolof ", then I get errors
CREATE OR REPLACE TRIGGER tri1 AFTER UPDATE ON dept FOR EACH ROW declare pragma AUTONOMOUS_TRANSACTION; BEGIN execute immediate 'create table dept_dum as select * from dept'; END; /
SQL> update dept set deptno=23 where deptno=10; update dept set deptno=23 where deptno=10 * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SCOTT.TRI1", line 6 ORA-04088: error during execution of trigger 'SCOTT.TRI1'
I am running this as scott user
My requirement is to
create table as select * from table_name where flag=1
This has to be done parallel for all the tables for which this flag is enabled and by that trigger delete all those rows which were backed up as table.
In a pl/sql procedure code, I created a normal table (create table) using dynamic sql. Then I used that table in procedure for further processing. But while compiling, it gave error that table does not exist. I can understand that he table is not present in DB, so the error came. But at the same time I need to create a table dynamically, use it and drop it. Does it mean that I need to make every query referencing that table as dynamic ??
My developer came with a requirement of creating partitions on a table which has 40 million records. His exact requirement is to create as many as partitions in such a way that 1 partition should not exceed 5k-10k records and these records should be inserted/updated on the same date (i.e. using a column as source_timestamp field). How to accomplish this?
I'm trying to create a new table which has a date field (date of birth) and I want to include a constraint which will not allow me to add values if they are over 21 years old. I'm using SQLPlus .
When we create a duplicate table, we use the below query:
create table table2 as select * from table1;
But table2 is created without any constraints inherited from table1.Can I know how can i create the table with all the constraints existing in parent table.
I wonder how I can limit the queries in a table? This limitation would be that from a certain time, it allows queries. Before the opening hours, permitting no select.
Example: Allow SELECTs in table only after 16 p.m.
Region Code,Name,Surname,ID_Number 1,John,Doe,67 1,Sarah,Jason,45 2,Bob,Marley,69 3,Steven,Johnson,900 2,Harry,Potter,890 3,Sandy,Bay,567 3,Else,Taylor,789 .....
I have about over 100 region codes and each region would like to get their own data. I can manually do the extraction and create a new table for every region, but it's going to take too long.
I'm having some trouble dealing with the PL/SQL coding. At the moment, I have a file (set of data) that is read using java. An array is created (line by line) and is passed into a temp. table, which is then called by the plsql (call PKG_xxx.PRC_xxx...) script/command for processing. Is there any way I could directly get the file loaded into plsql (into a temp. table maybe?)
The data are just numbers and letters. I already got the PL/SQL part to select the set I want for different cases. All I need is to get the file (directly or indirectly) for the procedure without going through java.
difference between the ways we create Reverse Key Index on the table.The two ways are as below:
1) CREATE INDEX <Index_Name> ON <Table_Name>(<Column_Name) REVERSE ; 2) CREATE INDEX <Index_Name> ON<Table_Name>(REVERSE(<Column_Name>)) ;
Which one is the appropraite one. Are both going to act in the same way.
I created index in both ways one by one on the table.But when I fire the select statement against the same table, The explain plan doesn't show any cost and it shows the full table scan in both ways.The select statement used is as below...I want to compare the column with string RBO(i.e('RBO%') at the start.
SELECT * FROM <Table_Name> WHERE REVERSE (<Column_Name>) LIKE '%OBR' ;
The select statement is giving me the correct result. Only the explain plan is showing Full Table Scan.can we use this reverse with IN operator also in the same way by reversing the values.
