how can i pass block name to procedure [message #279909] |
Sun, 11 November 2007 13:25 |
LAZYGIRL
Messages: 34 Registered: May 2006
|
Member |
|
|
hello guys
i have pre_insert trigger on one block
and i want to pass the name of the block ( table ) and col name to procedure to do a select statement on that block ( table )
i have written some thing like this
PROCEDURE insert_pk(blk_name in varchar2,
col_name in varchar2,
str in varchar2) IS
v_count NUMBER;
v_id blk_name.col_name%TYPE;
v_temp NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM blk_name;
IF v_count = 0 THEN
:blk_name.col_name := str || '001' ELSE
SELECT MAX(t.col_name) INTO v_id FROM blk_name;
but it gives me error in the select statment blk_name must be declared
do any one have an idea
|
|
|
|
|
Re: how can i pass block name to procedure [message #279962 is a reply to message #279929] |
Mon, 12 November 2007 01:47 |
|
Littlefoot
Messages: 21821 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
I think I understand now what was the question.
However, I don't understand WHY would you want to do it that way? What's wrong withSELECT MAX(column_name)
INTO variable
FROM table_name; It wouldn't even return an error if there are no records in the table (unless, of course, there's no such table or column in it).
What is behind that requirement? As you've mentioned the PRE-INSERT trigger, I *guess* you are trying to insert "maximum column value + 1" when a new record is created. Is that correct? If so, well, don't do it that way - use a sequence instead. Because, MAX + 1 will work fine in a single-user environment. Anything more than that will cause problems, sooner or later. There have been discussions about the issue, so - if you'd like to read more about it, search for "MAX SEQUENCE" (mainly in the (PL/)SQL Newbies Forum).
|
|
|
Re: how can i pass block name to procedure [message #280033 is a reply to message #279962] |
Mon, 12 November 2007 06:11 |
LAZYGIRL
Messages: 34 Registered: May 2006
|
Member |
|
|
thanks again
the idea behind that i want to put it in library and use it with any forms insted of repeating the code in diffrent forms
so i can put it in library and call that procedure and pass the related table to the procedure
and there must be somthing to do so
couse now i take copy of the code paste it in diffrent form in pre_insert trigger and as you say it is all about inserting primary key in the table
but i hate to repeat the code here and there
|
|
|
|
|
|
|