02-03-12, 11:00 #1Registered User
- Join Date
- Feb 2012
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;
FOR ThisRec IN cSchemas LOOP
INSERT INTO schema_sizes VALUES(ThisRec.timestamp1, ThisRec.DB, ThisRec.schema, ThisRec.Mbytes);
WHEN OTHERS THEN NULL;
execute immediate 'ALTER SESSION CLOSE DATABASE LINK ' || thisDB;
any help would be greatly appreciated
02-03-12, 11:23 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
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.
02-03-12, 13:37 #3Registered User
- Join Date
- Mar 2007
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.