I have a functionality in vb6.0 application to execute stored procedures into oracle database. when doing so, The TABS present in the stored procedures for indentation gets removed and a single SPACE is being applied.
Oracle DB: 19c Connection Type: ODBC
VB6.0 Code:
Private Sub Command1_Click()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strSql As String
Set conn = New ADODB.Connection
Set cmd = New ADODB.Command
conn.ConnectionString = "Data Source=MyDataBase; User ID=username; Password=password;"
conn.Open
If conn.State = adStateOpen Then
Set cmd.ActiveConnection = conn
'cmd.CommandType = adCmdText
strSql = RichTextBox1.Text
cmd.CommandText = strSql
cmd.Execute
'
End If
conn.Close
Set cmd = Nothing
Set conn = Nothing
End
End Sub
My Sample script:
CREATE OR REPLACE PROCEDURE A_SELECT_PATIENTS
IS
v_value VARCHAR2(100);
BEGIN
SELECT DISTINCT
PAT.SURNAME
INTO v_value
FROM
PATIENTS PAT
WHERE
PAT.PATNT_REFNO = 73917;
DBMS_OUTPUT.PUT_LINE('Surname: ' || v_value);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found.');
END;
script when viewed through sql developer
create or replace NONEDITIONABLE PROCEDURE A_SELECT_PATIENTS
IS
v_value VARCHAR2(100);
BEGIN
SELECT DISTINCT
PAT.SURNAME
INTO v_value
FROM
PATIENTS PAT
WHERE
PAT.PATNT_REFNO = 73917;
DBMS_OUTPUT.PUT_LINE('Surname: ' || v_value);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found.');
END;
I would be very thankful if anyone share a solution to preserve the Tab indentation from the script when looking from sql developer at the time of executing script through the code execution itself.
Note: it would be good it the solution is provided without adding any Provider into the above given connection string