Laurent Schneider

Hi Laurent,
I am not sure whether I should be asking this question to you but
I would like to know how can I buy your book in India?
Is there any Indian print planned by your Publisher?

Thanks.

Hello Laurent,

How are you doing?

I am just starting with databases so I think that your website is really good. Is there any better way to contact you?

I hope to hear from you.

Regards,

Ray Silva

Hi,

Can you give me a hint for this?

SQL> conn /as sysoper;
Connected.
SQL> conn /
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Thank you,
SIA

Hi Laurent,
How can I import data from an excel file into a table?
Any link in your site regarding this would be of great help.

Your link for spooling table values into excel was of great great help.

Hi Laurent,
I wonder if you can help me to solve a problem in sql. I need to be able to display the last (max) entry for each group in the column. Here is the setup code:
——————-
create table test_tb
(scale_code VARCHAR2(4));

insert into test_tb values (’B01′);
insert into test_tb values (’B02′);
insert into test_tb values (’B03′);
insert into test_tb values (’B04′);
insert into test_tb values (’B05′);
insert into test_tb values (’BP1′);
insert into test_tb values (’BP2′);
insert into test_tb values (’BP3′);
insert into test_tb values (’BR3′);
insert into test_tb values (’BU1′);
——————

So, I wonder if I can get:
B05,
BP3,
BR3,
BU1
using “select” in sql.

Thanks,
Eugene

Hi Laurent…

I met today your site.

Your articles are really very goods.

Your OCM certification is evidenced by the excellent quality of the material.

I hope one day to reach the OCM, still studying for this.

With your permission, I would like to add your site as one of the addresses listed on my blog.

Best regards!!!

Wagner Pinheiro

Hi Laurent,

I wud like to move to oracle DBA…I have just gone thru ur site…its really holding fabulous contents…Can I connect u thru mail ids mentioned above…

Best Rgds
Dushyant

Hi Laurent,
I am trying to come up with pivot query for our packaged app audit table.
Any change to any column is written as a new row. I came up with smaller scale example of it.
Here is the test data:
————————————–
create table ep_pivot
(sales_order_prefix varchar2(3),
sales_order_number number(10),
audit_timestamp date,
customer_purchase_order varchar2(25),
ship_address_id number(10),
ship_address_type_id varchar2(4)
);

Insert into ep_pivot
(BUSINESS_UNIT_ID, SALES_ORDER_PREFIX, SALES_ORDER_NUMBER, AUDIT_TIMESTAMP, SHIP_ADDRESS_ID,
SHIP_ADDRESS_TYPE_ID, CUSTOMER_PURCHASE_ORDER)
Values
(’81′, ‘J’, 64850, TO_DATE(’02/27/2001 08:19:12′, ‘MM/DD/YYYY HH24:MI:SS’), 13469,
‘BILL’, ‘3/23 AD BACKUP’);
Insert into ep_pivot
(BUSINESS_UNIT_ID, SALES_ORDER_PREFIX, SALES_ORDER_NUMBER, AUDIT_TIMESTAMP, SHIP_ADDRESS_ID,
SHIP_ADDRESS_TYPE_ID, CUSTOMER_PURCHASE_ORDER)
Values
(’81′, ‘J’, 64850, TO_DATE(’02/27/2001 08:19:39′, ‘MM/DD/YYYY HH24:MI:SS’), 13469,
‘BILL’, ‘645265/669601′);
Insert into ep_pivot
(BUSINESS_UNIT_ID, SALES_ORDER_PREFIX, SALES_ORDER_NUMBER, AUDIT_TIMESTAMP, SHIP_ADDRESS_ID,
SHIP_ADDRESS_TYPE_ID, CUSTOMER_PURCHASE_ORDER)
Values
(’81′, ‘J’, 64850, TO_DATE(’02/27/2001 08:20:38′, ‘MM/DD/YYYY HH24:MI:SS’), 13469,
‘BILL’, ‘645265/669601′);
Insert into ep_pivot
(BUSINESS_UNIT_ID, SALES_ORDER_PREFIX, SALES_ORDER_NUMBER, AUDIT_TIMESTAMP, SHIP_ADDRESS_ID,
SHIP_ADDRESS_TYPE_ID, CUSTOMER_PURCHASE_ORDER)
Values
(’81′, ‘J’, 64850, TO_DATE(’02/27/2001 08:20:53′, ‘MM/DD/YYYY HH24:MI:SS’), 13469,
‘BILL’, ‘645265/669601′);
Insert into ep_pivot
(BUSINESS_UNIT_ID, SALES_ORDER_PREFIX, SALES_ORDER_NUMBER, AUDIT_TIMESTAMP, SHIP_ADDRESS_ID,
SHIP_ADDRESS_TYPE_ID, CUSTOMER_PURCHASE_ORDER)
Values
(’81′, ‘J’, 64850, TO_DATE(’02/27/2001 08:21:18′, ‘MM/DD/YYYY HH24:MI:SS’), 13469,
‘BILL’, ‘645265′);
Insert into ep_pivot
(BUSINESS_UNIT_ID, SALES_ORDER_PREFIX, SALES_ORDER_NUMBER, AUDIT_TIMESTAMP, SHIP_ADDRESS_ID,
SHIP_ADDRESS_TYPE_ID, CUSTOMER_PURCHASE_ORDER)
Values
(’81′, ‘J’, 64850, TO_DATE(’03/14/2001 22:53:31′, ‘MM/DD/YYYY HH24:MI:SS’), 21321,
‘DC’, ‘645265′);
COMMIT;

Here is my query:
———————
Select business_unit_id, sales_order_prefix, sales_order_number, ts,
— PO
max( decode( rn, 1, customer_purchase_order, null )) original_cust_po
,max( decode( rn, 2, customer_purchase_order, null )) changed_cust_po
,max( decode( rn, 3, customer_purchase_order, null )) changed_cust_po
,max( decode( rn, 4, customer_purchase_order, null )) changed_cust_po
,max( decode( rn, 5, customer_purchase_order, null )) changed_cust_po
,max( decode( rn, 6, customer_purchase_order, null )) changed_cust_po
— Address Id
,max( decode( rn, 1, ship_address_id, null )) original_ship_address_id
,max( decode( rn, 2, ship_address_id, null )) changed_ship_address_id
,max( decode( rn, 3, ship_address_id, null )) changed_ship_address_id
,max( decode( rn, 4, ship_address_id, null )) changed_ship_address_id
,max( decode( rn, 5, ship_address_id, null )) changed_ship_address_id
,max( decode( rn, 6, ship_address_id, null )) changed_ship_address_id
— Address Type
,max( decode( rn, 1, ship_address_type_id, null )) original_ship_address_type_id
,max( decode( rn, 2, ship_address_type_id, null )) changed_ship_address_type_id
,max( decode( rn, 3, ship_address_type_id, null )) changed_ship_address_type_id
,max( decode( rn, 4, ship_address_type_id, null )) changed_ship_address_type_id
,max( decode( rn, 5, ship_address_type_id, null )) changed_ship_address_type_id
,max( decode( rn, 6, ship_address_type_id, null )) changed_ship_address_type_id
from (
select business_unit_id,sales_order_prefix,sales_order_number, audit_timestamp ts, customer_purchase_order,ship_address_id,ship_address_type_id,
row_number() over (partition by sales_order_prefix, sales_order_number order by audit_timestamp) rn
from ep_pivot
order by audit_timestamp
)
group by business_unit_id,sales_order_prefix, sales_order_number,ts,ship_address_id,ship_address_type_id

I wonder if it is possible (using sql) to show a value ONLY if it is changed. In my example “customer_purchase_order” didn’t change during 4 inserts.

Thanks,
Eugene

Hi Laurent,
Re. your very interesting entry on sftp, I have a similar brief at the moment, in that I’ve been tasked with altering a PL/SQL package to use sftp(or scp) rather than http://ftp.
Could you point me at an example of how this is achieved in PL/SQL?
All the example I can Google are ftp only.
Thanks in advance
James