SQL & PL/SQL :: Generate Create Table Statements From Xsd?
Apr 19, 2011 I am working in Oracle 10g.
I have a xsd file and what I need is to generate the create tables from the structure?
Is it possible or do I have to do it manually?
ADVERTISEMENT
SQL & PL/SQL :: Min Max - Create And Insert Statements?
Oct 4, 2010I don't have development env .create and insert statements work below for sample data:
View 8 Replies View RelatedPerformance Tuning :: Create Statements On A Highly Fragmented Disk
Nov 24, 2010On Oracle 10g, I create, delete and drop a lot of tables. Therefore, the disk is highly fragmented.The execution of a very simple create statement takes more than a minute. If I execute the same statement but first truncate the table and insert the data, it takes less than a second!
I think this has to do with the high fragmentation of the disk. Obviously, I can defragment the disk, but I will always have a high fragmentation since I use a lot of create, delete and drops.
how I can improve the performance of create statements on highly fragmented disks?
Ora-06502 - Create Generate Procedure That Create Dynamic Procedure Through DBMS_SQL
Mar 31, 2004ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.
View 3 Replies View RelatedAccess - Sql Statements Being Fired On Table (Test)?
Oct 4, 2012Oracle 11gR2
I have on table 'Test' in database and I want to know currently which sql statements are being fired on table 'Test' ?
Server Administration :: Particular Table / Oracle Enterprise Manager - Where Can See All Executed Statements
Feb 28, 2013know whether i can see anywhere all executed Statements in my DB (Deletes, Updates...)?
perhaps in a particular table or in Oracle Enterprise Manager?
Enterprise Manager :: What Is The Difference Between Create External Table Vs Create Table
Apr 29, 2011What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?
Is CREATE EXTERNAL TABLE included in CREATE TABLE?
SQL & PL/SQL :: Generate Script For Filling Table
Jun 9, 2011I've got table at Oracle test server table1 with columns ID, BTYPE, MYDESCRIPTION. Rows of this table have been inserted manually. Now my need is to write script for creating table (structure + data) for working server. I think about writing something like
CREATE TABLE table 1
AS
SELECT 1 AS ID, 'TYPE1' AS BTYPE, 'SOME TEXT' AS MYDESCRIPTION
UNION ALL
SELECT 2 AS ID, 'TYPE2' AS BTYPE, 'SOME TEXT 2' AS MYDESCRIPTION
But rows are too many to type... Any way of generating script for creating table at working server using existing table at test server? The problem is I don't have an access to working server.
PL/SQL :: Generate Stats For DBLink Table (SQL)
Sep 7, 2012I am trying to generate some statistics on tables connected by a dblink. I know with oracle you have table_columns which you can reference and pull some stats from.
Trying to get the column count and record counts for each table connected by a dblink. I have tried these queries below to see if I could see any db properties: (some just to try something different)
select * from "table_owner".table_column@dblink ;
select * from "status"@dblink;
select /*DRIVING_SITE(a) */ count(*) from @dblink a;
What is the best method to finding this out without spending a lot of time? I have over 30 tables which are with large record sets and would love to learn a faster approach then pulling a sample table and doing a manual count and query for each table to count the rows.
SQL & PL/SQL :: Insert Statements / Give A Commit One By One After Each Insert Statements?
Oct 11, 2012Can we execute more than one insert statements at a time (eg 10) in database and givecommit at the end of insert statements or else give a commit one by one after each insert statements ?
View 8 Replies View RelatedReports & Discoverer :: Generate Report To Database Table?
Aug 25, 2011Is it possible to save a report directly in a table instead of the typically destination? (file, cache,...)
View 4 Replies View RelatedSQL & PL/SQL :: Generate Columns Dynamically By Depending On The Rows In A Table In 11G?
Nov 27, 2012Is there any way to generate columns dynamically by depending on the rows in a table in 11G .
Ex: If the deptno in DEPT table is not constant,then how to generate the N numbers of columns based on the deptno. Below query is working when we hard coded the deptno (10,20,30,40).What else if we more number of departments and we don't know the departments also.
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as dbo
SQL> SELECT *
FROM (SELECT deptno, job, sum(sal) sal
FROM SCOTT.emp GROUP BY job, deptno) PIVOT(sum(sal) FOR deptno IN(10,
20,
30,
40));
[code].....
SQL & PL/SQL :: Generate The Number Of Rows Based On Table Field?
Mar 29, 2013generate the number of rows based on table field.
Sample code is given below.
CREATE TABLE T
(
docno VARCHAR2(10),
CODE VARCHAR2(8),
QTY NUMBER(3)
)
LOGGING
JDeveloper, Java & XML :: How To Generate String From Nested Table
Mar 19, 2013CREATE TYPE TEST_103 AS OBJECT(CHILD_CSE_ID VARCHAR2(25));
CREATE TYPE TEST_104 AS TABLE OF TEST_103;
CREATE TABLE TEST_105 (
CL_ID VARCHAR2(25),
CSE_ID VARCHAR2(14),
EXP_ID VARCHAR2(13),
[code]....
I want an XML output like as follows
<?xml version="1.0" encoding="UTF-8"?>
<Main_case>
<cl_id="A">
<cse_id="100">
<cs_lst>
[code]....
Forms :: How To Generate Numbers Automatically In Multirecord Table Using Oracle 6i
Sep 28, 2013I 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.
View 4 Replies View RelatedProcedure To Generate Crosstable From Data Inside Calves_per_breed Table
Dec 11, 2006I have two Tables, the one table is called (calves_per_breed) and contains all my query results. I then have another table (calves_per_breed_crosstable) which is used to place the generated count values in calves_per_breed to create a crosstable from the count data into calves_per_breed_crosstable.
I'm using the following procedure to generate the crosstable from the data inside the calves_per_breed table:
PROCEDURE pcalves_per_breed_genCrossTable
IS
BEGIN
---------------------------------------------------------------------------
--SEX CODES
update calves_per_breed_crosstable
set (m, f) = (select count(decode(geslag, 'M', 1, null)),
count(decode(geslag, 'F', 1, null))
from calves_per_breed)
[codee]....
Now this procedure works 100%, the only problem is it generates the ENTIRE table. if you know how a crosstable works, thetop right section of the table is exactly the same as the bottom left section of the table. I wish to optimize my code so that it only generates the values for the needed columns, and not ALL the columns, as values are generated twise now, which increases the query time! Here is a tipical output of the kalwers_per_ras_crosstable:
Note: You will notice that i used a Column called TID with string values to indicate the vertical columns for the crosstable. The Vertical Columns are the same as the top Columns(which are actual columns, and not row values as the vertical columns)
select *from kalwers_per_ras_crosstable:
TID M F NFR A B C SP RED BLACK SC1 SC2 SC3
----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
M 138 0 0 0 0 0 138 122 16 74 64 0
F 0 173 0 0 0 0 173 161 12 92 81 0
NFR 0 0 0 0 0 0 0 0 0 0 0 0
A 0 0 0 0 0 0 0 0 0 0 0 0
[code]....
Client Tools :: Generate Insert Script For Existing Table Data?
Aug 21, 2008Is theree any way to generate the insert script (with the data) for an existing table.
In Toad, we can generate the DDL for a particular table. Just curious if it can be done using any Tool.
SQL & PL/SQL :: Dynamic Function - Create Physical Table And Return Table Name In Out Variable
Aug 30, 2011I 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]......
SQL & PL/SQL :: Create Complete Hierarchical Table From Table With Only Two Columns - Parent And Child
Aug 13, 2012We 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.
SQL & PL/SQL :: Command Is Used To Create Table By Copying Structure Of Another Table Including Constraints?
Jul 14, 2012what command is used to create a table by copying the structure of another table including constraints ?
View 2 Replies View RelatedCreate Temp Table From Existing Table
Sep 2, 2010I want to create temp table, for this i am using:
CODEcreate global temporary table help_temp
as
select * from help;
but this is creating only the table structure, not copying the table data.
SQL & PL/SQL :: Create Table By Using Another Table With Specific Condition?
Jan 25, 2013can we create table with copying of another table with some specific condition.
example.suppose we have one table which name is emp with three columns.
empid
empname
empjoindate
i want create a table emptemp by using emp table where empjoindate between two dates.
Create A Table Which Contain History Of Main Table
Sep 8, 2008I 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.
Import CSV Using SQL Statements
Jun 9, 2011I know, importing a *.CSV-table is easy using a few clicks with the GUI, but I want to know, how to import and create new tables, using the existing *. CSV-files, with SQL statements.
how to import a .CSV-file using SQL-statements?
SQL & PL/SQL :: IF / THEN Statements With Different Datatypes
Sep 30, 2011I have a column in a database that contains both numerical and char data. I would like to be able to do two different things (two different queries)
1. divide the numerical data in the column by 10 and leave the char data alone (just return it)
2. detect the numerical data in the column and treat is as a different value so I can run averages & counts on it while disregarding the char data
I'm not at all sure how to do number 2. I thought a CASE statement would work for number 1, but then I realized CASE doesn't like different datatypes:
select
case when '1234' = 'checked' then 'checked'
when '1234' = 'gen.nograde' then 'gen.nograde'
when '1234' = null then null
else '1234'/10 end as "GRADE"
from dual
Error report:
SQL Error: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s"
SQL & PL/SQL :: Getting Insert Statements
Feb 26, 2010I have a read access on all the 200+ tables in a schema, How can i generate 200+ .sql files with insert statements on all the tables of schema.
View 13 Replies View RelatedConcatenating Two Select Statements
Oct 23, 2009Been trying to concatenate the following two select statements (to then pass as a parameter in DML statement) without much luck.
Select lpad(ST_DAY,'2',0) || '/' || lpad(ST_MONTH,'2',0) from RET_FORMATS;
Select to_char(sysdate,'YYYY')"Year" FROM DUAL;
Find Out What SQL Statements Currently Running
Feb 26, 2009 From database server, I need to monitor the details about the sql statements which are being currenlt running in client machines.
I tried with V_$SQLTEXT view where I can only see the SQL statements, hash value,address,SQL_id. but I'm not able to get the user name,name the client machine .
find out these details?.Which Data Dictionary i need to use ?.
SQL Statements Behind Self Service Page?
Aug 29, 2011I have a question regarding the SQL statements embedded behind the Self Service Pages in Oracle Applications.
Taking an example of Oracle E-business Suite, Is there a way to check what SQL statement is hard coded or embedded in a particular required Self Service Page?
SQL Either Or Statements In Third-party Software
Dec 27, 2010I'm in a bit of a pickle with some SQL syntax, and while my Oracle-fu is weak, an associate with SQL skills is also stuck. I am developing a database for my department that is backed by Oracle Enterprise 11g and the front end is ChemAxon (basically, chemistry database software). The short of it is that I have one large table with every compound from different groups. Each row has a flag (column PI) on it that indicates which group it belongs to (let us say 'Smith' and 'Jones' for example). The software can apply row-level filtering which will basically only show the rows that a particular username are allowed to see.
I have a FLAG_TABLE table, which contains two columns: USERNAME and FLAG. An example set up is this:
USERNAME FLAG
------------- ----------
smith_minion Smith
jones_minion Jones
jones_minion Smith
The software automatically applies a SQL filter that begins with:
SELECT DISTINCT CHEMAXON.STRUCTURES."ID" FROM CHEMAXON.STRUCTURES WHERE
I can set up filtering to work dandy, such that when smith_minion logs in, he can only see rows with the Smith flag (or jones_minion can see both Jones and Smith) by using the filter:
STRUCTURES.PI IS NULL or STRUCTURES.PI IN(SELECT ALL FLAG from FLAG_TABLE where USERNAME = '__IJC_USERNAME__')
('__IJC_USERNAME__' is how the third party software passes the logged in username into SQL)
But we have a new problem: there also exists a master user (chemaxon) who needs to see every row no matter what the flag. The row filtering is applied no matter who logs on, so we need to set up the SQL filter to basically say "If chemaxon, then select all rows, otherwise, select rows based on the username". This is proving a problem as the select statement must be prefaced with SELECT DISTINCT CHEMAXON.STRUCTURES."ID" FROM CHEMAXON.STRUCTURES WHERE.
I've tried using DECODE in a few capacities, but I am always thwarted. My last attempt was:
STRUCTURES.PI IS NULL or STRUCTURES.PI IN (DECODE('__IJC_USERNAME__','chemaxon',(!='something'),(SELECT ALL FLAG from FLAG_TABLE where USERNAME = __IJC_USERNAME__));
But this throws a ORA-00936 missing expression error, with a * under the != portion (I test it by replacing '__IJC_USERNAME__' with 'CHEMAXON').