I have encountered this code... Is this Pl/Sql? What do you think it is? [Script 1.0] script package up is import native def_1; procedure p( i_g text ) is l_txt text; begin with mem_m(idx) as msg do with book_aud(evt_id) as book do book.upd_pkt( evt_nr => i__...
I have a function in a package that returns a REF CURSOR to a RECORD. I am trying to call this function from a code block. The calling code looks like this: declare a_record package_name.record_name; cursor c_symbols is select package_name.function_name('argument') from dual; begin open c_symbols; loop ...
can nvl() function be cascaded,...it was asked me in IBM interview .....and why???? ...
Are computer languages copyrighted or have some restrictions imposed on them how they can be used? What does that mean in practice? If so, what can be done or what cannot be done? Could I make a compiler or ide or anything for any of them? For example for Pl/Sql? ...
Hi Experts, I have a column called check_num (bank check number) as VARCHAR2 type in a payment_line table(Oracle). The requirement is "I have to search all those checks which numbers are greater than 12345. Please suggest how can I achieve this? ...
I need to add the current year as a variable in an SQL statement, how can I retrieve the current year using SQL? i.e. BETWEEN TO_DATE('01/01/**currentYear** 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AND TO_DATE('31/12/**currentYear** 23:59:59', 'DD/MM/YYYY HH24:MI:SS') ...
I am trying to call a stored procedure over a database link. The code looks something like this: declare symbol_cursor package_name.record_cursor; symbol_record package_name.record_name; begin symbol_cursor := package_name.function_name('argument'); loop fetch symbol_cursor into symbol_record; exit w...
Package is very very basic. Loops through a cursor, and updates 2 values where the record_ids are equal. What's an appropriate unit test for this sort of procedure? I'm going to add some skeleton code because the answers so far, while good, tie to the crux of my issue here: What do I test? PROCEDURE set_shift_times_to_null( RETVAL OUT...
I have some Packages in a Oracle database. They contain stored procedures, functions and constants. I can call functions, in Java, using a Java CallableStatement. Also, I could execute a SQL statement as "select package1.function1(value1) from dual;". But I can't find how to get the value of a constant declared in the package, in Java. ...
I have a DB table which consists of 2.5 billion records. There are duplicates to the tune of 11 million. Which is the fastest way to delete these 11 million records? ...
Hi all, I tried to create a pivot table created from a table in Oracle 10g. here is the table structure : CREATE TABLE KOMUNIKA.STOCK_AREA ( PRODUCT_CODE VARCHAR2(20 BYTE) NOT NULL, PRODUCT_NAME VARCHAR2(50 BYTE), AREA_CODE VARCHAR2(20 BYTE), AREA_NAME VARCHAR2(50 BYTE), QUANTITY NUMBER(20,2) )...
Is there any equivalent to %TYPE in MSSQL2005? CREATE TABLE TEST (ID NUMBER(5)); DECLARE myVar TEST.ID%TYPE; BEGIN ................ END; ...
I have a select query that fetches some values based on user input, for EG :- SELECT company, accounts from testdrive WHERE company LIKE '&Company', accounts = '&Account' order by company desc On running this in Toad, the user is prompted for values.. but I want to know how can the USER specify the "conditions" ALONG with the va...
hi everyone, in plsql, i saw some one use for loop without define the loop index, and the database can execute correctly. but i can't find the description of this syntax in oracle documentation. can anyone explain it? great thanks! the following code is an example, notice the *inner_c* is not defined: declare v_current_nr NUMBER; b...
In Oracle, is it possible to match both a particular string format and an empty string using a single regex? Something along the lines of: SELECT 1 FROM DUAL WHERE REGEXP_LIKE('12345', '^([1-5]{5})|NULL$'); SELECT 1 FROM DUAL WHERE REGEXP_LIKE('', '^([1-5]{5})|NULL$'); SELECT 1 FROM DUAL WHERE REGEXP_LIKE(NULL, '^([1-5]{5})|...
hi everyone, i use oracle demo schema scott to do some plsql test ( the data in that schema are never changed ). i wrote the following program to get the employee number of each department. the problem is, there is just 4 departments but my program output 5 row. i can't find out the reason, anyone can help? great thanks. declare cur...
Hi all I'm working on converting a stored procedure from SQL server to Oracle. This stored procedure provides a direct resultset. I mean that if you call the stored procedure in eg Management Studio you directly obtain the resultset. By converting to Oracle I walk against the problem that I in Oracle will not display the resultset ...
Hello Oracle Experts, I'm wondering why my "analytics" line inside a "cursor" in Oracle Forms generates an error. This is how it's written: CURSOR READ_C1_REC IS SELECT DISTINCT MACH, TCODE, COUNT(ALL TRANS_NO) OVER (PARTITION BY MACH, TCODE ORDER BY MACH, TCODE) TOTALPERMACHPERTCODE FROM ( SELECT .....
I have an pl\sql script where I want to set the table name used in the script to a variable. So, from some examples I found on the web, I wrote the code below. The first section works, so I think my general syntax is correct,but the second section, where I attempt to use a variable for a table name it errors ("SQL Error: ORA-00903: inv...
Are there are tangible benefits in changing the PLSQL_CODE_TYPE from interpreted to native? Was wondering if there are any case studies on the same. If benefit justifies the change, how can I go about implementing the same ? My platform is Windows 2003 server, running Oracle 10gR2 (10.2.0.1.0) if that helps. ...