Home » SQL & PL/SQL » SQL & PL/SQL » Using TABLE functions in select statement (merged 3)
Using TABLE functions in select statement (merged 3) [message #382981] Mon, 26 January 2009 22:40 Go to next message
mnair
Messages: 5
Registered: January 2009
Junior Member
I have created the following procedure :-
CREATE OR REPLACE PROCEDURE test(iv_buyer    IN g.gln%TYPE,
                             it_bpinstanceid IN t_bpinstance_id,
                             it_docrefnum    IN t_docref_num,
                             iv_docnum       IN d.doc_num%TYPE,
                             iv_msgid        IN m.msg_id%TYPE,
                             iv_doctype      IN d.doc_type_name%TYPE,
                             ov_errmsg       OUT VARCHAR2) AS

tb_bpinstance_id t_bpinstance_id := t_bpinstance_id(NULL);
 BEGIN
 SELECT 1 BULK COLLECT INTO tb_bpinstance_id FROM dual WHERE 1 != 1;
        SELECT docbp.bpinst_sq_id BULK COLLECT
          INTO tb_bpinstance_id
          FROM d edidoc,
               g gln,
               dr docbp,
               (SELECT column_value
                  FROM TABLE(CAST(it_docrefnum AS t_docref_num))) tdoc
         WHERE edidoc.doc_num = tdoc.column_value
           AND edidoc.delivparty_gln = gln.gln_number
           AND edidoc.doc_sq_id = docbp.doc_sq_id
           AND edidoc.delivparty_gln = iv_buyer;
         IF (tb_bpinstance_id.COUNT != 0) THEN
         
          FORALL i IN tb_bpinstance_id.FIRST .. tb_bpinstance_id.LAST SAVE EXCEPTIONS

            INSERT INTO dummy
              (doc_bp_sq_id, doc_ref_sq_id, doc_sq_id, bpinst_sq_id)
            VALUES
              (docbpsqid_sq.NEXTVAL, NULL, n_docsqid, tb_bpinstance_id(i));
        END IF;
        COMMIT;

end;

The procedure runs successfully but does not insert records in to the dummy table.

I think the SELECT statement does not retrieve the records based on the joining condition.. Can anyone please tell me if the TABLE function used in the SELECT statement has any issues.


[EDITED by LF: applied [code] tags]

[Updated on: Tue, 27 January 2009 00:44] by Moderator

Report message to a moderator

Re: Using TABLE functions in select statement [message #382982 is a reply to message #382981] Mon, 26 January 2009 22:43 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Using TABLE functions in select statement [message #382984 is a reply to message #382981] Mon, 26 January 2009 22:46 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
do NOT repost same problem in multiple threads!
Re: Using TABLE functions in select statement (merged 3) [message #382995 is a reply to message #382981] Mon, 26 January 2009 23:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session(s). How could we answer without seeing what you did? The obvious first answer is: "did you commit?".

Regards
Michel

[Updated on: Mon, 26 January 2009 23:28]

Report message to a moderator

Re: Using TABLE functions in select statement (merged 3) [message #383005 is a reply to message #382981] Mon, 26 January 2009 23:44 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
What is count for

 IF (tb_bpinstance_id.COUNT != 0)
Re: Using TABLE functions in select statement (merged 3) [message #383007 is a reply to message #382981] Mon, 26 January 2009 23:45 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
HUH?
SELECT 1 BULK COLLECT INTO tb_bpinstance_id FROM dual WHERE 1 != 1;
Re: Using TABLE functions in select statement (merged 3) [message #383030 is a reply to message #383005] Tue, 27 January 2009 00:30 Go to previous messageGo to next message
mnair
Messages: 5
Registered: January 2009
Junior Member
If I pass the value 20119 to itdocrefnum parameter, the SELECT statement should return the bpinst_sq_id as 1010.
So the count for tb_bpinstance_id.COUNT should be 1, but it shows zero.
If I pass the values 20220 and 20550, the bpinst_sq_id values are 1020 and 1021. Here the count for tb_bpinstance_id.COUNT should be 2, but it shows zero. From this I assumed that the SELECT staement is not working.

This procedure was not written by me, but I just looked into it when I faced this issue.

As for the statement

SELECT 1 BULK COLLECT INTO tb_bpinstance_id FROM dual WHERE 1 != 1;

I assume that the person who worked on this code wanted to ensure that tb_bpinstance_id is null.

Re: Using TABLE functions in select statement (merged 3) [message #383034 is a reply to message #382981] Tue, 27 January 2009 00:37 Go to previous message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

I do not understand how specified inputs are/should produce "expected/desired output/results"
Previous Topic: Help with query
Next Topic: Is It Possible???
Goto Forum:
  


Current Time: Tue Dec 06 08:52:46 CST 2016

Total time taken to generate the page: 0.09363 seconds