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
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
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
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
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
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
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
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
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
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
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
Dec 21, 2012
how do you declare a variable in a store procedure
View 11 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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