Jump to content


  • Content count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About hugo.cardoso

  • Rank
    Advanced Member

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. hugo.cardoso

    pl/sql exception (error) raise

    Hello Mr. Burleson, Is it possible to know in a procedure if it was called/executed by a raise (exception) context? And (if it was) how to know something about that raise (with system variables or something like that)? May thanks and best regards, Hugo.
  2. Hi, There are secure ways to guarantee that only a specific user schema connection can be done from a client machine to a database? Wich are they? Thank you so much. Best regards, Hugo.
  3. hugo.cardoso

    type convertion and overloading

    Hi to all, I like sharing an unexpected (for me) but interesting thing. When I was creating a package from another to create my package version I came upon the following situation: The package was compiled and when executed stays in a logical "forever" in loop... The reason for this, it is a function (that is overloading in the original package and not in the new) with something like this in my package : FUNCTION AAA (p_in IN number) RETURN NUMBER IS BEGIN RETURN AAA(to_char(p_in)); END; Lesson learned
  4. Hello, I ask if i can use as a preliminar estimation of an index space growing (if it makes any sense) the 'total space index allocation divided by number of rows' multiplied by 'number of rows to insert'. Exists another possibility to make a simple estimation on this? Many thanks. Best regards Hugo.
  5. Hi to all, I need to do two procedures to a xml clob message: 1. read some elements and validate; 2. read almost all the message to register in relational tables; I think that for procedure 1. it is more efficient the use of xmltype extract methods and for procedure 2. it is more efficient the use of dbms_xmldom methods. Am I right? If no, I would appreciate an explanation.. Thanks, Hugo Cardoso.
  6. Hello, Congratulations to everyone! I have been implementing a script to change a lot of data in a database production. Because of this the database will be 100% dedicated to the execution of that script, in the sense that nothing else will be running in this period (the application will be stopped). My question is: what can i do to improve performance of that execution? is there any oracle manual online for this type of problem? I do not know if it's possible, but I'm thinking of things like disabling locking mechanism (if possible I could run instead of a process many processes in parallel), disabling index growing (during the process), disabling constraints, ... thanks for the help Hugo Cardoso.
  7. Hi to all, I need an urgent help. I want to execute scripts to estimate statistics for all the indexes and tables ( like "ANALYZE INDEX AA ESTIMATE STATISTICS;" and " ANALYZE TABLE ACTUALIZACAO_NR_PROCESSO_CLIN ESTIMATE STATISTICS FOR TABLE;") at weekends but at the same time must be in execution another processes that makes DML commands on data (interface processes to integrate data from and to other databases). This situation could be dangerous or result in any problem/error? Thanks in advance, hugo
  8. hugo.cardoso

    UTL_TCP connection

    Hi to all, I have a process that creates a specific temporary data file in the database server to send it to a configurated remote ftp server. It uses an UTL_TCP connection. The process normally works fine, but for one particular ftp server, after trying to put the data into the remote server, the server replies with the message "500 /name_of_the_file.txt: Access is denied.". I try to simulate the situation with a telnet connection to the database server but it works. I imagine that it is because the user i use in the telnet connection it's different then the user used by the UTL_TCP. My question is: What is the operation system user used by the UTL_TCP connection? Is it the operation system user used to install oracle database? Any sugestions will be appreciated. Independently the problem could be related to the remote ftp server, for now i think it is convenient simulate the situation in the operations system. thanks in advance, hugo
  9. hugo.cardoso

    Table-functions and ORA-03113

    Hi Burleson, I didn't find any trace file related to the error. The oracle connection was lost too, i had to re-connect after. To solve the problem i made one procedure similar to each table-function that it inserts data into a temporary table instead of placing them into the 'pipe row'. These new procedures are autonomous transaction with commit before return. Now, it works without any problem. (I like so much the table-function solution but in this case...) Many thanks anyway.
  10. hugo.cardoso

    Table-functions and ORA-03113

    Hello to everybody! I think i have a big problem and i don't know what can i do... I have processes to create data files (it uses UTL_FILE package) with DML commands. To get the data to put inside the data files I use some table functions. When running all the process it provokes "ORA-03113: end-of-file on communication channel". I already put all table functions with "pragma autonomous_transaction; " and commit's before each pipe row (despite of i don't have any dml command inside any table function) but the error it remains. Any sugestion will be apreciated. hugo
  11. hugo.cardoso

    soundex function

    I get an algorithm named 'Levenshtein distance' and i hope to get the desired result with it Many thanks for all the information. Best regards Hugo
  12. hugo.cardoso

    soundex function

    Hi to all, I have the following problem: Given a column name (word or small text) I want to choose from a set of column names the most seemed (if it is not equal). I'm thinking to use 'soundex' function, but I do not know if I can use it (and how use it) as a measured of proximity (choose the nearest) in the case of the function return it is not exactly the same. Any ideias will be apreciated. Thanks Hugo well, sorry, i make some tests and it seems to me that the 'soundex' function it is not good for me (for example it is insensitive to the numbers), I think i need to construct my proper function, am i right? thanks I think that the solution for my problem is in UTL_MATCH package built-in. Can I obtain the instalation script and use it in oracle 9i database?
  13. hugo.cardoso

    ROWIDs vs backup

    Hi to all, Can i do some kind of databse backup without modifying rowid's of the records? If i do an export/import can i do something to preserve rowids? If i have columns with rowids of other records in a table the only solution is implement processes like a pre-export and a post-import to recreate this kind of work? Many thanks in advance, Hugo
  14. hugo.cardoso

    Tools to create flat file

    OK! sorry for my mistake, sql-loader is for moving data in contrary direction.
  15. hugo.cardoso

    Tools to create flat file

    Hi, I have a good experience using sql-loader for this purpose (easy to use and good performance ).