Search Results

Search found 304 results on 13 pages for 'plsql'.

Page 7/13 | < Previous Page | 3 4 5 6 7 8 9 10 11 12 13  | Next Page >

• PL DOC source forge - 2 issues

  - by user1792793

  I'm attempting to use PLDOC source forge (http://pldoc.sourceforge.net/maven-site/) with my code to generate a neat page with comments of my liking. I'm coming across 2 issues, any help would be appreciated. 1 I've been using tags (/**, */) to make comments and this works perfectly for functions, but does not appear in procedures. My Functions and procedures are independent and not in packages, and trying to add comments before the PROCEDURE declaration just gets deleted when saved. 2 if i try to use the recommended method of getting the data directly from teh database (call pldoc.bat -url jdbc:oracle:thin:@localhost:1521:ORCL -user SCOTT -password TIGER -sql SYS_OWNER.DBMS_PIPE,SYS_OWNER.DBMS_OUTPUT), it puts all the available functions and procedures under the user SIS_OWNER (SIS_owner is the only link available on the left hand side). I want to change this so that I can view all the methods in the list instead. Problem with procedures stated in 1 still exists with this method. Please let me know if you have overcome this and any pointers would be great. Thanks

  Read the article

• using FUNCTION instead of CREATE FUNCTION oracle pl/sql

  - by sqlgrasshopper5

  I see people writing a function with FUNCTION instead "CREATE FUNCTION". When I saw this usage in the web I thought it was a typo or something. But in Oreilly's "Oracle 11g PL/SQL Programming" by Steven Feurenstein, the author had used the same thing. But I get errors when I execute that. Could somebody explain is it legal usage or not?. Thanks.

  Read the article

• How can I check a type's dependents order to drop them and replace/modify the initial type?

  - by pctroll

  I tried to modify a type using the following code and it gave me the error code: 'ORA-02303'. I don't know much about Oracle or PL/SQL but I need to solve this; so I'd appreciate any further help with this. Thanks in advance. The code is just an example. But then again, I need to check its dependents first. create or replace type A as object ( x_ number, y_ varchar2(10), member procedure to_upper ); /

  Read the article

• Oracle : Identifying duplicates in a table without index

  - by user288031

  When I try to create a unique index on a large table, I get a unique contraint error. The unique index in this case is a composite key of 4 columns. Is there an efficient way to identify the duplicates other than : select col1, col2, col3, col4, count() from Table1 group by col1, col2, col3, col4 having count() 1 The explain plan above shows full table scan with extremely high cost, and just want to find if there is another way. Thanks !

  Read the article

• ORACLE: can we create global temp tables or any tables in stored proc?

  - by mrp

  Hi, below is the stored proc I wrote: create or replace procedure test005 as begin CREATE GLOBAL TEMPORARY TABLE TEMP_TRAN ( COL1 NUMBER(9), COL2 VARCHAR2(30), COL3 DATE ) ON COMMIT PRESERVE ROWS / INSERT INTO TEMP_TRAN VALUES(1,'D',sysdate); INSERT INTO TEMP_TRAN VALUES(2,'I',sysdate); INSERT INTO TEMP_TRAN VALUES(3,'s',sysdate); COMMIT; end; when i executed it , i get an error message mentioning: create or replace procedure test005 as begin CREATE GLOBAL TEMPORARY TABLE TEMP_TRAN ( COL1 NUMBER(9), COL2 VARCHAR2(30), COL3 DATE ) ON COMMIT PRESERVE ROWS / INSERT INTO TEMP_TRAN VALUES(1,'D',sysdate); INSERT INTO TEMP_TRAN VALUES(2,'I',sysdate); INSERT INTO TEMP_TRAN VALUES(3,'s',sysdate); COMMIT; end; Error at line 1 ORA-00955: name is already used by an existing object Script Terminated on line 1. I tried to drop the TEMP_TRAN and it says table doesn't exist. So there is no TEMP_TRAN table existed in system. why am I getting this error? I am using TOAD to create this stored proc. Any help would be highly appreciated.

  Read the article

• Writing a query to find MAX number in PL/SQL

  - by user2461116

  I am suppose to Write a query that will display the largest number of movies rented by one member and that member's name. Give the output column a meaningful name such as MAXIMUM NUMBER. This is what I have. select max(maximum_movies) from (select count(*)maximum_movies from mm_member join mm_rental on mm_rental.member_id = mm_member.member_id group by first, last); I got the maximum number but the output should be like this. First Last Maximum_movies John Doe 4 But the output is Maximum_movies 4 Any suggestions?

  Read the article

• How to (unit-)test data intensive PL/SQL application

  - by doom2.wad

  Our team is willing to unit-test a new code written under a running project extending an existing huge Oracle system. The system is written solely in PL/SQL, consists of thousands of tables, hundreds of stored procedures packages, mostly getting data from tables and/or inserting/updating other data. Our extension is not an exception. Most functions return data from a quite complex SELECT statementa over many mutually bound tables (with a little added logic before returning them) or make transformation from one complicated data structure to another (complicated in another way). What is the best approach to unit-test such code? There are no unit tests for existing code base. To make things worse, only packages, triggers and views are source-controlled, table structures (including "alter table" stuff and necessary data transformations are deployed via channel other than version control). There is no way to change this within our project's scope. Maintaining testing data set seems to be impossible since there is new code deployed to the production environment on weekly basis, usually without prior notice, often changing data structure (add a column here, remove one there). I'd be glad for any suggestion or reference to help us. Some team members tend to be tired by figuring out how to even start for our experience with unit-testing does not cover PL/SQL data intensive legacy systems (only those "from-the-book" greenfield Java projects).

  Read the article

• Table transformation / field parsing in PL/SQL

  - by IMHO

  I have de-normalized table, something like CODES ID | VALUE 10 | A,B,C 11 | A,B 12 | A,B,C,D,E,F 13 | R,T,D,W,W,W,W,W,S,S The job is to convert is where each token from VALUE will generate new record. Example: CODES_TRANS ID | VALUE_TRANS 10 | A 10 | B 10 | C 11 | A 11 | B What is the best way to do it in PL/SQL without usage of custom pl/sql packages, ideally with pure SQL? Obvious solution is to implement it via cursors. Any ideas?

  Read the article

• pl/sql does not work with %rowtype

  - by Manolo

  I want to do a simple PL/SQL program on the Oracle 10g internet environment. The program is: DECLARE stud_rec students%ROWTYPE; last_name VARCHAR2:='Clinton'; BEGIN SELECT * INTO stud_rec FROM students WHERE student_id=100; END; I have a table called students with data inside of it. The issue is that when I want to run this in the SQL command window I got this message: ORA-06550: line 3, column 11: PLS-00215: String length constraints must be in range (1 .. 32767) I have checked the syntax and I cannot find the error. Any help? Thanks in advance

  Read the article

• Compare values in each column of two Oracle Types

  - by colinjameswebb

  I've been playing around with the pluto-test-framework today, and I'd like to get some existing functions into a test harness with it. I have lots of functions with this type of specification. FUNCTION DO_SOME_STUFF (pOldSchedule IN SCHEDULE_OBJ, pNewSchedule OUT SCHEDULE_OBJ, pLoggerContext IN OUT LOGGER_CONTEXT_OBJ) RETURN NUMBER; It takes pOldSchedule, does some stuff to it, and then returns pNewSchedule. The logger_context just does logging. As part of a test, I'd like to be able to compare the values in each of the columns of the type, without having to write individual IF statements. It'll need to return boolean to signify whether or not pOldSchedule and pNewSchedule match. Any ideas?

  Read the article

• pipelined function

  - by user289429

  Can someone provide an example of how to use parallel table function in oracle pl/sql. We need to run massive queries for 15 years and combine the result. SELECT * FROM Table(TableFunction(cursor(SELECT * FROM year_table))) ...is what we want effectively. The innermost select will give all the years, and the table function will take each year and run massive query and returns a collection. The problem we have is that all years are being fed to one table function itself, we would rather prefer the table function being called in parallel for each of the year. We tried all sort of partitioning by hash and range and it didn't help. Also, can we drop the keyword PIPELINED from the function declaration? because we are not performing any transformation and just need the aggregate of the resultset.

  Read the article

• can oracle types be updated like tables?

  - by Omnipresent

  I am converting GTT's to oracle types as explained in an excellent answer by APC. however, some GTT's are being updated based on a select query from another table. For example: UPDATE my_gtt_1 c SET (street, city, STATE, zip) = (SELECT src.unit_address, src.unit_city, src.unit_state, src.unit_zip_code FROM (SELECT mbr.ROWID row_id, unit_address, RTRIM(a.unit_city) unit_city, RTRIM(a.unit_state) unit_state, RTRIM(a.unit_zip_code) unit_zip_code FROM table_1 b, table_2 a, my_gtt_1 mbr WHERE type = 'ABC' AND id = b.ssn_head AND a.h_id = b.h_id AND row_id >= v_start_row AND row_id <= v_end_row) src WHERE c.ROWID = src.row_id) WHERE state IS NULL OR state = ' '; if my_gtt_1 was not a global temporary table but an oracle collection type then is it possible to do updates this complex? Or in these cases we are better off using the global temporary table?

  Read the article

• comma separated values in oracle function body

  - by dmitry

  I've got following oracle function but it does not work and errors out. I used Ask Tom's way to convert comma separated values to be used in select * from table1 where col1 in <> declared in package header: TYPE myTableType IS table of varchar2 (255); Part of package body: l_string long default iv_value_with_comma_separated|| ','; l_data myTableType := myTableType(); n NUMBER; begin begin LOOP EXIT when l_string is null; n := instr( l_string, ',' ); l_data.extend; l_data(l_data.count) := ltrim( rtrim( substr( l_string, 1, n-1 ) ) ); l_string := substr( l_string, n+1 ); END LOOP; end; OPEN my_cursor FOR select * from table_a where column_a in (select * from table (l_data)); CLOSE my_cursor END; above fails but it works fine when I remove select * from table (l_data) Can someone please tell me what I might be doing wrong here??

  Read the article

• Is it possible to pass an array to a PL/SQL procedure?

  - by Kel

  If it is possible, how does the parameter need to look like in the procedure? And how do you pass an array to a procedure?

  Read the article

• How do I return the rows from an Oracle Stored Procedure using SELECT?

  - by Calanus

  I have a stored procedure which returns a ref cursor as follows: CREATE OR REPLACE PROCEDURE AIRS.GET_LAB_REPORT (ReportCurTyp OUT sys_refcursor) AS v_report_cursor sys_refcursor; report_record v_lab_report%ROWTYPE; l_sql VARCHAR2 (2000); BEGIN l_sql := 'SELECT * FROM V_LAB_REPORT'; OPEN v_report_cursor FOR l_sql; LOOP FETCH v_report_cursor INTO report_record; EXIT WHEN v_report_cursor%NOTFOUND; END LOOP; CLOSE v_report_cursor; END; I want to use the output from this stored procedure in another select statement like: SELECT * FROM GET_LAB_REPORT() but I can't seem to get my head around the syntax. Any ideas?

  Read the article

• Oracle aggregate function to return a random value for a group?

  - by tpdi

  The standard SQL aggregate function max() will return the highest value in a group; min() will return the lowest. Is there an aggregate function in Oracle to return a random value from a group? Or some technique to achieve this? E.g., given the table foo: group_id value 1 1 1 5 1 9 2 2 2 4 2 8 The SQL query select group_id, max(value), min(value), some_aggregate_random_func(value) from foo group by group_id; might produce: group_id max(value), min(value), some_aggregate_random_func(value) 1 9 1 1 2 8 2 4 with, obviously, the last column being any random value in that group.

  Read the article

• Best way to use PL/SQL Pacakge Cursors from Pro*C

  - by Greg Reynolds

  I have a cursor defined in PL/SQL, and I am wondering what the best way to use it from Pro*C is. Normally for a cursor defined in Pro*C you would do: EXEC SQL DECLARE curs CURSOR FOR SELECT 1 FROM DUAL; EXEC SQL OPEN curs; EXEC SQL FETCH curs INTO :foo; EXEC SQL CLOSE cusr; I was hoping that the same (or similar) syntax would work for a packaged cursor. For example, I have a package MyPack, with a declaration type MyType is record (X integer); cursor MyCurs(x in integer) return MyType; Now I have in my Pro*C code a rather unsatisfying piece of embedded PL/SQL that opens the cursor, does the fetching etc., as I couldn't get the first style of syntax to work. Using the example EXEC SQL EXECUTE DECLARE XTable is table of MyPack.MyType; BEGIN OPEN MyPack.MyCurs(:param); FETCH MyPack.MyCurs INTO XTable; CLOSE MyPack.MyCurs; END; END-EXEC; Does anyone know if there is a more "Pure" Pro*C approach?

  Read the article

• How to use DML on Oracle temporary table without generating much undo log

  - by Sambath

  Hi, Using an Oracle temporary table does not generate much redo log as a normal table. However, the undo log is still generated. Thus, how can I write insert, update, or delete statement on a temporary table but Oracle will not generate undo log or generate as little as possible? Moreover, using /+append/ in the insert statement will generate little undo log. Am I correct? If not, could anyone explain me about using the hint /+append/? INSERT /*+APPEND*/ INTO table1(...) VALUES(...); Thank you.

  Read the article

• Oracle (Old?) Joins

  - by Grasper

  I have been porting oracle selects, and I have been running across a lot of queries like so: SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id; ...and: SELECT last_name, d.department_id FROM employees e, departments d WHERE e.department_id = d.department_id(+); Are there any guides/tutorials for converting all of the variants of the (+) syntax? What is that syntax even called (so I can scour google)? When was this standard phased out? Any info is appreciated.

  Read the article

• fetch only first row from oracle - is it faster?

  - by john

  My main goal with this question is optimization and faster run time. After doing lot of processing in the Stored Proc I finally return a count like below: OPEN cv_1 FOR SELECT COUNT(*) num_of_members FROM HOUSEHOLD_MEMBER a, HOUSEHOLD b WHERE RTRIM(LTRIM(a.mbr_last_name)) LIKE v_MBR_LAST_NAME || '%' AND a.number = '01' AND a.code = v_CODE AND a.ssn_head = v_SSN_HEAD AND TO_CHAR( a.mbr_dob, 'MM/DD/YYYY') = v_DOB; But in my code that is calling the SP does not need the actual count. It just cares that count is greater than 1. Question: How can I change this to return just 1 or 0. 1 when count is 0 and 0 when count 1. Will it be faster to do this rather than returning the whole count?

  Read the article

• Have PL/SQL Outputs in Real Time.

  - by martilyo

  Is it possible to have Outputs from PL/SQL in real time? I have a pretty huge package that runs for more than an hour and I'd like to see where the package is at a particular time. Anyways, I currently do this with a log table, which gets filled up with hundreds of log descriptions per run, I'm just curious if this is possible. Thanks!

  Read the article

• confusion about using types instead of gtts in oracle

  - by Omnipresent

  I am trying to convert queries like below to types so that I won't have to use GTT: insert into my_gtt_table_1 (house, lname, fname, MI, fullname, dob) (select house, lname, fname, MI, fullname, dob from (select 'REG' house, mbr_last_name lname, mbr_first_name fname, mbr_mi MI, mbr_first_name || mbr_mi || mbr_last_name fullname, mbr_dob dob from table_1 a, table_b where a.head = b.head and mbr_number = '01' and mbr_last_name = v_last_name) c above is just a sample but complex queries are bigger than this. the above is inside a stored procedure. So to avoid the gtt (my_gtt_table_1). I did the following: create or replace type lname_row as object ( house varchar2(30) lname varchar2(30), fname varchar2(30), MI char(1), fullname VARCHAR2(63), dob DATE ) create or replace type lname_exact as table of lname_row Now in the SP: type lname_exact is table of <what_table_should_i_put_here>%rowtype; tab_a_recs lname_exact; In the above I am not sure what table to put as my query has nested subqueries. query in the SP: (I am trying this for sample purpose to see if it works) select lname_row('', '', '', '', '', '', sysdate) bulk collect into tab_a_recs from table_1; I am getting errors like : ORA-00913: too many values I am really confused and stuck with this :(

  Read the article

• Insert or Update using Oracle and PL/SQL

  - by Shane

  I have a PL/SQL function that performs an update/insert on an Oracle database that maintains a target total and returns the difference between the existing value and the new value. Here is the code I have so far: FUNCTION calcTargetTotal(accountId varchar2, newTotal numeric ) RETURN number is oldTotal numeric(20,6); difference numeric(20,6); begin difference := 0; begin select value into oldTotal from target_total WHERE account_id = accountId for update of value; if (oldTotal != newTotal) then update target_total set value = newTotal WHERE account_id = accountId difference := newTotal - oldTotal; end if; exception when NO_DATA_FOUND then begin difference := newTotal; insert into target_total ( account_id, value ) values ( accountId, newTotal ); -- sometimes a race condition occurs and this stmt fails -- in those cases try to update again exception when DUP_VAL_ON_INDEX then begin difference := 0; select value into oldTotal from target_total WHERE account_id = accountId for update of value; if (oldTotal != newTotal) then update target_total set value = newTotal WHERE account_id = accountId difference := newTotal - oldTotal; end if; end; end; end; return difference end calcTargetTotal; This works as expected in unit tests with multiple threads never failing. However when loaded on a live system we have seen this fail with a stack trace looking like this: ORA-01403: no data found ORA-00001: unique constraint () violated ORA-01403: no data found The line numbers (which I have removed since they are meaningless out of context) verify that the first update fails due to no data, the insert fail due to uniqueness, and the 2nd update is failing with no data, which should be impossible. From what I have read on other thread a MERGE statement is also not atomic and could suffer similar problems. Does anyone have any ideas how to prevent this from occurring?

  Read the article

• Run Oracle Procedure just to lock row, without returning a resultset

  - by Pascal

  I want to run a procedure to force a row lock on a row, but I don't want to return a result set to the client, nor do I actually want to update anything. Below is the proc: CREATE OR REPLACE PROCEDURE SP_LOCK_Row (IDRow IN INTEGER) IS BEGIN SELECT * FROM TBLTable WHERE IDRow = IDRow FOR UPDATE; END; The problem is that I keep getting the error: PLS-00428: an INTO clause is expected in this SELECT statement. Is there a way for me to lock the row without actually having to return a result set back to the client? The SQL Server equivalent is: CREATE PROCEDURE dbo.SP_LOCK_Row( @IDRow INT) AS SELECT * FROM dbo.TBLTable WITH (UPDLOCK, ROWLOCK) WHERE IDRow = @IDRow Tks

  Read the article

• Getting user data from Active Directory using PL/SQL

  - by David Neale

  I had a discussion today regarding an Oracle procedure I wrote some time ago. I wanted to get 7500 user email addresses from Active Directory using PL/SQL. AD will return a maximum of 1000 rows and the LDAP provider used by Oracle will not support paging. Therefore, my solution was to filter on the last two characters of the sAMAccountName (*00,*01,*02...etc.). This results in 126 queries (100 for account names ending in digits, 26 for those ending in a letter...this was sufficient for my AD setup). The person I was speaking to (it was a job interview by the way) said he could have done it a better way, but he would not tell me what that method was. Could anybody hazard a guess at what this method was?

  Read the article

< Previous Page | 3 4 5 6 7 8 9 10 11 12 13  | Next Page >