10 Replies Latest reply on Jul 16, 2010 6:26 AM by eric henrard

Problem executing Stored procedure with Apex Listener 1.10.179.10.43

eric henrard Jul 5, 2010 12:25 PM

Hi,

I have some problem to run stored procedure with parameter (works without parameter)
this works with APEX LISTENER version 0.10.110.10.57. (tested on tomcat and on Oracle GlassFish Server 3.0.1)

Regards,

call is done via url like :
DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA?p_col_nam=RCH_MIG_DDL_DCT&p_MIG_SCH_SVC_REQ_IDN=1182&p_ext=.sql

receive : HTTP Status 500

APEX Listener version : 1.10.179.10.43

ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
init: # headers=45
declare nm owa.vc_arr := ?;
vl owa.vc_arr := ?;
begin
owa.init_cgi_env( ?, nm, vl );
htp.init; htp.HTBUF_LEN := 63;
? := sys_context('USERENV','SID');
end;
SID:492
CALL:
begin
DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA(p_ext=>?,
p_col_nam=>?);
commit;
end;
BINDS
p_ext:.sql
p_col_nam:RCH_MIG_DDL_DCT
p_ext:.sql
p_col_nam:RCH_MIG_DDL_DCT
EXEC FAILED:ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'DL_DRATB_SCH_MIG_TEM_DTA'
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'DL_DRATB_SCH_MIG_TEM_DTA'
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored

[#|2010-07-05T14:21:54.613+0200|SEVERE|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=23;_ThreadName=Thread-1;|_init_: # headers=45
declare nm owa.vc_arr := ?;
vl owa.vc_arr := ?;
begin
owa.init_cgi_env( ?, nm, vl );
htp.init; htp.HTBUF_LEN := 63;
? := sys_context('USERENV','SID');
end;
SID:485
CALL:
begin
DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA(p_ext=>?,
p_col_nam=>?);
commit;
end;
BINDS

p_ext:.sql
p_col_nam:CRO_MIG_DDL_DCT
p_ext:.sql
p_col_nam:CRO_MIG_DDL_DCT
EXEC FAILED:ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'DL_DRATB_SCH_MIG_TEM_DTA'
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
|#]

[#|2010-07-05T14:21:54.617+0200|SEVERE|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=23;_ThreadName=Thread-1;|ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'DL_DRATB_SCH_MIG_TEM_DTA'
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
|#]

1. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43

Kris Rice-Oracle Jul 6, 2010 10:45 PM (in response to eric henrard)

The production version of the listener added validation on the args for a procedure. It checks between all_args and the query string to ensure only args present in the signature are bound in. The only thing I can think of is if this an overloaded proc there could be something. what does the signature of this proc look like ?
-kris
Like Show 0 Likes(0)

Actions
2. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43

eric henrard Jul 8, 2010 8:33 AM (in response to Kris Rice-Oracle)

Kris,

Here is the tag of the procedure (inside a package):

PROCEDURE DL_DRATB_SCH_MIG_TEM_DTA
Argument Name Type In/Out Default?
P_COL_NAM VARCHAR2 IN
P_MIG_SCH_SVC_REQ_IDN NUMBER IN
P_EXT VARCHAR2 IN DEFAULT

may be the problem is that this procedure is called trought a public synonym?

Here also the output of dba_arguments for this proc:

select * from dba_arguments where PACKAGE_NAME='DRAP_APX_PUB' and object_name ='DL_DRATB_SCH_MIG_TEM_DTA';

OWNER OBJECT_NAME PACKAGE_NAME OBJECT_ID
OVERLOAD SUBPROGRAM_ID ARGUMENT_NAME POSITION SEQUENCE DATA_LEVEL
DATA_TYPE DEFAULT_VALUE
DEFAULT_LENGTH IN_OUT DATA_LENGTH DATA_PRECISION DATA_SCALE RADIX CHARACTER_SET_NAME
TYPE_OWNER TYPE_NAME TYPE_SUBNAME
TYPE_LINK
PLS_TYPE CHAR_LENGTH C

DRAF02P DL_DRATB_SCH_MIG_TEM_DTA DRAP_APX_PUB 122449
11 P_EXT 3 3 0
VARCHAR2
IN CHAR_CS

VARCHAR2 B

DRAF02P DL_DRATB_SCH_MIG_TEM_DTA DRAP_APX_PUB 122449
11 P_MIG_SCH_SVC_REQ_IDN 2 2 0
NUMBER
IN 22 10

NUMBER 0 0

DRAF02P DL_DRATB_SCH_MIG_TEM_DTA DRAP_APX_PUB 122449
11 P_COL_NAM 1 1 0
VARCHAR2
IN CHAR_CS

VARCHAR2 B

Regards,

Eric
Like Show 0 Likes(0)

Actions
3. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43

Kris Rice-Oracle Jul 8, 2010 4:57 PM (in response to eric henrard)

Eric,
Can you turn debugging on in the listener and post/send me the log from that. It's obviously not finding P_MIG_SCH_SVC_REQ_IDN for some reason. but I can't see a reason why from this.

In the apex-config.xml add a property:
<entry key="apex.debug.debugger">true</entry>

-kris
Like Show 0 Likes(0)

Actions
4. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43

eric henrard Jul 9, 2010 7:23 AM (in response to Kris Rice-Oracle)

Kris,

Here the output, I hope this can help you.

Thanks in advance.

Regards,

Eric

SEVERE: A web application registered the JBDC driver [oracle.jdbc.OracleDriver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.
APEX Listener version : 1.10.179.10.43
APEX Listener server info: Apache Tomcat/6.0.26
Using Config file:/u01/app/apex/apache-tomcat-6.0.26/temp/apex/apex-config.xml
-- listing properties --
PropertyCheckInterval=60
ValidateConnection=true
MinLimit=1
MaxLimit=10
InitialLimit=3
AbandonedConnectionTimeout=900
MaxStatementsLimit=10
InactivityTimeout=1800
MaxConnectionReuseCount=50000
==== doGet()====
isValidRequest(), procedure name:
Validating:DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA
*** Add procedure to cache: DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTAAddProc: 390 ms*** Total number of arguments: 3SID:1099Parse: 0 ms
-----
begin
DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA(p_ext=>?,
p_col_nam=>?);
commit;
end;
*** Found procedure in cache: DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA*** Total number of arguments: 3p_ext=.sql
p_col_nam=FNL_RCH_MIG_DDL_DCT
#### Error occurred in Signature for procedure:DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA. Reloading...
*** RELOADING procedure in cache: DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTAReload: 194 ms*** Found procedure in cache: DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA*** Total number of arguments: 3p_ext=.sql
p_col_nam=FNL_RCH_MIG_DDL_DCT
init: # headers=45
declare nm owa.vc_arr := ?;
vl owa.vc_arr := ?;
begin
owa.init_cgi_env( ?, nm, vl );
htp.init; htp.HTBUF_LEN := 63;
? := sys_context('USERENV','SID');
end;
SID:1099
CALL:
begin
DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA(p_ext=>?,
p_col_nam=>?);
commit;
end;
BINDS
p_ext:.sql
p_col_nam:FNL_RCH_MIG_DDL_DCT
p_ext:.sql
p_col_nam:FNL_RCH_MIG_DDL_DCT
EXEC FAILED:ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'DL_DRATB_SCH_MIG_TEM_DTA'
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'DL_DRATB_SCH_MIG_TEM_DTA'
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
Got results length:NULL
Like Show 0 Likes(0)

Actions
5. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43

Kris Rice-Oracle Jul 9, 2010 9:06 PM (in response to eric henrard)

Eric,
Can you try this? In the admin screens, on the Security tab. There is a Security Setting section and in there is a button to Clear Cache. I see the error that the loading the signature from the cache failed so I'm guessing that maybe that cache is invalid/corrupted. I'll take a closer look at the code but give this try and let me know.

-kris
Like Show 0 Likes(0)

Actions
6. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43

Liz Saunders-Oracle Jul 9, 2010 11:44 PM (in response to eric henrard)

Eric,

You mentioned that
"may be the problem is that this procedure is called through a public synonym".

What is your public synonym pointing to?

Liz
Like Show 0 Likes(0)

Actions
7. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43

eric henrard Jul 12, 2010 7:24 AM (in response to Liz Saunders-Oracle)

Liz,

Here the synonym :
SQL> select * from dba_synonyms where synonym_name like 'DRAP_APX_PUB';

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
DB_LINK
PUBLIC DRAP_APX_PUB DRAF02P DRAP_APX_PUB

Regards,

Eric
Like Show 0 Likes(0)

Actions
8. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43

eric henrard Jul 12, 2010 8:31 AM (in response to Kris Rice-Oracle)

Kris,

I see the button, but it is grised.

I put apex.cache.caching to true (+restart app) and same thing, the button is disabled. May be there are nothing to clear?

I tried also to give to complete name for the proc and ... always the same error message.

Regards,

Eric

==== doGet()====

|#]

[#|2010-07-12T10:28:24.637+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|
isValidRequest(), procedure name: <DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA>

|#]

[#|2010-07-12T10:28:24.637+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|Validating:DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA

|#]

[#|2010-07-12T10:28:24.651+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|*** Add procedure to cache: DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA|#]

[#|2010-07-12T10:28:24.731+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|AddProc: 80 ms|#]

[#|2010-07-12T10:28:24.731+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|*** Total number of arguments: 3|#]

[#|2010-07-12T10:28:24.735+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|SID:493|#]

[#|2010-07-12T10:28:24.735+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|Parse: 0 ms|#]

[#|2010-07-12T10:28:24.735+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|
-----
begin
DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA(p_ext=>?,
p_col_nam=>?);
commit;
end;

|#]

[#|2010-07-12T10:28:24.736+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|*** Found procedure in cache: DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA|#]

[#|2010-07-12T10:28:24.736+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|*** Total number of arguments: 3|#]

[#|2010-07-12T10:28:24.736+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|p_ext=.sql

|#]

[#|2010-07-12T10:28:24.736+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|p_col_nam=RCH_MIG_DDL_DCT

|#]

[#|2010-07-12T10:28:24.743+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|#### Error occurred in Signature for procedure:DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA. Reloading...

|#]

[#|2010-07-12T10:28:24.744+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|*** RELOADING procedure in cache: DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA|#]

[#|2010-07-12T10:28:24.826+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|Reload: 81 ms|#]

[#|2010-07-12T10:28:24.826+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|*** Found procedure in cache: DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA|#]

[#|2010-07-12T10:28:24.826+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|*** Total number of arguments: 3|#]

[#|2010-07-12T10:28:24.827+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|p_ext=.sql

|#]

[#|2010-07-12T10:28:24.827+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|p_col_nam=RCH_MIG_DDL_DCT

|#]

[#|2010-07-12T10:28:24.834+0200|SEVERE|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|_init_: # headers=44
declare nm owa.vc_arr := ?;
vl owa.vc_arr := ?;
begin
owa.init_cgi_env( ?, nm, vl );
htp.init; htp.HTBUF_LEN := 63;
? := sys_context('USERENV','SID');
end;
SID:493
CALL:
begin
DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA(p_ext=>?,
p_col_nam=>?);
commit;
end;
BINDS

p_ext:.sql
p_col_nam:RCH_MIG_DDL_DCT
p_ext:.sql
p_col_nam:RCH_MIG_DDL_DCT
EXEC FAILED:ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'DL_DRATB_SCH_MIG_TEM_DTA'
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
|#]

[#|2010-07-12T10:28:24.834+0200|SEVERE|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'DL_DRATB_SCH_MIG_TEM_DTA'
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
|#]

[#|2010-07-12T10:28:24.836+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|
Got results length:NULL

|#]
Like Show 0 Likes(0)

Actions
9. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43

785310 Jul 16, 2010 5:49 AM (in response to eric henrard)

Hi,

I'm testing the new apex listener to see if we can use it for our modplsql needs. I think I've come across the same problem as reported in this thread.

I think it's because the parameter name is given in uppercase letters. Below is my console output after I made a small debugging filter to investigate what the apex listener is doing. As one can see, when the parameter name is given in uppercase, the servlet gets it in uppercase from the call "getQuerysTring", but then later call "getParameterValues" with lowercase parameter name and that returns null.

If i do the same call with lowercase parameter name it succeeds.

==========================
2010-07-16 07:43:31.734:INFO::jetty-7.1.4.v20100610
2010-07-16 07:43:31.765:INFO::Deployment monitor D:\jetty3\contexts at interval 5
2010-07-16 07:43:31.765:INFO::Deployable added: D:\jetty3\contexts\javadoc.xml
2010-07-16 07:43:31.796:INFO::Deployable added: D:\jetty3\contexts\apexlistener.xml
2010-07-16 07:43:32.374:INFO::Deployment monitor D:\jetty3\webapps at interval 5
2010-07-16 07:43:32.374:INFO::Deployable added: D:\jetty3\webapps\i
2010-07-16 07:43:32.452:INFO::Started SelectChannelConnector@0.0.0.0:9090

DEBUG JOSTEIN: lowercasefilter doFilter
APEX Listener version : 1.10.179.10.43
APEX Listener server info: jetty/7.1.4.v20100610
Using Config file:D:\jetty3\tmp\\webapps\apex-config.xml
-- listing properties --
PropertyCheckInterval=60
ValidateConnection=true
MinLimit=1
MaxLimit=10
InitialLimit=3
AbandonedConnectionTimeout=900
MaxStatementsLimit=10
InactivityTimeout=1800
MaxConnectionReuseCount=50000

==== doGet()====

isValidRequest(), procedure name: <felles.pkg_testing.p_paramtest>

DEBUG JOSTEIN: getQueryString - TXT=hdDDfsalla
Validating:felles.pkg_testing.p_paramtest

*** Add procedure to cache: felles.pkg_testing.p_paramtestAddProc: 281 ms*** Total number of arguments: 1
** checkRequestValidationFunction(), <felles.pkg_testing.p_paramtest> exclude=false

DEBUG JOSTEIN: getQueryString - TXT=hdDDfsalla

DEBUG JOSTEIN: getQueryString - TXT=hdDDfsalla
SID:80
DEBUG JOSTEIN: getParameterValues - txt = null!!!
Parse: 0 ms
-----
begin
felles.pkg_testing.p_paramtest;
commit;
end;

*** Found procedure in cache: felles.pkg_testing.p_paramtest*** Total number of arguments: 1#### Error occu
rred in Signature for procedure:felles.pkg_testing.p_paramtest. Reloading...

*** RELOADING procedure in cache: felles.pkg_testing.p_paramtestReload: 141 ms*** Found procedure in cache:
felles.pkg_testing.p_paramtest*** Total number of arguments: 1_init_: # headers=44
declare nm owa.vc_arr := ?;
vl owa.vc_arr := ?;
begin
owa.init_cgi_env( ?, nm, vl );
htp.init; htp.HTBUF_LEN := 63;
? := sys_context('USERENV','SID');
end;
SID:80
CALL:
begin
felles.pkg_testing.p_paramtest;
commit;
end;
BINDS

EXEC FAILED:ORA-06550: linje 2, kolonne 2:
PLS-00306: feil antall eller type argumenter i kall til P_PARAMTEST
ORA-06550: linje 2, kolonne 2:
PL/SQL: Statement ignored
ORA-06550: linje 2, kolonne 2:
PLS-00306: feil antall eller type argumenter i kall til P_PARAMTEST
ORA-06550: linje 2, kolonne 2:
PL/SQL: Statement ignored

Got results length:NULL
==========================

/Jostein.
Like Show 0 Likes(0)

Actions
10. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43

eric henrard Jul 16, 2010 6:26 AM (in response to 785310)

Hi Jostein,

Thanks for the update, I tried to put all parameters in lowercase and it's work

I will use this workarround to put new version of Apex Listener.

Thanks for Help.

Regards,

Eric.
Like Show 0 Likes(0)

Actions

Go to original post