Home » SQL & PL/SQL » SQL & PL/SQL » Problem while using DBMS_SQL and passing table name as a parameter
| Problem while using DBMS_SQL and passing table name as a parameter [message #39593] |
Thu, 25 July 2002 03:49  |
Nirmal Kumar
Messages: 1
Registered: July 2002
|
Junior Member |
|
|
Iam writing a stored procedure using dynamic sql DBMS_SQL
package in which we pass the table name and column name ( date type)
as a parameter for which we need to delete the records older than x months ( again paramter to procedure) .
procedure is created successfully but giving error at Run-Time .
URGENT HELP NEEEEEEEEEEDED*******************
E-mail : Nirmal.Kumar@geind.ge.com
The Error is
SQL >exec ci.proc_purge('ci.t053015','maint_date',-18,1100);
*********************************************
Procedure >>> Proc_purge <<< started
AT 25-jul-2002 05:36:43
for table ci.T053015
BEGIN SELECT count(1) into :xcount FROM :xtable WHERE :xcol < (SELECT
add_months(SYSDATE,:xmonth ) from dual ) ; END ;
Exception in procedure Proc_purge ** for table ci.T053015
AT 25-jul-2002 05:36:43
SQL Code := -6550
SQL Err Message := ORA-06550: line 1, column 43:
PLS-00103: Encountered the
symbol "" when expecting one of the following:
<an identifier> <a
double-quoted delimited-identifier>
The symbol "<an identifier> was
PL/SQL procedure successfully completed.
set serveroutput on
CREATE or REPLACE PROCEDURE CI.PROC_PURGE ( table_name IN varchar2 , col_name IN varchar2, month1 IN NUMBER , No_records IN NUMBER )
is
icount NUMBER := 0;
iloop NUMBER := 0;
irem NUMBER := 0;
sqlstring VARCHAR2(2000) := '';
sqlstring1 VARCHAR2(2000) := '';
sqlstring2 VARCHAR2(2000) := '';
sqlstring3 VARCHAR2(2000) := '';
cur INTEGER;
iretcur INTEGER;
i NUMBER :=0 ;
BEGIN
DBMS_OUTPUT.ENABLE (100000) ;
DBMS_OUTPUT.PUT_LINE ('*********************************************');
DBMS_OUTPUT.PUT_LINE (' Procedure >>> Proc_purge <<< started ');
DBMS_OUTPUT.PUT_LINE (' AT ' || to_char(Sysdate,'dd-mon-yyyy hh:mi:ss') || '
for table ' || table_name );
/*check if user input is negative , it will delete current data ..make very sure user has not input
the negative number */
/* IF (month1 <= 0 ) THEN
DBMS_OUTPUT.PUT_LINE(' USER has given input of negative or zero month ' );
DBMS_OUTPUT.PUT_LINE(' i.e Trying to delete current data ' ||' from table ' ||
table_name );
DBMS_OUTPUT.PUT_LINE(' AT ' || to_char(Sysdate,'dd-mon-yyyy hh:mi:ss') || ' EXITING
...');
RETURN;
END IF;
*/
cur := DBMS_SQL.OPEN_CURSOR;
sqlstring1 := ' BEGIN ';
sqlstring2:= ' SELECT count(1) into :xcount FROM :xtable WHERE :xcol < (SELECT add_months(SYSDATE,:xmonth ) from dual ) ; ';
sqlstring3:= ' END ;';
sqlstring := sqlstring1 || sqlstring2 || sqlstring3;
dbms_output.put_line(sqlstring);
DBMS_SQL.PARSE(cur, sqlstring ,DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(cur,':xcount',icount);
DBMS_SQL.BIND_VARIABLE(cur,':xtable',table_name);
DBMS_SQL.BIND_VARIABLE(cur,':xcol',col_name);
DBMS_SQL.BIND_VARIABLE(cur,':xmonth',month1);
dbms_output.put_line(table_name ||' ' || col_name || to_char(month1) );
iretcur := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.VARIABLE_VALUE(cur,':xcount',icount);
irem := Mod(icount , No_records) ;
iloop := trunc(icount / No_records) ;
sqlstring := '';
sqlstring1 := ' BEGIN ';
sqlstring2 := ' DELETE FROM :xtable WHERE :xcol < (SELECT add_months(SYSDATE,:xmonth ) from dual ) AND ROWNUM <= :xno ; ';
sqlstring3 := ' END ; ' ;
sqlstring := sqlstring1 || sqlstring2 || sqlstring3;
dbms_output.put_line(sqlstring);
IF iloop > 0 THEN
For I in 1 .. iloop
LOOP
DBMS_SQL.PARSE( cur ,sqlstring , DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(cur,':xtable',table_name);
DBMS_SQL.BIND_VARIABLE(cur,':xno',no_records);
DBMS_SQL.BIND_VARIABLE(cur,':xcol',col_name);
DBMS_SQL.BIND_VARIABLE(cur,':xmonth',month1);
iretcur :=DBMS_SQL.EXECUTE(cur);
commit;
DBMS_OUTPUT.PUT_LINE ( I || ' time ' || ' ' || to_char(No_records)
|| ' records are deleted ');
DBMS_OUTPUT.PUT_LINE (' from table = ' || table_name ||
' based on date column = ' || col_name);
DBMS_OUTPUT.PUT_LINE (' which are older than ' || to_char(month1) || ' months ');
END LOOP ;
END IF ; --iloop > 0
IF irem > 0 THEN
sqlstring1 := ' BEGIN ';
sqlstring2:= ' SELECT count(1) into :xcount FROM :xtable WHERE :xcol < (SELECT add_months(SYSDATE,:xmonth ) from dual ) ; ';
sqlstring3:= ' END ;';
sqlstring := sqlstring1 || sqlstring2 || sqlstring3;
dbms_output.put_line(sqlstring);
DBMS_SQL.PARSE(cur ,sqlstring , DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(cur,':xtable',table_name);
DBMS_SQL.BIND_VARIABLE(cur,':xcol',col_name);
DBMS_SQL.BIND_VARIABLE(cur,':xmonth',month1);
iretcur :=DBMS_SQL.EXECUTE(cur);
commit;
DBMS_OUTPUT.PUT_LINE ( to_char(irem) || ' records are deleted from table = ' || table_name );
DBMS_OUTPUT.PUT_LINE (' based on date column = ' || col_name );
DBMS_OUTPUT.PUT_LINE (' which are older than ' || to_char(month1) || ' months ');
END IF ;
DBMS_SQL.CLOSE_CURSOR(cur);
DBMS_OUTPUT.PUT_LINE (' Proc_purge <<< finished AT '||
to_char(Sysdate,'dd-mon-yyyy hh:mi:ss') || ' for table ' ||table_name);
DBMS_OUTPUT.PUT_LINE (' ************************* ');
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(cur) THEN
DBMS_SQL.CLOSE_CURSOR(cur);
END IF;
DBMS_OUTPUT.PUT_LINE(' Exception in procedure Proc_purge ** for table ' || table_name);
DBMS_OUTPUT.PUT_LINE( ' AT ' ||to_char(Sysdate,'dd-mon-yyyy hh:mi:ss') );
DBMS_OUTPUT.PUT_LINE( ' SQL Code := ' || SQLCODE );
DBMS_OUTPUT.PUT_LINE( ' SQL Err Message := ' || SQLERRM );
ROLLBACK;
END Proc_purge;
/
show errors ;
/
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 04:09:03 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
