plsqldeveloper

oracle pl/sql bug: can't put_line more than 2000 characters

Has anyone else noticed this phenomenon where dbms_output.put_line is unable to print more than 2000 characters at a time? Script is: set serveroutput on size 100000; declare big_str varchar2(2009); begin for i in 1..2009 loop big_str := big_str||'x'; end loop; dbms_output.put_line(length(big_str)); dbms_o...

How to connect to remote Oracle DB with PL/SQL Developer?

I have a database "TEST", to which I connect at address 123.45.67.89:1521. How do I connect to it using PL/SQL Developer? ...

How to test an Oracle function that contains DML in PLSQL Developer?

Selecting the return value of an Oracle stored function that doesn't contain DML can be done by simply selecting the function: select function_name() from dual; If the function contains DML (in this case some inserts to log the arguments passed to the function), the above query is not allowed. (ORA-14551) How can I select/view the re...

Looping thorugh nested tables in oracle and returning a list of objects to java

I have this table created as follow: CREATE TABLE FORNECPRODS ( SUPPLIER FORNEC_OBJ , PRODUCTS PRODTABLE )NESTED TABLE "PRODUCTS" STORE AS "PRODUCTSTABLE"; / create or replace type PRODTABLE as table of PROD_OBJ; create or replace TYPE PROD_OBJ AS OBJECT ( ID_PROD ...

Update attribute of an element in a nested table

I have created this type: create or replace type PRODTABLE as table of PROD_OBJ; and I use that PRODTABLE in the follow PLSQL code: FUNCTION INSERT_PRODUCTS ( a_supplier_id IN FORNECEDOR.ID_FORNECEDOR%TYPE, a_prodArray IN PRODTABLE ) RETURN NUMBER IS v_error_code NUMBER; v_error_mess...

Customize PL/SQL exceptions in Oracle

Frequently I found myself doing some functions to insert/delete/update in one or more tables and I've seen some expected exceptions been taken care of, like no_data_found, dupl_val_on_index, etc. For an insert like this: create or replace FUNCTION "INSERT_PRODUCTS" ( a_supplier_id IN FORNECEDOR.ID_FORNECEDOR%TYPE, a_prodA...

oracle trigger after inserting or udpating a sales item

Hi there, I have this table that represents a weak entity and is a typical table for introducing items ordered: insert into ITEM_FORNECIMENTO values (a_orderId,a_prodId,a_prodQtd, a_buyPrice); I want my trigger to update the last column (the total price of products WITHOUT iva) to do this : totalPrice= totalPrice*(1+(iva/100), each t...

scope of raise exception, handling your own exceptions in PLSQL code

I have this procedure: create or replace PROCEDURE CONVERTE IS CURSOR oldemployees IS SELECT * FROM emp1 WHERE data_saida= NULL; new_ndep emp1.num_dep%type; bi_inexistente EXCEPTION; dep_inexistente EXCEPTION; employeeNr emp1.num_empregado%type; BEGIN FOR old_emp IN oldemployees LOO...

if-else, case or decode inside of a ref cursor

Hi there, I have this big code where I want 3 things in my search: 1- look for all the orders (delivered and not) that match the search: 2- look for all the pendent orders that match the search; 3- look for all the delivered orders that match the search; create or replace function search_order(search IN VARCHAR2, a_option NUMBER) R...

How to select results between two dates or by single year/month/day in oracle? (to_date, extract,sysdate)

I have a table for orders and it has 2 column with DATE types: delivery_date and order_date. In my sqldeveloper, all the dates I've inserted via Java or by hand are in the format 10.01.25 for the 25th Jan of this year. When I try to see the the total_price for orders between one date and another, I force the format like this: create...

regist payment for each month of the year using oracle data

I have the following tables: Employees with level (rank or wtv you may call it): income_value,id, etc. INCOME that stores the rank vs. the income value (very tinny and static) REGIST_INCOME table with the following columns: ID_REG; ID_EMPLOYEE; MONTH_and_Year_OF_PAYMENT DATE (I want to format to yy.mm); DATE_OF_PAYMENT DATE ...

to_date function pl/sql

undefine dates declare v_dateInput VARCHAR(10); v_dates DATE; begin v_dateInput := &&dates; v_dates := to_date(v_dateInput,'dd-mm-yyyy'); DBMS_OUTPUT.put_line(v_dates); end; Not sure why whenever I run this code with ,for example , input of 03-03-1990, this error shows up. Error report: ORA-01847: day of month must be betwe...

Boolean data type size and want to print its value?

I want to know size of data type boolean , i used VSIZE() function but it is not working for boolean and Want to print and store boolean value into table. Please let me know how oracle store boolean value ,is there any other way to see data type and value for boolean variable. Atleast tell me size of boolean i got this error when I ...

Concatenation with Zero is not occurring properly in code?

I was trying to reverse a number in PL/SQL. It's working fine, but when my number contains any 0, the output is unexpected. For example: 1234 output 4321 1000 output 1 1203 ouput 3021 10001 output 1 DECLARE r number(9); num number(9):=&p_num; BEGIN WHILE num>=1 LOOP IF mod(num,10)=0 THEN -- extracting last digit of a nu...

DOT(.) after Ampersand (host variable)& considering as a part of variable name?

Hi ALL, I know heading is not so clear.This is the picture I am using &a it is considering it as $a. ,in the output &a and &a. is giving the same output. and why single .(DOT) after &a is giving no error but if we put any character, operator or wild characters after &a gives error. This is the code. BEGIN FOr i in &&a...3 LOOP ...

Upper Bound in FOR loop does not get altered in loop,Why?

Hi ALL, I am trying to change the value of upper bound in For loop ,but the Loop is running till the upper bound which was defined in the starting. According to logic loop should go infinite, since value of v_num is always one ahead of i,But loop is executing three time.Please explain This is the code DECLARE v_num number:=3; ...

Trying to drop all tables from my schema with no rows?

I am trying to drop all tables in schema with no rows,but when i am executing this code i am getting an error THis is the code: create or replace procedure tester IS v_count NUMBER; CURSOR emp_cur IS select table_name from user_tables; BEGIN FOR emp_rec_cur IN emp_cur LOOP ...

Simple Oracle Stored Procedure getting Invalid object error

Hello, I am trying to write a simple Oracle Stored Procedure: CREATE OR REPLACE PROCEDURE act.skeleton IS DECLARE v_rowCount NUMBER; BEGIN SELECT COUNT(1) INTO v_rowCount FROM ex.emp; DBMS_OUTPUT.PUT_LINE(v_rowCount); END; However, when I try & run the procedure by issuing execute act.skeleton in PL/SQL Developer command window...

Invoking a function call in a string in an Oracle Procedure

Hello, I writing an application using Oracle 10g. I am currently facing this problem. I take in "filename" as parameter of type varchar2. A sample value that filename may contain is: 'TEST || to_char(sysdate, 'DDD')'. In the procedure, I want to get the value of this file name as in TEST147. When i write: select filename into ffilena...

sql trigger error:invalid specification

Hi everyone, i am creating a trigger and receiving some error, which i m not able to understand. Pls can anyone help me with that. create or REPLACE TRIGGER trig_data BEFORE INSERT ON data_db REFERENCING OLD AS OLD AND NEW AS NEW FOR EACH ROW BEGIN SELECT RAHUL_SEQUENCE.NEXTVAL INTO :NEW.USERID FROM DUAL; END; E...