Home » SQL & PL/SQL » SQL & PL/SQL » Calling a stored procedure from a PL/SQL block
Calling a stored procedure from a PL/SQL block [message #36168] Thu, 08 November 2001 07:47 Go to next message
sriram
Messages: 58
Registered: September 2000
Member
Hello All,

I am working on an automation PL/SQL block for creating duplicate (backup) tables. This PL/SQL block uses a stored procedure for executing the create statement. Now I am using a table to store the names of the tables I need to back up. The problem is, that when there is a tablename that does not exist in the database, the PL/SQL just bombs, throwing the 20001 exception on the screen. I need to find a work around, such that it skips the row if the table is not found and goes on to the next row without terminating. Please HELP!!!

Here is the PL/SQL block
set serveroutput on
undefine test_id
accept test_id prompt 'Enter the test id for the back up: '

set heading off

spool g:radarscriptsbu_ap_vchr_tables.spool

DECLARE
cursor c1 is select table_name from ap_vchr_tbls_for_bkp;
table_name varchar2(30);
test_id varchar2(3);
cr_str varchar2(1000);
tab_name varchar2(1000);
csr_handle INTEGER;
cSQL_Statement VARCHAR2(200);
outtabcnt INTEGER;
cnt_rows INTEGER;
outrow Varchar2(1000);

BEGIN
OPEN c1;
LOOP
FETCH c1 into table_name;
EXIT WHEN c1%NOTFOUND;
cr_str := 'CREATE TABLE '||table_name||'_BAK_'||'&&test_id'||' as select * from '||table_name;
EXEC_DDL(cr_str);

tab_name := table_name||'_bak_'||'&&test_id' ;
cSQL_Statement := 'SELECT '||'count(*) from '||tab_name;
csr_handle := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(csr_handle, cSQL_Statement, DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(csr_handle,1,outtabcnt);
cnt_rows := DBMS_SQL.EXECUTE(csr_handle);
LOOP
IF DBMS_SQL.FETCH_ROWS(csr_handle) = 0 Then
EXIT;
ELSE
DBMS_SQL.COLUMN_VALUE(csr_handle,1,outtabcnt);
outrow := '# rows = '||to_char(outtabcnt);
DBMS_OUTPUT.PUT_LINE(table_name||' '||tab_name||' '||outrow);
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(csr_handle);
END LOOP;
CLOSE c1;
END;

Here is the Stored procedure

CREATE OR REPLACE PROCEDURE exec_ddl (p_statement_txt VARCHAR2) IS
lv_exec_cursor_num PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
lv_rows_processed_num PLS_INTEGER := 0;
BEGIN
DBMS_SQL.PARSE (lv_exec_cursor_num, p_statement_txt, DBMS_SQL.NATIVE);
lv_rows_processed_num := DBMS_SQL.EXECUTE (lv_exec_cursor_num);
DBMS_SQL.CLOSE_CURSOR (lv_exec_cursor_num);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN (lv_exec_cursor_num) THEN
DBMS_SQL.CLOSE_CURSOR (lv_exec_cursor_num);
END IF;
RAISE_APPLICATION_ERROR(-20001,'Incorrect DDL Statement.');
END exec_ddl;

----------------------------------------------------------------------
Re: Calling a stored procedure from a PL/SQL block [message #36175 is a reply to message #36168] Thu, 08 November 2001 09:13 Go to previous messageGo to next message
hello
Messages: 17
Registered: November 2001
Junior Member
I guess u can include all the code that is erroring out in a begin, exception, end statement
catching the exception so that the fetch goes on normal

for example
begin
cr_str := 'CREATE TABLE '||table_name||'_BAK_'||'&&test_id'||' as select * from '||table_name;
EXEC_DDL(cr_str);

exception
when others then
null;
end;

----------------------------------------------------------------------
Re: Calling a stored procedure from a PL/SQL block [message #36177 is a reply to message #36168] Thu, 08 November 2001 09:19 Go to previous messageGo to next message
sriram
Messages: 58
Registered: September 2000
Member
But then, is it possible that I could find out, why the exception occured in the first place.... something like an error message that throws up the error.

----------------------------------------------------------------------
Re: Calling a stored procedure from a PL/SQL block [message #36179 is a reply to message #36168] Thu, 08 November 2001 09:27 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
on 8i or above I'd just use "execute immediate" to simplify things. Also, you can easily test for the existence of your table by "select 1 into v_dummy from user_tables where table_name=i.table_name". Alternatley, change your query to:
cursor c1 is select table_name from ap_vchr_tbls_for_bkp a, user_tables b
where upper(a.table_name) = b.table_name;

CREATE OR REPLACE procedure bakup_tables
is
tab_already_exists exception;
pragma exception_init(tab_already_exists, -955);
begin
for tab in (select table_name from cat) loop
begin
DBMS_OUTPUT.PUT_LINE ( 'trying to create ' || tab.table_name||'_'||to_char(sysdate, 'yyyymmdd') );
execute immediate 'create table '||tab.table_name||'_'||to_char(sysdate, 'yyyymmdd')||' as select * from '||tab.table_name;
exception
when tab_already_exists then null;
DBMS_OUTPUT.PUT_LINE ( '==>creation of ' || tab.table_name||'_'||to_char(sysdate, 'yyyymmdd')||' failed' );
end;
end loop;
exception
when others then raise;
end;
/

----------------------------------------------------------------------
Previous Topic: UTL_FILE, read and write the same file
Next Topic: doubt in pl\sql wrapping
Goto Forum:
  


Current Time: Thu Mar 28 05:01:31 CDT 2024