Problem executing Stored procedure with Apex Listener 1.10.179.10.43 - ORDS, SODA & JSON in the Database

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
|#] 

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 

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 

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 

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 

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 

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 

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 

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
|#] 

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. 

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.

Related

UTL_FILE to read a textfile and load in table :

Hi ,i have created a file 'writefile' using below and it is created  successfully. =================================declare  ltype utl_file.file_type;  ldir  varchar2(100) := 'DATA_PUMP_DIR';  lfile  varchar2(100) := 'writefile.sql';  s varchar2(10) := ',';begin  ltype := utl_file.fopen(ldir,lfile,'w');  for i in (select * from emp)    loop      utl_file.putf(ltype,i.empid||s||i.sal);      UTL_FILE.NEW_LINE(ltype);    end loop;  utl_file.fclose(ltype);end;==================================================out put <writefile.sql>184,4200185,4100186,3400.... i have created a new table tmp with empid and first_name columns, could you please let me know how could i read this file and load the data into new table tmp ...i tried using this but its not working..=================declaref UTL_FILE.FILE_TYPE;ldir varchar2(100) := 'DATA_PUMP_DIR';lfile varchar2(100) := 'writefile.sql';s VARCHAR2(200); BEGINf := UTL_FILE.FOPEN(ldir,lfile,'R');  IF UTL_FILE.IS_OPEN(f) THEN    LOOP      BEGIN        UTL_FILE.GET_LINE(f,lfile);        IF lfile IS NULL THEN          EXIT;        END IF;      INSERT INTO tmp      (empid, first_name)      VALUES      ( );      END;  END LOOP;  COMMIT;  END IF; END; ===========================================
You have to put all these values variables and then insert into oracle db.Use substr and instr. declaref UTL_FILE.FILE_TYPE;ldir varchar2(100) := 'DATA_PUMP_DIR';lfile varchar2(100) := 'writefile.sql';s VARCHAR2(200);v_row varchar2(2000);v1 number;v2 varchar2(50);BEGINf := UTL_FILE.FOPEN(ldir,lfile,'R');  IF UTL_FILE.IS_OPEN(f) THEN    LOOP      BEGIN        UTL_FILE.GET_LINE(f,v_row);       v1:=substr(v_row,1,1);       v2:=substr(v_row,3,50);      INSERT INTO tmp      (empid, first_name)      VALUES      (v1,v2);    EXCEPTION      WHEN NO_DATA_FOUND THEN        EXIT;        END;  END LOOP;  COMMIT;  END IF;END;   Other way is to use external table:  File: /tmp/text.txt 1,"AAA",1002,"BBB",2003,"CCC",300  CREATE OR REPLACE DIRECTORY TEST_DIR AS "/tmp";GRANT READ, WRITE ON DIRECTORY TEST_DIR TO public;  CREATE TABLE ext_table      (id      NUMBER,       empname VARCHAR2(20),       rate    NUMBER)    ORGANIZATION EXTERNAL      (TYPE ORACLE_LOADER       DEFAULT DIRECTORY TEST_DIR       ACCESS PARAMETERS         (RECORDS DELIMITED BY NEWLINE         FIELDS TERMINATED BY ","         OPTIONALLY ENCLOSED BY '"'         (id,          empname,          rate         )        )      LOCATION ('test.txt')     );  select * from ext_table;
Thanks Alex, could you please let me know what is the use of vrow , v1 and v2.
Just 3 variables. vRow is the current line from the filev1 is the empnov2 is the emp name 
Hi Alex, without creating a directory can we parse the XML data to oracle table. Please suggest..
Something like this? https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql

Error While running Distibution batch in RWMS

./run_distribution.sh QR*********************************************************************Shell Script : run_distribution.shParameters : Facility ID : QRExecution Start Time :Wed Sep 28 17:52:23 IST 2016run_distribution.sh Running...*********************************************************************Shell Script : run_distribution.shParameters : Facility ID : QRExecution Start Time :Wed Sep 28 17:52:23 IST 2016run_distribution.sh Running...  Session altered.    PL/SQL procedure successfully completed.  old   2:    facility_id_in  Distribution_Queue.Facility_ID%TYPE := '&1';new   2:    facility_id_in  Distribution_Queue.Facility_ID%TYPE := 'QR';old   3:    order_type_in   Distribution_Queue.Order_Type%TYPE := '&2';new   3:    order_type_in   Distribution_Queue.Order_Type%TYPE := 'MANUAL';old   4:    user_id_in      Distribution_Queue.User_ID%TYPE := '&3';new   4:    user_id_in      Distribution_Queue.User_ID%TYPE := 'ATNU';old   5:    action_ts_in    Distribution_Queue.Action_TS%TYPE := TO_DATE ('&4', 'MMDDRRHH24MISS');new   5:    action_ts_in    Distribution_Queue.Action_TS%TYPE := TO_DATE ('092816175221', 'MMDDRRHH24MISS');old   6:    wave_nbr_in     Distribution_Queue.Wave_Nbr%TYPE := TO_NUMBER ('&5');new   6:    wave_nbr_in     Distribution_Queue.Wave_Nbr%TYPE := TO_NUMBER ('173');END;   *ERROR at line 130:ORA-06550: line 130, column 4:PLS-00103: Encountered the symbol ";" when expecting one of the following:if    run_distribution.sh Successfully Completed...Execution End Time :Wed Sep 28 17:52:24 IST 2016*********************************************************************
Hi All, The issue has been resolved. Thankssyed 
Hi Syed,Can u elaborate how you solved this issue ? Thanks,Atnu M
Hi Atnu M, In distribution planning there a sub-menu called order Query editor. Default order query data is deleted ,& there are no options to create from GUI. Directly I insert the data in this table: select *from order_queries;Hope I answer your question. Regards,Syed akbar

Sequence generating random values when execute procedure

Hi all,I am facing one issue of sequence generating duplicate values. This sequence has been generated on table VCA_STAT(STAT_REF,STAT_CODE,DEN_STAT_DESCR). STAT_REF is  PRIMARY key and inserted using sequence. Below is sequence definition.CREATE SEQUENCE THOR.SEQ_VS START WITH 102876MAXVALUE 9999999999MINVALUE 1NOCYCLECACHE 20NOORDER; when I execute below query it gives the following result.querySelect Seq_Vs.Nextval From Dual;select SEQ_VS.currval from dual;Result102877 ,102878 ,102879 ,102880 ,102881 ,102882 ,102883 ,102884 ,102885 ,102886These values created by sequence are unique and doesn't present in vca_stat table.  But when I execute below procedure then it gives me unique constraint violation error. The value generated by sequence is already present in table vca_stat.Following values are generated after execution of procedure.613594, 613595,45277, 45276,45275, 45272ProcedureBEGIN    if pLabCount < 2 then       PA_ERR.PR_RAISE_ERROR('INS_VCAStat',14,'');    end if;    nDupLabRef := FN_DUPLICATE_STMT(pStmtLabel,pLabCount);     if nDupLabRef is NULL then        null;  -- i.e. Not a duplciate so do nothing    else        -- Duplicate Statement found        PA_ERR.PR_RAISE_ERROR ('INS_VCAStat',13, 'VCA Statement already exists: ' ||nDupLabRef);    end if; pStat_Ref := PA_VS.FN_GET_NEW_KEY;     INSERT INTO VCA_STAT (STAT_REF, STAT_CODE, DEN_STAT_DESCR)  VALUES ( pStat_Ref, pStat_Code, 'No Description' );   For i in 1..pLabCount loop   INSERT INTO VCA_STAT_LAB (STAT_REF, VCA_LAB_REF)   VALUES ( pStat_Ref, pStmtLabel(i) );  end loop;     sErrorText := '';    for cCheckRec in cCheckSROOperStat loop        sErrorText := sErrorText || ' - ' || ccheckrec.term_text_fv;    end loop; If sErrorText is not null then        -- Invalid VCA Statement as conflict with SRO_OPER_STAT        sErrorText := 'VCA Labels conflict with SRO 7th/8th Digit related VCA Labels.  Labels in conflict:' || sErrorText;        PA_ERR.PR_RAISE_ERROR ('PA_VS.INS_VCAStat',13, sErrorText);    end if;      PR_UPD_VCA_STAT_DESCR(pStat_Ref); Function getting sequence value :FUNCTION FN_GET_NEW_KEY  RETURN VCA_STAT.STAT_REF%TYPE IS    --    -- Get next sequence value from SEQ_VS    CURSOR CU_SVS    IS    SELECT      seq_vs.NEXTVAL    FROM        dual;    --    --    l_nextval VCA_STAT.STAT_REF%TYPE;  BEGIN    OPEN   cu_svs;    FETCH  cu_svs INTO l_nextval;    CLOSE cu_svs;    RETURN l_nextval;  END FN_GET_NEW_KEY;

I am getting a simple PLSQL error PLS-00382: expression is of wrong type on an array element.

I have not created a 2D array before in PLSQL. I have looked all over the internet, for a week, trying to solve my issue. I need someone to look at MY code and tell me what I am doing wrong. I have shortened and edited the code in preparation of copying here. I have also included the data layout and the code is commented for ease of understanding. Yes, I have checked this forum for answers but to me my code looks the same as the answers I've found. The error line is denoted by  ****ERROR****. I do want to loop instream rather than call packages or procedures due to the 2D aspect. Or does it HAVE to be a procedure instream? Please help!  And Thank you for your time. DECLAREws_scores sortest.SORTEST_TEST_SCORE%TYPE;                                                        -- THIS IS A VARCHAR2(15) DATATYPEstud_idx               number(5)    :=0;score_idx              number(5)    :=0; type t_scores is table of sortest.SORTEST_TEST_SCORE%TYPE index by pls_integer;type score_tab is table of t_scores index by pls_integer; type t_student_rec is record(lname varchar2(20),fname varchar2(20),hacc_id varchar2(9),hacc_pidm varchar2(6),test_date date,scores score_tab); type students_tab is table of t_student_rec index by pls_integer; students students_tab; stud_idx :=1;score_idx :=1;ws_score_count :=1; --READ THE FIRST DATA RECORD HERE AND DISCARDED AS THE HEADERBEGINFOR a IN 1..ws_row_counter                                                         -- ROW COUNTER WAS DERIVED BY OPENING THE FILE COUNTING THE ROWS AND CLOSING THE FILE BEFORE RE-OPENING FOR READ INTO ARRAYLOOPbegin-- get next row student or end loop of validating and loading array of students and scoresUTL_FILE.GET_LINE(score_roster_file,ws_input_data);EXCEPTIONwhen NO_DATA_FOUND then                                                      -- GO TO NEXT ARRAY AND READ THROUGH THIS ARRAY TO LOAD TEST SCORES TO DB(WHEN READY, FOR NOW GOTO END OF JOB)     dbms_output.put_line('Finished reading infile');     utl_file.fclose(score_roster_file);     goto end_job;     -- goto test_score_loadEND;ws_id  := NULL;ws_tesc_save := NULL;ws_pidm := NULL;ws_lname := NULL;ws_fname := NULL;ws_test_date := NULL;ws_score_plcmnt := 0;ws_scores := NULL;                                                                                                                                                     -- SEE WS_INPUT_DATA EXAMPLE BELOW.ws_input_count := ws_input_count + 1;ws_lname := substr(ws_input_data,1,20);ws_fname := substr(ws_input_data,21,20);ws_id    := UPPER(substr(ws_input_data,41,9));ws_test_date := to_date(substr(ws_input_data,61,10),'mm/dd/yyyy'); ws_score_plcmnt := 72;ws_scores := rtrim(substr(ws_input_data,ws_score_plcmnt,7));                                                         -- ALL SCORES ARE VARCHAR(7) IN THE FILE BEGINNING IN COL 72 IF NOT C1%ISOPEN THEN                                                                                                               -- CURSOR DEFINED ABOVE TO LOOKUP NECESSARY CODE   OPEN C1 (ws_id);END IF;FETCH C1 INTO ws_pidm; IF C1%NOTFOUND THEN  ws_nospriden_count := ws_nospriden_count + 1;  CLOSE C1;  UTL_FILE.GET_LINE(score_roster_file,ws_input_data);  goto next_record;                                                                                        -- NEXT RECORD TAG DEFINED ABOVEEND IF;CLOSE C1;beginstudents(stud_idx).lname := ws_lname;students(stud_idx).fname := ws_fname;students(stud_idx).hacc_id := ws_id;students(stud_idx).hacc_pidm := ws_pidm;students(stud_idx).test_date := ws_test_date;students(stud_idx).scores(score_idx) := ws_scores; --*********PROBLEM LINE********** first time through  --------****************ERROR*********************************students(stud_idx).scores(score_idx) := ws_scores;                      * ERROR at line 187:ORA-06550: line 187, column 43:PLS-00382: expression is of wrong typeORA-06550: line 187, column 3:PL/SQL: Statement ignored--------*************END OF ERROR**************************** while ws_score_count <= 16                                               -- I MIGHT CHANGE THIS BUT FOR NOW THERE ARE ONLY 16 SCORES IN THE FILE INCLUDING THOSE WITH NULL VALUESloop                                                                                      -- I EXPECT IT TO LOOP THROUGH ALL 16 SCORES AND STORE THE NULLS AND THE VALUES AS IT FINDS THEMws_score_plcmnt := ws_score_plcmnt + 7;if ws_score_plcmnt = 128 thenws_scores := ws_write;elseif rtrim(substr(ws_input_data,ws_score_plcmnt,7)) IS NULL thenws_scores := null;elsews_scores := rtrim(substr(ws_input_data,ws_score_plcmnt,7));end if;end if;ws_score_count := ws_score_count + 1;score_idx := score_idx + 1;students(stud_idx).scores(score_idx) := ws_scores;                          --iterativeEND LOOP;                                                                                        -- end loop of scoresend;stud_idx := stud_idx + 1;END LOOP;                                                                                        -- end loop of studentsend; --WS_INPUT_DATA IS A FILE FTP'D FROM A DIFFERENT SOURCE - FIXED WIDTH - READ USING UTL_FILE:                                                                                                        <-----------------------------------ALL SCORES ARE VARCHAR2 FIELDS (JUST A REPRESENTATION; ALL BLANKS ARE NULLS)-->lname                   fname                  ID             SSN   date             CPAR   CPEA    CPCM  CPRC   CPSS   LOPL     LOPS     WRITE  WRITE2 FSTO    EWS  MDAE   MDLE MDQE MDRN MDWPLast Name           First Name          Student ID          Test Start      ArithmeElementCollegeReadingSentencESL LanESL ReaESL SenWritepl   FoundatWriteplEA - ReEA - LiEA - QuEA - AlEA - WoSTUDLNAM         STUDFNAM        STUDID              06/04/2018               56         92                                                               5                                                  4           5         5           4         11

Error in X11 (unable to start device PNG)

Hello.
I have a problem with running embedded R script.
First script runs fine:
begin
sys.rqScriptCreate('Example1',
'function() {
ID <- 1:10
res <- data.frame(ID = ID, RES = ID / 100)
res}');
end;
The second one is not so fine:
select *
from table(rqEval(NULL,
'select 1 id, 1 res from dual',
'Example1'));
ORA-29400: data cartridge error
Error in X11(paste("png::", filename, sep = ""), width, height, pointsize, :
unable to start device PNG
ORA-06512: на "RQSYS.RQEVALIMPL", line 57
29400. 00000 - "data cartridge error\n%s"
Can anyone help me?
Thanks. 
Hi Sherry,
Thanks for reply.
I am using R version 2.13.2 on SPARC Solaris 64-bit.
capabilities()[["X11"]] function returns me TRUE.
And I have already solved my problem. I am start virtual framebuffer daemon and add to my Rprofile piece of code:
Sys.setenv("DISPLAY" = ":1")
Everything is ok! 
Sherry,
we have the same problem, when we perform the script:
SELECT PVALUE
FROM TABLE (
rqEval (CURSOR (SELECT 1 "ore.connect" FROM DUAL),
'select 1 pValue from dual',
'RF_TTEST_PVALUES'));
ORA-20000: RQuery error
Error in X11(paste("png::", filename, sep = ""), g$width, g$height, pointsize, :
unable to start device PNG
ORA-06512: a "RQSYS.RQEVALIMPL", line 104
ORA-06512: a "RQSYS.RQEVALIMPL", line 101.
we are using R version 2.15.1 on Platform: powerpc-ibm-aix5.3.0.0 (64-bit)
capabilities()[["X11"]] function returns false
Thanks. 
Another option is to disable embedded graphics capture by setting <tt>ore.graphics</tt> to <tt>FALSE</tt>. For you example it will look like this:
SELECT PVALUE
FROM   TABLE(rqEval(
               CURSOR(SELECT 1 "ore.connect", 0 "ore.graphics" FROM DUAL),
               'select 1 pValue from dual',
               'RF_TTEST_PVALUES'));Denis 
SELECT PVALUE
FROM TABLE(rqEval(
CURSOR(SELECT 1 "ore.connect", 0 "ore.graphics" FROM DUAL),
'select 1 pValue from dual',
'RF_TTEST_PVALUES'));Hi,
Thats is very good. Is it a way to do it in R client to avoid following error?
Example:
ORE> library(ORE)
ORE> ore.connect(user="rquser", password="pass", sid="TEST", host="r2151", port=1525, all=TRUE)
Error in .oci.GetQuery(conn, statement, data = data, prefetch = prefetch, :
Error in try({ : ORA-20000: RQuery error
Error in X11(paste("png::", filename, sep = ""), g$width, g$height, pointsize, :
unable to start device PNG
ORA-06512: at "RQSYS.RQEVALIMPL", line 104
ORA-06512: at "RQSYS.RQEVALIMPL", line 101
Regards,
Max 
Max,
We took care of the explicit SQL call in ORE 1.3. We will be taking care of <tt>ore.connect</tt> in the next release.
Denis

Categories

Resources