Home » SQL & PL/SQL » SQL & PL/SQL » Is it possible to call a function in SQL which calls a procedure doing inserts? (Oracle9i Enterprise Edition Release 9.2.0.1.0, Windows Server 2003)
Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338309] |
Mon, 04 August 2008 09:41  |
ein_stein2000
Messages: 14 Registered: August 2008
|
Junior Member |
|
|
I hopy you have not been confused by my topic, here a detailed description of my problem:
I've to call a procedure with SQL where I'm doing inserts. I know that it does not work to do inserts in a function I'm calling in SQL. So I'm calling a function in SQL. The function calls a procedure where I'm doing the inserts, but that does not work. Do I have an error in my code or is that not possible?
my function:
CREATE OR REPLACE FUNCTION callSetDefaultParameters RETURN NUMBER IS
bla NUMBER;
BEGIN
bla := -1337;
setDefaultParameters(bla);
RETURN bla;
END callSetDefaultParameters;
my procedure:
CREATE OR REPLACE PROCEDURE SCREWDRIVER_DEV2.setDefaultParameters (ret OUT NUMBER) IS
CURSOR c_tech_graph_node IS SELECT * FROM tech_graph_node WHERE urs = 0;
v_graph_node_count number;
v_max_primary_key tech_par.tech_par_id%TYPE;
E_NOTHING_TO_DO EXCEPTION;
BEGIN
ret := -1;
-- get count of tech nodes, if there are no tech nodes, nothing is to do
SELECT COUNT(*) INTO v_graph_node_count FROM tech_graph_node WHERE urs = 0;
DBMS_OUTPUT.PUT_LINE(v_graph_node_count || ' nodes to process ...');
IF v_graph_node_count <= 0 THEN
RAISE E_NOTHING_TO_DO;
END IF;
-- get highest primary key
SELECT MAX(tech_par_id) INTO v_max_primary_key FROM tech_par;
DBMS_OUTPUT.PUT_LINE('Highest primary key is: ' || v_max_primary_key);
-- insert the needed parameters into the table tech_par
FOR rec_tech_graph_node IN c_tech_graph_node LOOP
DBMS_OUTPUT.PUT_LINE('UPDATING node ID: ' ||
rec_tech_graph_node.tech_graph_node_id || ',
caption: ' ||
rec_tech_graph_node.caption);
-- increment the primary key and insert the parameter
v_max_primary_key := v_max_primary_key + 1;
INSERT INTO tech_par (TECH_PAR_ID,
TECH_PAR_TYPE_ID,
TECH_GRAPH_NODE_ID,
NAME,
DESCRIPTION,
DESC_LONG,
MODIFYABLE,
DEF_CUR_VALUE,
DEF_MIN_VALUE,
DEF_MAX_VALUE,
ORDER_NUMBER)
VALUES (v_max_primary_key,
2,
rec_tech_graph_node.tech_graph_node_id,
'default_destination',
'Default destination of RP',
'',
0,
'-',
'',
'',
-4);
-- some more inserts ...
commit;
END LOOP;
-- update the order of the technical parameters
UPDATE tech_par SET order_number = order_number + 5;
ret := v_graph_node_count;
DBMS_OUTPUT.PUT_LINE('<<EXIT>> ' || ret || ' nodes processed');
EXCEPTION
WHEN E_NOTHING_TO_DO THEN
DBMS_OUTPUT.PUT_LINE('<<EXIT>> Nothing to do -
because there are no nodes to process');
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('<<EXIT>> DUP_VAL_ON_INDEX');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('<<ERROR>> NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('<<ERROR>> Error#: ' || SQLCODE ||
' ErrorMsg: ' || substr(SQLERRM,1,100));
END setDefaultParameters;
/
my sql-statement calling the function:
select callSetDefaultParameters() from dual; but like mentioned, it does not work...
calling the function from SQL+ is working too:
variable bla NUMBER;
execute :bla := callSetDefaultParameters();
print bla;
calling the procedure from SQL+ is working:
variable bla NUMBER;
execute setDefaultParameters(:bla);
print bla;
anyone can help me with this problem? thx in advance!
|
|
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338322 is a reply to message #338315] |
Mon, 04 August 2008 10:28   |
ein_stein2000
Messages: 14 Registered: August 2008
|
Junior Member |
|
|
ThomasG wrote on Mon, 04 August 2008 16:50 | Welcome to the forum, and thanks for the detailed description.
However, one interesting part is missing, that is which error you actually get. 
|
haha lol, absolutely true ... I tried to explain my problem as detailed as possible but forgot to explain what my problem is sorry! so the probelm is: after executing the procedure/function in sql+ I can see my inserts in the table, but when executing the sql-command, it does not, so there are no inserts. I've also updated my initial post with more information.
ThomasG wrote on Mon, 04 August 2008 16:50 | What you art trying to to could be done with autonomous transactions if you run into the kind of error I suspect.
| I'll have a look at your link
ThomasG wrote on Mon, 04 August 2008 16:50 |
But may I ask why you can't call the procedure directly? Maybe there is a better way than to call it from the function in the select.
|
the problem is, I have to use jdbc to create/execute the scripts, so I can't use SQL+ or PL/SQL-commands like execute
|
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338324 is a reply to message #338309] |
Mon, 04 August 2008 10:37   |
ein_stein2000
Messages: 14 Registered: August 2008
|
Junior Member |
|
|
I can't edit my own message ... ok here are more details:
So the problem is that there are no inserts in my table after the SQL command:
select callSetDefaultParameters() from dual;
the output I get is: CALLSETDEFAULTPARAMETERS()
--------------------------
-1
1 row selected.
I also tried to call a commit; right after the select-statement, but the commit does not help
When calling the function in SQL+:
variable bla NUMBER;
execute :bla := callSetDefaultParameters();
print bla;
I can see my inserts in the table and the script output is:
17 nodes to process ...
Highest primary key is: 8086
UPDATING node ID: 1000, caption: exceptional<br>RONA
UPDATING node ID: 1001, caption: exceptional<br>Reroute
UPDATING node ID: 1003, caption: init
UPDATING node ID: 1004, caption: main
UPDATING node ID: 1005, caption: timeout
UPDATING node ID: 1006, caption: backup
UPDATING node ID: 1101, caption: exceptional<br>RONA
UPDATING node ID: 1102, caption: exceptional<br>Reroute
UPDATING node ID: 1104, caption: init
UPDATING node ID: 1105, caption: main
UPDATING node ID: 1106, caption: timeout
UPDATING node ID: 1107, caption: backup
UPDATING node ID: 1020, caption: Email<br>Section_2
UPDATING node ID: 1021, caption: Email<br>Section_1
UPDATING node ID: 1108, caption: Announcement<br>1
UPDATING node ID: 1109, caption: Announcement<br>2
UPDATING node ID: 1110, caption: Announcement<br>3
<<EXIT>> 17 nodes processed
setDefaultParameters is returning: 17
PL/SQL procedure successfully completed.
bla
----------
17
maybe these information can help you to help me
|
|
|
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338331 is a reply to message #338329] |
Mon, 04 August 2008 10:56   |
ein_stein2000
Messages: 14 Registered: August 2008
|
Junior Member |
|
|
first of all I didn't know that you can call a procedure from java, good to know, but this will not solve my problem, because I've the following function in java and I've to use this function without creating a new one:
public int dbUpdate(String updateQuery) {
if(updateQuery == null) return -1;
Statement stmt = null;
int nrows = 0;
try {
stmt = this.connection.createStatement();
nrows = stmt.executeUpdate(updateQuery);
}
catch(SQLException e) {
nrows = -1;
if(!handleSQLException(e, updateQuery)){
System.exit(-1);
}
}
finally {
try {
if(stmt != null) {
stmt.close();
}
}
catch(SQLException e) { }
}
return nrows;
}
so the "String" is read from a file where my sql-commands are saved
anacedent wrote on Mon, 04 August 2008 17:43 | It might help to actually COMMIT AFTER the INSERT!
It might help to actually COMMIT AFTER the INSERT!
It might help to actually COMMIT AFTER the INSERT!
It might help to actually COMMIT AFTER the INSERT!
|
I tried that to:
CREATE OR REPLACE PROCEDURE setDefaultParameters (ret OUT NUMBER) IS
CURSOR c_tech_graph_node IS SELECT * FROM tech_graph_node WHERE urs = 0;
v_graph_node_count number;
v_max_primary_key tech_par.tech_par_id%TYPE;
E_NOTHING_TO_DO EXCEPTION;
BEGIN
ret := -1;
-- get count of tech nodes, if there are no tech nodes, nothing is to do
SELECT COUNT(*) INTO v_graph_node_count FROM tech_graph_node WHERE urs = 0;
DBMS_OUTPUT.PUT_LINE(v_graph_node_count || ' nodes to process ...');
IF v_graph_node_count <= 0 THEN
RAISE E_NOTHING_TO_DO;
END IF;
-- get highest primary key
SELECT MAX(tech_par_id) INTO v_max_primary_key FROM tech_par;
DBMS_OUTPUT.PUT_LINE('Highest primary key is: ' || v_max_primary_key);
-- insert the needed parameters into the table tech_par
FOR rec_tech_graph_node IN c_tech_graph_node LOOP
DBMS_OUTPUT.PUT_LINE('UPDATING node ID: ' ||
rec_tech_graph_node.tech_graph_node_id || ',
caption: ' ||
rec_tech_graph_node.caption);
-- increment the primary key and insert the parameter
v_max_primary_key := v_max_primary_key + 1;
INSERT INTO tech_par (TECH_PAR_ID,
TECH_PAR_TYPE_ID,
TECH_GRAPH_NODE_ID,
NAME,
DESCRIPTION,
DESC_LONG,
MODIFYABLE,
DEF_CUR_VALUE,
DEF_MIN_VALUE,
DEF_MAX_VALUE,
ORDER_NUMBER)
VALUES (v_max_primary_key,
2,
rec_tech_graph_node.tech_graph_node_id,
'default_destination',
'Default destination of RP',
'',
0,
'-',
'',
'',
-4);
v_max_primary_key := v_max_primary_key + 1;
INSERT INTO TECH_PAR (TECH_PAR_ID,
TECH_PAR_TYPE_ID,
TECH_GRAPH_NODE_ID,
NAME,DESCRIPTION,
DESC_LONG,
MODIFYABLE,
DEF_CUR_VALUE,
DEF_MIN_VALUE,
DEF_MAX_VALUE,
ORDER_NUMBER)
VALUES (v_max_primary_key,
2,
rec_tech_graph_node.tech_graph_node_id,
'strategy0x65',
'Name of routing strategy',
'',
0,
'-',
'',
'',
-3);
commit;
-- some more inserts ...
END LOOP;
-- update the order of the technical parameters
UPDATE tech_par SET order_number = order_number + 5;
ret := v_graph_node_count;
DBMS_OUTPUT.PUT_LINE('<<EXIT>> ' || ret || ' nodes processed');
EXCEPTION
WHEN E_NOTHING_TO_DO THEN
DBMS_OUTPUT.PUT_LINE('<<EXIT>> Nothing to do -
because there are no nodes to process');
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('<<EXIT>> DUP_VAL_ON_INDEX');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('<<ERROR>> NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('<<ERROR>> Error#: ' || SQLCODE ||
' ErrorMsg: ' || substr(SQLERRM,1,100));
END setDefaultParameters;
/
|
|
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338357 is a reply to message #338348] |
Mon, 04 August 2008 13:33   |
szogu
Messages: 21 Registered: July 2008
|
Junior Member |
|
|
Please chcek the following link
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_5010.htm#i2153260
Specially the following paragraph
Quote: | Restrictions on User-Defined Functions
User-defined functions are subject to the following restrictions:
User-defined functions cannot be used in situations that require an unchanging definition. Thus, you cannot use user-defined functions:
In a CHECK constraint clause of a CREATE TABLE or ALTER TABLE statement
In a DEFAULT clause of a CREATE TABLE or ALTER TABLE statement
In addition, when a function is called from within a query or DML statement, the function cannot:
Have OUT or IN OUT parameters
Commit or roll back the current transaction, create a savepoint or roll back to a savepoint, or alter the session or the system. DDL statements implicitly commit the current transaction, so a user-defined function cannot execute any DDL statements.
Write to the database, if the function is being called from a SELECT statement. However, a function called from a subquery in a DML statement can write to the database.
Write to the same table that is being modified by the statement from which the function is called, if the function is called from a DML statement.
Except for the restriction on OUT and IN OUT parameters, Oracle Database enforces these restrictions not only for function when called directly from the SQL statement, but also for any functions that function calls, and on any functions called from the SQL statements executed by function or any functions it calls
|
Regards,
|
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338445 is a reply to message #338438] |
Tue, 05 August 2008 02:21   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
If the problem is the "ORA-14551: cannot perform a DML operation inside a query", then THAT can be worked around with an autonomous transaction, as I said earlier.
But be aware, that that might open up a whole can of worms. Any other programmer that sees a function will NOT assume that a function will change any data for example.
SQL>
SQL> CREATE TABLE tab_test (col VARCHAR2(10));
Table created.
SQL> -- This function doesn't work.
SQL> CREATE OR REPLACE FUNCTION ins_test (v_in VARCHAR2) RETURN VARCHAR2
2 IS
3 BEGIN
4 INSERT INTO tab_test VALUES(v_in);
5 COMMIT;
6 RETURN 'OK';
7 END;
8 /
Function created.
SQL>
SQL> SELECT ins_test('TEST') FROM dual;
SELECT ins_test('TEST') FROM dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "PFK.INS_TEST", line 4
SQL> -- This function works.
SQL> CREATE OR REPLACE FUNCTION ins_test (v_in VARCHAR2) RETURN VARCHAR2
2 IS
3 PRAGMA autonomous_transaction;
4 BEGIN
5 INSERT INTO tab_test VALUES(v_in);
6 COMMIT;
7 RETURN 'OK';
8 END;
9 /
Function created.
SQL>
SQL> SELECT ins_test('TEST') FROM dual;
INS_TEST('TEST')
-------------------------------------------------------------------------------
OK
SQL>
SQL> SELECT * FROM tab_test;
COL
----------
TEST
SQL>
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338619 is a reply to message #338445] |
Tue, 05 August 2008 08:25   |
szogu
Messages: 21 Registered: July 2008
|
Junior Member |
|
|
Quote: | the OUT parameter is not the return value a function, right? so my function should keep this condition
|
Right, Out parameter is not return value
Quote: | I think thats the only way I can solve my problem ... but honestly I dont have an idea how this could work
|
Here is an example
09:18:15 SQL> CREATE table test1
09:18:17 2 (col1 VARCHAR2(1),
09:18:17 3 col2 VARCHAR2(1)
09:18:17 4 );
Table created.
Elapsed: 00:00:00.00
09:18:17 SQL> CREATE table test2
09:18:27 2 (col1 VARCHAR2(1));
Table created.
Elapsed: 00:00:00.00
09:18:27 SQL> CREATE OR REPLACE FUNCTION ins_fu (p_param VARCHAR2)
09:18:36 2 RETURN VARCHAR2
09:18:36 3 IS
09:18:36 4 BEGIN
09:18:36 5 INSERT INTO test1(col1,col2)
09:18:36 6 VALUES ('A','B');
09:18:36 7 RETURN 'Y';
09:18:36 8 END;
09:18:36 9 /
Function created.
Elapsed: 00:00:00.00
09:18:38 SQL> INSERT INTO test2
09:18:48 2 SELECT ins_fu('C')
09:18:48 3 FROM DUAL;
1 row created.
Elapsed: 00:00:00.00
09:18:48 SQL> select *
09:19:10 2 from test1;
C C
- -
A B
Elapsed: 00:00:00.00
09:19:17 SQL> select *
09:19:22 2 from test2;
C
-
Y
Elapsed: 00:00:00.00
09:19:27 SQL>
Regards,
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338644 is a reply to message #338309] |
Tue, 05 August 2008 09:33   |
ein_stein2000
Messages: 14 Registered: August 2008
|
Junior Member |
|
|
you are my hero ThomasG 
I think it was too late yesterday to understand how to use the autonomous transaction. with your example everything is clear now and everything works great in a sql-session
@szogu: thx for your example, I'll try that too, because I can call the function with the SQL-command "select callSetDefaultParameters() from dual;" and everything works fine in a sql-session, but there are still errors while trying to axecute the sql-command with jdbc ... I'm gettin a exception: "java.sql.SQLException: ORA-06575: Paket bzw. Funktion CALLSETDEFAULTPARAMETERS in ungültigem Zustand", I'll have a look into this and try your way to solve the problem, if the problem with the exception can't be solved
[Updated on: Tue, 05 August 2008 09:40] Report message to a moderator
|
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338667 is a reply to message #338653] |
Tue, 05 August 2008 10:40   |
ein_stein2000
Messages: 14 Registered: August 2008
|
Junior Member |
|
|
anacedent wrote on Tue, 05 August 2008 17:15 |
06575, 00000, "Package or function %s is in an invalid state"
// *Cause: A SQL statement references a PL/SQL function that is in an
// invalid state. Oracle attempted to compile the function, but
// detected errors.
// *Action: Check the SQL statement and the PL/SQL function for syntax
// errors or incorrectly assigned, or missing, privileges for a
// referenced object.
privileges acquired via ROLE do NOT apply within PL/SQL procedures.
Prior to testing by doing:
SQL> select callSetDefaultParameters() from dual;
issue the following:
SQL> SET ROLE NONE;
SQL> select callSetDefaultParameters() from dual;
|
honestly I don't know what you mean I don't acquire any role in my procedure, here is my whole procedure:
CREATE OR REPLACE PROCEDURE setDefaultParameters (ret OUT NUMBER) IS
CURSOR c_tech_graph_node IS SELECT * FROM tech_graph_node WHERE urs = 0;
v_graph_node_count number;
v_max_primary_key tech_par.tech_par_id%TYPE;
E_NOTHING_TO_DO EXCEPTION;
PRAGMA autonomous_transaction;
BEGIN
ret := -1;
-- get count of tech nodes, if there are no tech nodes, nothing is to do
SELECT COUNT(*) INTO v_graph_node_count FROM tech_graph_node WHERE urs = 0;
DBMS_OUTPUT.PUT_LINE(v_graph_node_count || ' nodes to process ...');
IF v_graph_node_count <= 0 THEN
RAISE E_NOTHING_TO_DO;
END IF;
-- get highest primary key
SELECT MAX(tech_par_id) INTO v_max_primary_key FROM tech_par;
DBMS_OUTPUT.PUT_LINE('Highest primary key is: ' || v_max_primary_key);
-- insert the needed parameters into the table tech_par
FOR rec_tech_graph_node IN c_tech_graph_node LOOP
DBMS_OUTPUT.PUT_LINE('UPDATING node ID: ' ||
rec_tech_graph_node.tech_graph_node_id ||
', caption: ' ||
rec_tech_graph_node.caption);
-- increment the primary key and insert the parameter
v_max_primary_key := v_max_primary_key + 1;
INSERT INTO TECH_PAR ([too long line ...]
commit;
v_max_primary_key := v_max_primary_key + 1;
INSERT INTO TECH_PAR ([too long line ...]
commit;
v_max_primary_key := v_max_primary_key + 1;
INSERT INTO TECH_PAR ([too long line ...]
commit;
v_max_primary_key := v_max_primary_key + 1;
INSERT INTO TECH_PAR ([too long line ...]
commit;
v_max_primary_key := v_max_primary_key + 1;
INSERT INTO TECH_PAR ([too long line ...]
commit;
END LOOP;
-- update the order of the technical parameters
UPDATE tech_par SET order_number = order_number + 5;
commit;
ret := v_graph_node_count;
DBMS_OUTPUT.PUT_LINE('<<EXIT>> ' || ret || ' nodes processed');
EXCEPTION
WHEN E_NOTHING_TO_DO THEN
DBMS_OUTPUT.PUT_LINE('<<EXIT>> Nothing to do -
because there are no nodes to process');
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('<<EXIT>> DUP_VAL_ON_INDEX');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('<<ERROR>> NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('<<ERROR>> Error#: '
|| SQLCODE || ' ErrorMsg: ' || substr(SQLERRM,1,100));
END setDefaultParameters;
/
and here my function which calls the procedure:CREATE OR REPLACE FUNCTION callSetDefaultParameters RETURN NUMBER IS
bla NUMBER;
BEGIN
bla := -1337;
setDefaultParameters(bla);
DBMS_OUTPUT.PUT_LINE('setDefaultParameters
is returning: ' || bla);
RETURN bla;
END callSetDefaultParameters;
/
the interesting point is: when I access the oracle db with Oracel SQL Developer or with Toad for Oracle the function and the procedures are marked as "with error", when opening them in any of these tools and compiling them without any changes, there is no more error and the SQL-command is executed without an exception
[Updated on: Tue, 05 August 2008 10:42] Report message to a moderator
|
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338674 is a reply to message #338668] |
Tue, 05 August 2008 10:58   |
ein_stein2000
Messages: 14 Registered: August 2008
|
Junior Member |
|
|
NAME TYPE SEQUENCE LINE POSITION TEXT
CALLSETDEFAULTPARAMETERS FUNCTION 1 1 51 PLS-00103: Fand das Symbol "" als eines der folgenden erwartet wurde:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor external language
RBA_TREE_OBJECTS PACKAGE 1 1 28 PLS-00103: Fand das Symbol "" als eines der folgenden erwartet wurde:
end function package pragma private procedure subtype type
use <an identifier> <a double-quoted delimited-identifier>
form current cursor
RBA_TREE_OBJECTS PACKAGE BODY 1 1 33 PLS-00103: Fand das Symbol "" als eines der folgenden erwartet wurde:
begin end function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
SETDEFAULTPARAMETERS PROCEDURE 1 1 51 PLS-00103: Fand das Symbol "" als eines der folgenden erwartet wurde:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor external language
Das Symbol "" wurde ignoriert.
SETDEFAULTPARAMETERS PROCEDURE 2 2 77 PLS-00103: Fand das Symbol "" als eines der folgenden erwartet wurde:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
EDIT: I found out that the error could be the ending of my SQL-command. So adapted my java-code and edited the string I'm executing with "stmt.executeUpdate(updateQuery)" ... i tried the following things, but nothing worked:
1. adding a newline \n
2. adding a newline followed by a slash \n/
3. adding a newline followed by a slash, followed by a newline \n/\n
any suggestions?
[Updated on: Tue, 05 August 2008 11:28] Report message to a moderator
|
|
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338766 is a reply to message #338309] |
Tue, 05 August 2008 21:25   |
TheSingerman
Messages: 49 Registered: April 2008 Location: Brighton, Michigan
|
Member |
|
|
A quick comment on the whole Role business: In my experience, almost all of the cases of "function/procedure works in SQL*Plus, fails when called by application" were resolved by having privileges granted through a role. Try issuing this (in SQL*Plus):
SELECT * FROM SESSION_ROLES;
You may find you have more default logon roles than you thought. You may find you only have a crucial privilege through a role.
Just my $0.02 after a very quick browse.
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338839 is a reply to message #338766] |
Wed, 06 August 2008 01:45   |
ein_stein2000
Messages: 14 Registered: August 2008
|
Junior Member |
|
|
ThomasG wrote on Tue, 05 August 2008 19:42 | No, there is something wrong with the function and the procedure, that has to be fixed first, so that "select * from user_errors" returns NO lines.
(at least none from CALLSETDEFAULTPARAMETERS and SETDEFAULTPARAMETERS)
Newlines in the java call are most likely meaningless, they are not needed.
|
I think there is a problem with the encoding or something like that ... I've debuged the java code and copied the string from "stmt.executeUpdate(updateQuery);" direct into the java code and there where no errors in "select * from user_errors", maybe anyone can see a mistake in the java code:
// determine upgrade script filename
String fileName = getScriptFilename(major, minor);
// check the script exists
int length = 8192*10;
byte[] buffer = new byte[length + 2];
stream = getClass().getResourceAsStream(fileName);
if(stream == null) { // the script doesn't exist
if(++notFoundCounter > 2) { // more than 2 minor numbers skipped
notFoundCounter = 0;
major++; minor = 0; // increase major number
fileName = getScriptFilename(major, minor);
stream = getClass().getResourceAsStream(fileName);
if(stream == null) { // the major version doesn't exist
break; // enumeration done
}
}
continue;
}
// load the script
Logger.getLogger("db").info("Upgrading database with '" + fileName + "'");
length = stream.read(buffer);
sql += new String(buffer, 0, length); // append its content
stream.close();
stream = null;
// update the database version string
last_major = major;
last_minor = minor;
sql += "\nUPDATE info set value = '" +
Integer.toString(major) + "." + Integer.toString(minor) +
"' WHERE name = 'db_version'";
so "sql" is the string I process and send in statements to "stmt.executeUpdate(updateQuery);" ... EDIT: I think I'll rewrite the code and use a FileInputStreamReader instead of the strange way with the byte array
Barbara Boehmer wrote on Tue, 05 August 2008 23:30 | The pragma autonomous_transaction needs to be in the function, not the procedure.
| I've thought that it should be in the procedure, because in the function there are no DML commands, but I'll try the "pragma autonomous_transaction" in the function later, the db server is currently down for maintenance
TheSingerman wrote on Wed, 06 August 2008 04:25 | A quick comment on the whole Role business: In my experience, almost all of the cases of "function/procedure works in SQL*Plus, fails when called by application" were resolved by having privileges granted through a role. Try issuing this (in SQL*Plus):
SELECT * FROM SESSION_ROLES;
You may find you have more default logon roles than you thought. You may find you only have a crucial privilege through a role.
Just my $0.02 after a very quick browse.
|
thx for the hint, I'll have a look into this when the db server is again available
[Updated on: Wed, 06 August 2008 02:01] Report message to a moderator
|
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #339760 is a reply to message #338309] |
Fri, 08 August 2008 06:52   |
ein_stein2000
Messages: 14 Registered: August 2008
|
Junior Member |
|
|
the database server is again running 
TheSingerman wrote on Wed, 06 August 2008 04:25 | A quick comment on the whole Role business: In my experience, almost all of the cases of "function/procedure works in SQL*Plus, fails when called by application" were resolved by having privileges granted through a role. Try issuing this (in SQL*Plus):
SELECT * FROM SESSION_ROLES;
You may find you have more default logon roles than you thought. You may find you only have a crucial privilege through a role.
Just my $0.02 after a very quick browse.
|
here what I get: ROLE
------------------------------
CONNECT
LOGSTDBY_ADMINISTRATOR
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
OLAP_DBA
RESOURCE
15 rows selected.
so I think everything should work 
ThomasG wrote on Wed, 06 August 2008 09:14 |
- The select/insert which you claimed caused the error is nowhere to be seen in the Java code you posted.
- Post the Java code that is actually firing the SQL Statement.
- Write the SQL statement that is actually processes to STDOUT with System.out.println ringht before it is prepared and post that.
- Post the complete Java stack trace of the error.
|
you'll find everything you want in the bottom of my post, but it's definitely a problem with newlines in the sql-file ... but first the things you wanna see
the java code that processes my variable "sql" from my post before:if(!sql.isEmpty()) { // execute upgrade of all scripts at once
int ibegin = 0, iend, i1, i2, i3;
while(true) { // loop through all sql commands
// split the script into single command chunks
i1 = sql.indexOf("\ncommit;", ibegin);
i2 = sql.indexOf("\nGO", ibegin);
i3 = sql.indexOf("\n/", ibegin);
if(i1 == -1 && i2 == -1 && i3 == -1) { // no further separator found
break; // done
}
// find the closest separator
iend = Integer.MAX_VALUE;
if(i1 < iend && i1 != -1) iend = i1;
if(i2 < iend && i2 != -1) iend = i2;
if(i3 < iend && i3 != -1) iend = i3;
String cmd = sql.substring(ibegin, iend).trim();
// execute the command
System.out.println(--);
System.out.println(cmd);
System.out.println(--);
int rows = dbw.dbUpdate(cmd);
if(rows < 0) { // error
//dbw.rollbackTransaction();
tring message = lb.getString("UpgradeTool.failed");
Logger.getLogger("db").fatal(message);
//MessageDialog.showErrorDialog(null, message);
// show upgrade dialog
LoginFrame.setLookAndFeel();
new UpgradeDialog(sql.substring(ibegin), last_major, last_minor);
return false;
}
ibegin = sql.indexOf('\n', iend + 1); // go to the next line
}
} so you can see that in the variable "cmd" is my sql-statement ... here to code of the function "dbUpdate(String cmd)":public int dbUpdate(String updateQuery) {
if(updateQuery == null) return -1;
Statement stmt = null;
int nrows = 0;
try {
stmt = this.connection.createStatement();
nrows = stmt.executeUpdate(updateQuery);
}
catch(SQLException e) {
nrows = -1;
if(!handleSQLException(e, updateQuery)){
System.exit(-1); // if the user does not want to reconnect -> exit
}
}
finally {
try {
if(stmt != null) {
stmt.close();
}
}
catch(SQLException e) { }
}
return nrows;
}
so this is the code ... then here my 2 tests with the function, I've added and tested the stored procedure with toad, so the stored procedure which i call in the function works ... now the content of the file (copy & paste, sry for the long lines):CREATE OR REPLACE FUNCTION callSetDefaultParameters RETURN NUMBER IS bla NUMBER; BEGIN bla := -1337; setDefaultParameters(bla); DBMS_OUTPUT.PUT_LINE('setDefaultParameters is returning: \' || bla); RETURN bla; END callSetDefaultParameters;
/ System.out of cmd is: --
CREATE OR REPLACE FUNCTION callSetDefaultParameters RETURN NUMBER IS bla NUMBER; BEGIN bla := -1337; setDefaultParameters(bla); DBMS_OUTPUT.PUT_LINE('setDefaultParameters is returning: \' || bla); RETURN bla; END callSetDefaultParameters;
--
Lets have a look into the database, there are no errors, select * from user_errors;
2nd test: content of the file:CREATE OR REPLACE FUNCTION callSetDefaultParameters RETURN NUMBER IS bla NUMBER;
BEGIN bla := -1337; setDefaultParameters(bla); DBMS_OUTPUT.PUT_LINE('setDefaultParameters is returning: \' || bla); RETURN bla; END callSetDefaultParameters;
/ System.out:--
CREATE OR REPLACE FUNCTION callSetDefaultParameters RETURN NUMBER IS bla NUMBER;
BEGIN bla := -1337; setDefaultParameters(bla); DBMS_OUTPUT.PUT_LINE('setDefaultParameters is returning: \' || bla); RETURN bla; END callSetDefaultParameters;
--
very important: there is NO(!) java exception!! but there are errors like you can see with select * from user_errors;
NAME TYPE SEQUENCE LINE POSITION
------------------------------ ------------ ---------- ---------- ----------
TEXT
--------------------------------------------------------------------------------
CALLSETDEFAULTPARAMETERS FUNCTION 1 1 63
PLS-00103: Fand das Symbol "" als eines der folgenden erwartet wurde:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
Das Symbol "" wurde ignoriert.
1 row selected.
So my question what encoding/charset do I need to send newlines to Oracle without having these error? Any ideas?
[Updated on: Fri, 08 August 2008 06:54] Report message to a moderator
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #339771 is a reply to message #339760] |
Fri, 08 August 2008 07:41   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
An error in the SQL will create a Java exception.
But depending what is in the "handleSQLException" thing you might not see it.
change
catch(SQLException e) {
nrows = -1;
if(!handleSQLException(e, updateQuery)){
System.exit(-1); // if ....
}
}
to
catch(SQLException e) {
e.printStackTrace(); // Print exception to STDOUT
nrows = -1;
if(!handleSQLException(e, updateQuery)){
System.exit(-1); // if ....
}
}
to see it.
Edit:
Ehhh.. Wait a minute... you CREATE the pl/sql procedures/functions, too, on the fly, at runtime, via the Java program? Did I read that right?
DON'T do that. Never. Ever. Not even if hell freezes over.
[Updated on: Fri, 08 August 2008 07:59] Report message to a moderator
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #339836 is a reply to message #339760] |
Fri, 08 August 2008 16:01   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
This may or may not be one of your problems, but I will try one more time to explain about roles and procedures. Various privileges are granted to various roles and you may be granted those roles. You may also be granted some of those privileges directly, not through a role. Procedures only recognize privileges granted directly and ignore privileges granted through roles. So, if you need a privilege that has been granted through a role, but not directly, your procedure will produce an error. The errors can be somewhat misleading in that they may indicate that something doesn't exist, because it does not have the required privilege to see it. If something works from SQL with roles enabled, but doesn't work after you "set role none", then you know that is your problem. That is how you test to determine if that is the problem. Many tools utilize such roles, so sometimes things won't run with the tool, but will run through SQL*Plus. The fact that you have a lot of roles doesn't mean that things should be working. It means that you should suspect that one of your problems is that you don't have the privileges that you need outside of the roles. You have a bunch of problems and nothing is going to return without error until you have fixed the right combination of all of them at once. You need to break it down into pieces. First get to where your procedure works from SQL*Plus, then get to where your function that calls your procedure works from SQL*Plus, then get to where you can call it from a select like you want to, with the pragma autonomous_transaction in the correct place in the function. Then try to call it from java and work on that problem.
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #339844 is a reply to message #338309] |
Fri, 08 August 2008 21:59   |
TheSingerman
Messages: 49 Registered: April 2008 Location: Brighton, Michigan
|
Member |
|
|
Quote: | here what I get:
ROLE
------------------------------
CONNECT
LOGSTDBY_ADMINISTRATOR
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
OLAP_DBA
RESOURCE
15 rows selected.
so I think everything should work Sad
|
Does everybody at your site stay connected to Oracle all day long with DBA, SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, DELETE_CATALOG_ROLE, EXP_FULL_DATABASE, IMP_FULL_DATABASE, OLAP_DBA and JAVA_ADMIN enabled?
You do realize that you have no security or controls present at all for you in SQL*Plus (stored procedures are a different matter, but that is less important)?
In 9i, you should be working with just the CONNECT, RESOURCE, and PLUSTRACE roles enabled, along with any roles which give you needed access to the objects you are currently working with. What you are doing is the Windows equivalent of always logging on as Administrator -- and making 'Guest' and 'Power User' Administrators as well.
You would never let a web server connect to Oracle with an account with the DBA role; you should make every effort to avoid using it for the same reasons.
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #340032 is a reply to message #338309] |
Mon, 11 August 2008 02:11   |
ein_stein2000
Messages: 14 Registered: August 2008
|
Junior Member |
|
|
ThomasG wrote on Fri, 08 August 2008 14:41 | An error in the SQL will create a Java exception.
But depending what is in the "handleSQLException" thing you might not see it.
change
catch(SQLException e) {
nrows = -1;
if(!handleSQLException(e, updateQuery)){
System.exit(-1); // if ....
}
}
to
catch(SQLException e) {
e.printStackTrace(); // Print exception to STDOUT
nrows = -1;
if(!handleSQLException(e, updateQuery)){
System.exit(-1); // if ....
}
}
to see it.
|
handleSQLException(e, updateQuery) does a printStackTrace() in a popup window, but there is NO exception while inserting the function ...
ThomasG wrote on Fri, 08 August 2008 14:41 | Edit:
Ehhh.. Wait a minute... you CREATE the pl/sql procedures/functions, too, on the fly, at runtime, via the Java program? Did I read that right?
DON'T do that. Never. Ever. Not even if hell freezes over.
| but that's what I have to do, we have an update procedure while the program starts and in this update procedure changes in the database are made with updatescripts, so creating/deleting a table/constraing, doing inserts/updates are no problem, but my job is to create this new stored procedure and insert the procedure with such an update script ... and my boss does not care if the hell freezes or not ...
first of all thx for the explanation barbara!
Barbara Boehmer wrote on Fri, 08 August 2008 23:01 | First get to where your procedure works from SQL*Plus => WORKS then get to where your function that calls your procedure works from SQL*Plus => WORKS, then get to where you can call it from a select like you want to => WORKS, with the pragma autonomous_transaction in the correct place in the function => I've tested the autonomous_transaction in the function and in the procedure, it works in both, but not at the same time, so I've placed autonomous_transaction in the procedure. Then try to call it from java and work on that problem. => WORKS if the procedure/function is only one line without any newlines
| nevertheless I'll ask our oracle experts (after they are back from their holidays) if there could be a problem with the roles/rights
TheSingerman wrote on Sat, 09 August 2008 04:59 | Does everybody at your site stay connected to Oracle all day long with DBA, SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, DELETE_CATALOG_ROLE, EXP_FULL_DATABASE, IMP_FULL_DATABASE, OLAP_DBA and JAVA_ADMIN enabled?
You do realize that you have no security or controls present at all for you in SQL*Plus (stored procedures are a different matter, but that is less important)?
In 9i, you should be working with just the CONNECT, RESOURCE, and PLUSTRACE roles enabled, along with any roles which give you needed access to the objects you are currently working with. What you are doing is the Windows equivalent of always logging on as Administrator -- and making 'Guest' and 'Power User' Administrators as well.
You would never let a web server connect to Oracle with an account with the DBA role; you should make every effort to avoid using it for the same reasons.
|
I'm not responsible for the database server, afaik there is not really a administrator for the server. The server I'm using is a developer database server, everyone has the password for the administrator, everyone can do whatever he wants/have to do, so security is not an issue
so the "only" problem I've are the newlines/linebreaks ... a solution is to put everything into one line before sending the string to the database, but that's not very nice ... the best solution would be to get to know what encoding/charset I need, so I can send the string with newlines/linebreaks to the database
[Updated on: Mon, 11 August 2008 02:12] Report message to a moderator
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #340120 is a reply to message #340032] |
Mon, 11 August 2008 09:11   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Please post a copy and paste of a run of compilation of your procedure with pragma autonomous_transaction and your function without it and calling your function from a select statement, followed by a commit, as you say that works, because otherwise I don't believe that the whole combination can work that way. For the whole combination to work that way, the pragma autonomous_transaction must be in the function, because it is the function that has the restriction, but only when called from the select statement. You may be able to run the select your way, but will get a different error detecting the autonomous transaction and doing a rollback when you try to commit. So, I believe you are moving on to the next step without having correctly resolved the previous one. These errors then may show up as more generalized errors in the next step.
|
|
|
|
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #340334 is a reply to message #338309] |
Tue, 12 August 2008 06:46  |
ein_stein2000
Messages: 14 Registered: August 2008
|
Junior Member |
|
|
Barbara Boehmer wrote on Mon, 11 August 2008 16:11 | Please post a copy and paste of a run of compilation of your procedure with pragma autonomous_transaction and your function without it and calling your function from a select statement, followed by a commit, as you say that works, because otherwise I don't believe that the whole combination can work that way. For the whole combination to work that way, the pragma autonomous_transaction must be in the function, because it is the function that has the restriction, but only when called from the select statement. You may be able to run the select your way, but will get a different error detecting the autonomous transaction and doing a rollback when you try to commit. So, I believe you are moving on to the next step without having correctly resolved the previous one. These errors then may show up as more generalized errors in the next step.
|
please download my attachment, rename "test.sql" to "test.zip", extract the zip-file and you will get the following 4 files:
test_creates.sql -> creates the needed tables and also some test inserts
test_script.sql -> my script: creating a stored procedure with "PRAGMA autonomous_transaction", creating a function (without PRAGMA autonomous_transaction) that calls the stored procedure and finally the sql-statement that calls the function
test_cleanup.sql -> cleanup script, will revert all changes done by test_script.sql, so you can start another test
test_drop.sql -> will drop everything created in "test_creates.sql"
I've tested the script with with TOAD for ORACLE 9.1.0.62 and SQLDeveloper on our Oracle9i Enterprise Edition Release 9.2.0.1.0 database. You will see, everything works fine. You can also execute all scripts with JDBC, BUT the function/stored procedure will be in an "invalid" state in the database, therefore the "select callSetDefaultParametersX() from dual;" will also not work ... if you fixe the function/stored procedure, you can also call the select with jdbc ... if you find any error, please let me know 
ThomasG wrote on Tue, 12 August 2008 09:48 | Quote: |
Quote: |
DON'T do that. Never. Ever. Not even if hell freezes over.
|
but that's what I have to do.
|
Then you are now on your own, since it will definitely never work. What if two people start the program at the same time?
DON'T COMPILE STORED PROCEDURES OR FUNCTIONS AT RUN TIME.
|
everything is working now with the help of a colleague we are currently working on the problem: we will send the stored procedures/function in one single line without newlines to the database and it's working 
And like mentioned in my post before: I've to compile the stored procedure on the fly. I had hours of discussion with my boss about the upgrade process and the way he wants me to do the upgrade of the database. I'm not happy with that and I would never handle the a database upgrade in this way I've to handle it, but I'm only a student in a summer job, trying to do the work I've to do
-
Attachment: test.sql
(Size: 3.79KB, Downloaded 663 times)
|
|
|
Goto Forum:
Current Time: Wed Feb 12 08:30:43 CST 2025
|