OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Schema name (Oracle 10.2)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Schema name [message #572198]

Fri, 07 December 2012 06:12

pointers
Messages: 324
Registered: May 2008

Senior Member

Hi,

Sometimes we share couple of scripts to DBA to run over production.

Because of the schema name is not mentioned explicitly in the script and dba runs as a super user, the script get fail with the object is not found.
e.g

DECLARE
   v_job_id   NUMBER;
BEGIN
   IF pkg_test.f_job_id_lesen IS NULL
   THEN
      v_job_id := pkg_test.f_starten ('BB');
   END IF;
END;
/

Above, the schema name is not mentioned for pkg_test package

I know, eiether I need to give the schema name or ask the DBA to run the script in the specific schema.

But I am looking for any commnad (sqlplus or sql) which can be mentioned in the script so that the script assumes the schema name by default.

Upon searching I found, the below command.

ALTER SESSION SET CURRENT_SCHEMA="scott";

Is there any best approach for this.

Regards,
Pointers

Report message to a moderator

Re: Schema name [message #572199 is a reply to message #572198]

Fri, 07 December 2012 06:15

Michel Cadot
Messages: 54712
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is the best a approach.
Another one is to use proxy user and authentication but it is more complex and not better.

Regards
Michel

Report message to a moderator

Re: Schema name [message #572200 is a reply to message #572199]

Fri, 07 December 2012 06:27

pointers
Messages: 324
Registered: May 2008

Senior Member

Hi Micheal,

Thank you very much for the details.

Is it ok to use this command (ALTER SESSION SET CURRENT_SCHEMA="scott";) in the scripts which will be run over production, I dont know if there is any side effects or anything that i need to consider in the production environment.

Regards,
Pointers

Report message to a moderator