Home » SQL & PL/SQL » SQL & PL/SQL » building a pl/sql query from select statement (10g, RH Linux)
building a pl/sql query from select statement [message #561063] Wed, 18 July 2012 11:10 Go to next message
candi
Messages: 28
Registered: July 2012
Junior Member
Hi, first of all I am a PL-SQL newbie but have good experience in t-sql etc but finding the transition difficult so apologies in advance Embarassed

I am looking to build a query to update a current value in a DBA view to a new one.

i.e. updating directories based on the current value:

basically in english I want to build a pl/sql that looks like this:

CREATE OR REPLACE DIRECTORY 'DIRECTORY_NAME' AS 'DIRECTORY_PATH'(substr(directory_path, 1,5) + '/&dbname' {i.e. this is different for every database name }+ 'DIRECTORY_PATH'(string after /xyz/)
WHERE DIRECTORY_NAME in
( select DIRECTORY_NAME
from DBA_DIRECTORIES
WHERE DIRECTORY_PATH
like '/xyz/%'
)

i.e. resulting output should be:
CREATE OR REPLACE DIRECTORY 'ABC' AS '/xyz/DBNAME/abc/def/';
(when the directory previously was 'xyz/abc/def/') i.e. basically inserting the db name into the directory.

where DBNAME is a variable

more directories are added frequently so therefore this needs to be a dynamic procedure to change the directories in the db.

thanks in advance

Report message to a moderator

Re: building a pl/sql query from select statement [message #561065 is a reply to message #561063] Wed, 18 July 2012 11:19 Go to previous messageGo to next message
BlackSwan
Messages: 20097
Registered: January 2009
Senior Member
>where DBNAME is a variable
who, what, how does this variable get populated?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: building a pl/sql query from select statement [message #561066 is a reply to message #561065] Wed, 18 July 2012 11:24 Go to previous messageGo to next message
candi
Messages: 28
Registered: July 2012
Junior Member
Hi BlackSwan

Sorry, I didn't want to overcomplicate my first question, I thought I would worry about the &dbname after, but for an example:

case when '&db_name' like 'MOM%' then '/xyz/MOM/-'
when '&db_name' like 'DAD%' then '/xyz/DAD/-'
when '&db_name' like 'SISTER%' then '/xyz/SISTER/-'

Report message to a moderator

Re: building a pl/sql query from select statement [message #561067 is a reply to message #561066] Wed, 18 July 2012 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 20097
Registered: January 2009
Senior Member
SQL (including DDL) must be static at compile time.
Realize & understand that DDL does implicit COMMIT.
EXECUTE IMMEDIATE must be (ab)used to issue DDL from inside PL/SQL.

The whole approach leaves a bad taste in my mouth, but I can understand the logic behind it.

Report message to a moderator

Re: building a pl/sql query from select statement [message #561068 is a reply to message #561063] Wed, 18 July 2012 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 
SQL> select regexp_replace('/xyz/abc/def/', '^(/[^/]*/)(.*)$', '\1DBNAME/\2') from dual;
REGEXP_REPLACE('/XYZ
--------------------
/xyz/DBNAME/abc/def/


Regards
Michel

Report message to a moderator

Re: building a pl/sql query from select statement [message #561084 is a reply to message #561067] Wed, 18 July 2012 14:07 Go to previous messageGo to next message
candi
Messages: 28
Registered: July 2012
Junior Member
BlackSwan wrote on Wed, 18 July 2012 11:37
SQL (including DDL) must be static at compile time.
Realize & understand that DDL does implicit COMMIT.
EXECUTE IMMEDIATE must be (ab)used to issue DDL from inside PL/SQL.

The whole approach leaves a bad taste in my mouth, but I can understand the logic behind it.


Thanks nothing sinister going on just cloning a db onto another machine (for test) and it has a different directory structure. Just trying to automate everything!

Report message to a moderator

Re: building a pl/sql query from select statement [message #561163 is a reply to message #561084] Thu, 19 July 2012 04:36 Go to previous messageGo to next message
candi
Messages: 28
Registered: July 2012
Junior Member
sorry to bump this up, thanks for the replace query above but thats not really what I need.

I am cloning one db to another on another machine. Directory structure is different on 2nd machine. I need to update the dba_directories view automatically to reflect the new directory structure. Rather than just do it manually I want to write a script to change the directory path on the fly i.e.
select * from dba_directories where directory_path like "xyz/%"
then based on this output create the statement:

create or replace directory 'X' as 'xyz/DBNAME/-';

for each directory which meets the select criteria.

simples Very Happy

Report message to a moderator

Re: building a pl/sql query from select statement [message #561166 is a reply to message #561163] Thu, 19 July 2012 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the regexp I posted you will do it:
select 'create or replace directory '||directory_name||' as '''||
       regexp_replace(directory_path, '^(/[^/]*/)(.*)$', '\1&DBNAME/\2')||''';'
from dba_directories
/

Regards
Michel

Report message to a moderator

Re: building a pl/sql query from select statement [message #561167 is a reply to message #561163] Thu, 19 July 2012 04:44 Go to previous messageGo to next message
Littlefoot
Messages: 16966
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel posted some code; did you try it?

An alternative approach: why wouldn't you, instead, keep the directory structure (i.e. 2nd machine = 1st machine)?

P.S. Forgot to tell: creating a directory is one thing. What about granting privileges? Will you "automate" that too?

[Updated on: Thu, 19 July 2012 04:45]

Report message to a moderator

Re: building a pl/sql query from select statement [message #561168 is a reply to message #561166] Thu, 19 July 2012 04:44 Go to previous messageGo to next message
Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or if want to be more automatic and do not ask for a value:
select 'create or replace directory '||directory_name||' as '''||
       regexp_replace(directory_path, '^(/[^/]*/)(.*)$', '\1'||name||'/\2')||''';'
from dba_directories, v$database
/

Regards
Michel

Report message to a moderator

Re: building a pl/sql query from select statement [message #561171 is a reply to message #561168] Thu, 19 July 2012 04:52 Go to previous messageGo to next message
candi
Messages: 28
Registered: July 2012
Junior Member
thats brilliant, thanks, now how to put it in a pl/sql block in order to execute it?

Report message to a moderator

Re: building a pl/sql query from select statement [message #561174 is a reply to message #561171] Thu, 19 July 2012 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 
BEGIN
  FOR rec IN (the query) LOOP
    execute immediate rec.<query result column name>;
  END LOOP;
END;
/

Regards
Michel

Report message to a moderator

Re: building a pl/sql query from select statement [message #561202 is a reply to message #561174] Thu, 19 July 2012 08:21 Go to previous messageGo to next message
candi
Messages: 28
Registered: July 2012
Junior Member
sorry I am still lost. I don't know what to put in between the <>.

shoot me!

Report message to a moderator

Re: building a pl/sql query from select statement [message #561203 is a reply to message #561202] Thu, 19 July 2012 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The alias you will give to the query column result and to which I gave no alias (and so no name).
And remove the ';' to the result lines as there are not necessary (and even forbidden) in EXECUTE IMMEDIATE.

Regards
Michel

Report message to a moderator

Re: building a pl/sql query from select statement [message #561207 is a reply to message #561203] Thu, 19 July 2012 08:55 Go to previous messageGo to next message
candi
Messages: 28
Registered: July 2012
Junior Member
first of all, thanks again. I have this code:

(created a work table OLD_DIR)

BEGIN
FOR rec IN (select 'create or replace directory '||dir_name||' as '''||
regexp_replace(dir_path, '^(/[^/]*/)(.*)$', '\1'||name||'/\2')||''';' as NEW_DIR
from OLD_DIR, v$database
where dir_path like '/xyz%') LOOP
execute immediate rec.NEW_DIR;
END LOOP;
END;

but I get an error:
ORA-00911: invalid character
ORA-06512: at line 6

would it be simpler to use a cursor
ie
declare cursor cr_new_dir is
<QUERY>
BEGIN
for rec in NEW_DIR loop
execute immediate rec.NEW_DIR
end loop;
end;

(but this also dosent work) Sad


[Updated on: Thu, 19 July 2012 08:56]

Report message to a moderator

Re: building a pl/sql query from select statement [message #561208 is a reply to message #561207] Thu, 19 July 2012 08:57 Go to previous messageGo to next message
BlackSwan
Messages: 20097
Registered: January 2009
Senior Member
see a picture of my car.
It doesn't work.
tell me how to make my car go.

Since we don't have your tables or data,
we can not compile, run or test posted code.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

[Updated on: Thu, 19 July 2012 08:58]

Report message to a moderator

Re: building a pl/sql query from select statement [message #561209 is a reply to message #561208] Thu, 19 July 2012 09:19 Go to previous messageGo to next message
mnitu
Messages: 131
Registered: February 2008
Location: Reims
Senior Member
Kindly suggest to replace execute immediate rec.NEW_DIR by Dbms_output(rec.NEW_DIR) in order to inspect the sql statement you are generating.
Also suggest to not using ";" in order to finish your SQL statement; SQL doesn't need this character it's PL/SQL or sqlplus who needs it.
Please follow forum guidelines.

Report message to a moderator

Re: building a pl/sql query from select statement [message #561211 is a reply to message #561209] Thu, 19 July 2012 09:30 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member
@candi....
how the v$database contain directory_name...

select 'create or replace directory '||dir_name||' as '''|| regexp_replace(dir_path, '^(/[^/]*/)(.*)$', '\1'||name||'/\2')||''';' as NEW_DIR
from  v$database  where dir_path like '/xyz%'

Report message to a moderator

Re: building a pl/sql query from select statement [message #561212 is a reply to message #561211] Thu, 19 July 2012 09:32 Go to previous messageGo to next message
candi
Messages: 28
Registered: July 2012
Junior Member
OLD_DIR has the directory name (basically a select into from dba_directories) ??

Report message to a moderator

Re: building a pl/sql query from select statement [message #561213 is a reply to message #561212] Thu, 19 July 2012 09:36 Go to previous messageGo to next message
BlackSwan
Messages: 20097
Registered: January 2009
Senior Member
>OLD_DIR has the directory name (basically a select into from dba_directories) ??

The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
Then print the variable before passing it to EXECUTE IMMEDIATE.
COPY the statement & PASTE into sqlplus to validate its correctness.

Report message to a moderator

Re: building a pl/sql query from select statement [message #561215 is a reply to message #561212] Thu, 19 July 2012 09:38 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member
i am not clear what are you going with execute immediate statement
begin
for i in ( select 'create or replace directory '||DIRECTORY_NAME||' as '''||
regexp_replace(DIRECTORY_PATH, '^(/[^/]*/)(.*)$', '\1'||name||'/\2')||''';' as NEW_DIR
from dba_directories , v$database ) loop


dbms_output.put_line(i.NEW_DIR);
end loop;
end;
/

Report message to a moderator

Re: building a pl/sql query from select statement [message #561217 is a reply to message #561215] Thu, 19 July 2012 09:43 Go to previous messageGo to next message
candi
Messages: 28
Registered: July 2012
Junior Member
thanks muralikri basically I want to update the directory_path on the fly....Michel suggested execute immediate

Report message to a moderator

Re: building a pl/sql query from select statement [message #561220 is a reply to message #561217] Thu, 19 July 2012 09:49 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member
Where you to update directory_path ?

Report message to a moderator

Re: building a pl/sql query from select statement [message #561222 is a reply to message #561220] Thu, 19 July 2012 09:52 Go to previous messageGo to next message
candi
Messages: 28
Registered: July 2012
Junior Member
update directory path in dba_directories

i.e.
query directory_path on dba_directories that meet criteria
then generate create or replace statement with new directory_path (as described before)
then execute the generated DML statment to update the dba_directories view.

Report message to a moderator

Re: building a pl/sql query from select statement [message #561223 is a reply to message #561220] Thu, 19 July 2012 09:56 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member
i am clear ...will post soon..

Report message to a moderator

Re: building a pl/sql query from select statement [message #561233 is a reply to message #561174] Thu, 19 July 2012 11:06 Go to previous messageGo to next message
candi
Messages: 28
Registered: July 2012
Junior Member
Smile
finally got it to work, many thanks.

BEGIN
FOR i IN (select 'create or replace directory '||DIR_NAME||' as '''||
regexp_replace(DIR_PATH, '^(/[^/]*/)(.*)$', '\1'||name||'/\2')||'''' as NEW_DIR
from OLD_DIR , v$database
where DIR_PATH like '/xyz%'
and DIR_NAME='TEST' ) LOOP
execute immediate(i.NEW_DIR);
END LOOP;
END;
/

pesky ; in there causing the problems Laughing

Report message to a moderator

Re: building a pl/sql query from select statement [message #561237 is a reply to message #561233] Thu, 19 July 2012 11:40 Go to previous message
Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
finally got it to work, many thanks.


I was offline but you could just read what I wrote in my last post:
Quote:
And remove the ';' to the result lines as there are not necessary (and even forbidden) in EXECUTE IMMEDIATE.

Wink

Regards
Michel

Report message to a moderator

Previous Topic: how can i lock particular row in a table
Next Topic: Approach Needed ( Pl/SQl table?)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue May 21 13:28:06 CDT 2013

Total time taken to generate the page: 0.84199 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.