Re: PL/SQL Question ...

From: C. <c_ferguson_at_rationalconcepts.com>
Date: Wed, 23 Jun 1999 23:42:49 -0700
Message-ID: <3771D369.BF15D64E@rationalconcepts.com>

Hi,
  Check into using dbms_sql.parse(). Pass in the schema as a variable into your procedure and then build the statement.  Here's a quick example:
create or replace procedure test (schema varchar2) as

    v_schema       varchar2(100) := schema;
    v_Cursor       INTEGER;
    s_SQLStatement      VARCHAR2(4000);     /* oracle 8.0.4 */

BEGIN

  v_Cursor := DBMS_SQL.OPEN_CURSOR;
  v_SQLStatement  :=  'Select * from '  || v_schema  ||
'.T_FIKA_KNOWNASFIID';

  DBMS_SQL.PARSE(v_Cursor, v_SQLStatement, DBMS_SQL.V7);

• Add your logic to handle the return.

  DBMS_SQL.CLOSE_CURSOR(v_Cursor);
EXCEPTION
  WHEN OTHERS THEN

     DBMS_SQL.CLOSE_CURSOR(v_Cursor);
     DBMS_OUTPUT.PUT_LINE(SQLERRM);

END test;
/

Hope that helps.
Cindy Ferguson, Rational Concepts, INC.

narenn_at_my-deja.com wrote:

> I am stuck with this for a while. How do you declare
> something like this with the owner being a variable ?
>
> var-name owner.table.column%type;
>
> I wrote a procedure with above datatype. I don;t want to
> hardcode the table owner name in the script. How do i
> make the owner a variable ?
>
> In other words my problem is this. The procedure i need to
> compile access table's which are not owned by the user id
> i use to compile the procedure but has read permission to
> it. The only way i can access the table is like
> this, owner.table_name. eg : FC_RTE_A1.T_FIKA_KNOWNAFIID
> where FC_RTE_A1 is the owner and T_FIKA_KNOWNASFIID is
> the table name. I don;t want to hard code FC_RTE_A1 ie
> owner of the table because the ownership will change
> between various enviroments i need to compile this script
> in!
>
> Any ideas ? I looked through PL/SQL manual from Oracle. I
> could not find any way to do this ?
>
> Please e-mail me at narendra.nathmal_at_fmr.com if you
> have any suggestions.
>
> Thanks
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Thu Jun 24 1999 - 01:42:49 CDT