Advertisement

02.23.2007 at 02:08PM PST, ID: 22410241
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
7.2

How to pass a parameter from a calling procedure to another procedure in PL/SQL

Asked by 7Souls in PL / SQL, Oracle Database, Oracle 9.x

Tags: , ,

Hi I'm trying to have one procedure schedule another but I would like the scheduled procedure to pass a parameter called 'jobno' to the called procedure 'SQL_EXEC_PROC'. I'm not sure how to do this since I'm new to PL/SQL. Here's what I got.

I've created at table called A_Record_table and it looks like this:

SQL> desc stgadm.a_record_table
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 JOB                                       NOT NULL NUMBER(10)
 RETURN_CDE                                NOT NULL NUMBER(10)
 RUN_DATE                                  NOT NULL DATE

and what I'm trying to do is do a DBMS_JOB.Submit procedure that calls another procedure that executes any ole SQL and then inserts into table A_Record_table the Jobno, the Sqlerrm, and the SYSDATE.

this is what I have and I'm having trouble getting them together.

Procedure SQL_EXEC_PROC as
       v_cnt number;
       return_code number;
   BEGIN
      execute immediate 'select count(1) from V$database' into v_cnt;
        return_code := SQLCODE;
        insert into stgadm.a_record_table(JOB, RETURN_CDE, RUN_DATE) values(jobno, return_code, sysdate);
      commit;
   end;

DECLARE
    jobno NUMBER;
    text      varchar2(200);
BEGIN
    text:= 'STGADM.SQL_EXEC_PROC;';
    DBMS_JOB.SUBMIT(jobno,text,TRUNC(SYSDATE, 'MI'), 'TRUNC(SYSDATE,''MI'')+2/(24*60)');
      commit;
      DBMS_OUTPUT.PUT_LINE('job number:'||TO_CHAR(jobno));
end;      

Any help, thanks,
7SoulsStart Free Trial
[+][-]02.23.2007 at 03:08PM PST, ID: 18600048

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]02.26.2007 at 12:22AM PST, ID: 18607804

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: PL / SQL, Oracle Database, Oracle 9.x
Tags: procedure, parameters, pass
Sign Up Now!
Solution Provided By: sujith80
Participating Experts: 3
Solution Grade: A
 
 
[+][-]02.26.2007 at 07:35AM PST, ID: 18609770

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]02.26.2007 at 12:55PM PST, ID: 18612383

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]02.26.2007 at 01:06PM PST, ID: 18612468

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]02.26.2007 at 09:52PM PST, ID: 18614968

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]02.27.2007 at 07:53AM PST, ID: 18617838

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]02.28.2007 at 02:52AM PST, ID: 18623796

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]02.28.2007 at 06:29AM PST, ID: 18625133

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]02.28.2007 at 06:57AM PST, ID: 18625406

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]02.28.2007 at 07:57AM PST, ID: 18626044

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]03.01.2007 at 04:14AM PST, ID: 18632330

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]03.28.2007 at 10:23AM PDT, ID: 18810067

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.19.2007 at 02:34PM PDT, ID: 19320206

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 7-day free trial to view this Administrative Comment or ask the Experts your question.

 
[+][-]07.13.2007 at 04:35AM PDT, ID: 19479736

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 7-day free trial to view this Administrative Comment or ask the Experts your question.

 
[+][-]07.20.2007 at 03:31PM PDT, ID: 19535890

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 7-day free trial to view this Administrative Comment or ask the Experts your question.

 
 
Loading Advertisement...
20081112-EE-VQP-42