Home » Developer & Programmer » Forms » how can i pass block name to procedure
how can i pass block name to procedure [message #279909] Sun, 11 November 2007 13:25 Go to next message
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 #279913 is a reply to message #279909] Sun, 11 November 2007 15:19 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure you can count records in a block like that ... SELECT COUNT(*) needs something different than a form block.

There might be another way to do that, but - for a "small-sized" blocks (i.e. those that don't have many records) I'd simply count records one by one: go to the first record and add 1 to the counter while looping through the block (using NEXT_RECORD) till the end of the block (i.e. last record -> :SYSTEM.LAST_RECORD is true).
Re: how can i pass block name to procedure [message #279929 is a reply to message #279909] Sun, 11 November 2007 23:31 Go to previous messageGo to next message
LAZYGIRL
Messages: 34
Registered: May 2006
Member
thanks Littlefoot for youe replay but you miss understand what i ask for

my question was how to pass the table name to procedure to do select or any action on that table


so any one have idea how can i do that




Re: how can i pass block name to procedure [message #279962 is a reply to message #279929] Mon, 12 November 2007 01:47 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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 with
SELECT 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 Go to previous messageGo to next message
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



Re: how can i pass block name to procedure [message #280040 is a reply to message #280033] Mon, 12 November 2007 06:21 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

it is all about inserting primary key in the table

Once again: do NOT do it that way! Use a sequence instead; if you wish, you can even create a database trigger which would take care about it. Just don't use "MAX + 1".
Re: how can i pass block name to procedure [message #280045 is a reply to message #280033] Mon, 12 November 2007 06:28 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

Search for EXEC_SQL in this forum and check the below link

http://www.orafaq.com/forum/m/252964/84678/#msg_252964


Mudabbir
Re: how can i pass block name to procedure [message #280049 is a reply to message #280045] Mon, 12 November 2007 06:41 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As I see it, it is not "can it be done" problem, but "design" problem. And I stand behind my words: primary key value should not be created as "MAX + 1", whether it is possible or not (yes, it is possible).
Re: how can i pass block name to procedure [message #280130 is a reply to message #280049] Mon, 12 November 2007 10:33 Go to previous message
LAZYGIRL
Messages: 34
Registered: May 2006
Member
thanks you both guys for your help and advice

Previous Topic: Can we connect Forms 6i with Sql server
Next Topic: Global Variable in developer 9i
Goto Forum:
  


Current Time: Sun Dec 11 00:11:26 CST 2016

Total time taken to generate the page: 0.08247 seconds