4 Replies Latest reply: Dec 6, 2012 3:51 AM by Jim Smith RSS

    running in parameters in sql developer

    primefsu
      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
          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
            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
              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
                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.