Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sqlloader error

chandra_1986May 19 2021

HI Experts,

We are getting below error:-. we need to modify the script so that,
If the script has ora error then it should load the same .csv file again and then come out of the script

SQL*Loader: Release 12.1.0.2.0 - Production on Wed May 19 09:35:04 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Control File: las_agt2.ctl
Data File: /opt/app/t1c32/ora01/t1c32/dba/agt2.csv
Bad File: ag3.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 500
Continuation: none specified
Path used: Direct

Table report.agt_prod_stag, loaded from every logical record.
Insert option in effect for this table: TRUNCATE TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * , O(") CHARACTER
EVENTID NEXT 4000 , O(") CHARACTER
EVENTNAME NEXT 4000 , O(") CHARACTER
TIMESTAMP NEXT * , O(") DATETIME YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"
EVENTMETHOD NEXT 4000 , O(") CHARACTER
RESOURCENAME NEXT 4000 , O(") CHARACTER
TARGETTYPE NEXT 4000 , O(") CHARACTER
OPERATIONNAME NEXT 4000 , O(") CHARACTER
FUNCTIONSTATUS NEXT 4000 , O(") CHARACTER
PAGEID NEXT 4000 , O(") CHARACTER
BN NEXT 4000 , O(") CHARACTER
JOBID NEXT 4000 , O(") CHARACTER
RID NEXT 4000 , O(") CHARACTER
JOBTYPE NEXT 4000 , O(") CHARACTER
UID NEXT 4000 , O(") CHARACTER
TECHREGION NEXT 4000 , O(") CHARACTER
MGMTREPORTINGFUNCTION NEXT 4000 , O(") CHARACTER
RECORDPUBLISHINDICATOR NEXT 4000 , O(") CHARACTER
VERSION NEXT 4000 , O(") CHARACTER

SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCATE keyword) for table report.agt_prod_stag
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Comments

Regarding writing to Oracle, start by reading the https://oracle.github.io/node-oracledb/doc/api.html
Consuming "a web service" can be done with the http module which is part of Node.js. Check out the manual and doc like https://nodejs.org/en/knowledge/HTTP/clients/how-to-create-a-HTTP-request/

Billy Verreynne

Why nodejs and not use only PL/SQL? Far fewer moving parts.

Arif2018

i am not sure on how pl/sql works , Appreciate if you can post some links. All i want is to read the url and insert data into Oracle table.

Billy Verreynne

Oracle has the UTL_HTTP package API and the HttpUriType advance datatype (oo class).
Sample code provided in manual and you can also look at https://community.oracle.com/tech/developers/discussion/comment/10450611/#Comment_10450611 and as for the oo class:

SQL> select httpuritype( 'http://google.com' ).getClob() as "HTML" from dual;

HTML
-------------------------------------------------------------------------------
<!doctype html><html itemscope="" itemtype="http://schema.org/WebPage" lang="en
-ZA"><head><meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
..snipped..

In PL/SQL use:

clobVariable := new HttpUriType(urlVariable).getClob();
1 - 4

Post Details

Added on May 19 2021
6 comments
200 views