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 Go to next message
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 #338314 is a reply to message #338309] Mon, 04 August 2008 09:45 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above


> but that does not work

My car does not work.
Tell me how to make my car go.
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338315 is a reply to message #338309] Mon, 04 August 2008 09:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Welcome to the forum, and thanks for the detailed description.

However, one interesting part is missing, that is which error you actually get. Wink

What you art trying to to could be done with autonomous transactions if you run into the kind of error I suspect.

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.

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 Go to previous messageGo to next message
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. Wink


haha lol, absolutely true ... I tried to explain my problem as detailed as possible but forgot to explain what my problem is Very Happy 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 #338323 is a reply to message #338309] Mon, 04 August 2008 10:30 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
It might help to actually COMMIT after the INSERT,
but then again it may not.
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 Go to previous messageGo to next message
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 Smile
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338326 is a reply to message #338322] Mon, 04 August 2008 10:40 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ah, but you CAN call oracle procedures from Java over JDBC connections.

Some examples :

PL/SQL and JDBC Examples

JDBC FAQ

Basic JDBC Samples
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338327 is a reply to message #338323] Mon, 04 August 2008 10:40 Go to previous messageGo to next message
ein_stein2000
Messages: 14
Registered: August 2008
Junior Member
anacedent wrote on Mon, 04 August 2008 17:30
It might help to actually COMMIT after the INSERT,
but then again it may not.


I tried that too but without success...
Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338329 is a reply to message #338309] Mon, 04 August 2008 10:43 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
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!
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 Go to previous messageGo to next message
ein_stein2000
Messages: 14
Registered: August 2008
Junior Member
ThomasG wrote on Mon, 04 August 2008 17:40
Ah, but you CAN call oracle procedures from Java over JDBC connections.

Some examples :

PL/SQL and JDBC Examples

JDBC FAQ

Basic JDBC Samples

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 #338337 is a reply to message #338331] Mon, 04 August 2008 11:07 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Comment out the entire exception block in the procedure.

You will have no way of catching the DBMS_OUTPUTS in Java anyway, so you will have to let the exceptions rise to the client to know about them.

Also, post the exact SQL*Plus session when you run the

select callSetDefaultParameters() from dual;

Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338348 is a reply to message #338337] Mon, 04 August 2008 12:06 Go to previous messageGo to next message
ein_stein2000
Messages: 14
Registered: August 2008
Junior Member
ThomasG wrote on Mon, 04 August 2008 18:07
Comment out the entire exception block in the procedure.

You will have no way of catching the DBMS_OUTPUTS in Java anyway, so you will have to let the exceptions rise to the client to know about them.

Also, post the exact SQL*Plus session when you run the

select callSetDefaultParameters() from dual;



will try/do in the office tomorrow
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 Go to previous messageGo to next message
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 #338438 is a reply to message #338357] Tue, 05 August 2008 01:47 Go to previous messageGo to next message
ein_stein2000
Messages: 14
Registered: August 2008
Junior Member
thx for your answer, a few questions to the interesting part of your quote:

Quote:

In addition, when a function is called from within a query or DML statement, the function cannot:

Have OUT or IN OUT parameters


the OUT parameter is not the return value a function, right? so my function should keep this condition

Quote:

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.


I think thats the only way I can solve my problem ... but honestly I dont have an idea how this could work Sad

I'll get a coffee and think about the problem ...
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ein_stein2000
Messages: 14
Registered: August 2008
Junior Member
you are my hero ThomasG Very Happy

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 #338653 is a reply to message #338309] Tue, 05 August 2008 10:15 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
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;
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 Go to previous messageGo to next message
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 Sad 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 #338668 is a reply to message #338667] Tue, 05 August 2008 10:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member

do a "select * from user_errors;" BEFORE recompiling, while the procedure is still invalid, and post the result.
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 Go to previous messageGo to next message
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 #338698 is a reply to message #338674] Tue, 05 August 2008 12:42 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
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.



Re: Is it possible to call a function in SQL which calls a procedure doing inserts? [message #338721 is a reply to message #338674] Tue, 05 August 2008 16:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
The pragma autonomous_transaction needs to be in the function, not the procedure.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #338855 is a reply to message #338839] Wed, 06 August 2008 02:14 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member

  1. The select/insert which you claimed caused the error is nowhere to be seen in the Java code you posted.
  2. Post the Java code that is actually firing the SQL Statement.
  3. Write the SQL statement that is actually processes to STDOUT with System.out.println ringht before it is prepared and post that.
  4. Post the complete Java stack trace of the error.



[Updated on: Wed, 06 August 2008 02:15]

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 Go to previous messageGo to next message
ein_stein2000
Messages: 14
Registered: August 2008
Junior Member
the database server is again running Smile

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 Sad
ThomasG wrote on Wed, 06 August 2008 09:14

  1. The select/insert which you claimed caused the error is nowhere to be seen in the Java code you posted.
  2. Post the Java code that is actually firing the SQL Statement.
  3. Write the SQL statement that is actually processes to STDOUT with System.out.println ringht before it is prepared and post that.
  4. 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;
no rows selected.

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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
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 #340267 is a reply to message #340120] Tue, 12 August 2008 02:48 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
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.

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 Go to previous message
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 Smile

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 Smile 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 Smile

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 207 times)
Previous Topic: cursor fetching
Next Topic: referencing a composite primary key as foreign key
Goto Forum:
  


Current Time: Sun Dec 04 14:49:18 CST 2016

Total time taken to generate the page: 0.04824 seconds