SQL & PL/SQL :: Executing Procedure - Command Not Properly Ended

Jan 18, 2012

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

View 8 Replies


ADVERTISEMENT

SQL Command Not Properly Ended?

Jan 13, 2013

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?

View 6 Replies View Related

ORA-00933 / SQL Command Not Properly Ended

Mar 15, 2013

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;

View 3 Replies View Related

SQL & PL/SQL :: ORA-00933 / SQL Command Not Properly Ended

Jan 13, 2011

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

View 13 Replies View Related

SQL & PL/SQL :: Insert Into Table - Command Not Properly Ended

Oct 23, 2012

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?

View 4 Replies View Related

SQL & PL/SQL :: Converting Column Into Row - Command Not Properly Ended

Aug 9, 2010

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

View 8 Replies View Related

SQL & PL/SQL :: Multiple Row Insert Not Working - Command Not Properly Ended

Mar 7, 2013

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:

View 8 Replies View Related

SQL & PL/SQL :: Alias Error - ORA-00933 SQL Command Not Properly Ended?

Mar 7, 2013

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

View 2 Replies View Related

Delete Statement Doesn't Work - SQL Command Not Properly Ended

Aug 28, 2007

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:

View 7 Replies View Related

SQL & PL/SQL :: Update Table With Join / ORA-00933 / Command Not Properly Ended

Jan 18, 2013

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]....

View 8 Replies View Related

PL/SQL :: Left Outer Join Drama - SQL Command Not Properly Ended?

Oct 16, 2012

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.

View 18 Replies View Related

PL/SQL :: Executing SQLPLUS Command From Stored Procedure

Aug 28, 2012

I am trying to execute the SQLPLUS command (CONNECT) from the stored procedure.It is throwing below error message.

Stored Procedure:

SQL> select user from dual
2 ;
USER
------------------------------
SYS
SQL> create or replace
2 PROCEDURE PROCEDURE1 AS
3 BEGIN
4 sqlplus sys/sys@D as sysdba;
5 --execute immediate 'create user 'kkk' identified by 'kkk';
6 END PROCEDURE1;
7 /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE PROCEDURE1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/9 PLS-00103: Encountered the symbol "SYS" when expecting one of the
following:
:= . ( @ % ;
The symbol ":=" was substituted for "SYS" to continue.
[code]....

View 29 Replies View Related

What DML / DDL Command A User Is Executing

Sep 9, 2012

is there anyway to know that what dml/ddl command a user is executing and on which table the impact is taking place?

View 2 Replies View Related

Dynamically Executing A Select Command

Aug 4, 2010

How to dynamically execute a select command whether it is retrieved a single record or multiple record in oracle database 10g. i have tried with the command execute immediate but it was not successful.

is it possible can i delete or drop multiple tables in a single drop and delete statement.

View 5 Replies View Related

Getting Errors While Executing Procedure

Oct 4, 2010

I am getting errors while executing the following block.

create TYPE c_Rec as object(a VARCHAR2(1), b NUMBER);
DECLARE
-- TYPE c_Rec as object(a VARCHAR2(1), b NUMBER);
TYPE c_collection IS TABLE OF c_Rec;
l_coll c_collection := c_collection();
BEGIN
[code]........

error

06530. 00000 - "Reference to uninitialized composite"

View 1 Replies View Related

PL/SQL :: Executing Procedure Using Variables

Feb 6, 2013

I'm trying to execute a procedure within a Declare/Begin/End statement and using variables as input parameters in my procedure but I keep on getting an Invalid SQL Statement Error. My code is below:

declare

  START_dt  VARCHAR2(30);
  END_DT VARCHAR2(30);
 
  begin
       
    SELECT '01-APR-2011'
    INTO END_DT
    FROM DUAL;
 [code]....

The table the procedure is pulling data from doesn't have proper date/time stamps but my procedure takes the varchar dates above and turns them into dates in the procedure so the input date parameters are left as just string characters.

View 6 Replies View Related

SQL & PL/SQL :: Executing Server Procedure From Oracle

Jul 31, 2013

How to execute Sql Server procedure from Oracle.I have created a dblink and when calling directly to that procedure it is throwing error.

--creationg sqlserver procedure
create proc getRecords
as
print 'testing';
--creating db link
create database link remote connect by ss identified by ss using 'remote';
[code]....

I have found DBMS_HS_PASSTHROUGH package is capable of doing it but not sure how to do.
Nathan

View 11 Replies View Related

PL/SQL :: Executing Procedure Having Refcursor As Out Parameter

May 13, 2013

I've the following package having a procedure with OUT parameter as refcursor .The following is the sample example having same structure with different table name,exception handling in my dev environment

CREATE OR REPLACE PACKAGE TEST_PACK
  IS
Type refCursor  is  REF CURSOR;
PROCEDURE TEST_PROC (out_data out refCursor);
END;
[code]...

i am getting the follwoing errror
ORA-00932: inconsistent datatypes: expected - got.

View 3 Replies View Related

Forms :: Executing Procedure Based On Variable Name

Apr 1, 2011

I need to execute a procedure based on a value in a form. So the procedure name will be changing for value selected in a list.

I need to know a method where i could store the procedure name in a table and when ever i select a value from the list, the respective procedure needs to be executed.

View 1 Replies View Related

SQL & PL/SQL :: Executing Dynamic Procedure With Record Type

Jul 26, 2012

I'm not attaching any tables / data..etc...I just want to know how to pass the record type to a procedure (which are actually obtained from a table) -- see ** below where I'm getting an error..Need to pass the whole record type "l_shl_order_msg"

CREATE OR REPLACE PROCEDURE CM_BUILD_MSG_PRC (P_IN_BLD_MSG_CURSOR IN SYS_REFCURSOR,
P_OUT_BLD_MSG_CURSOR OUT SYS_REFCURSOR)
IS
l_shl_order_msg CRAE_INTERFACE.GLB_VAR_PKG.deid_SHELL_order_typ;
V_MSG_SHELL_NAME VARCHAR2(1000);
V_MESG_TEXT_SEGMENT VARCHAR2(1000);
V_TEXT VARCHAR2(1000);
V_MSG_TEXT VARCHAR2(4000);
V_MSG_FINAL_TEXT VARCHAR2(4000);
V_MSG_PROC VARCHAR2(1000);
V_SQL VARCHAR2(4000);
V_CNT NUMBER;
L_STATUS VARCHAR2(100);
L_REASON VARCHAR2(1000);
[code]...

I get an error saying that "wrong number or types of arguments in call to ||"..Not sure how to pass record type dynamically...

View 10 Replies View Related

SQL & PL/SQL :: ORA-06550 / Error Executing Stored Procedure

Nov 18, 2010

Procedure: CREATE OR REPLACE procedure test (a number, b varchar2) is

begin
dbms_output.put_line(a ||'->'||b);
end;

Anyonymous Block:

begin
exec test(1,'m');
end;
/
When i run this i am getting this error
ORA-06550: line 2, column 7:
PLS-00103: Encountered the symbol "TEST" when expecting one of the following
[code]...

View 5 Replies View Related

SQL & PL/SQL :: Executing A Procedure Containing A Global Temporary Table

Mar 3, 2010

create or replace procedure test
as
stmt varchar2(2000);
begin
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tt_Local(ID VarChar2(38)) ON COMMIT PRESERVE ROWS';

stmt := 'INSERT INTO tt_Local SELECT cardnumber FROM cards';
execute immediate stmt;
end;

-- when am trying to execute this

begin
test;
end;
-- showing ora-01031, insufficient privileges.

View 9 Replies View Related

Executing Stored Procedure / Function From Database Link?

Feb 24, 2011

I have 2 databases:

Database A
Database B

In Database A I have a series of config tables and stored procedures/functions In Database B I have a lot of tables.

I would like to execute my stored procedures and all associated functions in database A on my data in database B.

Now I've figured out that creating a database link enables me to do SQL selects on data in both A and B...but how do I run SP/Funcs ? I've read something about packages but not sure if I'm heading in the right direction.

Do I need to create a simple synonym ? Can I use the existing DB link ? or is there a special way of calling them, or...

I like the A/B database set up since I can keep battle tested code in one location and have it work across multiple dbs...saves me having to create everything in every database.

View 2 Replies View Related

SQL & PL/SQL :: How To Send Email In HTML Format By Executing Anonymous Procedure

Oct 11, 2010

How to send email in HTML format as an attachment/email body by executing oracle anonymous procedure

View 1 Replies View Related

Client Tools :: Error / ORA-00900 / Invalid SQL Statement After Executing Procedure

Jan 3, 2011

CREATE OR REPLACE PROCEDURE test
IS
CURSOR cusers IS SELECT user_name, user_date FROM users;
uname users.user_name%TYPE;
udate users.user_date%TYPE;
BEGIN
OPEN cusers;

[code].....

When I try to execute this procedure I get following error:

ORA-00900: invalid SQL statement

Compilation of procedure is successful.

SQL code for creating the USERS table is here:

CREATE TABLE "USERS"
("USER_ID" NUMBER(10,0) NOT NULL ENABLE,
"USER_NAME" VARCHAR2(50) NOT NULL ENABLE,
"USER_EMAIL" VARCHAR2(50) NOT NULL ENABLE,
"USER_PASS" VARCHAR2(50) NOT NULL ENABLE,
"USER_DATE" DATE NOT NULL ENABLE,
CONSTRAINT "USERS_PK" PRIMARY KEY ("USER_ID") ENABLE
)

View 3 Replies View Related

SQL & PL/SQL :: Copy Command In A Procedure

Feb 22, 2010

I got to pull 30M data into local DB from a remote DB. As of now we have procedure to perform this task using an

Insert /*+append*/ table1
select *
from rdb_view;

I observed using the copy command in sql plus has taken very less time than the procedure.Can we use the Copy command inside the PL/SQL block? I tried with dynamic SQL but It is not allowed I guess.

View 16 Replies View Related

SQL & PL/SQL :: Using EXECUTE IMMEDIATE For DDL Command In Procedure For Password Change?

Nov 6, 2011

I have a procedure in this procedure i use.

EXECUTE IMMEDIATE 'alter user '||use||' identified by '||modp ||' replace '||oldp;

but when i execute it show insufficient privilages but i create for this procedure as public.and grant execute facility to the user.

View 6 Replies View Related

Forms :: FRM-40404 Post Command In Save Procedure To Apply Some Record In Database Temporary

Sep 26, 2011

I am working on form 10g, here i am using post command in save procedure to apply some record in database temporary.

But Frm-40404 Message raise during saving record i need post commond but i dont want to this message should popup, is there any method to avoid the message only?

View 7 Replies View Related

Brtools Command Versus Oracle Command For Shutdown And Startup (statistics)

Sep 13, 2013

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 Related

Server Administration :: Server Installation Gets Abruptly Ended For Client Get TNS Errors

Jan 11, 2011

after 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.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved