Home » SQL & PL/SQL » SQL & PL/SQL » Execute the sql script contents in plsql environment.
Execute the sql script contents in plsql environment. [message #392046] Mon, 16 March 2009 03:26 Go to next message
nmascrene
Messages: 5
Registered: February 2009
Location: Mumbai
Junior Member

Hi,

Requirement : A pl/sql block which will execute the contents of same sql script(DML,DDL queries ) in different existing schemas when logged in one schema.

I have created a plsql block which will get the schema names from a table table_schema using a cursor (select schema_name
from table_schema). Then executing the following statement.

EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA='||schemaname;

Now i am able to switch between different schemas. But when i switch to one schema i m suppose to execute contents of any(dynamic) sql script with (DML,DDL queries) so that all the schemas have the same changes.

I am trying to make use of util packages . to read the file contents and execute it . I am not sure if im right to make of util packages since file reads line by line , sql stmt ends with a ; and if there are many huge queries then how do we seperate them and execute immediate individually .. I am not sure if this solution of using util packages works! .. Kindly suggest some solution.

thnx.
Re: Execute the sql script contents in plsql environment. [message #392049 is a reply to message #392046] Mon, 16 March 2009 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It will work until you also want to execute PL/SQL block which does not end at the first ";".
Also a SQL statement can finish with a line containing "/" instead of ending with ";" but you are the master of the script file and so can set some rules for it.

Regards
Michel

[Updated on: Mon, 16 March 2009 03:57]

Report message to a moderator

Re: Execute the sql script contents in plsql environment. [message #392052 is a reply to message #392049] Mon, 16 March 2009 04:02 Go to previous messageGo to next message
nmascrene
Messages: 5
Registered: February 2009
Location: Mumbai
Junior Member

thank you for the reply Smile
If this works my query is ..
But when a file is read line by line . How do we seperate one complete sql statement so that i can dynamically execute using execute immediate stmt. Can u help me with the logic to be applied..
Re: Execute the sql script contents in plsql environment. [message #392053 is a reply to message #392052] Mon, 16 March 2009 04:11 Go to previous messageGo to next message
nmascrene
Messages: 5
Registered: February 2009
Location: Mumbai
Junior Member

I have one more doubt related to this .. if the value in v$parameter table is not set for using util package . How can i get the path set .I have only select privilege on this table. The admin can update this table .. ?
Re: Execute the sql script contents in plsql environment. [message #392054 is a reply to message #392046] Mon, 16 March 2009 04:11 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Think you're looking at this the wrong way round.
Instead of reading scripts from within the database why don't you dynamically create the script that loops over users.
Write a procedure that will create a script that changes user to each user in turn and calls the other scripts for each user.
Re: Execute the sql script contents in plsql environment. [message #392055 is a reply to message #392052] Mon, 16 March 2009 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand your question, you explained how to do it: read the file until you get ";" at end of line.

Regards
Michel
Re: Execute the sql script contents in plsql environment. [message #392056 is a reply to message #392053] Mon, 16 March 2009 04:14 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
nmascrene wrote on Mon, 16 March 2009 10:11
I have one more doubt related to this .. if the value in v$parameter table is not set for using util package . How can i get the path set .I have only select privilege on this table. The admin can update this table .. ?

Use Oracle directory object not parameter setting.

Regards
Michel

Previous Topic: Recursion
Next Topic: SCN Number
Goto Forum:
  


Current Time: Sun Dec 11 08:31:34 CST 2016

Total time taken to generate the page: 0.04561 seconds