SQL & PL/SQL :: Create Table As Select On 10.2.0.5?
Oct 26, 2011
running on 10.2.0.5 and above satisfies the following:
having locally managed tablespace with assm enabled
create table t1 (n1 number) storage (next 1M);
insert into t1 values (1); commit;
create table t2 storage (next 2M) as select * from t1;
This will indeed set the storage option next to 2M;
The same situation in 10.2.0.4 will allways set next option for the ctas statement to the initial value of table t1.
Is this an impact of patch set 4 (10.2.0.5)? Why does it differ from 10.2.0.4 to newer releases?
View 3 Replies
ADVERTISEMENT
Sep 13, 2004
Can we create a table from a Select query ?
View 5 Replies
View Related
Nov 10, 2010
Is it a possible to create table using clause below together with index ?
create table the_table
as
select col1, col2 from table2
I got procedure which create a table in the schema B. The procedure is called from schema A. But when I write into procedure query for create index
then I got a error:
ORA-01031: insufficient privileges when
...executing
Therefore I think about to create table together with index.
begin
B.proc.cre_table;
end;
View 6 Replies
View Related
May 10, 2012
I have a SQL statement that returns a set of columns...but...when I create table as <SQL statement> I get the same columns but with 2 of the columns containing each others data, e.g:
SQL Select:
COL1 COL2 COL3 COL4
___________________________________
AND10200000017805CG-4CG-3
Create Table as <SQL Select>:
COL1 COL2 COL3 COL4
___________________________________
AND10200000017805CG-3CG-4
The SQL Select is correct and the Create Table As <SQL Select> is wrong.
Here is my SQL:
Create table ALTERNATENUMBERS as
SELECT ctry,
id,
MAX(DECODE(tp,'EN', RN)) EN,
MAX(DECODE(tp,'RN', RN)) RN,
MAX(DECODE(tp,'AN', RN)) AN
[code]....
Unfortunately I cannot give you any data (too much of it) and small scale testing works, it's only when I run it on the 11million records do I get some (not all), just some of the data being mixed up between columns.
Now, I've tried:
1. Using SQLPLus - no joy
2. Creating the Table and then inserting the data into a blank table - also no joy
3. Using a VIEW - no joy, listagg doesn't work in VIEW tables
I do understand that without data it's hard to replicate the issue but why this statement works as a SELECT but when written to a table has data anomolies?
View 4 Replies
View Related
Apr 29, 2011
What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?
Is CREATE EXTERNAL TABLE included in CREATE TABLE?
View 3 Replies
View Related
Jul 8, 2008
CREATE sequence customer_id start with (select max(customer_id) from customer) increment by 1
i am trying to do that, can it be done or should i just find the max id myself and replace that select with a no?
View 2 Replies
View Related
Feb 2, 2011
I have a query like this:
*************************
SELECT vendor_id, summary_flag
FROM ap_suppliers
WHERE vendor_id = :param; --'4551'
The Results:
************
VENDOR_ID SUMMARY_FLAG
-----------------------
4551 N
Then I create the procedure:
*****************************
CREATE OR REPLACE PROCEDURE myproc4 (
p_vendor_id IN ap_suppliers.vendor_id,
p_summary_flag OUT ap_suppliers.summary_flag
)
AS
BEGIN
[code]....
Warning: compiled but with compilation errors.
I want to create a procedure that call vendor_id (parameter) and the output like the this:
VENDOR_ID SUMMARY_FLAG
-----------------------
4551 N
View 4 Replies
View Related
Jun 11, 2008
I have a table structure like :-
Create table test(A varchar2(50),B NUMBER);
The data in that table is like that:-
A B
----------------------
2*3
2*4*5
4*5
column B contain no data.
I want to create a function which can be used in a select query,and the output should come like that :-
A B
----------------------
2*3 6
2*4*5 40
4*5 20
Means column B contains the resultant value of column A.And the above output should come through a select statement.You can use any function inside the select statement.
View 20 Replies
View Related
Dec 21, 2011
I'm trying to create a sort of nested-query within my select of attributes . i.e.
select A.a,
B.b,
(select count(C.*)
from C
where C. = B.d
group by C.y)
from A a,
B b
where A.d = B.d
and ...
Over-simplifying my query:
select B.desc "Location",
F.desc "Source",
A.amt "Amount",
sum(G.G_CNT) "No. Units",
c.desc "Status"
[code]....
I need to incorporate a count of the number of units from TableG that have a certain status. I tried the following but when I tried to run it, I get an error saying that it's not a Group By expression -the red part is highlighted in TOAD.
select B.desc "Location",
F.desc "Source",
A.amt "Amount",
sum(G.G_CNT) "No. Units",
(select count(*)
from TableG G2
where G2.D_ID = D.ID
and G2.status = 10
group by G2.D_ID)"Count",
c.desc "Status"
[code]....
Any thoughts how I can incorporate a query in my select of attributes?how to Group By something.
View 8 Replies
View Related
Nov 1, 2013
how do I create a procedure for a SELECT query like the following?
When I create a procedure; I get an error "Error(80,1): PLS-00428: an INTO clause is expected in this SELECT statement" PROCEDURE MyProcISBEGINselect 'Dakota' as ALIAS ,A.StartDate ,B.EndDatefrom Customer A ,Clients bwhere a.cType = b.cTypeand b.Active =0ORDER BY StartDate, EndDateEND MyProc;
View 17 Replies
View Related
Sep 9, 2013
I have a table with columns job_id, jan, feb, mar ... , and year
I need to create a select query that will get the data from 18 months ago based on sysdate.
So something like:
Select to_char(add_months(sysdate, -18),'MON') from table1 where job_id = 56947 and year = to_char(add_months(sysdate, -18),'YYYY');
However I need the result of to_char(add_months(sysdate, -18),'MON') to actually act as a column name, not a string result.
View 23 Replies
View Related
Jun 11, 2012
i am using Apex 3.2 ver.
i want to use below code in LOV select list
BEGIN
IF UPPER(:P23_SERVICE_TYPE) like 'GUIDE%' THEN
SELECT NAME D, CODE R FROM SPECIAL_SERV_MAS
WHERE NVL(ACTIVE_FLG,'N') = 'Y'
AND NVL(GUIDE_FLAG,'N') = 'Y'
and CITY_CODE LIKE NVL (:P23_CITY_CODE, '%')
[code]....
When i put this code in my LOV Select list Section then display me Error
Not Found The requested URL /pls/apex/f was not found on this server.
Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server Server at tidevserv1 Port 7777
View 3 Replies
View Related
Aug 31, 2010
I have the below data in table test_1.
select * from test_1
IDNameTotal
-----------
1A100
2B100
3C100
4D100
test_2 table contains the concatination of ID's with comma seperated. Actually in this table ID column is of datatype varchar2.
select * from test_2
ID
----
1,2,3
My requirement is to select the data from test_1 table where the id values in this table exists in test_2 table. I tried with the belowselect statement, but could not get any data.
SELECT * FROM test_1 WHERE to_char(id) IN (SELECT id FROM test_2)
create table test_1 (id number, name varchar2(100), total number)
create table test_2(id varchar2(100))
insert into test_1 values (1,'A',100)
insert into test_1 values (2,'B',100)
insert into test_1 values (3,'C',100)
insert into test_1 values (4,'D',100)
View 4 Replies
View Related
Aug 30, 2011
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]......
View 2 Replies
View Related
Aug 13, 2012
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.
View 3 Replies
View Related
Jul 14, 2012
what command is used to create a table by copying the structure of another table including constraints ?
View 2 Replies
View Related
Sep 2, 2010
I 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.
View 5 Replies
View Related
Jan 25, 2013
can 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.
View 2 Replies
View Related
Sep 8, 2008
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.
View 5 Replies
View Related
Dec 11, 2011
I have the same problem like in below link
[URL]
View 3 Replies
View Related
Sep 24, 2010
Below is my table structure.
create table test_join (
user_name varchar2(20),
attribut varchar2(30),
attribute_val varchar2(30)
);
[code].......
I need to query an users name, manager,location and the manager's manager. Say the info for user A would be,
User : A
Location : Bangalore
Manager : B
Manager's Manager : C
I can get this in two step process.
select attribut,attribute_val from test_join where user_name = 'A';
select attribute_val from test_join where user_name =
(select attribute_val from test_join where user_name = 'A' and ATTRIBUT = 'manager') and
ATTRIBUT = 'manager';
But is there a way to get this in one single query ?
View 11 Replies
View Related
Aug 23, 2005
I have requirement where I need to use a regular select statement for a PL/SQL table. How can I do this? Here is a code example:
Create or replace PACKAGE cv IS
type comp_rec is RECORD
(deptno number,
[Code]....
In the function AAA, I am trying to return a ref cursor. I want to base the ref cursor on a PL/SQL table rather than a Oracle table. But unfortunately
open temp_cv for select * from temp_tbl;
is erroring out.
Can I do this?? that is, use a PL/SQL table in a regular select. If not, then, is there a workaround ?
View 4 Replies
View Related
Sep 2, 2009
I want to find out all the tables that contains a known column name?
I know a column name called 'receiving_number', but don't what what table it's in.
View 2 Replies
View Related
Jul 12, 2013
I do not know the table name but have a query that for sure returns the table name and I want to select a column value from the table into my PLSQL variable.
Here is the code :
DECLARE
v_VerTabName VARCHAR2(30);
v_Minor NUMBER(2);
[Code]....
But when I compile this I get ORA-00936 : Missing expression at the EXECUTE IMMEDIATE line.
View 3 Replies
View Related
May 10, 2012
If there are 100 colums or above in a table and we dont want select a single(any one) column from those 100 columns. is there any command or function to do this in oracle.
I can do it by writting names of all 99 columns but as this method is lenghty, so need some efficient way to do this.
like in SQL server we have a "except" keyword through which we can rule out the columns .
ex: select * except (column_name1,column_name_2)
from Table_name.
View 6 Replies
View Related
Nov 14, 2012
if you grant select on a table to a user then u revoke select on this table that user still can desc this table, is this ok? what if i don't want that user to desc it?
View 2 Replies
View Related
Jun 23, 2010
I am dealing with a table containing millions of records. I have table loans_list table and he data looks similar to this..
LOAN_IDSEQUENCE_NUMCOMPLETE_DATE
1237000
1237005
1237010
5237010 6/23/2010 10:07:02 AM
5237000 6/23/2010 10:07:02 AM
12237000
I am trying to select only those loan_id from this table which contain all these 3 sequence_num = 7000,7005,7010 and containing null compelete date. I tried different way to write the query but can't think of efficient way of writing this query yet.
Since this table contain million of records, i dont prefer to call this table more than once in a query. I am just trying to avoid the longer time delay for the execution of this query..
View 7 Replies
View Related
Jul 10, 2010
Which part of the Posting Guidelines (
[URL]......)
did you NOT read & follow?
Was it the part about using SEARCH (
[URL]......
or GOOGLE (
[URL]......
before posting?
View 1 Replies
View Related
Jun 21, 2011
I have a table with multiple column. A column named address have value of multiple lines. Now i want to select the address only of the first line. How does it possible.
Example:
address
319/a, Bashirvila
Tilpapara
Dhaka
Now i want to display only 319/1, Bashirvila.
View 4 Replies
View Related
Jun 7, 2012
How to find display the o/p like manager name under dependent employess same like parent child relation ship on noraml emp table:
sample o/p:
name job
xx manger
yy sales
yy1 sales
aa manager
rr marketing
rr1 marketing
View 6 Replies
View Related
