Re: need to use variable in FROM clause

From: <gmei_at_my-deja.com>
Date: Tue, 29 Aug 2000 20:01:07 GMT
Message-ID: <8oh4pk$38e$1@nnrp1.deja.com>

Hi:

Following is the code I wrote a while back. It should give you enough info of how to use dynamic sql.

Hope this helps.

Guang

---

CREATE OR REPLACE PACKAGE hpxmig_cre_ind_store IS

• CONSTANTS: KSUCCESS CONSTANT INTEGER := 0; KERROR CONSTANT INTEGER := -1;

  ---

  PROCEDURE cre_ind_store ;

END hpxmig_cre_ind_store;
/

---

CREATE OR REPLACE PACKAGE BODY hpxmig_cre_ind_store IS

---

PROCEDURE cre_ind_store
IS

  lSTORAGE_PARAMETERS_PK_IND   VARCHAR2(2000);
  lSTORAGE_PARAMETERS_TAB      VARCHAR2(2000);
  lSQL                         VARCHAR2(2000);
  lCURSOR_HANDLE               INTEGER;
  lSTATUS                      INTEGER;

  lINDEX_NAME                  VARCHAR2(30);
  lTABLESPACE_NAME_IND         VARCHAR2(30);
  lINITIAL_EXTENT_IND          NUMBER;
  lNEXT_EXTENT_IND             NUMBER;
  lMIN_EXTENTS_IND             NUMBER;
  lMAX_EXTENTS_IND             NUMBER;
  lPCT_INCREASE_IND            NUMBER;

  lPK_CONSTRAINT_NAME          VARCHAR2(30);

BEGIN

• get old table's PK constraint name:

  SELECT CONSTRAINT_NAME
  INTO lPK_CONSTRAINT_NAME
  FROM USER_CONSTRAINTS
  WHERE TABLE_NAME = 'HPXSTORE'
  AND CONSTRAINT_TYPE = 'P';

• get old table's PK index storage parameters: SELECT INDEX_NAME, TABLESPACE_NAME , INITIAL_EXTENT, NEXT_EXTENT , MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE INTO lINDEX_NAME, lTABLESPACE_NAME_IND, lINITIAL_EXTENT_IND, lNEXT_EXTENT_IND , lMIN_EXTENTS_IND, lMAX_EXTENTS_IND, lPCT_INCREASE_IND FROM USER_INDEXES WHERE INDEX_NAME = lPK_CONSTRAINT_NAME AND TABLE_NAME = 'HPXSTORE' AND UNIQUENESS = 'UNIQUE';

  lSTORAGE_PARAMETERS_PK_IND := ' STORAGE ' ||

            '(INITIAL '     || lINITIAL_EXTENT_IND  ||
            ' NEXT '        || lNEXT_EXTENT_IND     ||
            ' MINEXTENTS '  || lMIN_EXTENTS_IND     ||
            ' MAXEXTENTS '  || lMAX_EXTENTS_IND     ||
            ' PCTINCREASE ' || lPCT_INCREASE_IND    ||
            ' )';


  --

• INDEX: IND_Store_Ctlog --

  lSQL := 'CREATE INDEX IND_Store_Ctlog ON HpxStore(CatalogID)

          TABLESPACE ' || lTABLESPACE_NAME_IND ||
          lSTORAGE_PARAMETERS_PK_IND ;

• DBMS_OUTPUT.PUT_LINE (lSQL);

  lCURSOR_HANDLE := DBMS_SQL.OPEN_CURSOR;   DBMS_SQL.PARSE (lCURSOR_HANDLE, lSQL,dbms_SQL.V7);   DBMS_SQL.CLOSE_CURSOR (lCURSOR_HANDLE);

END cre_ind_store;

---

END hpxmig_cre_ind_store;
/

In article <8ogrda$n55$1_at_nnrp1.deja.com>,   tandym_at_hotmail.com wrote:

> I want to be able to pass a table name to a procedure and have it
> execute a generic select statement.  I have had problems doing this
> (PLS-00201) and have a vague notion that I will need to user DBMS_SQL
> to do it.  PLease help - all examples I have been able to find don't
> show how to use a variable in a FROM clause.  My code is below.  I am
> using Oracle 8.1.5 and PL/SQL 8.1.5 on an NT box.
>
> ----------------------------------------------------------------------
 --

> --PROC_EVENT_DET_OTHERS
> --Is Called from PROC_EVENT_DETAIL_PARENT for details of tables that  do

> --not require joins to other tables
> --Accepts request number and table name
> --Passes Back CURSOR
> ----------------------------------------------------------------------

 --

> procedure proc_event_det_others(v_table IN varchar2,
> 	v_requestnr IN NUMBER,
> 	c_event_detail IN OUT audit_cur_typ) IS
>
> BEGIN
>
> 	OPEN c_event_detail FOR
>
> 	SELECT *
> 	FROM V_TABLE
> 	WHERE requestnr = V_REQUESTNR;
>
> END;
> -------------------
>
> Thanks,
>
> sonya
> please respond email as well, if possible
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Aug 29 2000 - 15:01:07 CDT