SQL & PL/SQL :: Declaring Global Variable Inside Package?
Sep 11, 2012
I want to declare global variable inside package. get the correct query. how to assign value for that variable.
SQL> create or replace
2 PACKAGE new_pack
3 AS
4 g_id employee_details1.employee_id%type;
5 PROCEDURE emp_project(
6 st_date IN DATE,
7 Prj_id out VARCHAR2,
8 prj_name out VARCHAR2,
9 Prj_location out VARCHAR2);
10
11 END new_pack;
12 /
Package created.
SQL> CREATE OR REPLACE
2 PACKAGE body new_pack
3 AS
4 PROCEDURE emp_project(
5 st_date IN DATE,
6 Prj_id OUT VARCHAR2,
7 prj_name OUT VARCHAR2,
8 Prj_location OUT VARCHAR2)
[Code] ..........
Warning: Package Body created with compilation errors.
SQL> show error
Errors for PACKAGE BODY NEW_PACK:
LINE/COL ERROR
-------- -----------------------------------------------------------------
12/12 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
constant exception
table LONG_ double ref
char time timestamp interval date binary national character
nchar
View 10 Replies
ADVERTISEMENT
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
Jun 17, 2010
how can i declare an array inside a stored procedure in Oracle. Right now, I have the following declaration.
procedure MarkLoanMappings(
p_AL_LA_ID in ACTIVE_LOAN.AL_LA_ID%TYPE,
p_AL_ASG_ID in ACTIVE_LOAN.AL_ASG_ID%TYPE,
p_AL_CFH_ID in ACTIVE_LOAN.AL_CFH_ID%TYPE,
p_Period in ACTIVE_LOAN.AL_PRCS_PERIOD%TYPE)
[code]....
When I try to compile it, I get the error "component EXISTS must be declared".
View 14 Replies
View Related
Jul 7, 2010
I'm currently looking for a way to declare a cursor in the 'declare' block using a previously defined variable that got its value from a query. For instance:
declare
my_company_id INTEGER := 'select c.company_id from company_table c where company_name='Wal-Mart';
cursor employees is
select e.employee_id from employees e where e.company_id = my_company_id;
Any way to do this?
View 4 Replies
View Related
Mar 29, 2013
I need to declare a record type in a package spec and used that define record type as a parameter in a procedure.
A procedure will call this package passing a record type . e.g xxtest.tmpprc(employee_rec employee_type);
(TYPE record_type_name IS RECORD (column_name1 datatype, column_name2 datatype, ...);
CREATE OR REPLACE package xxtest as
PROCEDURE tmpprc(trecordType IN VARCHAR2);
END;
View 16 Replies
View Related
May 5, 2013
if table exists then, i whould like to delete it and re create it again.to do so i have two method pls tel me which one i should follow.
declaraion of variable v_strTN twoice in 1) query, does the twoice declaration gives any benefit or it is redundant?
1)
DECLARE
v_strTN VARCHAR2(30);
BEGIN
BEGIN
SELECT TABLE_NAME INTO v_strTN FROM USER_TABLES
WHERE UPPER(TABLE_NAME)='ABC';
[code]....
View 3 Replies
View Related
Jan 15, 2012
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
View 4 Replies
View Related
Aug 20, 2010
I am writing a procedure that will be called from a java wrapper.
The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.
Create or replace proc_name ()
update table........
delete from ..........
CREATE GLOBAL TEMPORARY TABLE TSAAG
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)
insert into............
drop table TSAAG;
End;
creating a global temp table inside a procedure is expensive...
Do we have anything like creating table before and calling the instanse of it in procedure.
Do we have any alternatives to this..
View 3 Replies
View Related
Dec 6, 2011
how can i pass global variable from form to db trigger ?
i have this trigger:
Create Or Replace Drop Trigger Access_Group_Category_Priv_Trg
After Delete Or Insert Or Update
On Scott.Access_Group_Category_Priv
[Code].....
View 4 Replies
View Related
Aug 12, 2010
I want to define global variable in oracle form 6i....so this variable may i used anywhere in project...but i don't know where exactly global variable defined?
View 7 Replies
View Related
Apr 25, 2012
I'm making a function A that does many calls to procedures in an other package B. To make this function more readable, I'd like to specify synonyms for the procedures in B. I only need the synonyms inside this function, I don't want to make database synonyms.
For example:
Function get_all_employees return clob
is
v_emp clob;
begin
[Code]....
View 1 Replies
View Related
Nov 23, 2012
I was trying to create types inside package like:
-create or replace package pack1 is
type udtable is table of integer index by binary_integer;
end;
-create or replace package BODY pack1 is
type udtable is table of integer index by binary_integer;
end;
Although this is not exact query what i have doubt in my mind. is it possible to create object types/collections inside/with packages?
View 6 Replies
View Related
Jul 31, 2012
This assignment allows you to pick your own subject in which you Code a Package Header that contains the following:
Global Variable Specification
Type
Four (4) Procedure Specifications
Two (2) Function Specifications
Submit your code in a series of Notepad or Wordpad documents. Include the code for the assignment and the output that is realized from running the code. Note: You are responsible for creating the database on which the project will run. This includes the responsibility for loading the database with sufficient data.Grading for this assignment will be based on answer quality, logic/organization of the project to include the compilation, and execution of the project code.
View 2 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
Mar 24, 2013
i want to put a variable inside a view. create or replace view loading as
SELECT H.ORGANIZATION_ID ORG_ID,H.HEADER_ID,H.REQUEST_NUMBER, H.DATE_REQUIRED ISSUE_DATE,H.ATTRIBUTE1,
H.ORGANIZATION_ID,L.INVENTORY_ITEM_ID,SS.SALES_ID,S.SECONDARY_INVENTORY_NAME SUB_INV_CODE,S.DESCRIPTION SALESPERSON,I.SEGMENT1 ITEM_CODE,
[code]....
i want this condition to be like and to_date(H.DATE_REQUIRED,'dd-mm-rrrr') = to_date('Vdate','dd-mm-rrrr') i want Vdate as a variable parameter.
View 2 Replies
View Related
Dec 13, 2011
In current form, i use *pll file to pass Global.<variable name> to the form *.fmb
The problem is that if i copy a string of 4000 characters (which i need to) to Global.<variable name>, it will automatically cut a whole chunk to shorter string (less than 1000).
Is there a better way to that Global.<variable name> can hold 4000 characters?
View 1 Replies
View Related
Feb 6, 2010
I have more then one checkbox on my form,I want to get which one is checked on runtime and store it in global variable?
View 6 Replies
View Related
Nov 24, 2010
I am calling a select query inside a procedure but i need to set environment variable 'set linesize 200' inside that procedure but i am not able to create the procedure due to some error. I am attaching the procedure query here with:
before the select query i need to insert this environment variable : "set linesize 200"
create or replace
procedure TABLESPACE_USAGE
is
l_mailhost VARCHAR2(64) := 'ip address';
l_from VARCHAR2(64) := 'email id';
l_subject VARCHAR2(64) := 'TABLESPACE_USAGE1';
l_to VARCHAR2(128) := 'email id';
[code]......
View 2 Replies
View Related
Jul 8, 2011
I have below peice of SQL,Im trying to form the P_attribute1 value by using the Loop index(i) but the P_attribute1 value which i assigned in declaration not displaying in the output.
DECLARE P_attribute1 VARCHAR2(100) := '110000027';
P_attribute VARCHAR2(100);
l_cnt NUMBER := 10;
v_buffer VARCHAR2(500);
v_buffer1 VARCHAR2(500);
BEGIN
FOR i IN 1 .. l_cnt
LOOP
v_buffer := v_buffer || '' || 'P_attribute' || i || '' || '!';
END LOOP;
dbms_output.put_line('v_buffer :' || v_buffer);
END;
v_buffer :P_attribute1!P_attribute2!P_attribute3!P_attribute4!P_attribute5!P_attribute6!P_attribute7!P_attribute8!P_attribute9!P_attribute10!
View 6 Replies
View Related
Jun 18, 2013
I have two procedure , from first procedure having some ref cursor output.
from second procedure I need to call first procedure and i need to process ref cursor output from first procedure so I decide to use bind variable to process ref cursor output but it showing error .
can I define bind variable inside the procedure , then how can I define it .
SQL> CREATE OR REPLACE PROCEDURE emp_by_job (
2 p_job VARCHAR2,
3 p_emp_refcur OUT SYS_REFCURSOR
4 )
5 IS
6 BEGIN
[code].....
View 10 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
May 1, 2013
i 'm using APEX 4.2.1.00.08 and i 'm wondering if there is a way to translate "Function and Global Variable Declaration" textarea. Every other script textarea is available for translation, but not this.
I know that this is code is loaded on header but may contains important alert messages of global functions.
View 1 Replies
View Related
Jul 26, 2011
CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....
I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?
I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?
View 10 Replies
View Related
Sep 30, 2010
Where I am copying the data from one database to another I am getting this error.
ORA-01458: invalid length inside variable character string
This is applicable for only character string.
The source Database is
Oracle9i Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
The destination database is
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE 9.0.1.1.1 Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production
View 4 Replies
View Related
Mar 6, 2012
i want to store all rows of columns into single variable and then use in inside of SP
declare
CUR_REC SECURITY_TYPE%rowtype;
begin
select *
into CUR_REC
from SECURITY_TYPE;
[code]....
it return ORA-01422: exact fetch returns more than requested number of rows error. Is any chance to implemented above scenario in oracle 10g
View 4 Replies
View Related
Apr 16, 2009
I want to insert data into a RAW column in a table. The below code snippet is used for the same
OCIRaw **l_rawPtr = new OCIRaw*[10];
for(int i=0;i<10;i++)
{l_rawPtr[i] = NULL; }
int max = 0;
for(int i=0;i<10;i++)
{
OCIRawAssignBytes(l_environment, l_error, l_char, strlen((const char*)l_char), &l_rawPtr[i])
[code].....
When the statement is getting executed, i am getting the following error. Error - ORA-01458: invalid length inside variable character string
View 3 Replies
View Related
Jun 7, 2011
Identify a useful persistent package variable for the system.
If i am not wrong a package is like a box where you can add functions procedures inside of it!
variables are something like (x number :=2;) which those are part of functions and all.
2nd question is identify suitable package for the system that implements constraints.create the program package program.
View 3 Replies
View Related
Aug 20, 2013
Below SQL query (for making a view) is not working.
CASE WHEN IN_TYPE = PKG_CONSTANTS.INV_TYPE_1
then 'New' WHEN IN_TYPE = PKG_CONSTANTS.INV_TYPE_2 THEN 'old' ELSE 'N/A' end as
Status PKG_CONSTANTS.INV_TYPE_1 ==> PKG_CONSTANTS is a package and INV_TYPE_1 is a constant variable. INV_TYPE_1 CONSTANT VARCHAR2(10) := '55600'; INV_TYPE_2 CONSTANT VARCHAR2(10) := '55601';
error: oracle.dbtools.raptor.controls.sqldialog.ObjectActionController$EditorObjectActionListener .. some 10 similar errors are coming in Loggin Page of SQL Developer
View 11 Replies
View Related
May 12, 2011
How to sent Global variable between two forms in oracle form 10g ? ( with web.show_document )
View 3 Replies
View Related
Mar 29, 2013
I have an Type-object typeObj1 that consists another Type-object typeObj2. this def has another Type-object typeObj3. how to access variable declared inside typeObj3. I have syntax below for each Type.
CREATE OR REPLACE TYPE typeObj1
AS OBJECT
(
SYSTEM_IDENTIFER VARCHAR2(50),
PROCESS_TYPE VARCHAR2(50),
abc typeObj2
)
/
[Code]...
/I have tried to access the type-object in where clause in following way
FROM TABLE(CAST(I_typeObj1 AS typeObj1)) ITTPRC,
......
Where
.......
AND (ADDKEY.ADDTN_INFO_KEY_TYP_NM IN (SELECT ADDTN_INFO_KEY_TYP_NM FROM TABLE(ITTPRC.abc)))
AND (ADTINF.ADDTN_RQST_TYP_VAL_DT IN (SELECT ADDTN_RQST_VAL_DT FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VAL_NUM IN (SELECT ADDTN_RQST_VAL_NUM FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VALUE IN (SELECT ADDTN_RQST_VALUE FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
In this way i am able to access the variable inside typeObj3. But problem is i am getting error "ORA-01427 single-row subquery returns more than one row" when i pass more that one typeObj2.
I passed the values like this in proc execution.
T_T_A_I_V := typeObj3('asdasd',NULL,NULL),
typeObj3('String654',NULL,NULL),
typeObj3('abcdef',NULL,NULL));
T_T_A_I_I := typeObj2('CampusCode',T_T_A_I_V),
typeObj2('PlanNumber',T_T_A_I_V);
What i have done is removed typeObj3 from typeObj2, variables defined in typeObj3 are added in typeObj2 then i got ride of above error. is it correct
View 4 Replies
View Related