Home » SQL & PL/SQL » SQL & PL/SQL » How to use a schema name parameter in a procedure
icon5.gif  How to use a schema name parameter in a procedure [message #303761] Sun, 02 March 2008 12:42 Go to next message
stu123
Messages: 4
Registered: March 2008
Junior Member
I'm trying to create a procedure that takes a schema name as a parameter. Below is a simple example:

create or replace
procedure mergeClients(schemaName varchar) as
    cursor clients is select * from schemaName.client;

begin
    for currentClient in clients loop 
        dbms_output.put_line('hello');
    end loop;
end;


I'm obviously using the schemasName parameter incorrectly in the cursor declaration, but I'm not really sure what the correct format should be.

The error is:
Error(3,48): PL/SQL: ORA-00942: table or view does not exist

Can anyone help?
Re: How to use a schema name parameter in a procedure [message #303764 is a reply to message #303761] Sun, 02 March 2008 12:48 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You can not use PL/SQL in the way you 1st attempted because PL/SQL is compiled & resolved at compile time.
You need to use dynamic SQL.
Re: How to use a schema name parameter in a procedure [message #303765 is a reply to message #303761] Sun, 02 March 2008 13:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
dynamic sql example:


SCOTT@orcl_11g> create table client as select * from dept
  2  /

Table created.

SCOTT@orcl_11g> create or replace procedure mergeClients
  2  	 (schemaName in varchar2)
  3  as
  4  	 clients sys_refcursor;
  5  	 currentClient scott.client%ROWTYPE;
  6  begin
  7  	 open clients for 'select * from ' || schemaName || '.client';
  8  	 loop
  9  	      fetch clients into currentClient;
 10  	      exit when clients%notfound;
 11  	      dbms_output.put_line (currentClient.dname);
 12  	 end loop;
 13  end;
 14  /

Procedure created.

SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> set serveroutput on
SCOTT@orcl_11g> exec mergeclients ('scott')
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

Re: How to use a schema name parameter in a procedure [message #303766 is a reply to message #303761] Sun, 02 March 2008 13:02 Go to previous messageGo to next message
stu123
Messages: 4
Registered: March 2008
Junior Member
Thanks for the reply anacedent Smile Can you give me an example of how the procedure would need to be modified?
Re: How to use a schema name parameter in a procedure [message #303767 is a reply to message #303761] Sun, 02 March 2008 13:03 Go to previous messageGo to next message
stu123
Messages: 4
Registered: March 2008
Junior Member
Thats fantastic! Thanks Barbara Cool
Re: How to use a schema name parameter in a procedure [message #303771 is a reply to message #303761] Sun, 02 March 2008 14:20 Go to previous messageGo to next message
stu123
Messages: 4
Registered: March 2008
Junior Member
Hey Barbara, one more question... In your example, the client table belongs to the same user that is running the procedure - scott. This allows you to get away with line 5:
currentClient scott.client%ROWTYPE;


This won't work in my case because the procedure is run by a different user and therefore 'scott' needs to be 'schemaName'. Is this possible?
Re: How to use a schema name parameter in a procedure [message #303774 is a reply to message #303771] Sun, 02 March 2008 15:25 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
no you cannot. Search for dbms_sql in sql reference manual.

Regards

Raj
Re: How to use a schema name parameter in a procedure [message #303781 is a reply to message #303771] Sun, 02 March 2008 17:41 Go to previous message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
There are various ways that you can do things, depending on the circumstances. If the table structures are the same, then you can use any of the schemas in schema.table%rowtype or just table%rowtype and let it use the default schema. You can also base the type on a declared pl/sql record type that matches the columns that you are selecting. You can also create the procedure with invoker's rights, instead of definer's rights, if you are going to be selecting from the invoker's table. It all depends on what you want. I have demonstrated usage of a pl/sql record type with two schemas below.

SCOTT@orcl_11g> create user otherschema identified by otherschema
  2  /

User created.

SCOTT@orcl_11g> grant connect, resource to otherschema
  2  /

Grant succeeded.

SCOTT@orcl_11g> connect otherschema/otherschema
Connected.
OTHERSCHEMA@orcl_11g> 
OTHERSCHEMA@orcl_11g> create table client
  2    (deptno number (2),
  3  	dname  varchar2 (14),
  4  	loc    varchar2 (13))
  5  /

Table created.

OTHERSCHEMA@orcl_11g> insert into client values (1, 'newdept', 'somewhere')
  2  /

1 row created.

OTHERSCHEMA@orcl_11g> commit
  2  /

Commit complete.

OTHERSCHEMA@orcl_11g> grant select on client to scott
  2  /

Grant succeeded.

OTHERSCHEMA@orcl_11g> connect scott/tiger
Connected.
SCOTT@orcl_11g> 
SCOTT@orcl_11g> create table client as select * from dept
  2  /

Table created.

SCOTT@orcl_11g> create or replace procedure mergeClients
  2  	 (schemaName in varchar2)
  3  as
  4  	 clients sys_refcursor;
  5  	 type client_rec is record
  6  	   (deptno number (2),
  7  	    dname  varchar2 (14));
  8  	 currentClient client_rec;
  9  begin
 10  	 open clients for 'select deptno, dname from ' || schemaName || '.client';
 11  	 loop
 12  	      fetch clients into currentClient;
 13  	      exit when clients%notfound;
 14  	      dbms_output.put_line (currentClient.dname);
 15  	 end loop;
 16  end;
 17  /

Procedure created.

SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> set serveroutput on
SCOTT@orcl_11g> exec mergeclients ('scott')
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> exec mergeclients ('otherschema')
newdept

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 




P.S. We are having a windstorm here and the power was out for a few hours. A tree probably landed on a power line somewhere as frequently happens. My battery supply only lasts a couple of hours after the power goes out and I don't like to run it dry. The wind warning is till 4 pm tomorrow, so I might be offline for a while if we get more outages, as frequently happens during these windstorms.

Previous Topic: how make IN operator not case sensitive ?
Next Topic: numeric value converted to wordings
Goto Forum:
  


Current Time: Sat Dec 03 11:53:45 CST 2016

Total time taken to generate the page: 0.12222 seconds