ORA-06508 PL/SQL: could not find program unit being called [message #607509] |
Fri, 07 February 2014 14:53 |
meteo
Messages: 89 Registered: April 2010 Location: beirut
|
Member |
|
|
I created a form called read_web_file.fmb
this form has
a- block called METAR_BLOCK under which a text item called metar_code with initial values airport code (OLBA, LCLK )
b- block called Button under which a button PASS_VAUES is created and a procedure p993_GET_Qry_To_avia_weather has been called
After executing the following procedure an error is happened: ORA-06508 PL/SQL: could not find program unit being called.
PROCEDURE p993_GET_Qry_To_avia_weather
IS
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_buffer_size NUMBER(10) := 512;
l_line_size NUMBER(10) := 70;
l_lines_count NUMBER(10) := 10;
l_string_request VARCHAR2(512);
l_url VARCHAR2(512);
l_line VARCHAR2(128);
l_raw_data RAW(512);
l_clob_response CLOB;
BEGIN
/*
send HTTP request depending on the html file structure fount at the url : http:/aviationweather.gov/adds/metars/ :
...
< FORM name="textForm" method="GET" action="">
<INPUT SIZE=14 NAME="station_ids" TYPE="text">
............
<INPUT NAME="submitmet" TYPE="submit" VALUE="Submit">
<INPUT TYPE="reset" VALUE="Reset FORM"></FORM>
....... ...
*/
l_url := 'http://aviationweather.gov/adds/metars?station_ids='||:METAR_BLOCK.metars;
l_http_request := UTL_HTTP.begin_request(url =>l_url ,method => 'GET');
UTL_HTTP.set_header(r => l_http_request, name => 'User-Agent', value => 'IE8');
UTL_HTTP.set_header(l_http_request, 'Host', 'aviationweather.gov');
l_http_response := UTL_HTTP.get_response(l_http_request);
DBMS_OUTPUT.put_line('Response> status_code: "' || l_http_response.status_code || '"');
DBMS_OUTPUT.put_line('Response> reason_phrase: "' ||l_http_response.reason_phrase || '"');
DBMS_OUTPUT.put_line('Response> http_version: "' ||l_http_response.http_version || '"');
BEGIN -- response block
<<response_loop>>
LOOP
UTL_HTTP.read_raw(l_http_response, l_raw_data, l_buffer_size);
l_clob_response := l_clob_response || UTL_RAW.cast_to_varchar2(l_raw_data);
END LOOP response_loop;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_response);
END; -- Response Block
DBMS_OUTPUT.put_line('Response> length: "' || LENGTH(l_clob_response) || '"');
DBMS_OUTPUT.put_line(CHR(10) || '=== Print first ' || l_lines_count || ' lines of HTTP response... ===' || CHR(10) || CHR(10));
<<print_response>>
FOR i IN 0..CEIL(LENGTH(l_clob_response) / l_line_size) - 1 LOOP
l_line := SUBSTR(l_clob_response, i * l_line_size + 1, l_line_size);
DBMS_OUTPUT.put_line('[' || LPAD(i, 2, '0') || ']: ' || SUBSTR(TRIM(l_line),1,50) || '...');
EXIT WHEN i > l_lines_count - 1;
END LOOP print_response;
IF l_http_request.private_hndl IS NOT NULL THEN
UTL_HTTP.end_request(l_http_request);
END IF;
IF l_http_response.private_hndl IS NOT NULL THEN
UTL_HTTP.end_response(l_http_response);
END IF;
I run my form in debug mode when the cursor arrive on the instruction: l_http_request := UTL_HTTP.begin_request(url =>l_url ,method => 'GET' ) or in any instruction which invoke a procedure or function in the package UTL_HTTP body i have got the mentioned error .
any one Can help me how I can using and wrap oracle supplied packages (just as utl_url or utl_http) through my own stored procedures and call them from my forms??
Thank you and i appreciate any support.
|
|
|
|
Re: ORA-06508 PL/SQL: could not find program unit being called [message #607513 is a reply to message #607512] |
Fri, 07 February 2014 15:10 |
meteo
Messages: 89 Registered: April 2010 Location: beirut
|
Member |
|
|
this is what I have made:
1) I run SQL plus and
connect as system user and:
SQL> grant execute on UTL_HTTP to meteo;
Grant succeeded.
2) connect my username/password and
select utl_http.request('http://aviationweather.gov/adds/metars/') from dual;
Respnose:
UTL_HTTP.REQUEST('HTTP://AVIATIONWEATHER.GOV/ADDS/METARS/')
--------------------------------------------------------------------------------
<meta name="DC.date.created" content="2003-07-21" scheme="ISO8601" >
<meta name="DC.date.reviewed" content="2007-10-09" scheme="ISO8601" ><script typ
e="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jque
ry.min.js"></script>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery
ui/1.8.7/jquery-ui.min.js"></script>
<script type="text/javascript" src="/static/adds/javascript/tafs/getMetarsTafs.j
s"></script>
<script type="text/javascript" src="/static/javascript/navigation.js"></script>
<script type="text/javascript" src="/static/javascript/library.js"></script><lin
k href="/layout/awc/favicon.ico" rel="shortcut icon" >
UTL_HTTP.REQUEST('HTTP://AVIATIONWEATHER.GOV/ADDS/METARS/')
--------------------------------------------------------------------------------
<link href="/layout/awc/mystyle.css" media="" rel="stylesheet" type="text/css" >
<link href="/layout/awc/cwi/main.css" media="" rel="stylesheet" type="text/css"
>
<link href="/layout/css/form.css" media="" rel="stylesheet" type="text/css" >
<link href="/layout/css/layout.css" media="" rel="stylesheet" type="text/css" >
<link href="/layout/awc/cwi/menu.css" media="" rel="stylesheet" type="text/css"
>
<link href="/static/css/user.css" media="" rel="stylesheet" type="text/css" >
</head>
<body onload="init()" onunload="unload(map)">
UTL_HTTP.REQUEST('HTTP://AVIATIONWEATHER.GOV/ADDS/METARS/')
--------------------------------------------------------------------------------
<div style="width:600px">
<div id="user_form"><a href="/qicp/login" >QICP Login</a> <a h
ref="/qicp/index/description">Registration info</a></div><!-- end #user_form
from my form if i call a procedure or function in UTL_HTTP still the same problem ORA-06508 PL/SQL: could not find program unit being called???
|
|
|
|
Re: ORA-06508 PL/SQL: could not find program unit being called [message #607515 is a reply to message #607514] |
Fri, 07 February 2014 15:32 |
meteo
Messages: 89 Registered: April 2010 Location: beirut
|
Member |
|
|
sure, I open my form meta_taf.fmb , connect as meteo user, compile my form and run it . everything go well but when I press the button submit under which a trigger has been written (p993_GET_Qry_To_avia_weather with code already posted) still error message ORA-06508 PL/SQL: could not find program unit being called happen!!!
Just I would like to ask you what did you mean by (same data base)?? About my forms all are executed under the user meteo and data base:dbrc.
[Updated on: Fri, 07 February 2014 15:36] Report message to a moderator
|
|
|
Re: ORA-06508 PL/SQL: could not find program unit being called [message #607528 is a reply to message #607514] |
Sat, 08 February 2014 03:40 |
meteo
Messages: 89 Registered: April 2010 Location: beirut
|
Member |
|
|
form Enterprise Manager Console I connect as system user, under the package node I edit UTL_HTTP(Specification) and copied its contents, now form my form l create a package I named it UTL_HTTP and past the content here compile the package in my form and well compiled ; when I try to make he same steps to UTL_HTTP body under the package node for the system user in Enterprise Manager Consoler, I have got an error source could not be displays because it is wrapped.
now from my form and under Program Unit node I have just UTL_HTTP(Specification) I think if i have UTL_HTTP(Body) under my Program Unit node my problem may be solved?? is there any suggestion??
|
|
|
|
Re: ORA-06508 PL/SQL: could not find program unit being called [message #607538 is a reply to message #607528] |
Sat, 08 February 2014 07:02 |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I'll try, but I don't know Forms. So maybe no help.
Is it possible that the procedure you are running is in another schema, and that schema does not have privileges on utl_http? If so, it still shouldn't be a problem because by default you should have a public synonym on utl_http, and execute should be granted to public. Please can you run these queries to check:orclz>
orclz> select owner,objecT_name,object_type from dba_objects where object_name='UTL_HTTP';
OWNER OBJECT_NAME OBJECT_TYPE
--------------- ------------------------------ ---------------
SYS UTL_HTTP PACKAGE BODY
SYS UTL_HTTP PACKAGE
PUBLIC UTL_HTTP SYNONYM
APEX_040200 UTL_HTTP SYNONYM
orclz> select grantee,privilege from dba_tab_privs where owner='SYS' and table_name='UTL_HTTP';
GRANTEE PRIVILEGE
-------------------- -------------------------
PUBLIC EXECUTE
ORDPLUGINS EXECUTE
APEX_040200 EXECUTE
orclz>
|
|
|
|
|
|