SQL & PL/SQL :: Error On DECLARE Statement?

Dec 19, 2011

I am getting an error when trying to run this DECLARE statement and I'm not sure why. I'm fairly new to PL/SQL and have looked up how to format a DECLARE statement and this seems right to me.

DECLARE

v_eventstart date;
v_maxhbdate date;
v_6monthPOS date;

[code]...

The error is stating "encountered the symbol "end-of-file" when expecting one of the following... " and is refering to the beginning of this statement.

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Declare Returned Value - Getting Error

Jan 28, 2013

while trying to execute this

declare ret_val number;
begin
exec p_buildinfo('252657020001', to_date('20120820','YYYYMMDD'),to_date('20120928','YYYYMMDD'),ret_val, 0);
DBMS_OUTPUT.PUT_LINE('Value Returned Is : '||ret_val) ;
end;

I getting the below error

ORA-06550: line 3, column 10:
PLS-00103: Encountered the symbol "P_BUILDINFO" when expecting one of the following:

:= . ( @ % ;
The symbol ":=" was substituted for "P_BUILDINFO" to continue.

the procedure structure is

CREATE OR REPLACE
PROCEDURE p_buildsinfo ( var_p_cod CHAR := NULL,
var_p_dat_from DATE := NULL,
var_p_dat_to DATE := NULL,
po_var_l_nxt_seq IN OUT NUMBER,
var_p_consol_flg NUMBER default 0
)

View 14 Replies View Related

SQL & PL/SQL :: Declare And Set Parameters

Dec 22, 2010

the same query i put below but in oracle.

I'm trying to declare and set parameters in oracle

I'm trying to do something similar as the follow SQL Server Sentence:
------------------------------------------------------
DECLARE

@Year SMALLINT,
@City varchar(255)

SET @Year = 2010
SET @City = ''NY','NJ''

[Code]....

View 3 Replies View Related

PL/SQL :: Case Statement - Getting Error?

Oct 24, 2013

select iloan_code,inst_due_date,paid_flag,late_fee,case  late_fee  when sysdate-inst_due_date between 1 and 10 then  10              when sysdate-inst_due_date > 10 and late_fee <>10 then 5              when sysdate-inst_due_date > 10 and late_fee = 10 then 15              else 0 end as new_late_fee from st_il_schedule where paid_flag='N'; i am getting error

View 3 Replies View Related

SQL & PL/SQL :: Have Exception In Declare Block

Feb 20, 2012

Can we have any exception in DECLARE block (shown are just for example)?

Declare
exception occured
begin
code
end;

declare
variables;
begin
declare
variables;
begin
code;
end;
end;

View 6 Replies View Related

SQL & PL/SQL :: Declare Identifier In Oracle 10g?

May 22, 2013

When I am running script in oracle 10g ,getting error message as "PLS-00201: identifier 'UTL_SMTP' must be declared".

how to declare identifier 'UTL_SMTP' in oracle 10g or how to give execute access to current user

View 6 Replies View Related

SQL & PL/SQL :: Create Tables Error Statement

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

SQL & PL/SQL :: Creating DDL Statement Giving Error?

Apr 29, 2011

I have a question. If i insert some values to a table and then write a create statement. But if the create statement gives me error (eg: table name already exist). And without commiting if i come out the session will the insert commit?

View 9 Replies View Related

PL/SQL :: 00900 Invalid SQL Statement Error?

Jul 30, 2012

I'm teaching myself to write stored procedures, working in TOAD 10.5 with Oracle 10g. I keep getting error ORA-00900: invalid SQL Statement. Here's the code, cut down to bare minimum sample size. I can't see where I'm doing anything wrong.

If I cut out the cursor (taking it down to just "Begin" and "End"), it does run, but I can't see anything wrong with the cursor.

CREATE OR REPLACE PROCEDURE IN_PROCESS_CASES_BOS
IS
V_HELLO VARCHAR2(10);
CURSOR C_MAIN IS
SELECT 'HELLO' FROM DUAL;

[code]....

View 6 Replies View Related

Insert Into Statement - Getting ORACLE Error?

Jan 22, 2012

I am creating the following two tables...no issues here:
CODECREATE TABLE COURSE_SECTION
  (
  Csecid NUMBER(8) CONSTRAINT COURSE_SELECTION_NUMBER_pk PRIMARY Key,
  Cid NUMBER(6) NOT NULL CONSTRAINTS COURSE_SELECTION_Cid_fk REFERENCES COURSE,
  Termid NUMBER(5) NOT NULL CONSTRAINTS COURSE_SELECTION_Termid_fk REFERENCES TERM,
 
[code]...

The issue I am having is actually inserting data into the table:
CODEINSERT INTO ENROLLMENT
  VALUES (100, 1000, 'A' );
INSERT INTO ENROLLMENT
  VALUES (100, 1003, 'A' );

[code]...

But I get an ORACLE error of

ORA-02291- integrity constraint (User1.ENROLLMENT_CSECID_FK) violated - parent key not foundHow can the parent key not be found when I have it declared/created in the above statement?

View 3 Replies View Related

PL/SQL :: Declare Variable As Row Type For A Table?

Jul 19, 2012

Can I declear a variable in PLSQL as the row type for a table, who's name is unknown during compile time, but will be determined when the PLSQL is runnning. The code is like following:

Procedure operTable( tableName IN VARCHAR2)
IS
TYPE ty_Row IS tableName%ROWTYPE
v_Row ty_Row;
CURSOR v_quey_cur

[code]...

View 5 Replies View Related

PL/SQL :: Declare Variable In Stored Procedure

Dec 21, 2012

how do you declare a variable in a store procedure

View 11 Replies View Related

Using Of ROWNUM In Insert Statement Gives Error In Oracle 11g

May 24, 2011

I have a query regarding the use of rownum inside the insert statement.

For example, I have a sample table as: sample1(aa date, bb number);

Insert
INTO sample1
VALUES (SYSDATE, ROWNUM);

this statement is working fine in Oracle 9i but gives error in Oracle 11.2.0.1. The error is ORA-976 ,

Why this error coming in Oracle 11g and how to resolve it?

Our Environment: UNIX AIX 5.3, Oracle 11.2.0.1 database

View 1 Replies View Related

SQL & PL/SQL :: Invalid Identifier Error In Select Statement?

Mar 18, 2011

Where I run this update query, I get the error:

Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:

UPDATE XXX_CURR_EOM SET ID =
(select CAPITALPLAN.ID from capitalplan, XXX_CURR_EOM
where
XXX_CURR_EOM.ID = CAPITALPLAN_id)

don't know what it means.

View 10 Replies View Related

Forms :: Getting Error While Using MERGE Statement In Application

May 19, 2010

I am using MERGE statement first time in my application but I am facing error.

begin
merge into store.comm_dept a
using factory_data.comm_dept b
on (a.cd_dcode = b.cd_dcode)
when matched then
[code]........

ERROR IS: encountered the symbol 'INTO' when expecting on of the following:
:=.(@%;

View 7 Replies View Related

SQL & PL/SQL :: Declare A User Define Exception Name As Per Defined Name?

Jan 25, 2011

Can we able to declare a user define exception name as per defined exception name?Ex: we have predefined exception called :"NO_DATA_FOUND "

Now in my PL/SQL block can i able to declare "NO_DATA_FOUND" like

Begin
no_data_found exception;
//statements
raise no_data_found;
end

View 6 Replies View Related

SQL & PL/SQL :: Difference Between Variables Declared Under Begin And Declare?

Feb 24, 2013

i just what to understand the differnce between the variable declared under the Begin and variable declared under Declared. to understand this i tried a sample plsql procedure as show below

create or replace
PACKAGE BODY TEST_DEV AS
PROCEDURE TESTING(I_NAME VARCHAR2,
I_AGE NUMBER) AS
INTEGE binary_integer := 100;
[Code] .......

the Line L_nums1 NUMBER := 100/0; is throwing error(PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following:)

if i remove the line L_nums1 NUMBER := 100/0; and just declare the variable out side the begin , and using inside the same variable inside the begin works fine

Why question is why we are not able to decalre, initialize and use the same Number variable insde the begin

View 4 Replies View Related

SQL & PL/SQL :: How To Declare A Variable Which Reference Level Type

Aug 5, 2010

I have a problem :

In package head I declare a variable , which reference 'level' type , but I don't know how to declare the variable , because I don't know what type of 'level'.

View 9 Replies View Related

Truncate Statement Failing Randomly With ORA-03291 Error

Mar 31, 2009

My question is on TRUNCATE statement.

So if the truncate syntax goes like
TRUNCATE { TABLE [ schema. ]table
[ { PRESERVE | PURGE } MATERIALIZED VIEW LOG ]
| CLUSTER [ schema. ]cluster
}
[ { DROP | REUSE } STORAGE

Where it is really not mandatory to add (DROP STORAGE) to the truncate statement knowing fully well the Watermark is dropped. Lately some of my Truncate statements that are part of Plsql package have been failing randomly with a ORA-03291 error. I iteratively walk through a list to truncate some tables and 1 or 2 out of 10 randomly fail. But work fine when I rerun. Its been a night mare for couple of weeks now. This code was working alright for last several years now in 10g and previously 9i. Do you believe some changes to settings on the Oracle database or to blame for ?

ORA-03291: Invalid truncate option - missing STORAGE keyword
ORA-06512: at "xxxx.xxxxxxxxx", line 35
ORA-06512: at line 2

View 7 Replies View Related

SQL & PL/SQL :: Oracle Error For Invalid Identifier In Execute Immediate Statement

Apr 5, 2011

I have a procedure as below. To sum up the procedure in one line it dynamically forms a string to get the values of the type which is passed as an input to the procedure.

I call the procedure as

exec exec_imm( exec_imm_t(1,'asd','1/2-34'));

ERROR:
Error starting at line 9 in command:
exec exec_imm( exec_imm_t(1,'asd','1/2-34'))
Error report:
ORA-00904: "P_TYPE_DATA"."ADDRESS": invalid identifier
ORA-06512: at "PTK_ADM.EXEC_IMM", line 26
ORA-06512: at line 1
00904. 00000 - "%s: invalid identifier"

NOTE:
When I try to execute the procedure with the execute immediate statement I get the above error. But when I execute the select statement which is nothing but the value in v_type_data directly (as seen in the comments in the code below) there is no error. But when the same v_type_data is used in execute immediate, I get an error.

CREATE OR REPLACE procedure exec_imm(p_type_data exec_imm_t)
AS
v_type_str CLOB := NULL;
v_type_data CLOB := NULL;
v_type_name VARCHAR2(25) := NULL;
BEGIN
[code]......

View 6 Replies View Related

SQL & PL/SQL :: Merge Statement In Procedure - Internal Error Code

Feb 9, 2011

I have a merge statement in one procedure , this job runs on daily . It is running successfully since Jan-19-2010 and giving the below error now.

ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbgcur_9], [4224596], [1],
[4294967250], [2], [], [], []

It is giving error in the beginning of the MERGE statement.

View 11 Replies View Related

Ora-00904 Error For NDS Dynamic Alter Table Statement

Nov 30, 2011

how to play around with NDS dynamic sql and I'm trying to add a column on the fly.Basically the procedure is trying to take a table name, column name, and eventually a data type and adds it to a table.

It works fine without the bind variable for the column name, accepting the table name on the fly.As soon as it tries to use the column name I get an ORA-00904 invalid identifier exception.

Here is the procedure I'm using

CODEcreate or replace
procedure test(tbl_name varchar2, col_name varchar2) IS
qry varchar2(500);
begin

[code]....

Here is how I'm executing it.

CODEexecute test(tbl_name => 'BB_SHOPPER', col_name => 'MEMEBER');

View 3 Replies View Related

Forms :: How To Declare Global Variable And Call Same In Other Form

Oct 6, 2008

I want to call a value from one from to another form in d2k forms 4.5. I think this can be done only by declaring a global variable. I don't know how to declare global variable and also calling the same in other forms.

View 14 Replies View Related

SQL & PL/SQL :: How To Declare Global Variable In Package Body (Not Spec)

Feb 26, 2013

I want to know how we can declare a Global Variable in Package body(Not Spec), So that i can use it in any procedures or function(Defined in same package).

View 11 Replies View Related

SQL & PL/SQL :: Hide Declaration Or Declare It Inside The Body Of Package

Mar 11, 2010

I have a package which has couple of Procedures and functions. I use some constants between the functions and procedure and also functions r called from the other procedure and stuff...

So I declared all these in the header

Like this

CREATE OR REPLACE PACKAGE "PROCESS_HISTORY" IS
type table_name_i is varray(100) of VARCHAR2(30);
c_add constant number := 1;
c_del constant number := 2;
c_select_frm_tmp constant number := 1;
[code]......

Now is there anyway I could hide the stuff that I don't want outside people to see? or can I declare them (constants and some functions) some where inside the body and use them?

View 9 Replies View Related

Precompilers, OCI & OCCI :: Variable Declarations In DECLARE SECTION?

Mar 9, 2010

I am getting some odd results from a Database Insert Function. The function receives an Array of elements. The elements are a defined structure (containing around 21 data items). I need to insert these records into an Oracle table.For each element of the Array the function reads the structure into a local c structure of the same type.

I then go through this local structure and get a copy of the data into local variables declared in the EXEC SQL BEGIN DECLARE SECTION of the function. I then use the local vars to do the insert, using null INDICATOR variable to handle those variables that could be empty. The local variables look like this....

EXEC SQL BEGIN DECLARE SECTION;
int dbServiceTypeId;
int dbRecordType;
char dbRbmCustRef[MAX_CUST_REF_LEN];
int dbServiceSeq;

[code]...

BUT.. when I uncomment the dbCOS variable (even though I don't populate it or try to include it in the insert) I get the following in the table (The RATE value goes missing completely and the multiplier is wrong)...

ID SERVICE CODE R/T RATE MULTI
41325-SCODE-1084 1 542139762
11326-SCODE-1086 1 542139762
11326-SCODE-1086 2 542139762
21327-SCODE-1087 1 542139762
21327-SCODE-1087 2 542139762
21327-SCODE-1087 3 542139762
21327-SCODE-1087 3 542139762

However, a printf statement just before the insert based that returns the variables shows the following...

Service Type ID: '4'
Record Type ID: '1'
Service Cust Ref: '1325-SCODE-1084'
Service Unit Rate: '1200.00'

Indeed, for this record the Multiplier doesn't even get populated. The other odd thing is if I recomment the dbCOS but remove the dbOraDateFmt variable definitions, it corrupts the data again, though different fields. I can't understand why individual local variables are behaving this way. Is this a problem with the way variables are declared in this section?

View 3 Replies View Related

Forms :: Declare Message For @ Is Not Type In Email Address

Oct 24, 2011

i want if user is not type the @ in email text box than from show the message "check your email address" but i cant do that

declare
ok_flag number :=0;
begin
ok_flag :=instr('emailaddress','@');
if
ok_flag < 1 then
message('invalid email');
else
message('thanks');
end if;
end;

View 8 Replies View Related

Server Utilities :: Schema Impdb Invalid Statement Error

May 11, 2010

I am trying to import a schema backup using sqldeveloper and getting this syntax error:

ORA-00900: invalid SQL statement

Here is the import statement:

impdp sys/*****@ccpsp schemas=ccpsp_staging directory=dmpdir DUMPFILE=CCPSP_USERCCPSP_STAGINGSCHEMABACKUP_07MAY2010.dmp LOGFILE=IMPDP_CCPSPStagingSchemaImport.log;

View 3 Replies View Related

Server Utilities :: Privileges Error - Executing INSERT Statement For Table

Jul 9, 2013

I am using OWB to load a table which write sql loader command. When running the load i am getting below error.

SQL*Loader-643: error executing INSERT statement for table "STG_EWORK"."STG_ISF_LUCC"

I am unable to guess which privileges is missing.

My control file as below

OPTIONS (SKIP=2,BINDSIZE=50000,ERRORS=0,ROWS=200,READSIZE=65536)
LOAD DATA
CHARACTERSET WE8MSWIN1252
INFILE '\devora003.dev.tfl.localPDWPDW_SourceISF_LUCC_Loadfile.csv'
CONCATENATE 1
INTO TABLE "STG_EWORK"."STG_ISF_LUCC"

[code]....

View 4 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







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