Output parameter and dynamic sql

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Robert Scheer
    #1

    Output parameter and dynamic sql

    Hi.

    I have a stored procedure on a Oracle 8.1.6 database that generates a
    dynamic sql statement. This stored procedure has an output parameter
    that needs to return a count from a view. I can generate and run the
    sql successfuly, but when I try to return the count I get errors. I am
    showing the relevant part of the procedure, since the rest of
    concatenations are used to compose the sql.

    CREATE OR REPLACE PROCEDURE getScoreEntries (user VARCHAR2, level
    NUMBER, ret OUT NUMBER)
    AS

    sCall VARCHAR2(500);
    nTotal NUMBER;

    BEGIN
    sCall := 'SELECT COUNT(*) INTO nTotal FROM vw_Scores WHERE
    UserName=''' || user || '''';
    sCall := sCall || --dynamic sql continues here...

    EXECUTE IMMEDIATE sCall;
    ret := nTotal;

    END getTotalEntries ;

    Can anyone help me with this issue?

    Thanks,
    Robert Scheer
    Tags: None
    • Romeo Olympia
      #2
      Re: Output parameter and dynamic sql

      A quick look of your code shows two things that need revising:
      (1) You need to use the INTO clause of EXECUTE IMMEDIATE when you want
      to get the results of a single-row query. In your case, the "count(*)"
      value.
      (2) Use bind arguments whenever possible (USING clause). That's the
      "where username" condition for you.

      So try something like this:

      ....
      sCall := 'SELECT COUNT(*) FROM vw_Scores WHERE UserName = :username';

      EXECUTE IMMEDIATE sCall INTO nTotal USING user;
      ....

      Do read the PL/SQL guide for more detailed info. HTH.

      rbscheer@my-deja.com (Robert Scheer) wrote in message news:<cfd22ab6. 0407201757.996c 4ca@posting.goo gle.com>...
      Hi.
      >
      I have a stored procedure on a Oracle 8.1.6 database that generates a
      dynamic sql statement. This stored procedure has an output parameter
      that needs to return a count from a view. I can generate and run the
      sql successfuly, but when I try to return the count I get errors. I am
      showing the relevant part of the procedure, since the rest of
      concatenations are used to compose the sql.
      >
      CREATE OR REPLACE PROCEDURE getScoreEntries (user VARCHAR2, level
      NUMBER, ret OUT NUMBER)
      AS
      >
      sCall VARCHAR2(500);
      nTotal NUMBER;
      >
      BEGIN
      sCall := 'SELECT COUNT(*) INTO nTotal FROM vw_Scores WHERE
      UserName=''' || user || '''';
      sCall := sCall || --dynamic sql continues here...
      >
      EXECUTE IMMEDIATE sCall;
      ret := nTotal;
      >
      END getTotalEntries ;
      >
      Can anyone help me with this issue?
      >
      Thanks,
      Robert Scheer

        Comment

        • Romeo Olympia
          #3
          Re: Output parameter and dynamic sql

          A quick look of your code shows two things that need revising:
          (1) You need to use the INTO clause of EXECUTE IMMEDIATE when you want
          to get the results of a single-row query. In your case, the "count(*)"
          value.
          (2) Use bind arguments whenever possible (USING clause). That's the
          "where username" condition for you.

          So try something like this:

          ....
          sCall := 'SELECT COUNT(*) FROM vw_Scores WHERE UserName = :username';

          EXECUTE IMMEDIATE sCall INTO nTotal USING user;
          ....

          Do read the PL/SQL guide for more detailed info. HTH.

          rbscheer@my-deja.com (Robert Scheer) wrote in message news:<cfd22ab6. 0407201757.996c 4ca@posting.goo gle.com>...
          Hi.
          >
          I have a stored procedure on a Oracle 8.1.6 database that generates a
          dynamic sql statement. This stored procedure has an output parameter
          that needs to return a count from a view. I can generate and run the
          sql successfuly, but when I try to return the count I get errors. I am
          showing the relevant part of the procedure, since the rest of
          concatenations are used to compose the sql.
          >
          CREATE OR REPLACE PROCEDURE getScoreEntries (user VARCHAR2, level
          NUMBER, ret OUT NUMBER)
          AS
          >
          sCall VARCHAR2(500);
          nTotal NUMBER;
          >
          BEGIN
          sCall := 'SELECT COUNT(*) INTO nTotal FROM vw_Scores WHERE
          UserName=''' || user || '''';
          sCall := sCall || --dynamic sql continues here...
          >
          EXECUTE IMMEDIATE sCall;
          ret := nTotal;
          >
          END getTotalEntries ;
          >
          Can anyone help me with this issue?
          >
          Thanks,
          Robert Scheer

            Comment

            • sybrandb@yahoo.com
              #4
              Re: Output parameter and dynamic sql

              rbscheer@my-deja.com (Robert Scheer) wrote in message news:<cfd22ab6. 0407201757.996c 4ca@posting.goo gle.com>...
              Hi.
              >
              I have a stored procedure on a Oracle 8.1.6 database that generates a
              dynamic sql statement. This stored procedure has an output parameter
              that needs to return a count from a view. I can generate and run the
              sql successfuly, but when I try to return the count I get errors. I am
              showing the relevant part of the procedure, since the rest of
              concatenations are used to compose the sql.
              >
              CREATE OR REPLACE PROCEDURE getScoreEntries (user VARCHAR2, level
              NUMBER, ret OUT NUMBER)
              AS
              >
              sCall VARCHAR2(500);
              nTotal NUMBER;
              >
              BEGIN
              sCall := 'SELECT COUNT(*) INTO nTotal FROM vw_Scores WHERE
              UserName=''' || user || '''';
              sCall := sCall || --dynamic sql continues here...
              >
              EXECUTE IMMEDIATE sCall;
              ret := nTotal;
              >
              END getTotalEntries ;
              >
              Can anyone help me with this issue?
              >
              Thanks,
              Robert Scheer
              Don't see an INTO clause after the execute immediate statement.

              Other than that: do you think anyone here can guess what your
              unspecified errors are?

              Sybrand Bakker
              Senior Oracle DBA

                Comment

                • Robert Scheer
                  #5
                  Re: Output parameter and dynamic sql

                  Hi Romeo,

                  thanks, it worked!

                  Robert Scheer


                  rolympia@hotmai l.com (Romeo Olympia) wrote in message news:<42fc55dc. 0407202248.2b95 3649@posting.go ogle.com>...
                  A quick look of your code shows two things that need revising:
                  (1) You need to use the INTO clause of EXECUTE IMMEDIATE when you want
                  to get the results of a single-row query. In your case, the "count(*)"
                  value.
                  (2) Use bind arguments whenever possible (USING clause). That's the
                  "where username" condition for you.
                  >
                  So try something like this:
                  >
                  ...
                  sCall := 'SELECT COUNT(*) FROM vw_Scores WHERE UserName = :username';
                  >
                  EXECUTE IMMEDIATE sCall INTO nTotal USING user;
                  ...
                  >
                  Do read the PL/SQL guide for more detailed info. HTH.
                  >
                  rbscheer@my-deja.com (Robert Scheer) wrote in message news:<cfd22ab6. 0407201757.996c 4ca@posting.goo gle.com>...
                  Hi.

                  I have a stored procedure on a Oracle 8.1.6 database that generates a
                  dynamic sql statement. This stored procedure has an output parameter
                  that needs to return a count from a view. I can generate and run the
                  sql successfuly, but when I try to return the count I get errors. I am
                  showing the relevant part of the procedure, since the rest of
                  concatenations are used to compose the sql.

                  CREATE OR REPLACE PROCEDURE getScoreEntries (user VARCHAR2, level
                  NUMBER, ret OUT NUMBER)
                  AS

                  sCall VARCHAR2(500);
                  nTotal NUMBER;

                  BEGIN
                  sCall := 'SELECT COUNT(*) INTO nTotal FROM vw_Scores WHERE
                  UserName=''' || user || '''';
                  sCall := sCall || --dynamic sql continues here...

                  EXECUTE IMMEDIATE sCall;
                  ret := nTotal;

                  END getTotalEntries ;

                  Can anyone help me with this issue?

                  Thanks,
                  Robert Scheer

                    Comment

                    Previous template Next
                    Working...