Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Location
    Bucharest
    Posts
    6

    Question Unanswered: can't see the result of my procedure

    "Hello world"!

    I made a procedure that takes a name of a table as a parameter, it doesn't have errors(or at least this is what it shows me), but when I execute it I can't see anything. this is the code:

    CREATE or replace procedure pp
    (tbl in VARCHAR2)
    as
    Str varchar2(50);
    Begin
    Str:= 'Select cant from '||tbl;
    execute immediate(Str);
    End pp;
    /

    exec pp('products');

    Procedure succesfully completed.


    What should I do to actually see....something?

    Thank you!
    Patricia.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What should I do to actually see....something?
    There is nothing to "see"
    PL/SQL runs inside the database engine & has to direct contact with the outside world.
    You could SPOOL the SELECT out to a file (capture.sql) & then @capture.sql
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2009
    Location
    Bucharest
    Posts
    6
    Thanks for fast reply, anacedent, I don't know what actually means spool, and how to do it, but i'm going to google it right now. hope to solve this tonight .

    Patricia

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Oracle is not SQL Server, you can't just do a select and expect the result set to be returned.

    You want to look into pipelined functions, that will enabled you to do a SELECT * FROM pp

    Setting Up Transformations with Pipelined Functions

    The other alternative would be REF CURSORS

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Simple testing

    Hi Patricia,

    If you want to do some simple testing of your procedures, try something like this:
    Code:
    SQL> CREATE OR REPLACE PROCEDURE pp (tbl IN VARCHAR2, rslt OUT sys_refcursor)
      2  AS
      3     str   VARCHAR2 (50);
      4  BEGIN
      5  
      6     str := 'SELECT * FROM '|| tbl ||' WHERE ROWNUM < 11';
      7  
      8     OPEN rslt FOR str;
      9  
     10  END;
     11  /
    
    Procedure created.
    
    SQL> VAR plist REFCURSOR;
    SQL> EXEC pp('PRODUCTS',:plist);
    
    PL/SQL procedure successfully completed.
    
    SQL> PRINT plist
    
    PRODUCT_ID PRODUCT_NAME                                       LIST_PRICE
    ---------- -------------------------------------------------- ----------
          1781 CDW 20/48/E                                               233
          2264 CDW 20/48/I                                               223
          2260 DFD 1.44/3.5                                               67
          2266 DVD 12x                                                   333
          3077 DVD 8x                                                    274
          2259 FD 1.44/3.5                                                39
          2261 FD 1.44/3.5/E                                              42
          3082 Modem - 56/90/E                                            81
          2270 Modem - 56/90/I                                            66
          2268 Modem - 56/H/E                                             77
    
    10 rows selected.
    
    SQL>
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Jun 2009
    Location
    Bucharest
    Posts
    6
    hehe, thank you LKBrwn_DBA. u rock

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •