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


ADVERTISEMENT

PL/SQL :: How To Find Difference Between Timestamp In Two Variables

Aug 1, 2012

how to find the difference between time stamp in two variables.

Say in a java file i am getting start time and end time i am storing it in a string variables now i need to know what is the time difference of them in millisecond

Srting1 of java will give me some thing like
StartTime =2012-08-01 15:14:20.36 +5:30
Srting2 of java will give me some thing like
EndTime =2012-08-01 15:14:21.254 +5:30

I need to take the millisecond difference between them the code i have written in java and connecting oracle database and doing some logic's in java so i need to capture start time and end time of code.!

View 22 Replies View Related

SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?

Mar 11, 2010

What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.

View 2 Replies View Related

Application Express :: LOV Select List / How To Create Query With Begin And End

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

SQL & PL/SQL :: Local Variables Are Bind Variables

Apr 27, 2012

Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.

Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?

Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=p1;
END;
------------
Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
v1 varchar2(100):=p1;
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=v1;
END;

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

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 :: 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 :: 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 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

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 :: 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

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

PLS-00201 / Identifier Must Be Declared

Oct 30, 2008

I am trying to execute the below and getting the error:

PLS-00201: identifier 'DBMS.REFRESH' must be declared

The mv is in my schema.. so I am owner. I thought I once read that a dbms.refresh had to be in a block.. so I added the begin and end.. but that didn't resolve it.

I have tried two ways, both resulting in the above error:

execute dbms.refresh('mv_bb_basket');
begin
dbms.refresh('mv_bb_basket');
end;

View 1 Replies View Related

SQL & PL/SQL :: PLS-00302 / Component Must Be Declared

May 10, 2011

I'm getting above error when I run following code

BEGIN
OPEN c_report_data;
FOR rec in c_report_data
LOOP
IF
rec.ESR_SALESMAN <> lv_rep_num THEN
ln_ttl_rep_cnt := ln_ttl_rep_count + 1 ;
ELSE
ln_ttl_rep_cnt := ln_ttl_rep_cnt;
END IF;

View 9 Replies View Related

SQL & PL/SQL :: Bind Variable OLD Not Declared

Nov 28, 2011

I am facing the same problem: SP2-0552: Bind variable "OLD" not declared. When my script create_trigger.sql is executed,there is no error but when i execute it inside a pl/sql block it get above error...In the trigger we are using if conditions

if(:new.sumthing=1)and (:old.sumthing=0)the
do this..

View 1 Replies View Related

SQL & PL/SQL :: PLS-00201/ Identifier Must Be Declared

Aug 3, 2011

I need export data from remote server i.e. in Oracle8i envtt. but in my machine having client oracle10g.

I m using command from local machine :

expdp test/test@test_env_tns dumpfile=abcd_dat.dat logfile='abcd.log

then i m getting error :

UDE-00008: operation generated ORACLE error 6550
ORA-06550: line 1, column 52:
PLS-00201: identifier 'SYS.DBMS_DATAPUMP' must be declared
ORA-06550: line 1, column 52:
PL/SQL: Statement ignored
[/code]

Note : I m executing command from my local machine command prompt

View 8 Replies View Related

PLS-00201 - Identifier Must Be Declared

Jul 16, 2012

I am using oracle 10g database (10.2.0.1.0), which trouugh an error while i am trying to EXPORT.

View 4 Replies View Related

Using Declared Variable Within Select Statement?

Apr 5, 2011

here is what i am trying to do: im as using oracle 8 with sqltool

i have a Very large query. and i notice that many things are repeating. so i want to add them to a variable, instead of re-typing them. for example:

select SomeID from SomeTable;

i want SomeID to be put into a variable. but i still want to be able to get a normal select query at the end so that i can see the returned value:

i tried things like:
declare x number;
begin
set x=45454
select x from SomeTable;
end;

but could not get it to work.

View 2 Replies View Related

Forms :: Error 201 Identifier Must Be Declared?

May 15, 2013

I am importing excel data to oracle table by column mapping using forms 6i. I am using ole2 package and i also created one package.which is given below. My code gets compiled, I am unable to insert record to the table.Values are passed to the procedure, i am able to see the values while debugging, but all values are seen in record 1 (as looping is there) all other remaining records are empty.i.e records are changing but seen only in record 1 and all other records are empty.I also get message 'DATA INSERTED INTO THE TABLE' but when i check it with sql, the table is empty.

PROCEDURE get_excel IS
APPLICATION OLE2.OBJ_TYPE;
WORKBOOKS OLE2.OBJ_TYPE;
WORKBOOK OLE2.OBJ_TYPE;
WORKSHEETS OLE2.OBJ_TYPE;

[code]...

View 29 Replies View Related

SQL & PL/SQL :: Trigger - Identifier Must Be Declared Error?

Jul 19, 2011

I'm trying to create trigger:

CREATE OR REPLACE TRIGGER TBI_ID
BEFORE
INSERT
ON zoo.risk_eval#ctypein

[code]...

Here is error message:

ORA-24344: success with compilation error
2/14 PLS-00201: identifier 'ZOO.RISK_EVAL_CTYPEIN#ID_SEQ' must be declared
2/3 PL/SQL: Statement ignored

View 10 Replies View Related

SQL & PL/SQL :: SP2-0552 / Bind Variable NEW Not Declared

May 7, 2013

I am trying to create a simple trigger for practice. I am getting SP2-0552: Bind variable "NEW" not declared. error.

Below is the trigger script:

create or replace User_Cxt_Trigger
AFTER
INSERT or UPDATE OR DELETE ON NAME_VALUE_PREFS
referencing new as new old as old
FOR EACH ROW

[code]....

View 17 Replies View Related

SQL & PL/SQL :: PLS-00201 / Identifier DBMS_LOCK Must Be Declared

Dec 9, 2011

i m getting this DBMS_LOCK error, i have DBA privilege

Declare
l_count integer := 0;
l_count_t integer := 0;
error_msg varchar2(4000);
begin
select count(*) into l_count from user_objects where object_name='PAKAGE_NAME' and object_type='PACKAGE';
if l_count=0 then
DBMS_OUTPUT.PUT_LINE('ERROR: Package PAKAGE_NAME does not exist in the database');

[code].....

ERROR: Package PAKAGE_NAME or its body is in INVALID state
PLS-00201: identifier 'DBMS_LOCK' must be declared

PL/SQL procedure successfully completed.

View 3 Replies View Related

Replication :: PLS-00201 Identifier Must Be Declared?

Dec 28, 2007

I am getting following error

SQL> BEGIN
2
3 DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
4
5 gname => 'ORCL_REP',

[code]...

ERROR at line 1:
ORA-06550: line 1, column 8:
PLS-00201: identifier 'SYS.DBMS_REPCAT_UTL2@ORCL.BABU' must be declared
ORA-06550: line 1, column 8:

[code]...

View 1 Replies View Related







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