Solved

Name_In and COPY built in

Posted on 2004-10-05
2
4,521 Views
Last Modified: 2013-12-12
I am still unable to understand what is the purpose of above buit ins. what are the problems if I am not using them.

Please give some example as teacher.

Thanks

0
Comment
Question by:hyynes
2 Comments
 
LVL 22

Accepted Solution

by:
Helena Marková earned 25 total points
ID: 12225727
I think that the purpose of these built-ins is well described in the on-line help: "About indirectly referencing parameters".

You cannot use bind variable syntax to refer directly to a parameter in a trigger, user-named routine, menu command, or stored procedure that is not declared in the same module as the parameter itself.

I have found on Metalink a nice example of using NAME_IN:

Example
-------
To capture the rowid of the record in the current block,concatenate '.ROWID' column to :SYSTEM.CURRENT_BLOCK,a system variable which stores the name of the block
that the cursor is in.

Apply the NAME_IN built-in to return the value of the concatenated variable:

NAME_IN(:SYSTEM.CURRENT_BLOCK || '.ROWID');

Create the following sample code:

DECLARE

v_rowid VARCHAR2(30);

/* v_rowid ROWID; */

BEGIN

v_rowid := NAME_IN(:SYSTEM.CURRENT_BLOCK || '.ROWID');

END;
----- end of example from Metalink

Of course you can write also
 v_rowid := :blockname.ROWID;
but if you want to create a function and call it anywhere then it is necessary to use NAME_IN:

FUNCTION MY_FUNC RETURN VARCHAR2 IS
  v_rowid VARCHAR2(30);
BEGIN
  v_rowid := NAME_IN(:SYSTEM.CURRENT_BLOCK || '.ROWID');
  RETURN v_rowid;
END  MY_FUNC;

I hope this will be a little help for you.
0
 
LVL 8

Assisted Solution

by:sapnam
sapnam earned 25 total points
ID: 12227746
This is how we use NAME_IN.  We have several forms in which we have a header block and we have a detail block.  The detail block has debit and credit amounts.  The header block also has an amount.  We have to display total debit and total credit for the document during entry (before commit).  The names of the header and detail blocks change from one form to another.  So we have written a program unit TOTAL to which we pass the names of the header and detail block and use the NAME_IN built in to get the amounts and display the total.
as under

Procedure total (detail char,master char,dtl_flag char,dtl_val char,hdr_val char) IS
  tmp_sum1 NUMBER(13,2);
  tmp_sum2 NUMBER(13,2);
BEGIN
  tmp_sum1 := NVL(TO_NUMBER(NAME_IN(hdr_val)),0);  -- 99 level amount
  tmp_sum2 := 0;
  GO_BLOCK(detail);
  FIRST_RECORD;
  LOOP
     IF NAME_IN(DTL_FLAG) = 'C' THEN
        tmp_sum1 := tmp_sum1+NVL(TO_NUMBER(NAME_IN(dtl_val)),0);
     ELSIF
        NAME_IN(dtl_flag) = 'D' THEN
        tmp_sum2 := tmp_sum2+NVL(TO_NUMBER(NAME_IN(dtl_val)),0);
     ELSIF
        NAME_IN(dtl_flag) IS NULL THEN
        NULL;
     END IF;
     IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
        EXIT;
     ELSE
     NEXT_RECORD;
     END IF;
  END LOOP;
  :b2.crd_amt := tmp_sum1;          -- Shows credit total on screen
  :b2.dbt_amt := tmp_sum2;          -- Shows debit total on screen
END;
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question