am learning the Oracle PL/SQL language using CBT Nuggets tutorials and practicing what I learn. I seem to be getting confused with the ROUND function...For example, what am I doing wrong in this statement:
select avg(salary) from employees,
ROUND (avg(salary) , 2;
Basically I am trying to round the result to two decimal places but get the error ORA - 00933 - SQL command not properly ended?
i have a problem with a syntax in sql navigator. everytime a run this sql command, this message appears:
ORA-00933: SQL command not properly ended.
INSERT INTO table1
(year, id, Date, categ)
VALUES (2013, id, '15-mar-2013', 2)
select year, id, date, categ from table1 where id=5000 and year=2013;
also
INSERT INTO table1
(year, id, Date, categ)
VALUES (2013, 5000, '15-mar-2013', 2)
select year, id, date, categ from table1 where id=5000 and year=2013;
I created a table named dept2 which is a duplicate of the dept table in scott schema.
create table dept2 as select * from dept;
after that I altered it woth the following statements :
alter table dept2 add ddate date;
alter table dept2 modify deptno unique;
all operations were successful , now when I execute the following ddl statements :
alter table dept2
add constraint xx unique (loc,dname)
add constraint yy check (mod (deptno,10) =0) disable
modify ddate default sysdate
modify deptno number (5)
drop unique (deptno)
I get the error : SQL Error: ORA-00933: SQL command not properly ended
when I remove the statement of drop unique (deptno) it works
INSERT INTO Table (NID, NType) SELECT DTreeNotifySequence.NextVal, 1 FROM Dee WHERE ID =354344 commit;
I get the following error:
ORA-00933: SQL command not properly ended
I tried it in several ways...are more semicolons needed?
My requirement with one table which contains 20 columns. Now I want to convert 18 column into rows. I tried with the following query but its throwing sql error is given below.
select * from tmpl_pop_age_range_col_lvl
UNPIVOT
(
quantity FOR product IN (COUNT_0_TO_4 as 'a', COUNT_5_TO_9 as 'b', COUNT_10_TO_14 as 'c',
COUNT_15_TO_19 as 'd',
COUNT_20_TO_24 as 'e',
COUNT_25_TO_29 as 'f',
[code].....
ERROR at line 4: ORA-00933: SQL command not properly ended
After creating this procedure...we r not able to execute it...
SQL> ed
Wrote file afiedt.buf
1 create or replace procedure dyn_pro1
2 (tablename varchar2,colname varchar2, prodid number) is
3 cmd varchar2(100);
4 begin
5 cmd := 'delete ' || tablename || ' where'|| colname|| '=' ||prodid;
6 execute immediate cmd ;--using College_id;
7* end;
SQL> /
Procedure created.
SQL> exec dyn_pro1('stu_4','COLLEGE_ID',200);
BEGIN dyn_pro1('stu_4','COLLEGE_ID',200); END;
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "APPS.DYN_PRO1", line 6
ORA-06512: at line 1
The multiple row insert is not working
CREATE TABLE example (
example_id INT NOT NULL,
name VARCHAR( 50 ) NOT NULL,
value VARCHAR( 50 ) NOT NULL,
OTHER_VALUE VARCHAR( 50 ) NOT NULL
);
For the below query its showing error .
INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');
the error message is.
Error starting at line 1 in command:
INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4')
Error at Command Line:3 Column:39
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
SQL> select lo.location_id,te.employee_id
2 from LOCATIONS AS lo,
3 TEST AS te
4 where te.employee_id = 169;
ERROR at line 2:
ORA-00933: SQL command not properly ended
The following script doesnt work.
DELETE
FROM PSPROJECTITEM I
WHERE I.PROJECTNAME = 'TEMP1'
and (I.OBJECTTYPE between 79 and 84
or (I.OBJECTTYPE = 58
and I.OBJECTID1 = 104
[code]...
It gives the following error:
Error at Command Line:12 Column:6
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
I'm unable to get the below update SQL to run in Oracle, it's giving me th below error
ORA-00933: SQL command not properly ended.
UPDATE
PDR.PH_Family_Match_by_Chassis a
SET a.Launched = 'Y'
INNER JOIN
PDR.domCHASSIS
ON
a.chassis_id = PDR.domCHASSIS.chassis_id
[code]....
What I am trying to do is get my report to list every room in the table even if there is nothing scheduled in the room for the selected date. I add a command to the report to force the left outer join but I keep running into errors. This is how I have it worded:
SELECT
"ROOM"."ROOM_ID",
"PATIENT_CARE_EVENT"."OR_NUM"
FROM
"ROOM"."ROOM" LEFT OUTER JOIN "PATIENT_CARE_EVENT"."PATIENT_CARE_EVENT"
ON "PATIENT_CARE_EVENT"."OR_NUM"="ROOM"."ROOM_ID" AND "PATIENT_CARE_EVENT"."PROCEDURE_DATE_DT" IN {?Start Date} TO {?End Date}
Someone else suggested that I change the IN/TO wording in the last line to BETWEEN/AND. When I do that it gives me an error stating that the table or view does not exist.
we are running SAP application against oracle database. say, if I use brspace or brtools (from SAP side) to shutdown or startup database or collect stats, does this mean it not recommend to use oracle command to shutdown/start & collect stats?
View 3 Replies View Relatedafter a long time,(i never remember when was the last time i installed 9i)i was asked to install 9i.So, whether i install server or cilent i get this error <attachment>
after i ignore it and finished the installation,The server installation gets abruptly ended for the client i get TNS errors.
why how ever way i try i cant get the joins on the tables properly.... well i know i have to work hard....if join is not proper the data i extract is also not proper.Well now i have 3 tables...
ps_operations
Name Null? Type
----------------------------------------- -------- --------
ASSB_PT_NBR_SEQ_ID NOT NULL NUMBER(8)
OPERATION_NBR NOT NULL VARCHAR2(10)
EFFECTIVE_FROM_DT NOT NULL DATE
DML_TS NOT NULL DATE
DML_USER_ID NOT NULL VARCHAR2(30)
OPERATION_DESC NOT NULL VARCHAR2(70)
HOURS_PER_PIECE_QTY NOT NULL NUMBER(9,6)
PIECES_PER_HOUR_RATE_QTY NOT NULL NUMBER(15,7)
EFFECTIVE_TO_DT DATE
EXTRACT_IND VARCHAR2(1)
[code]...
I have never worked on CPK and UK....so i dont know how to use them to join the tables,.
I am using the following merge statement it's not working properly. If I tried to insert (or) update the existed record. I am getting unique key constraint violated error.
DECLARE
p_buid NUMBER;
p_ordernum NUMBER;
[Code].....
I'm trying to Create a procedure that generates:
Customer
Description of Order
Amount of Order
Total Of Order
As this Generates More than One Row I thought i would need to use cursor in order to store the results and then use them from the object So this is what i Coded:
CREATE OR REPLACE PROCEDURE UP_CustOrders
AS
/*I Am Selecting A Cursor Cause It Return More Than One Row*/
CURSOR ReportCursor IS
SELECT Company
[code]..
It appears It creates the procedure but im not sure why it has compilation errors Perhaps im missing some symbols? Here are the tables:
CREATE TABLE Customers
(CustNum NUMBER(10) NOT NULL,
Company VARCHAR2(20) NOT NULL,
CustRep NUMBER(10),
[code]...
I enter a form, do nothing, and exit_form. It closes but reenter again. The second try, does not cause this effect and ends properly. Checked and commented code around the form, but no use.What could be happening ?
View 4 Replies View RelatedThere are two servers A and B ,and i am maintaining one table suppose table1 and another table suppose table2 , DML triggers are made for both of these tables.The action of DML trigger is that the movement we are inserting values , suppose a table1 on server A so automatically the same set of value in table2 on server Bso my problem is that the movement i inserted value on table1 on server A ,
the same set of values were inserted into table2 of server B and as the values were inserted intotable2 , the same set of values were inserted into the tabld1 because of the trigger. How to stop the non ending loop which occurs due to the simultaneous firing of triggers and insertion of same records into both the table endlessly...
version :---Personal Oracle Database 10g Release 10.2.0.3.0 TNS for 32-bit Windows: Version 10.2.0.3.0
In weblogic server Axis2 is deployed and it is showing Active.And we have uploaded ODI invoke service also in AXIS2 succesfully and it is showing Active status also...By using the soap its generating the XML with below mentioned error..
some info:- We rae using ODI 10g.
Weblogic 10.3.5.0
XML GENERATING:--_
<xs:element name="inPhasesUptoAndIncludingPostDispatch" nillable="true" type="xs:anyType" />
<xs:element name="key" nillable="true" type="xs:anyType" />
<xs:element name="localPolicyAssertions" nillable="true" type="xs:anyType" />
[code]...
The XML page cannot be displayed Cannot view XML input using XSL style sheet. correct the error and then click the Refresh button, or try again later.
The operation completed successfully. Error processing resource [URL]
</xs:schema></wsdl:types><wsdl:message name="invokeScenarioMessage"><wsdl:part name="part1" element="ns...
It seems some issue with With Clause along with Row Number. Output is strange when it comes with SELECT MIN()... But Looks fine Once you add "order by" or "where" clasue inside WITH Clause
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
[code]....
But Output looks fine once you add ORDER By clause inside WITH clause
SQL> WITH EM AS
2 (
3 SELECT EMPNO, ROWNUM RN
4 FROM SCOTT.EMP T ORDER BY ROWNUM
[code]....
why it happens ?
#service setup:
$ srvctl add service -d dc1ext -s addval.dc1 -r "dc1ext1" -a "dc1ext2" -P BASIC
$ srvctl start service -d dc1ext -s addval.dc1
begin
DBMS_SERVICE.MODIFY_SERVICE(
service_name=>'addval.dc1',
aq_ha_notifications => true,
failover_method=>DBMS_SERVICE.FAILOVER_METHOD_BASIC,
[code]......
my attempt at verifying the TAF is working.
SQL> run
1 SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*)
2 FROM GV$SESSION
3* GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER
MACHINE FAILOVER_TYPE FAILOVER_M FAI COUNT(*)
---------------------------------------------------------------- ------------- ---------- --- ----------
anetapp02.dc2 NONE NONE NO 10
anetapp01.dc2 NONE NONE NO 10
app02.st2 NONE NONE NO 5
racdb02 NONE NONE NO 44
anetapp03 NONE NONE NO 10
anetapp04 NONE NONE NO 10
GNOME\APTSEA-6GKJHF1 SELECT BASIC NO 3
app01.st2 NONE NONE NO 5
anetapp05 NONE NONE NO 10
anetapp01 NONE NONE NO 10
anetapp03 NONE NONE NO 10
so it looks as thought the sessions are failing over, but my app guy says he get a few errors during the process.. i need it setup to move the current query over without returning an error.. do i not have this setup properly?
How do you setup a default style for displaying the output columns in sqlplus? The columns look clumsy when queried. They display correctly in Toad but, I wanted to learn through sqlplus and I am relatively new to Oracle world. Here is the DESC of table
SQL> DESC EMP.GROUP_TYPE
Name
Null? Type
-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------- -------- --------------------------------------------------------
------------------------------------------------------------
GROUP_TYPE
[code]...
The other day, we had a query run amok in our 2-node production cluster. The 3 temp files for the temp tablespace were all still set to autoextend unlimited, something I forgot to change after a recent upgrade. I created 3 new temp files and tried to delete the huge temp files. I did this from sqlplus with this commad:
ALTER TABLESPACE PSTEMP DROP TEMPFILE '+DATA/isis/tempfile/pstemp.291.641298061';
The huge files are still in ASM storage. dba_temp_files reports that the status of them is AVAILABLE but they have no RELATIVE_FNO. Grid Control reports their status as OFFLINE and their size as 0. They are actually close to 20 GB each.
I tested the above alter statement in two test instances, also RACed with ASM storage and the temp files were successfully deleted, but they were much smaller in size. At this point, how do I delete the three 20GB files from ASM in our production instance? Why didn't they delete the first time?
I have created a Table and a log of materialized view in an Schema SchemaAA of Server A
CREATE TABLE TABLA_TEST
(
TEST_PK NUMBER,
TEST_TEXTO VARCHAR2(50),
CONSTRAINT PK_TEST PRIMARY KEY (TEST_PK)
);
CREATE MATERIALIZED VIEW LOG ON TABLA_TEST;
I have created a DB Link from server B with user REPLIC
I have granted SELECT to REPLIC to the table and the log materialized View.
I have created a table in instanceBA of Server B
CREATE TABLA_TEST
(
HOSPCODE NUMBER(3),
TEST_PK NUMBER,
TEST_TEXTO VARCHAR2(50),
FECHA_INSERT DATE,
FECHA_UPDATE DATE,
FECHA_DELETE DATE,
CONSTRAINT TTEST_PK PRIMARY KEY (HOSPCODE, TEST_PK);
I have created a materialized view in schema SchemaBB of Server B with a trigger
CREATE MATERIALIZED VIEW WORKAREA.TABLA_TEST70
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS
SELECT TEST_PK,
TEST_TEXTO
[code].........
All schemas have the appropriate grants.
When inserting in TABLA_TEST (SchemaAA) and refreshing MView TABLA_TEST70 things go nicely.
But, when updating a record in the original TABLA_TEST and refreshing the MView the results in TABLA_TEST in SchemaBA are as if I have deleted the record (FECHA_DELETE is set to SYSDATE).
i have created a tree hierarchy....when the user clicks on any node,that particular form will open....But there is some problem...i click on form A,it opens.,...then i exit that form via a exit button on the toolbar....and then I click on form B on the tree....but first the form A only opens...and then again i exit form A, then form B will open..
i have written the following code in when_tree_node_selected trigger :
if condition then
call_form(path,no_hide,do_replace);
When I first access the form and query for records, it works as per the expectation and with scroll bar to scroll up and down. But when I do "View>Record>Last", it reaches to last record, but not able to use the scroll bar to scroll the records up(I am on last record, so can't do downward scrolling).
The scroll bar is displaying in Full, no room to scroll up down.It happens only when I do "View>Record>Last".
I installed oracle 10g R2 in my laptop with win7 ultimate(32-bit).But when i try to run sqlplus from command prompt...It retrns error..
C:Usersabhishek>sqlplus
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly.
I checked the oracle folder but there is db_1 and oradata folder inside product..but no admin folder.. Listener and database service is started..
while aim starting listener But Services in windows services showing listerner status as "started"
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.NTS.rethi.com)(P
ORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
[code]....
I have a query with FULL hint that is behaving in a strange manner. The query fetches around 700000 of data. Sometimes it fetches the data with the hint and sometimes it does not fetch any data with the hint and then I have to remove the hint and have to fetch the data. Below is the query,
select /*+ FULL(COMP_TM) FULL(TRANS_TM) FULL(INVC_TM) */
CUST_BE_ID ,
DISTR_BE_ID ,
FG_BE_ID ,
KIT_BE_ID ,
BG_ID_NO_BE_ID ,
[code]....
The statistics gathering activity of FACT_DLY_ALGND_SLS table takes around 5 hours to complete. It is a range partitioned table with subpartitions.
I applied the Jan 09 CPU to my database this past Saturday and now my daily schema export is not running properly:
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in WE8MSWIN1252 character set and UTF8 NCHAR character set server uses UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
[code]...