Automatically Create Many Tables From 1 Big Table?
Aug 30, 2007
I have a table with over 100000 records.
The format and data is something like this:
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 have 2 tables that doesn't have primary keys. These 2 tables have same number of rows. I want to create a new table from getting some columns from table1 and some columns from table 2. I want to combine first row from table1 and first row from table2.
Below is example
TABLE1
ACOL1 ACOL2 ACOL3 A1 A2 A3 B1 B2 B3 C1 C2 C3
TABLE2
BCOL1 BCOL2 BCOL3 11 12 13 21 22 23 31 32 33
COMBINED_TABLE
ACOL1 BCOL2 BCOL3 A1 12 13 B1 22 23 C1 32 33
I tried below query but no luck. It gives below error:
Query : create table COMBINED_TABLE AS select a.ACOL1, b.BCOL2, b.BCOL3 from (select ACOL1,rownum from TABLE1) a, (select BCOL2, BCOL3, rownum from TABLE2) b WHERE a.rownum = b.rownum
Error : ORA-01747:"invalid user.table.column, table.column, or column specification"
I would like to create a trigger on a table which populates a log table. In addition to using the table where the trigger will exist, I would like to populate a couple more fields in the log table with with data from 2 other tables.
e.g.
NAME_TABLE -reg_id -name
ADDRESS_TABLE *trigger to be fired when a new record is created here. -reg_id -srv_id
PROCESS_TABLE -srv_id -start_time -end_time
This is what I would like the logging table to look like:
i have create service rate card Form for transport Service and i have declare transport component into master lookup table .There is 11 component into master.To Difine rate for a particular transport service i have insert these component name with there rate into another table transport_rate _card.
To do this i have created page with these 11 component name item and there 11 rate item to enter component rate with respective services. Problem is if i create 11 component name text item and 11 text item to enter there conmponent rate but if user add 3 or 4 or 5 more componenet name into master then i'll be need to add extra component name text item and component rate text item.so i need to create form where if user add more component into master then component name item and component rate item should be increase automaticaly.
How i can create form where item added auotmaticaly acording to component which are exist into master table. there is only component name into master table.
I just want to know whether can i create a trigger where the sysdate can be automatically written into a table? for ex: if to_char(sysdate,'HH24:MI:SS') = 18:00:00 then it should be written in a table..?? is there anyother way instead of triggers??
I'm working on a java program connected to a Oracle 9i db. I'm inexperienced about Oracle specific capabilities. The experienced Oracle users
I would like to know if there's a way to create a temporary table so that: - the table will be deleted automatically when a specific db connection closes (obligatory) - the table is visible for just one specific connection (optional)
What I would like to do is: 1 - Get the result set for query A. 1a - process the results 1b - store the results in a performance friendly way ( I thought about a temp table) 2 - Run query B over result set A. 3 - Run query C over result set A. X - The stored result set A has to be removed before/when the program ends (ideally when the db connection closes).
The problem isn't how to do this in java. The problem is to do it in a way so that the table will be automatically removed when the db connection used to create it will be closed so that's unimportant if the java program crashes or ends normally.
i have emp table of 5 columns empno,ename,dept,sal,edit_timing(edit timing datatype is timestamp).and my form contains empno,ename,dept,sal. is if i insert a record into forms it automatically insert the date and timing into edit_timing columns.
I have a table with table_ID, date_created, user_id.
I have sequence, and a BEFORE INSERT trigger, which uses the seq to increment the table_ID by 1, everytime the webpage is saved.
ques: (1).If i have date_creted defaulted to sysdate in the table, do I need to have it in the trigger? -- Update create_date field to current system date :NEW.DATE_CREATED := sysdate;
(2).How can I insert the user_id in the table, each time user SAVE the page ? web page procedure is getting the user info at the beginning. can i add it in the trigger
I have two control block i.e. class_register and student_info. In which student_info is multi-record block which contains stud_id, stud_name, stud_roll_no. Based on the class_register block, stud_id and stud_name is generated in the student_info table. Now I want to generate stud_roll_no automatically until the last record is present.
I have created 2 different databases and each have their own schema user for eg we have database ALIVE with user allive and the other database RLIVE with user rllive , actually we have implemented new module in ALIVE database and in the process we created many tables , synonyms , index and other objects now we want to list out all the tables ,sequences as scripts which are not present in RLIVE and create them in RLIVE as new objects.
I need to run 5 scripts to create 5 tables, seems each table needs to take 2 hrs, so I plan to put all 5 commands together
so i would have tables.sql to contain the following
do you know how to add time in beginning and end , so I can figure it out how long does this take to create 5 tables, use spool too?
create table a as select * from ........ ; create table b as select * from ........ ; create table c as select * from ........ ; create table d as select * from ........ ; create table e as select * from ........ ;
We have an application is must be connected to our database for specific requirements in our company but this application has a very bug thing as we must write the super DB password "Like HR password as example" clear in some files and these files must be shared so developers can use the HR password to do any action !!! I know that this application is a problem but we have to install
I can do this by creating trigger on each table will restrict DML. As example: if the operating system user is XXX, the trigger restrict the action. But not logic at all to create more than 1000 triggers on schema (This will impact badly on DB performance).
So, i need to create one trigger to fire before doing any DML on all schema tables. As example: If "MMM" the administrator operating system user trying to do insert action, he can do the action. BUT If "DEV" the developer operating system user trying to do insert action, the trigger must fire here to restrict this action.
Be noted also, i need this trigger not depend on any specific tool like Toad as any user can simply rename the exe file for toad then he can pass the trigger. At least, trigger must depend on (Operating system user & Action_type)
Suppose two users (CONNECT and RESOURCE roles) A and B who work on the same project. How can B create synonyms of all tables of A without asking admin to do that? Of course, we assume that these users have the CREATE SYNONYM privilege. My problem is that B does not have access to user_tables of A, so he cannot obtain the list of A's tables to create synonyms.
is it possible to create tables using variables in pl/sql such as the following example which is actually in php. i would like to create a table dynamically.
$create_samp_table = CREATE TABLE $MCSCHEMA.".$samp_id." AS SELECT * FROM $MCSCHEMA.".$samp_src_table." WHERE ROWNUM = 0
I'm trying to do is create 4 simple tables. This is my first project using SQL so I'm totally new to it, not just SQL itself but database design/management, including foreign/primary key concepts. I think that's why the errors are being generated due to a duplication of foreign key names (perhaps?) but I really don't where I'm going wrong with the design structure in terms of the FK's and the relationships.
Added the error report in the attachment.
Create Table Hotel (Hotel_No Char(4 Byte) Not Null, H_Name VarChar2(20 Byte) Not Null, H_Address VarChar2(30 Byte), Constraint Hotel_PK Primary Key (Hotel_No));
Here the SUB1 and SUB2 are "tables" and are similar in their structure. The "Main_Table" will be update dynamically and the no of rows in this table will vary.
Now my question , i need to create a view which will have all the rows from these tables ,in the current case it is something like
create or replace view sample as select * from SUB1 union all select * from SUB2
How can this be achived. I tried as shown below:
spool file_to_exe.sql select 'select * from ' || AA ||' union all ' from Main_table; spool off
i will end up in a union all "extra" , if i do like this.
I'm stuck on the CREATE VIEW part and the SELECT statement to get the output of the CREATE VIEW. We were instructed not to use AND and simply use JOIN on this exercise.
Question - For employee John Smith, give the project(s) he is working on, his department's name and the name of his division. See crows foot diagram on a separate file.
I want to create a block of code that would search in all tables in a schema for a column_name where its data_length is like 4000 let's say. This data_length is actually dedicated for a comment column. If found, all not null column with like 4000 data_length will be changed by string "Comment has been removed". I have the following script below but it seemed lacking.
begin for rec in (select table_name, column_name from user_tab_columns where column_name like ?data_length? = 4000 order by 1,2) loop begin execute immediate 'update '||rec.table_name||' set '||rec.column_name||' = ''Comment has been removed'' where '||rec.column_name||' is not null'; commit; [code]........
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
Create small functional indexes for special cases in very large tables.
When there is a column having one values in 99% records and another values that have to be search for, it is possible to create an index using null value. Index will be small and the rebuild fast.
Example
create index vh_tst_decode_ind_if1 on vh_tst_decode_ind (decode(S,'I','I',null),style)
It is possible to do index more selective when the key is updated and there are many records to create more levels in b-tree.
create index vh_tst_decode_ind_if3 on vh_tst_decode_ind (decode(S,'I','I',null), decode(S,'I',style,null) )
To access the record can by like:
SQL> select --+ index(vh_tst_decode_ind_if3) 2 style ,count(*) 3 from vh_tst_decode_ind 4 where 5 decode(S,'I','I',null)='I' 6 group by style 7 ;
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.
