This discussion is archived
4 Replies Latest reply: Dec 6, 2012 1:51 AM by Jim Smith RSS

running in parameters in sql developer

primefsu Newbie
Currently Being Moderated
Every time I run a package body in SQL Developer. A screen shot pops with this information:

DECLARE
REPORT_TYPE VARCHAR2(200); --in parameter
ORG_CONTRACT VARCHAR2(200); -- in parameter
CUR_CONTRACT VARCHAR2(200); -- in parameter
REPORT_BEG_D DATE; --in parameter
REPORT_END_D DATE; --in parameter
MY_FILE VARCHAR2(200); --out parameter
BEGIN
REPORT_TYPE := NULL;
ORG_CONTRACT := NULL;
CUR_CONTRACT := NULL;
REPORT_BEG_D := NULL;
REPORT_END_D := NULL;

RUN_REPORTS.RUN_CLIENTLIST(
REPORT_TYPE => REPORT_TYPE,
ORG_CONTRACT => ORG_CONTRACT,
CUR_CONTRACT => CUR_CONTRACT,
REPORT_BEG_D => REPORT_BEG_D,
REPORT_END_D => REPORT_END_D,
MY_FILE => MY_FILE
);
/* Legacy output:
DBMS_OUTPUT.PUT_LINE('MY_FILE = ' || MY_FILE);
*/
:MY_FILE := MY_FILE;
END;

What lines should I use to place the IN parameters?
  • 1. Re: running in parameters in sql developer
    APC Oracle ACE
    Currently Being Moderated
    This a standard harness which SQL Developer generates from a template.

    This is the section which populates the variables that are sent to the procedure:
    REPORT_TYPE := NULL;
    ORG_CONTRACT := NULL;
    CUR_CONTRACT := NULL;
    REPORT_BEG_D := NULL;
    REPORT_END_D := NULL;
    So that's what you need to edit to pass your required values. Oracle generates the harness like this to give us some options when it comes to determ8ining the values, e.g. referencing other variables, using SQL or PL/SQL calls, etc.

    Cheers, APC
  • 2. Re: running in parameters in sql developer
    primefsu Newbie
    Currently Being Moderated
    APC,

    What happens when I put the IN parameters in the first session, and I then open the window again and it still has the IN parameters. When will these IN parameters go away with a fresh set of NULL values?
  • 3. Re: running in parameters in sql developer
    APC Oracle ACE
    Currently Being Moderated
    As I understand it SQL Developer does not allow multiple session for the same connection. So all the open tabs for the same connection are in one session. So obviously this means taht we can only do one thing per connection, not per tab. Thus your second report won't run until the first report ends.

    If you want to do two things in parallel with the same user you will need to create a second connection - different connection name, same connection properties.

    This is a limitation of SQL Developer.

    Cheers, APC
  • 4. Re: running in parameters in sql developer
    Jim Smith Expert
    Currently Being Moderated
    APC wrote:
    As I understand it SQL Developer does not allow multiple session for the same connection. So all the open tabs for the same connection are in one session. So obviously this means taht we can only do one thing per connection, not per tab. Thus your second report won't run until the first report ends.

    If you want to do two things in parallel with the same user you will need to create a second connection - different connection name, same connection properties.

    This is a limitation of SQL Developer.

    Cheers, APC
    Not entirely true. You can open an "Unshared worksheet" which uses a separate instance of the connection. You can only have one object browser per connection though.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points