SQL & PL/SQL :: ORA-01003 No Statement Parsed On CREATE TABLE
Feb 17, 2010
I have the following create table statement:
create table SELECTION_BOOKMARK(
INSTALLATION_ID SMALLINT NOT NULL,
BOOKMARK_ID SMALLINT NOT NULL,
NAME VARCHAR2(50) NOT NULL,
SORT_ORDER SMALLINT NULL
);
When I execute this statement as part of a SQL script, it throws the error "ORA-01003: no statement parsed". If I execute it as a stand alone sql command, it returns no error.
Here's the strange thing, though. Even when it throws the error message the table is created. Later on in the script, I execute the following command:
alter table SELECTION_BOOKMARK add primary key(INSTALLATION_ID,BOOKMARK_ID);
This command also throws the above mentioned error, but once again the command executes - the primary key is created.Well, I don't like having a script that throws errors. Eventually I'll have to give this script to a dba in another organization and I don't want my script to be throwing errors - even if it's apparently working correctly despite the errors.
View 12 Replies
ADVERTISEMENT
Oct 8, 2012
I've to create a table which has 650 fields and the total length of CREATE TABLE statement got to be more than 4000 characters.I've to create the table by inserting the CREATE TABLE statment in a variable (V1) then by using EXECUTE IMMEDIATE V1 Since VARCHAR2 only supports upto 4000 characters length string, how can I create such table??
DECLARE
V1 VARCHAR2(4000);
BEGIN
V1 :=
-- CREATE TALBE STATEMENT WITH LENGTH MORE THAN 4000
EXECUTE IMMEDIATE V1;
END;
Quote:got the error -- PL/SQL: numeric or value error: character string buffer too small
How can I create such table??
View 7 Replies
View Related
Aug 5, 2010
this statement is taking 1hr , can we reduce the timing?
CREATE TABLE DGT_ITEMEFFORTDATA (ENTERPRISEID, OWNERTYPE, OWNERID, SUPEROWNERTYPE, SUPEROWNERID,
ITEMTYPE, ITEMID, STAGEID, USERID, DATEIDENTIFIED,
DATECLOSED, ACTIVITYCODEID, PHASEID, RELEASEID, MONTHID,
QUARTERID, INITIALEFFORT, BASELINEDEFFORT,
ACTUALEFFORT, ITEMSTATUS, ALLOCATIONSTATUS, STAGESTATUS,
OCCURANCETYPE, DSLPROJECTTYPE, METRICCALCRUNID,
[code].....
This is the explain plan of the above query
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11M| 4137M| 46149 (
| 1 | UNION-ALL | | | |
| 2 | TABLE ACCESS FULL| DGT_ITEMEFFORTDATA_DAILY | 3455K| 428M| 14575
[code].....
This is the index details
1DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDITEMID4
2DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDITEMTYPE3
3DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDOWNERID2
4DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDOWNERTYPE1
There is no index on DGT_ITEMEFFORTDATA_TEMP table
[code].....
View 27 Replies
View Related
May 18, 2010
Can we have same execution plan for a create table statement where the name of the table changes every time as follows:
create table test
as
select * from t1
Here table name changes from test to another table name next time
View 6 Replies
View Related
Aug 10, 2011
HOW to use variable P_TMPLID in following statement
TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;
because its throwing error while compiling
and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION
View 2 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
Nov 3, 2012
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));
Create Table Room
(Room_No VarChar2(4) Not Null,
Hotel_No Char(4) Not Null,
R_Type Char(1),
R_Price Number(5,2),
Constraint Room_PK Primary Key (Room_No, Hotel_No),
Constraint Hotel_No_FK (Hotel_No) References Hotel(Hotel_No));
Create Table Booking
(Hotel_No Char(4) Not Null,
Guest_No Char(4) Not Null,
Date_From Date Not Null,
Date_To Date,
Room_No VarChar2(4),
Constraint Booking_PK Primary Key (Hotel_No, Guest_No, Date_From),
Constraint Guest_No_FK Foreign Key (Guest_No) References Guest(Guest_No),
Constraint Hotel_No_Room_No_FK (Hotel_No, Room_No) References Room(Hotel_No, Room_No),
Constraint Hotel_No_FK (Hotel_No) References Hotel(Hotel_No));
Create Table Guest
(Guest_No Char(4) Not Null,
G_Name VarChar2(30),
G_Address VarChar2(35),
Constraint Guest_PK Primary Key (Guest_No));
View 31 Replies
View Related
Dec 14, 2012
Is it possible to create sequence using EXECUTE IMMEDIATE statement. The sequence name will be the bind variable.
DECLARE
TEMP VARCHAR2(20);
BEGIN
TEMP := :P2_INFO;
EXECUTE IMMEDIATE 'CREATE SEQUENCE' TEMP;
END;
This creates a sequence named TEMP, but i want the name :P2_INFO entered by user.
View 10 Replies
View Related
Feb 21, 2012
Create table X(
var1 varchar2(20) null,
var2 varchar2(20) not null,
constraint pk_var1 primary key(var1)
We all know Primary key doesnt treat NULL as a value. But the above statement is fine to be executed without problem.
Is this something to be highlighted? or am i not right in understanding 'var1 varchar2(20) null '?
View 2 Replies
View Related
Mar 3, 2011
I am attempting to read from the maillog of our server, but I wish to make as few changes as possible for fear of blocking other systems access to the file.
I was initially going to call create directory maillogs as '/var/log/maillog' and then drop directory maillogs; when I was done but I found my user does not have "create any directory" permissions.
Rather than compromise security of the existing database configuration, I thought I would permanently add the maillogs to the list of available data directories. Are there any implications to the filesystem if I do this, or should I be able to add this without consideration of affects.
Understand that I will only be opening the file for (R) READ TEXT access only.
Primarily I am concerned that Oracle (in the background) will keep a file pointer open or something of that nature that would block other programs from writing to the file even after I close the file pointer. I want to make as little impact as possible to the file system.
View 4 Replies
View Related
Jun 3, 2010
I need to create a SQL insert statement with mutipleValues and an select statement I am trying this it works -
insert into uwa_crew_ids(crew_ID, CREATION_DATE, CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_NAME, first_name )
values
( uwa_crew_ids_s.nextVal,sysdate, 1767,sysdate, 1767,'TEST1', 'NITA')
This statement does not work (with or without keyword) Is there any alternate syntax
insert into uwa_crew_ids(crew_ID, CREATION_DATE, CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_NAME, first_name )
( uwa_crew_ids_s.nextVal,sysdate, 1767,sysdate, 1767,(select last_name, first_name from uwa_crew_ids where guid = '8795EAAFBE694F8EE0440003BA2AEC90' ))
View 3 Replies
View Related
Jun 6, 2013
oracle 11gr2
linux
how to generate insert script by using command prompt. Actually am using toad generating insert statement but it is taking too long time to generate.
View 4 Replies
View Related
Mar 2, 2010
Is there any difference between include program header before CREATE OR REPLACE PACKAGE statement and program header after CREATE OR REPLACE PACKAGE statement
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 18, 2012
I am writing a query and I did get it to work but it shouldn't be this hard, I feel like I am doing something wrong, and there has to be a more elegent solution.
This query works:
SELECT a.d
FROM (
SELECT S_ID a, LOOKUP_DESC d, S_CODE f
FROM SSS
JOIN LOOKUP ON S_CODE LIKE LOOKUP_CODE
UNION ALL
[Code]...
I feel like I should be able to execute the query like this: This query doesn't work:
SELECT a.d
FROM (
SELECT S_ID a, LOOKUP_DESC d, S_CODE f
FROM SSS
JOIN LOOKUP ON S_CODE LIKE LOOKUP_CODE
[Code]...
-- not some huge query
View 4 Replies
View Related
May 19, 2011
I want to know the DDL statements executed on a table. Example:
i have a table test1 with structure as below: test1 (aa number, bb varchar2(10))
After some period of time, one DDL statement to alter the column is issued.
alter table test1 modify (bb varchar2(30));
so new table structure is: test1(aa number, bb varchar2(30))
Basically i would like to find these 2 DDL statements executed on this table - test1, so that i compare which column get modified and what modification done.
Is this information stored in any data dictionary table?
note - audit, flashback option not enabled in this database.
View 3 Replies
View Related
May 14, 2013
I have a table TEMP_TEST_1 to be loaded with multiple rows which contains the available items for a store with their sequence numbers. I also have another table TEMP_INV_1 which holds a column for inventory count of the items, and this column is to be updated after loading the first table TEMP_TEST_1.
The table TEMP_TEST_1 is updated through all possible channels in OLTP. So while updating the inventory, which is the best way. My update should either add the total number of inserts per item into the TEMP_TEST_1 table's INVENTORY_CNT column i.e. existing value + count of new rows OR a full update by taking the complete row count (Grouped by item id) and update the INVENTORY_CNT column
--------------------------------------------------------------------------------
-- Initial setup SQL's, TEMP_TEST_1 is updates on OLTP through multiple channels
--------------------------------------------------------------------------------
CREATE TABLE TEMP_TEST_1 ( ITEMID NUMBER ( 4 ),
INVENTORY_CNT NUMBER ( 2 ) );
CREATE TABLE TEMP_INV_1 ( ITEMID NUMBER ( 4 ),
ITEMSEQ NUMBER ( 5 ) );
INSERT INTO
[code].......
--------------------------------------------------------------------------------
-- Inserts for new items in the store
--------------------------------------------------------------------------------
INSERT INTO
TEMP_INV_1
VALUES
( 1,
11 );
INSERT INTO
TEMP_INV_1
[code].......
View 4 Replies
View Related
May 26, 2011
i'm trying to use a collection in a select statement as a table, but i've got an error which i don't understand.
This exemple is very simple. I'm trying to validate the solution. The object will contain the result of a query from a big table, and i have 4 request to make on it. I don't want to select 4 times the big table to get the result, but i need the whole result to make my requests (intersect, minus and union)
CREATE OR REPLACE
TYPE zy_w AS OBJECT(
CODE_INFORMATIONVARCHAR2(4),
[Code]....
****************
Rapport d'erreur :
ORA-06550: Ligne 12, colonne 3 :
PL/SQL: ORA-00947: nombre de valeurs insuffisant
ORA-06550: Ligne 5, colonne 3 :
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
View 9 Replies
View Related
Nov 8, 2011
this is the correct syntax for updating a table with a select statement included. Table created easily and the alter table ran fine, but the update is running quite a log time.
My ultimate goal is to populate the "children" field with a count of children for each household id.
create table NON_GBC_Members nologging as
select distinct hcp.household_master_ID
from mrtcustomer.household_child_profile hcp
where hcp.child_birth_dt between '31-OCT-2000' and '30-OCT-2011'
group by hcp.household_master_id
minus
[code]....
View 5 Replies
View Related
Oct 29, 2012
{code{
i want to apply merge stmt on single table.
CREATE TABLE TEST11(TNO NUMBER(5), TVAL VARCHAR2(100), TID VARCHAR2(10));
INSERT INTO TEST11 VALUES(1,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(2,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(3,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(4,'VIJAYA','');
INSERT INTO TEST11 VALUES(5,'VIJAYA','');
[Code]....
My requiremen is if record is exists then i wan to update some value, if record not existes the i wan to insert new record
View 3 Replies
View Related
Sep 1, 2011
there is a possible to use a select statement on collection table?
View 1 Replies
View Related
Sep 30, 2010
I have a table with a column type long raw. Now i want to insert an image from directory by an insert statement. Is it possible?
View 2 Replies
View Related
Jun 18, 2013
I want to grant a privilege through an insert statement into a sys table.Why do not grant the privilege through the classic way : grant select on t to l_user; ?
Because I want to do it remotely.I am connected to db1.I want to grant select on t2 to u2_b from u2_a.I assume that all DDL are DML. So a grant is equivalent "somewhere" to an insert.I tried to do my requirement locally, and here is the output.
SQL> conn scott/aa
Connecté.
SQL> -- step 1 : try to grant "normally" a select on dept to hr from scott
SQL> grant select on dept to hr;
Autorisation de privilèges (GRANT) acceptée.
SQL>
SQL> conn sys/a as sysdba
Connecté.
SQL> -- step 2 : Then, we connect to sys to see the row inserted in dba_tab_privs
SQL>
SQL> col GRANTEE format A10
SQL> col OWNER format A10
[code]...
Then if I can do it locally, I can do it remotely through a db link.
View 2 Replies
View Related
Jul 19, 2013
How do I collapse all nulls for every table column in a SQL Statement?
View 15 Replies
View Related
Aug 10, 2010
Can I have all the rows in a table updated at once in the merge statement?
MERGE INTO providermaster a
using
(
SELECT * FROM PROVIDERMASTER@INGEST) b
ON (b.MASTERPROVIDERID=a.MASTERPROVIDERID)
WHEN MATCHED THEN
UPDATE ....... I want to update all the other rows at once..
View 24 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