Hi All, I have a database process written in PL/SQL that i would like to test using DBUNIT. The pl/sql program processes data from one table and generates new data into a new table. In some cases it also updates fields on the original table. I am a bit confused in how i can use dbunit to test this. Reading up on it, it looks like i h...
Originally, the model (including tables, views, packages, etc.) was built from the DML scripts (generated by TOAD). The scripts look like: DROP TABLE TABLESPACE.TABLENAME CASCADE CONSTRAINTS; CREATE TABLE TABLESPACE.TABLENAME ... Over time the model has changed - I've added new columns to the tables, altered some vews, added new metho...
Can i use insert into tables in a procedure (on oracle) ? example: procedure my_procedure (aa1 number ,aa2 number ) is begin insert into lam_table values(aa1,aa2,null) ;(*ofcourse depending on the tables ) ... ... end ; ** note i tried it and it worked but there were a message in the bottom that said (successfully compiled n...
I have the following small function that does not compile: function f_query_01 Return interval Day to second is start_time timestamp(3); end_time timestamp(3); time_diff interval Day to second; c_query_number number; begin start_time := systimestamp; select count(*) into c_query_number from wg; <--This is the line th...
I use DB2 9.7 for Linux. The stored procedure is implemented in PL/SQL (Oracle's programming language), so, the record set is an output parameter (SYS_REFCURSOR). CREATE OR REPLACE PROCEDURE TEST_CURSOR ( CV_1 OUT SYS_REFCURSOR ) IS BEGIN OPEN CV_1 FOR SELECT 1 COLUMN FROM DUAL; END TEST_CURSOR; I don't know how to dec...
I need to retrieve data from an oracle table using a pl/sql stored procedure and odp.net. What is the standard way of doing this? ...
I've seen magical Vim commands before that you could add to your .vimrc to have folds created upon opening a particular type of file. I remember having such code that would create the folds, upon opening the file, at every Ruby method and class. Then, with one command, I could collapse all those method folds. Does anyone know how to d...
I am working on a rather large SQL script to be used with Oracle, but I'm running into an issue. First, let me outline how the script operates. Declare variables`CUSTOMERID NUMBER;``SERVERID NUMBER;` Create a customer if it doesn't exist `SELECT ID INTO CUSTOMERID FROM CUSTOMER WHERE NAME = 'The Customer I just Inserted';` Create the s...
Hello, is it used in the correct form in PL/SQL ? SELECT ename ,hiredate , TO_CHAR(TRUNC(ADD_MONTHS(hiredate,6),'MONTH'),'Le DD MONTH YYYY') AS "Révision" FROM emp thx ...
I use PHP and Oracle, with crontab executing the PHP scripts at scheduled times. My current logging/auditing solution involves simple log files. I'd like to save my cron execution logs to a database instead. Right now I'm trying to design the process so that when a cron job starts I create a record in an CronExecution table. Then eve...
I am going through some pl/sql code with no comments. Trying to make sense of it and optimizing it. Here is the sample: INSERT INTO gtt1 --75711 rows (USER_ID, role_id, participant_code, status_id ) SELECT r.user_id, r.role_id, r.participant_code, MAX(status_id) FROM user_role r, cmp_role c WHERE r.role_id = c.role_id AND r.pa...
I have a package that will be inserting a single parent record in one table, and dependent child records in another table with a FK on the parent_id. Each of these functions will be called by an external program, and all calls to insert children may not be contained within the same transaction. I'm wondering if there is any way for me ...
Hi, I'm Dinesh from Bhiwani, and I want to know about error handling in PL/SQL. Can anyone help me to find brief description on this topic? ...
* EDIT6: * This is what ended up working for me (from accepted answer): var ret1 number var tran_cnt number var msg_cnt number var rc refcursor exec :tran_cnt := 0 exec :msg_cnt := 123 exec get_account(Vret_val => :ret1, Vtran_count => :tran_cnt, Vmessage_count => :msg_cnt, Vaccount_id => 1, rc1 => :rc) print :tran_cnt print :msg_cnt pr...
Hi, I have a column in a database table that contains several urls and I was wondering what is the best way to get these urls from the database table into a javascript function. Example code of how to approach this would be much appreciated. Thanks. ...
We need to migrate our database from MSSQL to Oracle and we have over 100 stored procedures written in PL/SQL TSQL. I know its a long shot, but has anybody ever successfully used an automatic migration tool to do the work? I've seen some tools on the web, but I have no way to judge their ability to handle various code. Any personal exper...
How can I make a package that returns results in table format when passed in csv values. select * from table(schema.mypackage.myfunction('one, two, three')) should return one two three I tried something from ask tom but that only works with sql types. I am using oracle 11g. Is there something built-in? ...
I am trying to update a table, but I can't get my syntax to work. I added new elements from a temp table to my element table and I want to update column_name in metadata_attribute and table_name in metadata_table table. This is what I have: UPDATE METADATA_ATTRIBUTE SET C.ELEMENT_ID = (SELECT ELEMENT_ID FROM ...
I have a select query on a relational table in a plsql procedure. I want to convert the results of this query into a user defined type object to return via odp.net. How would I go about doing this? ...
I need to consume a file that is being generated on an Oracle server and I have been told to get it using the UTL_FILE package. I intend on consuming the file in a groovy script and have a connection to the database. It is a simple text file and I'd like to pull it down to the server and consume it as I would any other text file but I ...