Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    3

    Unanswered: exec a procedure and pass in a parameter

    I am trying to create a procedure that will read in a param and then use that within a cursor - see code below

    i am trying to read data from another database via a db_link, the db_link name is the parameter being passed in using the command

    BEGIN get_disk_times('TargetDB'); END;

    the error being thrown is regarding the PL/SQL: ORA-01729: database link name expected relating to the line 'CURSOR'

    Create or replace PROCEDURE get_schema_sizes_SIE(thisDB IN VARCHAR) IS

    CURSOR cSchemas IS SELECT TRUNC(SYSDATE)-1 timestamp1, '||thisDB||' DB, owner schema, SUM(bytes)/1024/1024 Mbytes FROM dba_segments@'||thisDB||'
    GROUP BY OWNER
    ORDER BY OWNER ASC;

    BEGIN

    FOR ThisRec IN cSchemas LOOP
    BEGIN
    INSERT INTO schema_sizes VALUES(ThisRec.timestamp1, ThisRec.DB, ThisRec.schema, ThisRec.Mbytes);
    EXCEPTION
    WHEN OTHERS THEN NULL;
    END;
    END LOOP;
    commit;
    execute immediate 'ALTER SESSION CLOSE DATABASE LINK ' || thisDB;

    END get_schema_sizes_SIE;

    any help would be greatly appreciated

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL statements must be static within PL/SQL procedures
    or EXECUTE IMMEDIATE must be used.
    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
    Mar 2007
    Posts
    623
    Maybe, instead of guessing syntax of cursor for dynamic SQL, you should consult it in the documentation. Cursors (as part of PL/SQL language) are described in PL/SQL User's Guide and Reference book, which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/
    For 11gR2, the relevant chapter is placed here: http://docs.oracle.com/cd/E11882_01/...mic.htm#i13057

    But, maybe a single dynamic (EXECUTE IMMEDIATE) INSERT SELECT statement may be better choice over this cursor approach.

    Finally, just a note about WHEN OTHERS THEN NULL exception hiding section: it just states, that you do not bother whether the INSERT fails, its failure will be silently ignored. So, in my opinion, the whole procedure is just a waste of time, as inserting nothing is also correct (non-failing) behaviour. If you do not feel so, remove that whole EXCEPTION clause forever.

Posting Permissions

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