Home » SQL & PL/SQL » SQL & PL/SQL » PAssing table and column names as parameters (Oracle 10 g)
PAssing table and column names as parameters [message #325535] Fri, 06 June 2008 09:21 Go to next message
gutta_rajen@yahoo.com
Messages: 16
Registered: September 2006
Junior Member
Hi ,

I am using below procedure to pass table names and column names as parameters.

When i tried to execute the procedure iam getting following error

error code is ORA-00972: identifier is too long

Proceure i created is

CREATE OR REPLACE PROCEDURE SIEBEL_TEST.w_sevreq_d_delete_dynamic (
p_table_name IN user_tables.table_name%TYPE,
column_name IN VARCHAR,
date_wid IN VARCHAR
)
IS
TYPE w_sevreq_fact_row_wid IS REF CURSOR;

w_service_request w_sevreq_fact_row_wid;
f_row_wid user_tables.table_name%TYPE;
date1 VARCHAR2 (10);
counter NUMBER;
counter_10k NUMBER;
BEGIN
DBMS_OUTPUT.put_line ('HELLO123245 ');
date1 := 'MM/DD/YYYY';

OPEN w_service_request FOR 'select'
|| column_name
|| 'from'
|| p_table_name
|| 'WHERE TRUNC (updated_dt) >= TO_DATE (date_wid,Date1)';

counter := 0;
counter_10k := 0;
DBMS_OUTPUT.put_line ('HELLO ');

LOOP
FETCH w_service_request
INTO f_row_wid;

EXIT WHEN w_service_request%NOTFOUND;
EXIT WHEN counter_10k = 10000;
counter := counter + 1;
counter_10k := counter_10k + 1;

--DBMS_OUTPUT.PUT_LINE ('COUNTER IS ' || COUNTER);
IF counter = 100
THEN
COMMIT;
EXIT;
counter := 0;
END IF;

BEGIN
DBMS_OUTPUT.put_line ('Deleted row id are ' || f_row_wid);

EXECUTE IMMEDIATE 'DELETE FROM'
|| p_table_name
|| 'WHERE row_wid = f_row_wid';
END;

DBMS_OUTPUT.put_line ('Deleted row id are ' || f_row_wid);
--DELETE FROM W_SRVREQ_F WHERE SR_WID = W_SEVREQ_FACT_ROW_WID.ROW_WID;
END LOOP;

DBMS_OUTPUT.put_line (f_row_wid);

CLOSE w_service_request;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('error code is ' || SQLERRM);
END;
/

ITS urgent.PLease Let me know how to overcome this issue
Re: PAssing table and column names as parameters [message #325537 is a reply to message #325535] Fri, 06 June 2008 09:23 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above.

>ITS urgent.
Please explain in detail why it is urgent for ME to solve this problem for you.

[Updated on: Fri, 06 June 2008 09:27] by Moderator

Report message to a moderator

Re: PAssing table and column names as parameters [message #325540 is a reply to message #325537] Fri, 06 June 2008 09:37 Go to previous messageGo to next message
gutta_rajen@yahoo.com
Messages: 16
Registered: September 2006
Junior Member
Hi,

As i am doing production support we are in a phase to delete the unwanted data from the tables by passing tables names dynamically
Re: PAssing table and column names as parameters [message #325544 is a reply to message #325540] Fri, 06 June 2008 09:50 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I cannot believe you justify the reason for mentioning urgent in your post. I highly recommend you to read the pl/sql reference manual and re-think about your logic again.

Regards

Raj
Re: PAssing table and column names as parameters [message #325546 is a reply to message #325544] Fri, 06 June 2008 09:56 Go to previous messageGo to next message
gutta_rajen@yahoo.com
Messages: 16
Registered: September 2006
Junior Member
THank you
Re: PAssing table and column names as parameters [message #325547 is a reply to message #325540] Fri, 06 June 2008 09:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd guess that you're passing in a column name longer than 30 chrs.

That code is probably the single least efficient way I have EVER seen of deleting data from a table.

WHY would you write something to dynamically select values from a table and then dynamically delete rows with matching those values?

You could just issue a DELETE statement in sql*plus!

Also, your dynamic select takes account of the DATE_WID parameter (which should be a date, not a varchar2) but your Dynamic Delete does not use this parameter, so you may well be deleting rows that you don't expect.

Add into this the incremental commits, and that exception block, and I'd vote for this as the worst code I've seen in the last month.
Re: PAssing table and column names as parameters [message #325552 is a reply to message #325547] Fri, 06 June 2008 10:01 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I started typing a long essay and half way through I decided to cut it short because it seems to be a never ending one.
Re: PAssing table and column names as parameters [message #325561 is a reply to message #325540] Fri, 06 June 2008 10:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
gutta_rajen@yahoo.com wrote on Fri, 06 June 2008 16:37
Hi,

As i am doing production support we are in a phase to delete the unwanted data from the tables by passing tables names dynamically

from this production code
DBMS_OUTPUT.put_line ('HELLO123245 ');


If this is production code, you really, really have a problem..

[Updated on: Fri, 06 June 2008 10:17]

Report message to a moderator

Re: PAssing table and column names as parameters [message #325567 is a reply to message #325547] Fri, 06 June 2008 10:27 Go to previous messageGo to next message
gutta_rajen@yahoo.com
Messages: 16
Registered: September 2006
Junior Member
Hi
Appreciate your comments.

This is rough draft.
we are having a long que of Dimenssion tables and fact tables. we want to delete the data from fact with respective to the Dim.

Instead of creating a procedure of each DIm and fact . i want to use only thos procedure topass table and columns names to delete rows in all the tables (DIM and fact)


Re: PAssing table and column names as parameters [message #325588 is a reply to message #325535] Fri, 06 June 2008 10:58 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
DBMS_OUTPUT.put_line ('HELLO123245 ');

Very useful message. Why do you not rather write the content of SQL statement you are trying to run dynamically using EXECUTE IMMEDIATE?
Looking at the code, you shall benefit from reading the Performing SQL Operations with Native Dynamic SQL chapter in PL/SQL User's Guide and Reference, found with many other documentation books eg. on http://tahiti.oracle.com/. Concentrate on binding.
Re: PAssing table and column names as parameters [message #325592 is a reply to message #325588] Fri, 06 June 2008 11:15 Go to previous message
gutta_rajen@yahoo.com
Messages: 16
Registered: September 2006
Junior Member
THak you Fly boy
Previous Topic: Materialized view + explain plan
Next Topic: update the same table in trigger
Goto Forum:
  


Current Time: Sat Dec 10 22:19:43 CST 2016

Total time taken to generate the page: 0.07534 seconds