Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
PL SQL equivalent for mentioned VB code

Hi,
I have converted a part of the below code from VB to SQL logic, could I get some more help on the rest. Could I please get the conversion for below :-
If InStr(str1, ",") = 0 Then
k = Len(LTrim(RTrim(str1)))
If (k < 4) Then
' space for other chars
ln = "INSERT INTO ATION (ATION,CODES) VALUES('" & rSt1(1) & "', '" & Left(str1, 4) & Space(4 - k) & ", , , ,')"
Else
' take only 4chars
ln = "INSERT INTO ATION (ATION,CODES) VALUES('" & rSt1(1) & "', '" & Left(str1, 4) & ", , , ,')"
End If
Converted part :-
If InStr(V_CODES, ',') = 0 Then
k := LENGTH(LTrim(RTrim(V_CODES)));
If (k < 4) Then
--space for other chars
INSERT INTO ATION (ATION,CODES) VALUES(V_ATION, SUBSTR(V_CODES, 4) || RPAD(4 - k) & ", , , ,')
Else
' take only 4chars
INSERT INTO ATION (ATION,CODES) VALUES(V_ATION, 'Left(str1, 4) & ", , , ,')"
End If
Thanks, I just want to be sure about the code. Help & advice appreciated
Answers
-
Try [not tested]
>> INSERT INTO ATION (ATION,CODES) VALUES(V_ATION, SUBSTR(V_CODES, 4) || RPAD(4 - k) & ", , , ,')
to
INSERT INTO ATION (ATION,CODES) VALUES(V_ATION, SUBSTR(V_CODES, 4) || RPAD(4 - k) || ', , , ,')
>> INSERT INTO ATION (ATION,CODES) VALUES(V_ATION, Left(str1, 4) & ", , , ,')
to
INSERT INTO ATION (ATION,CODES) VALUES(V_ATION, RPAD(str1, 4) || ', , , ,')
-
LENGTH(LTrim(RTrim(V_CODES)));
Replace with
LENGTH(Trim(V_CODES));
--------------
' take only 4chars
INSERT INTO ATION (ATION,CODES) VALUES(V_ATION, 'Left(str1, 4) & ", , , ,')"
Replace with
INSERT INTO ATION (ATION,CODES) VALUES(V_ATION, 'substr(rpad((str1,4,', '),1,4))"
-
Its not a very good idea to do conversion code-by-code basis. VB and PL/SQL are two very different programming language. And they both handle SQL very differently. I would suggest you tell us what you want to implement in your code in plain English and we could help you write PL/SQL code for that.
-
Maybe (underline used instead of space)
with
test_values as
(select '12345678' v_codes from dual union all
select '__12345_' from dual union all
select '__12_' from dual union all
select '1' from dual
)
select v_codes,
rpad(substr(trim(both '_' from v_codes),1,4),4,'_') the_need
from test_values
V_CODES THE_NEED 12345678 1234 __12345_ 1234 __12_ 12__ 1 1___ Regards
Etbin
-
Here is the entire logic, now help me convert this into PL SQL.
Set rSt1 = locdbs.OpenRecordset("SELECT ltrim(rtrim(CODES)),ATION FROM T_A_L", DB_OPEN_DYNASET) -- Can be dumped into a cursor
Debug.Print "AN" -- DBMS OUTPUT
Do Until rSt1.EOF -- WHILE CURSOR%FOUND LOOP
tmpstr = ""
count = 1
str1 = rSt1(0) -- FETCH OUTPUT OF CURSOR INTO A VARIABLE
If InStr(str1, ",") = 0 Then
k = Len(LTrim(RTrim(str1)))
If (k < 4) Then
' space for other chars
ln = "INSERT INTO ATION (ATION,CODES) VALUES('" & rSt1(1) & "', '" & Left(str1, 4) _
& Space(4 - k) & ", , , ,')"
Else
' take only 4chars
ln = "INSERT INTO ATION (ATION,CODES) VALUES('" & rSt1(1) & "', '" & Left(str1, 4) _
& ", , , ,')"
End If
Accessdb.Execute ln
'If "'" is present in the value then do the manipulation below
Else
' take the chars four by four and append into tmpstr
Do Until InStr(str1, ",") = 0
str2 = LTrim(RTrim(Left(Mid(str1, 1, (InStr(str1, ",") - 1)), 4)))
If Len(tmpstr) = 0 Then
If (Len(str2) = 3) Then
tmpstr = str2 & " "
ElseIf (Len(str2) = 2) Then
tmpstr = str2 & " "
Else
tmpstr = str2
End If
Else
If (Len(str2) = 3) Then
tmpstr = tmpstr & "," & str2 & " "
ElseIf (Len(str2) = 2) Then
tmpstr = tmpstr & "," & str2 & " "
Else
tmpstr = tmpstr & "," & str2
End If
End If
str3 = Mid(str1, (InStr(str1, ",") + 1), (Len(str1) - InStr(str1, ",")))
str1 = str3
count = count + 1
If InStr(str1, ",") = 0 Then
If (Len(LTrim(RTrim(str1))) = 2) Then
str3 = LTrim(RTrim(str1)) & " "
ElseIf (Len(LTrim(RTrim(str1))) = 3) Then
str3 = LTrim(RTrim(str1)) & " "
Else
str3 = str1
End If
str1 = str3
tmpstr = tmpstr & "," & Left(str1, 4) & ","
If count = 2 Then
tmpstr = tmpstr & " , ,"
ElseIf count = 3 Then
tmpstr = tmpstr & " ,"
End If
ln = "INSERT INTO ATION(ATION,CODES) VALUES('" _
& rSt1(1) & "', '" & tmpstr & "')"
Accessdb.Execute ln
End If
Loop
End If
rSt1.MoveNext
Loop
apquery = "SELECT DISTINCT (ATION) FROM ATION"Set ATION = Accessdb.OpenRecordset(apquery, DB_OPEN_DYNASET)
Do Until ATION.EOF
If (StrComp(prdid, "CAE") = 0) Then
apnotes = "SELECT NOT_F_NAME, NOT_NOTE_VALUE FROM T_N WHERE " _
& " NOT_I_NUMBER LIKE '" & ATION(0) & "%'" & " AND NOT_PRD_ID = 'ENI'"
ElseIf (StrComp(prdid, "CAS") = 0) Then
apnotes = "SELECT NOT_F_NAME, NOT_NOTE_VALUE FROM T_N WHERE " _
& " NOT_I_NUMBER LIKE '" & ATION(0) & "%'" & " AND NOT_PRD_ID = 'P1I'"
ElseIf (StrComp(prdid, "CAP") = 0) Then
apnotes = "SELECT NOT_F_NAME, NOT_NOTE_VALUE FROM T_N WHERE " _
& " NOT_I_NUMBER LIKE '" & ATION(0) & "%'" & " AND NOT_PRD_ID = 'P2I'"
Else
apnotes = "SELECT NOT_F_NAME, NOT_NOTE_VALUE FROM T_N WHERE " _
& " NOT_I_NUMBER LIKE '" & ATION(0) & "%'" & " AND NOT_PRD_ID = '" & prdid & "'"
End If
Set apnotevalues = OracleConn.OpenRecordset(apnotes, DB_OPEN_DYNASET)
Do Until apnotevalues.EOF
If (IsNull(apnotevalues(0)) = False And Mid(apnotevalues(0), 1, 1) <> " ") Then
If InStr(apnotevalues(0), "CODES") = 0 Then
la = ""If InStr(apnotevalues(0), "COMMENT") = 0 Then
la = "UPDATE ATION SET " & LTrim(RTrim(apnotevalues(0))) & " = '" & LTrim(RTrim(apnotevalues(1))) & "' " _
& " WHERE ATION = '" & Mid(ATION(0), 1, 7) & "'"
Else
la = "UPDATE ATION SET " & LTrim(RTrim(apnotevalues(0))) & " = '" & LTrim(RTrim(apnotevalues(1))) & "' " _
& " WHERE ATION = '" & Mid(ATION(0), 1, 7) & "'"
End IfAccessdb.Execute la
End If
End If
apnotevalues.MoveNext
Loop
apnotevalues.Close
ATION.MoveNext
Loop
ATION.Close -
This is VB code. First step would be to Reverse engineer it and transform it into a Design document.
-
This is not so intense Karthick, can you help me in converting the major functional parts of the above code. -
99ab9d4f-b078-43b9-8b7d-63f49b16f665 wrote:
This is not so intense Karthick, can you help me in converting the major functional parts of the above code.Karthick is right - don't just convert. I suspect you have more than just this VB code to convert.
You will end up with an Oracle system running VB - an epic fail project.
Find out the business requirements and get someone who knows Oracle to write correct, efficient code for Oracle.
-
99ab9d4f-b078-43b9-8b7d-63f49b16f665 wrote:
This is not so intense Karthick, can you help me in converting the major functional parts of the above code.Your entire code can be converted into single INSERT INTO .. SELECT statement. But there is lot of string manipulations are being done. Explain in plain English what its all about. To infer it myself from your VB code that too after having a good lunch, sorry cant do it
-
Not knowing VB I can only guess
with
t_a_l as
(select '12345678' codes from dual union all
select '__12345_,12,___123' from dual union all
select '__123,,12_' from dual union all
select '1,___,__123456,12' from dual union all
select '' from dual union all
select '1,12,123,1234,12345,123456' from dual
)
select codes,
rpad(substr(trim(both '_' from regexp_substr(codes,'[^,]+',1,level)),1,4),4,'_') the_code,
rpad(nvl(substr(trim(both '_' from regexp_substr(codes,'[^,]+',1,level)),1,4),'_'),4,'_') with_nulls
from t_a_l
connect by level <= regexp_count(codes,',') + 1
and prior codes = codes
and prior sys_guid() is not null
CODES THE_CODE WITH_NULLS 1,12,123,1234,12345,123456 1___ 1___ 1,12,123,1234,12345,123456 12__ 12__ 1,12,123,1234,12345,123456 123_ 123_ 1,12,123,1234,12345,123456 1234 1234 1,12,123,1234,12345,123456 1234 1234 1,12,123,1234,12345,123456 1234 1234 1,___,__123456,12 1___ 1___ 1,___,__123456,12 - ____ 1,___,__123456,12 1234 1234 1,___,__123456,12 12__ 12__ 12345678 1234 1234 __123,,12_ 123_ 123_ __123,,12_ 12__ 12__ __123,,12_ - ____ __12345_,12,___123 1234 1234 __12345_,12,___123 12__ 12__ __12345_,12,___123 123_ 123_ - - ____ Regards
Etbin