Home » SQL & PL/SQL » SQL & PL/SQL » Help me build this string
Help me build this string [message #228868] Wed, 04 April 2007 08:06 Go to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
i am trying to create a command that should look something like this but im failing drastically.

        
 DBMS_JOB.submit(job_id_rec.id_number,'ccsuk.process_daily('NEW_JOB');',trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/5)+1)*5)/(24*60),
            null,
            false);    
          commit;


'NEW_JOB' will be stored in a variable called job_name_in. I tried this but it didnt work.

 billing_errors_job:='''||'ccsuk.process_daily('||job_name_in||')'||''';
        




The string i want to build (i.e. billing_errors_job)goes in the second parameter of dbms_job.submit as shown below.

    DBMS_JOB.submit(job_id_rec.id_number,billing_errors_job,             trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/5)+1)*5)/(24*60),
            null,
            false);    
          commit;




I want the string to go into the second parameter as 'ccsuk.process_daily('NEW_JOB');' inclusive of the single quotes.

Any ideas?
Re: Help me build this string [message #228874 is a reply to message #228868] Wed, 04 April 2007 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DBMS_JOB.submit(job_id_rec.id_number,'ccsuk.process_daily('''||job_name_in||''');',...

Regards
Michel
Re: Help me build this string [message #228887 is a reply to message #228874] Wed, 04 April 2007 08:30 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member

Hi thanks for your response. When i execute dbms_job.submit, i execute it as shown below

  
  DBMS_JOB.submit(job_id_rec.id_number,billing_errors_job,sysdate+1,null,false);
  
  


The string in billing_errors_job should be 'ccsuk.process_daily('NEW_JOB');' inclusive of all single quotes. 'NEW_JOB' will come from job_name_in
I dont want to enter the string directly when i call dbms_job.submit, i want to enter the string via the variable billing_errors_job as shown below

  billing_errors_job:='ccsuk.process_daily('NEW_JOB');' --(including all single quotes)
  DBMS_JOB.submit(job_id_rec.id_number,billing_errors_job,sysdate+1,null,false);
  
  


Thanks
Re: Help me build this string [message #228903 is a reply to message #228887] Wed, 04 April 2007 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Double the quotes around NEW_JOB (don't use double-quotes, double the sinlg quotes).

Regards
Michel
Re: Help me build this string [message #228904 is a reply to message #228887] Wed, 04 April 2007 08:59 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,
i guess you are asking about building the string like this

billing_errors_job:='ccsuk.process_daily('NEW_JOB');'

just do as Michel Cadot said

like

billing_errors_job:='ccsuk.process_daily('''||job_name_in||''');'


and then your
DBMS_JOB.submit(job_id_rec.id_number,billing_errors_job,sysdate+1,null,false);


here is a small example

SQL> declare
  2  billing_errors_job varchar2(256);
  3  job_name_in varchar2(256);
  4  begin
  5  job_name_in:=('&job_name_in');
  6  billing_errors_job:='ccsuk.process_daily('''||job_name_in||''');';
  7  dbms_output.put_line(billing_errors_job);
  8  end;
  9  /
Enter value for job_name_in: new_name
old   5: job_name_in:=('&job_name_in');
new   5: job_name_in:=('new_name');
ccsuk.process_daily('new_name');

PL/SQL procedure successfully completed.



or else i am sorry.
and please ignore me..

regards
shanth
Re: Help me build this string [message #228933 is a reply to message #228904] Wed, 04 April 2007 10:37 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
shanthkumaar wrote on Wed, 04 April 2007 08:59
hi,
Enter value for job_name_in: new_name
old 5: job_name_in:=('&job_name_in');
new 5: job_name_in:=('new_name');
ccsuk.process_daily('new_name');

PL/SQL procedure successfully completed.[/code]


or else i am sorry.
and please ignore me..

regards
shanth



Thanks for your response.

Your string comes out as ccsuk.process_daily('new_name');
i need it to come out as 'ccsuk.process_daily('new_name');'
(note the single quotes)
Re: Help me build this string [message #228934 is a reply to message #228933] Wed, 04 April 2007 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

But 'ccsuk.process_daily('new_name');' is wrong.
What is correct is what has been posted.

Regards
Michel
Re: Help me build this string [message #228943 is a reply to message #228934] Wed, 04 April 2007 10:56 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Quote:

But 'ccsuk.process_daily('new_name');' is wrong.
What is correct is what has been posted.

Regards
Michel



Thanks for your reply.

According to the documentation for dbms_job, the job to be put in the queue has to be specified as a string.

See the following URL
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#8434

have a look at the first NOTE just before the examples.

Many thanks for your help.
Re: Help me build this string [message #228950 is a reply to message #228943] Wed, 04 April 2007 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but 'NEW_NAME' is a string in a string so you have to double the quotes around it.

Regards
Michel
Re: Help me build this string [message #229001 is a reply to message #228950] Wed, 04 April 2007 23:59 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

ziggy25 wrote

Quote:
Your string comes out as ccsuk.process_daily('new_name');
i need it to come out as 'ccsuk.process_daily('new_name');'
(note the single quotes)


if you want a single quotes added before and at the end of
ccsuk.process_daily('new_name'); just append the quotes like this

example

SQL> declare
2 billing_errors_job varchar2(256);
3 job_name_in varchar2(256);
4 begin
5 job_name_in:=('&job_name_in');
6 billing_errors_job:=''''||'ccsuk.process_daily('''||job_name_in||''');''';
7 dbms_output.put_line(billing_errors_job);
8 end;
9 /
Enter value for job_name_in: new_name
old 5: job_name_in:=('&job_name_in');
new 5: job_name_in:=('new_name');
'ccsuk.process_daily('new_name');'

PL/SQL procedure successfully completed.




Michel Cadot wrote
Quote:
Yes but 'NEW_NAME' is a string in a string so you have to double the quotes around it.


yes as he says for 'NEW_NAME' you have to double the quotes around it


regards,
shanth

[Updated on: Wed, 04 April 2007 23:59]

Report message to a moderator

Re: Help me build this string [message #229061 is a reply to message #228868] Thu, 05 April 2007 04:20 Go to previous message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Perfect thanks
Previous Topic: New cursor if first cursor has no records
Next Topic: Calculating Percent on running total
Goto Forum:
  


Current Time: Sat Dec 03 14:02:20 CST 2016

Total time taken to generate the page: 0.08702 seconds