create folder using sql

[craigey]

Hi all,

I'm trying to create a folder using either host mkdir or running a job which runs mkdir. The mkdir seems to work, but creates a folder called my_directory rather then using the value of the variable.

Declare
 my_Directory varchar2(100);
 
Begin
 
select p_value into My_Directory
from PART_CONFIG where p_name = 'TS_PATH' and p_group='CAP';
 
DBMS_SCHEDULER.CREATE_JOB (
job_name =>'create_dir',
job_type =>'executable',
job_action => 'c:\windows\system32\cmd.exe /c mkdir my_Directory',
enabled =>true,
auto_drop => true
);
commit;
end;
/

the ts_path does contain the value "c:\new"
I've also tried using:

HOST mkdir:my_Directory

I'd also like to check if the folder exists before creating the folder if possible.

Any help would be greatly apreciated.

 

[craigey]

ok, so managed to get this working, but I'd prefer to use the command
HOST mkdir:my_directory
rather then running a job. Any ideas. I keep getting bind variable not declared.

 

[sem]

Where do you need that folder, on server or client? HOST command works on client side while the job is on server.

Then my_Directory is visible only inside pl/sql block, so you need to "externalize" it:

define My_Directory=''
select p_value My_Directory
from PART_CONFIG where p_name = 'TS_PATH' and p_group='CAP';
host &&My_Directory

Regards, Dima

http://seminihin.narod.ru

 

[craigey]

The folder needs to be created on the server. host &&My_Directory seems to do the same as host on its own & I can't see the My_directory variable.


There are several folders that i need to create. Is there anyway of creating these folders automatically using SQL?

SQL> select p_value My_Directory
  2  from PART_CONFIG where p_name = 'TS_PATH';

MY_DIRECTORY
-----------------------------------------------------

C:\new0\
C:\new1\
C:\new2\
C:\new3\
C:\new4\
C:\new5\

6 rows selected.

SQL>

 

[sem]

It only seems to do. Have you defined My_Directory first? In any case your query can not be used to select into as in your first message. Besides HOST is processed on client.

I didn't work with DBMS_SCHEDULER but if your code doesn't contain other errors, it should look like this:

begin
  for f in (select p_value my_Directory
              from PART_CONFIG
             where p_name = 'TS_PATH'
               and p_group = 'CAP') loop
  
    DBMS_SCHEDULER.CREATE_JOB(job_name   => 'create_dir',
                              job_type   => 'executable',
                              job_action => 'c:\windows\system32\cmd.exe /c mkdir ' ||
                                            f.my_Directory,
                              enabled    => true,
                              auto_drop  => true);
    commit;
  end loop;
end;
/

Regards, Dima

http://seminihin.narod.ru

 

[craigey]

Sorry I'm a little lost. The DBMS_scheduler job works fine, but it only creates 1 folder (the one in the cap p_group), rather then using all the path variables which are stored in each p_value for different groups. ie my table looks something like:

p_group   p_value    p_name
cap       c:\new\    ts_path
aaa       c:\new1\   ts_path
bbb       c:\hello\  ts_path

The code will only be run once on the server, so there shouldn't be a problem with the host command running on the client. It would also be beneficial as the mkdir command will work on most platforms, where as I would have to change the job_action for each individual platform the code may run on.

Thanks again

 

[Thargy]

Craigey,

can you zoom out a bit from the problem, and give us an English language statement of what business process you're trying to achieve?

That way we might be able to suggest alternative methods to what you already have, or come up with a work-around for the problem.

Regards

T

Grinding away at things Oracular

 

[craigey]

ok.

The database has several partitioned tables. These partitions are put in seperate folders. eg C:\table_a, c:\table_b, c:\table_c

There is a job on the db which creates 14 days worth of partitons & puts the apropriate partitions in the apropriate folders hence why the part_config table contains these paths.

The problem is that if the folders do not exist the job fails & can result in partitions not being created. I'm trying to create the folders as per the TS_path values in the part_config table so that the job will not fail.

The script runs on various platforms, Solaris, Windows etc so a mkdir command would be more suited then running a job that relies on cmd.exe being available.

I hope this clears things up a little.

 

[Thargy]

Craigey,

at home I have a book with some potentially useful info, which I will have to refer to. Is a solution delayed by say a couple of days any good?

T

Grinding away at things Oracular

 

[craigey]

yep. I'm happy to wait. Thanks.

 

[Thargy]

OK, I'll do some digging this evening.

T

Grinding away at things Oracular

 

[sem]

The issue is not in calling HOST from client but rather in executing on it: all the directories would be created on client pc.

I think that DBMS_SCHEDULER doesn't manage to create a directory and autodrop a job between calls so you should name give a unique name (e.g. directory name itself) to each "job":

begin
  for f in (select p_value my_Directory
              from PART_CONFIG
             where p_name = 'TS_PATH'
               and p_group = 'CAP') loop
  
    DBMS_SCHEDULER.CREATE_JOB(job_name   => f.my_Directory,
                              job_type   => 'executable',
                              job_action => 'c:\windows\system32\cmd.exe /c mkdir ' ||
                                            f.my_Directory,
                              enabled    => true,
                              auto_drop  => true);
    commit;
  end loop;
end;
/

Regards, Dima

http://seminihin.narod.ru

 

[craigey]

It doesn't seem to like the c:\ in the job_name

ERROR at line 1:
ORA-27452: C:\new is an invalid name for a database object.

Also will your script actually loop through the different p_groups?

 

[sem]

craigey, this is YOUR script . I mean I copied your own query. You may "generate" a unique name as 'dir'||rownum or any way you like.

Why do you need pl/sql to create directories? Especially to do it only once? I suppose that using DBMS_SCHEDULER for such simple task is an overkill. Why don't you simply connect to server and create those directories?

Regards, Dima

http://seminihin.narod.ru

 

[craigey]

my original query was to create one folder from one group. The loop would be pointless unless it looped through the different p_group's and created each directory. I did mention that I had managed to get ths to work (without the loop)

The script is a bit overkill as it would be easy to create the directories manually, but when we have done this previously, occasionally people have mis-spelled at least one directory or have created the folders on the wrong drive.

In your previous script the job name was create_dir & this was fine. In the last version you posted the job name was f.my_directory. DBMS_SCHEDULER seems to dislike the ":\" in the job name.

The DBMS_scheduler script is great for windows environments, but would need to be modified to work on solaris etc.

Thanks again

 

[Thargy]

Cragiey,

I swotted up a bit on partitioning last night. Are you in the "rolling window" scenario, where you're range partitioning data by date, and want a new partition created for the latest day's data, and want the oldest partition dropped? If so, a separate tablespace (and file) per partition seems like a good idea.

On that basis, why not use OMF and forget about creating folders and files? Just let Oracle do the donkey work for you.

If you're storing say a Terabyte of data, then OMF is almost certainly not the technology to use, but I don't have any idea of your data volumes. Is the need for file creation solely a result of a need for a new partition, or is there something else going on?

From your post, it appears that what you really want is an automatic process to create a new tablespace for each new partition, as the need arises. Is that a fair assessment of what you're trying to do?

Regards

T

Grinding away at things Oracular

 

[craigey]

it is almost exactly like the rolling window situation, where partitioning is by date & OMF would be a good idea, but the database is likely to be several TB in size.

There is already a package that creates a new tablespace for each new partition. The problem is that the package doesn't check if the folders exist, which means partitions might not be created untill someone notices a gap in the partition names.

 

[Thargy]

Ah, the fog lifts.

Why not choose to not have the problem?

If you are creating one tablespace (and one file) per day, then why not create one folder for all the files for the table in question, and just create lots of files within that folder?

The folder is permanent, it's just the files that come and go, as new ones arrive, and old ones are safely archived away. I can't see the requirement for a folder per day, or even a folder per month. If your housekeeping routines are working correctly, the one target folder will never hold more than a fixed number of files - one in, one out, every day, guaranteed.

How say you Craigey?

Grinding away at things Oracular

 

[craigey]

There data would have to be several years old before it can be archived off this would mean a vast number of files in the one folder before any could be archived off.

I apreciate all the suggestions / ideas, but we need to make the directories every time the script is run. If the folders exist the script fails, but it's not a problem. If someone decides to change the location of the partition. The script would then create the folders on the new drive / path, before the package started creating partitions on the new drive / path.

 

[sem]

craigey,

1. You may write a simple dll and call it via extproc mechanism
2. You may create simple java stored procedure calling OS command
3. You may call sqlplus to generate command script and execute the result (sqlplus should be launched on server)

I'm sure all these solutions were discussed here, just try to search instead of waiting for anybody willing to make your work

Regards, Dima

http://seminihin.narod.ru