Home » SQL & PL/SQL » SQL & PL/SQL » How we can declare a global Variable in Package Body(Not Spec) (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit,OS: XP Window )
How we can declare a global Variable in Package Body(Not Spec) [message #578242] Tue, 26 February 2013 01:09 Go to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

HI, 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).
Re: How we can declare a global Variable in Package Body(Not Spec) [message #578243 is a reply to message #578242] Tue, 26 February 2013 01:13 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
declare it before the first procedure or function..
Re: How we can declare a global Variable in Package Body(Not Spec) [message #578244 is a reply to message #578242] Tue, 26 February 2013 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I advice you to read and study:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: How we can declare a global Variable in Package Body(Not Spec) [message #578245 is a reply to message #578243] Tue, 26 February 2013 01:17 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

I will creating it Dynamically in one of procedure.Suppose i have package PK1 and procedures defined as
P1(variable can be used here)
P2(Here i need to declare dynamically)
P3(variable can be used here)
Re: How we can declare a global Variable in Package Body(Not Spec) [message #578247 is a reply to message #578245] Tue, 26 February 2013 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 58934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a REAL code.
What does mean declaring a variable dynamically?

Regards
Michel
Re: How we can declare a global Variable in Package Body(Not Spec) [message #578251 is a reply to message #578247] Tue, 26 February 2013 02:18 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

--TABLES---
CREATE TABLE emp (
   empno    INT PRIMARY KEY,
   ename    VARCHAR(10),
   job      VARCHAR(9),
   mgr      INT NULL,
   hiredate DATE,
   sal      NUMERIC(7,2),
   comm     NUMERIC(7,2) NULL,
   dept     INT);
/
begin
insert into emp values 
  (1,'JOHNSON','ADMIN',6,SYSDATE,18000,NULL,4);
insert into emp values 
  (2,'HARDING','MANAGER',9,SYSDATE,52000,300,3);
insert into emp values 
  (3,'TAFT','SALES I',2,SYSDATE,25000,500,3);
insert into emp values 
  (4,'HOOVER','SALES I',2,SYSDATE,27000,NULL,3);
insert into emp values 
  (5,'LINCOLN','TECH',6,SYSDATE,22500,1400,4); 
end;
/
CREATE TABLE dept (
   deptno INT NOT NULL,
   dname  VARCHAR(14),
   loc    VARCHAR(13));
/
begin
insert into dept values (1,'ACCOUNTING','ST LOUIS');
insert into dept values  (2,'RESEARCH','NEW YORK');
insert into dept  values (3,'SALES','ATLANTA');
insert into dept  values (4, 'OPERATIONS','SEATTLE');
end;


Creating the colletionsTypes here
CREATE OR REPLACE TYPE OBJ_EMP AS OBJECT
(
  EMPNO    INTEGER,
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      INTEGER,
  HIREDATE DATE,
  SAL      NUMBER(7, 2),
  COMM     NUMBER(7, 2),
  DEPT     INTEGER
);

CREATE OR REPLACE TYPE TYPE_EMP IS TABLE OF OBJ_EMP;


Package

CREATE OR REPLACE PACKAGE PK1 AS
  PROCEDURE P1;
  PROCEDURE P2(P_TAB VARCHAR2, P_TYPE VARCHAR2);
  PROCEDURE P3;
END;


BODY

CREATE OR REPLACE PACKAGE BODY PK1 AS
  PROCEDURE P1 IS
  BEGIN
--- i will fetch the reocrds here using table function 
--for i in M_BULK.first..M_BULK.last loop  like that 
NULL;
  END;

  PROCEDURE P2(P_TAB VARCHAR2, P_TYPE VARCHAR2) IS
    M_STR VARCHAR2(1000);
  BEGIN
    /*Procedure to Fetch data  dynamically from p_tab using bulk collect into variable*/
     /*After that M_BULK i want to use it in procedure p1 to fetch values*/
     
    M_STR := 'DECLARE ' || ' M_BULK /*GLOBAL VARIABLE*/  ' || P_TYPE || '; ' ||
             ' BEGIN ' || 
             ' SELECT * BULK COLLECT  INTO M_BULK FROM ' ||
             P_TAB || ';' || P_TYPE || ' := M_BULK ;' || ' END; ';
             
    EXECUTE IMMEDIATE M_STR;             
  
  END;

  PROCEDURE P3 IS
  BEGIN
    P2('EMP','TYPE_EMP');
    P2('DEPT','TYPE_DEPT');
  END;

END;


Now calling will be


BEGIN 
  PK1.P3;
END;  

Re: How we can declare a global Variable in Package Body(Not Spec) [message #578252 is a reply to message #578251] Tue, 26 February 2013 02:20 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

M_BULK is dynamically created with data structure of 1st emp and then Dept rowtype.
Re: How we can declare a global Variable in Package Body(Not Spec) [message #578253 is a reply to message #578252] Tue, 26 February 2013 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 58934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you want to do with "P_TYPE || ' := M_BULK ;'"?
A type cannot be an operand of an operation.

Quote:
After that M_BULK i want to use it in procedure p1 to fetch values


Declare M_BULK in the package specification and refer it as "PK1.M_BULK" in your BULK COLLECT, no DECLARE section.

Regards
Michel
Re: How we can declare a global Variable in Package Body(Not Spec) [message #578254 is a reply to message #578253] Tue, 26 February 2013 02:44 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Quote:

What do you want to do with "P_TYPE || ' := M_BULK ;'"?
A type cannot be an operand of an operation.


Ignore this one.

Quote:

Declare M_BULK in the package specification and refer it as "PK1.M_BULK" in your BULK COLLECT, no DECLARE section.


M_BULK's datatype is dynamically changing.1st time will have structure of EMP table and 2nd time it will have dept table structure.So cannot declare it in Spec.
Re: How we can declare a global Variable in Package Body(Not Spec) [message #578255 is a reply to message #578254] Tue, 26 February 2013 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 58934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So declare 2 variables and generate the (dynamic) code with the appropriate variable name.

Regards
Michel
Re: How we can declare a global Variable in Package Body(Not Spec) [message #578256 is a reply to message #578255] Tue, 26 February 2013 02:52 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

I don't need Static way.There can be more than 30-40 tables which we will refer M_BULK as its structure.SO only dynamic way.
Re: How we can declare a global Variable in Package Body(Not Spec) [message #578257 is a reply to message #578256] Tue, 26 February 2013 03:08 Go to previous message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
And then? Which dynamic action do you want to perform over that dynamic variable?
Or are you happy just fetching it from a table without anything else? Then there is no need to do it at all.

For simple printing the object variable content, you can use PRINT_TABLE procedure by Tom Kyte.
You may study its implementation in this link: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958
(and eventually adjust it to your exact requirements)
Previous Topic: Is employee is manager?
Next Topic: find difference in two schemas
Goto Forum:
  


Current Time: Fri Aug 29 09:38:59 CDT 2014

Total time taken to generate the page: 0.09937 seconds