Skip to Main Content
Forums

Oracle Forms

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

WEBUTIL within form Problem

658605Sep 8 2008 — edited Sep 8 2008
dears,

i was design an form that read an excel sheet and upload the excel sheet contant into a table on DB, the excel sheet contain only 2 columns, the problem is when i compile the form CTRL+K there is no errors shown but when the form run i click Browes button but it give me error as below

ORA-06508 PL/SQL could not find program unit being called .

the program unit is client_get_file_name it a package within WEBUTIL attached library, the problem is the form cant call any package or anything from WEBUTIL attached library.

on the form i have 2 buttons

First Button Code: "Browes"

Declare
V_FILE VARCHAR2(2000);
BEGIN
V_FILE := client_get_file_name(NULL, NULL, NULL, NULL, open_file, TRUE);
:main_block.FNAME := V_FILE;
EXCEPTION
WHEN OTHERS THEN
my_alert('There Is Error: '||sqlerrm); -- My_alert(p_text) is program unit
RAISE FORM_TRIGGER_FAILURE;
END;

Second Button code : "Import to DB"

DECLARE
MYFILE CLIENT_TEXT_IO.FILE_TYPE;
filename varchar2(100);
temp varchar2(1000);
t1 varchar2(1000);
t2 varchar2(1000);
cust_id number;
dfrom date;
dto date;
ccode varchar2(100);
r varchar2(1000);
transfer_status boolean;

begin

filename := :main_block.fname;
MYFILE := CLIENT_TEXT_IO.FOPEN(filename, 'r');
SYNCHRONIZE;
SYNCHRONIZE;
CLIENT_TEXT_IO.get_line(MYFILE,temp);
t1:=temp;
while temp is not null loop
CLIENT_TEXT_IO.get_line(MYFILE,temp);
t1:=temp;
select substr(t1,1,instr(t1,',') -1)
into t2
from dual;
ccode:=t2;

select substr(t1,instr(t1,',') +1)
into t1
from dual;

insert into trc_upload -- table on DB
values(t2,t1);
commit;


end loop;

CLIENT_TEXT_IO.FCLOSE(MYFILE);
EXCEPTION
WHEN DDE.DDE_APP_FAILURE THEN
my_alert('WINDOWS APPLICATION CANNOT START.');

WHEN DDE.DDE_PARAM_ERR THEN
my_alert('A NULL VALUE WAS PASSED TO DDE');

WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
my_alert('DDE CANNOT ESTABLISH A CONVERSATION');

WHEN DDE.DMLERR_NOTPROCESSED THEN
my_alert('A TRANSACTION FAILED');
end;


so please to help me on this issue

Note:

the form deployed under UNIX on Application Server

Thanks

Murad.

Comments

Mike Kutz

No procedure needed.

A simply hierarchic SQL using the CONNECT BY clause should be suffice.

with my_table_name( a_number, a_name, a_group, primary_number ) as (

  select 1, 'TEST 1', 'ABC', null from dual union all

  select 2, 'TEST 2', 'ABC', 1 from dual union all

  select 3, 'TEST 3', 'ABC', 1 from dual union all

  select 4, 'TEST 4', 'ABC', 2 from dual union all

  select 5, 'TEST 5', 'ABC', 2 from dual union all

  select 6, 'TEST 6', 'ABC', 3 from dual union all 

  select 7, 'TEST 7', 'ABC', 4 from dual union all 

  select 8, 'TEST 8', 'ABC', 5 from dual

)

select * from my_table_name

connect by prior a_number = primary_number

start with a_number = 2

order by a_number;

2 people found this helpful
kattavijay-JavaNet

Thanks for your response mike ,

how can i execute if it is dynamic table with random data !!

unknown-7404

Don't be silly - random data doesn't have a pattern.


PhHein

Moved from General questions

BluShadow 
katta vijay wrote:




Thanks for your response mike ,




how can i execute if it is dynamic table with random data !!

As already mentioned, random data doesn't have a tree structure so such a question is pointless to ask.

Also, a properly designed application and database doesn't have "dynamic" table names.  You should know the tables you're accessing and the structure of those tables.  The moment you start thinking "how can I do this dynamically?" you should stop yourself and ask "what's wrong with the design?"

Mike already provided the answer for how to traverse hierarchical data in a table.  That answers your question, and there's no need for any PL code to do it, as SQL is perfectly capable of processing it.

Another method if you're on 11gR2 upwards, is to use recursive subquery factoring, for example:

SQL> ed
Wrote file afiedt.buf


  1  with my_table_name(a_number, a_name, a_group, primary_number) as
  2                    (select 1, 'TEST 1', 'ABC', null from dual union all
  3                     select 2, 'TEST 2', 'ABC', 1 from dual union all
  4                     select 3, 'TEST 3', 'ABC', 1 from dual union all
  5                     select 4, 'TEST 4', 'ABC', 2 from dual union all
  6                     select 5, 'TEST 5', 'ABC', 2 from dual union all
  7                     select 6, 'TEST 6', 'ABC', 3 from dual union all
  8                     select 7, 'TEST 7', 'ABC', 4 from dual union all
  9                     select 8, 'TEST 8', 'ABC', 5 from dual
 10                    )
 11  --
 12  -- end of test data
 13  --
 14      ,r as (select &starting_num as start_num from dual)
 15      ,rec(n) as
 16            (select a_number as n
 17             from my_table_name, r
 18             where primary_number = r.start_num
 19             union all
 20             select a_number as n
 21             from my_table_name, rec
 22             where primary_number = rec.n
 23            )
 24  select *
 25* from   rec
SQL> /
Enter value for starting_num: 2
old  14:     ,r as (select &starting_num as start_num from dual)
new  14:     ,r as (select 2 as start_num from dual)


         N
----------
         4
         5
         7
         8

Which is using recursion techniques rather than hierarchical techniquest to achieve the same.

1 person found this helpful
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 6 2008
Added on Sep 8 2008
#form, #forms, #moved-from-devops
1 comment
2,264 views