Script stores procedure creating in single line [duplicate]
up vote
0
down vote
favorite
This question already has an answer here:
How to enter newline character in Oracle?
2 answers
I have written VBA code in excel to create a SP in oracle and it working fine.
But when I opened SP in oracle it showing entire code in single line. I have tried known ways to give line breaks but nothing works.
My actual VB code:
If sp_exist_chk_flag > 0 Then
Else
cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & Chr(13) & _
"As" & Chr(13) & _
"REP DATA_REP.REP_ID%TYPE;" & Chr(13) & _
"SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & Chr(13) & _
"COMMIT;" & Chr(13) & _
"END;"
cmd_meta.Execute
MsgBox "SP Created"
End If
In Oracle I can see SP is created as below
create or replace PROCEDURE TEST_SP As REP DATA_REP.REP_ID%TYPE; SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz'; COMMIT; END;
My actual SP is too long, so my vb code making it very long line.
Please help me on this with VB code or oracle command to look like an actual SP with proper line breaks in oracle.
excel vba oracle excel-vba stored-procedures
edited Nov 20 at 13:01
Pᴇʜ
20.1k42650
asked Nov 20 at 12:52
Scales
82
marked as duplicate by Pᴇʜ, Comintern vba
Users with the vba badge can single-handedly close vba questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 21 at 1:52
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
up vote
0
down vote
favorite
This question already has an answer here:
How to enter newline character in Oracle?
2 answers
I have written VBA code in excel to create a SP in oracle and it working fine.
But when I opened SP in oracle it showing entire code in single line. I have tried known ways to give line breaks but nothing works.
My actual VB code:
If sp_exist_chk_flag > 0 Then
Else
cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & Chr(13) & _
"As" & Chr(13) & _
"REP DATA_REP.REP_ID%TYPE;" & Chr(13) & _
"SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & Chr(13) & _
"COMMIT;" & Chr(13) & _
"END;"
cmd_meta.Execute
MsgBox "SP Created"
End If
In Oracle I can see SP is created as below
create or replace PROCEDURE TEST_SP As REP DATA_REP.REP_ID%TYPE; SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz'; COMMIT; END;
My actual SP is too long, so my vb code making it very long line.
Please help me on this with VB code or oracle command to look like an actual SP with proper line breaks in oracle.
excel vba oracle excel-vba stored-procedures
edited Nov 20 at 13:01
Pᴇʜ
20.1k42650
asked Nov 20 at 12:52
Scales
82
marked as duplicate by Pᴇʜ, Comintern vba
Users with the vba badge can single-handedly close vba questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 21 at 1:52
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
@ Peh - I clearly mentioned I am using excel and it is a VBA code
– Scales
Nov 20 at 13:06
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
This question already has an answer here:
How to enter newline character in Oracle?
2 answers
I have written VBA code in excel to create a SP in oracle and it working fine.
But when I opened SP in oracle it showing entire code in single line. I have tried known ways to give line breaks but nothing works.
My actual VB code:
If sp_exist_chk_flag > 0 Then
Else
cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & Chr(13) & _
"As" & Chr(13) & _
"REP DATA_REP.REP_ID%TYPE;" & Chr(13) & _
"SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & Chr(13) & _
"COMMIT;" & Chr(13) & _
"END;"
cmd_meta.Execute
MsgBox "SP Created"
End If
In Oracle I can see SP is created as below
create or replace PROCEDURE TEST_SP As REP DATA_REP.REP_ID%TYPE; SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz'; COMMIT; END;
My actual SP is too long, so my vb code making it very long line.
Please help me on this with VB code or oracle command to look like an actual SP with proper line breaks in oracle.
excel vba oracle excel-vba stored-procedures
edited Nov 20 at 13:01
Pᴇʜ
20.1k42650
asked Nov 20 at 12:52
Scales
82
This question already has an answer here:
How to enter newline character in Oracle?
2 answers
I have written VBA code in excel to create a SP in oracle and it working fine.
But when I opened SP in oracle it showing entire code in single line. I have tried known ways to give line breaks but nothing works.
My actual VB code:
If sp_exist_chk_flag > 0 Then
Else
cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & Chr(13) & _
"As" & Chr(13) & _
"REP DATA_REP.REP_ID%TYPE;" & Chr(13) & _
"SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & Chr(13) & _
"COMMIT;" & Chr(13) & _
"END;"
cmd_meta.Execute
MsgBox "SP Created"
End If
In Oracle I can see SP is created as below
create or replace PROCEDURE TEST_SP As REP DATA_REP.REP_ID%TYPE; SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz'; COMMIT; END;
My actual SP is too long, so my vb code making it very long line.
Please help me on this with VB code or oracle command to look like an actual SP with proper line breaks in oracle.
This question already has an answer here:
How to enter newline character in Oracle?
2 answers
excel vba oracle excel-vba stored-procedures
excel vba oracle excel-vba stored-procedures
edited Nov 20 at 13:01
Pᴇʜ
20.1k42650
asked Nov 20 at 12:52
Scales
82
edited Nov 20 at 13:01
Pᴇʜ
20.1k42650
asked Nov 20 at 12:52
Scales
82
edited Nov 20 at 13:01
Pᴇʜ
20.1k42650
edited Nov 20 at 13:01
Pᴇʜ
20.1k42650
edited Nov 20 at 13:01
Pᴇʜ
20.1k42650
20.1k42650
asked Nov 20 at 12:52
Scales
82
asked Nov 20 at 12:52
Scales
82
asked Nov 20 at 12:52
Scales
82
82
marked as duplicate by Pᴇʜ, Comintern vba
Users with the vba badge can single-handedly close vba questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 21 at 1:52
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Pᴇʜ, Comintern vba
Users with the vba badge can single-handedly close vba questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 21 at 1:52
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
@ Peh - I clearly mentioned I am using excel and it is a VBA code
– Scales
Nov 20 at 13:06
add a comment |
@ Peh - I clearly mentioned I am using excel and it is a VBA code
– Scales
Nov 20 at 13:06
@ Peh - I clearly mentioned I am using excel and it is a VBA code
– Scales
Nov 20 at 13:06
@ Peh - I clearly mentioned I am using excel and it is a VBA code
– Scales
Nov 20 at 13:06
add a comment |
1 Answer
1
active
oldest
votes
up vote
2
down vote
accepted
Not sure if it will make a difference, but try using the vbCrLf constant instead of Chr(13).
If sp_exist_chk_flag > 0 Then
Else
cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & vbCrLf & _
"As" & vbCrLf & _
"REP DATA_REP.REP_ID%TYPE;" & vbCrLf & _
"SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & vbCrLf & _
"COMMIT;" & vbCrLf & _
"END;"
cmd_meta.Execute
MsgBox "SP Created"
End If
answered Nov 20 at 12:57
dcp
42.5k16119145
If LinuxUnix is used tryChr(10)which is avbLfinstead ofChr(13).
– Pᴇʜ
Nov 20 at 12:59
And it not thatChr(10)
– Tom
Nov 20 at 12:59
@dcp - Thanks it working :)
– Scales
Nov 20 at 13:05
@Scales - That's great! Please be sure to accept the answer if it solved your issue.
– dcp
Nov 20 at 13:12
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
Not sure if it will make a difference, but try using the vbCrLf constant instead of Chr(13).
If sp_exist_chk_flag > 0 Then
Else
cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & vbCrLf & _
"As" & vbCrLf & _
"REP DATA_REP.REP_ID%TYPE;" & vbCrLf & _
"SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & vbCrLf & _
"COMMIT;" & vbCrLf & _
"END;"
cmd_meta.Execute
MsgBox "SP Created"
End If
answered Nov 20 at 12:57
dcp
42.5k16119145
If LinuxUnix is used tryChr(10)which is avbLfinstead ofChr(13).
– Pᴇʜ
Nov 20 at 12:59
And it not thatChr(10)
– Tom
Nov 20 at 12:59
@dcp - Thanks it working :)
– Scales
Nov 20 at 13:05
@Scales - That's great! Please be sure to accept the answer if it solved your issue.
– dcp
Nov 20 at 13:12
add a comment |
up vote
2
down vote
accepted
Not sure if it will make a difference, but try using the vbCrLf constant instead of Chr(13).
If sp_exist_chk_flag > 0 Then
Else
cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & vbCrLf & _
"As" & vbCrLf & _
"REP DATA_REP.REP_ID%TYPE;" & vbCrLf & _
"SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & vbCrLf & _
"COMMIT;" & vbCrLf & _
"END;"
cmd_meta.Execute
MsgBox "SP Created"
End If
answered Nov 20 at 12:57
dcp
42.5k16119145
If LinuxUnix is used tryChr(10)which is avbLfinstead ofChr(13).
– Pᴇʜ
Nov 20 at 12:59
And it not thatChr(10)
– Tom
Nov 20 at 12:59
@dcp - Thanks it working :)
– Scales
Nov 20 at 13:05
@Scales - That's great! Please be sure to accept the answer if it solved your issue.
– dcp
Nov 20 at 13:12
add a comment |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
Not sure if it will make a difference, but try using the vbCrLf constant instead of Chr(13).
If sp_exist_chk_flag > 0 Then
Else
cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & vbCrLf & _
"As" & vbCrLf & _
"REP DATA_REP.REP_ID%TYPE;" & vbCrLf & _
"SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & vbCrLf & _
"COMMIT;" & vbCrLf & _
"END;"
cmd_meta.Execute
MsgBox "SP Created"
End If
answered Nov 20 at 12:57
dcp
42.5k16119145
Not sure if it will make a difference, but try using the vbCrLf constant instead of Chr(13).
If sp_exist_chk_flag > 0 Then
Else
cmd_meta.CommandText = "create or replace PROCEDURE TEST_SP " & vbCrLf & _
"As" & vbCrLf & _
"REP DATA_REP.REP_ID%TYPE;" & vbCrLf & _
"SELECT REP_ID INTO REP FROM DATA_REP WHERE REP_NAME='xyz';" & vbCrLf & _
"COMMIT;" & vbCrLf & _
"END;"
cmd_meta.Execute
MsgBox "SP Created"
End If
answered Nov 20 at 12:57
dcp
42.5k16119145
answered Nov 20 at 12:57
dcp
42.5k16119145
answered Nov 20 at 12:57
dcp
42.5k16119145
answered Nov 20 at 12:57
dcp
42.5k16119145
42.5k16119145
If LinuxUnix is used tryChr(10)which is avbLfinstead ofChr(13).
– Pᴇʜ
Nov 20 at 12:59
And it not thatChr(10)
– Tom
Nov 20 at 12:59
@dcp - Thanks it working :)
– Scales
Nov 20 at 13:05
@Scales - That's great! Please be sure to accept the answer if it solved your issue.
– dcp
Nov 20 at 13:12
add a comment |
If LinuxUnix is used tryChr(10)which is avbLfinstead ofChr(13).
– Pᴇʜ
Nov 20 at 12:59
And it not thatChr(10)
– Tom
Nov 20 at 12:59
@dcp - Thanks it working :)
– Scales
Nov 20 at 13:05
@Scales - That's great! Please be sure to accept the answer if it solved your issue.
– dcp
Nov 20 at 13:12
If LinuxUnix is used try
Chr(10) which is a vbLf instead of Chr(13).– Pᴇʜ
Nov 20 at 12:59
If LinuxUnix is used try
Chr(10) which is a vbLf instead of Chr(13).– Pᴇʜ
Nov 20 at 12:59
And it not that
Chr(10)– Tom
Nov 20 at 12:59
And it not that
Chr(10)– Tom
Nov 20 at 12:59
@dcp - Thanks it working :)
– Scales
Nov 20 at 13:05
@dcp - Thanks it working :)
– Scales
Nov 20 at 13:05
@Scales - That's great! Please be sure to accept the answer if it solved your issue.
– dcp
Nov 20 at 13:12
@Scales - That's great! Please be sure to accept the answer if it solved your issue.
– dcp
Nov 20 at 13:12
add a comment |
@ Peh - I clearly mentioned I am using excel and it is a VBA code
– Scales
Nov 20 at 13:06