Back to Collection Items
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
-- This example assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @success int
-- Use this online tool to generate code from sample JSON: Generate Code to Create JSON
-- The following JSON is sent in the request body.
-- {
-- "definedTags": {},
-- "displayName": "est exercitation proident voluptate",
-- "freeformTags": {},
-- "privateIpId": "proident magna sunt elit"
-- }
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'UpdateNewObject', @success OUT, 'definedTags'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'displayName', 'est exercitation proident voluptate'
EXEC sp_OAMethod @json, 'UpdateNewObject', @success OUT, 'freeformTags'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'privateIpId', 'proident magna sunt elit'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'application/json'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'x-content-sha256', '{{content_sha256}}'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Authorization', '{{signature}}'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Date', '{{date}}'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'if-match', 'officia sed'
DECLARE @sbRequestBody int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbRequestBody OUT
EXEC sp_OAMethod @json, 'EmitSb', @success OUT, @sbRequestBody
DECLARE @resp int
EXEC sp_OAMethod @http, 'PTextSb', @resp OUT, 'PUT', 'https://iaas.{{region}}.oraclecloud.com/20160918/publicIps/:publicIpId', @sbRequestBody, 'utf-8', 'application/json', 0, 0
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbRequestBody
RETURN
END
DECLARE @sbResponseBody int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbResponseBody OUT
EXEC sp_OAMethod @resp, 'GetBodySb', @success OUT, @sbResponseBody
DECLARE @jResp int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @jResp OUT
EXEC sp_OAMethod @jResp, 'LoadSb', @success OUT, @sbResponseBody
EXEC sp_OASetProperty @jResp, 'EmitCompact', 0
PRINT 'Response Body:'
EXEC sp_OAMethod @jResp, 'Emit', @sTmp0 OUT
PRINT @sTmp0
DECLARE @respStatusCode int
EXEC sp_OAGetProperty @resp, 'StatusCode', @respStatusCode OUT
PRINT 'Response Status Code = ' + @respStatusCode
IF @respStatusCode >= 400
BEGIN
PRINT 'Response Header:'
EXEC sp_OAGetProperty @resp, 'Header', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbRequestBody
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jResp
RETURN
END
EXEC @hr = sp_OADestroy @resp
-- Sample JSON response:
-- (Sample code for parsing the JSON response is shown below)
-- {
-- "assignedEntityId": "eu reprehenderit aliquip nulla",
-- "assignedEntityType": "NAT_GATEWAY",
-- "availabilityDomain": "irure commodo",
-- "compartmentId": "est dolor proident",
-- "definedTags": {},
-- "displayName": "ipsum quis dolore",
-- "freeformTags": {},
-- "id": "sint commodo dolor culpa",
-- "ipAddress": "dolore quis esse aliqua",
-- "lifecycleState": "UNASSIGNED",
-- "lifetime": "EPHEMERAL",
-- "privateIpId": "non in consectetur laborum nostrud",
-- "publicIpPoolId": "quis aliquip anim",
-- "scope": "REGION",
-- "timeCreated": "1986-03-06T20:25:38.760Z"
-- }
-- Sample code for parsing the JSON response...
-- Use this online tool to generate parsing code from sample JSON: Generate JSON Parsing Code
DECLARE @assignedEntityId nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @assignedEntityId OUT, 'assignedEntityId'
DECLARE @assignedEntityType nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @assignedEntityType OUT, 'assignedEntityType'
DECLARE @availabilityDomain nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @availabilityDomain OUT, 'availabilityDomain'
DECLARE @compartmentId nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @compartmentId OUT, 'compartmentId'
DECLARE @displayName nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @displayName OUT, 'displayName'
DECLARE @id nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @id OUT, 'id'
DECLARE @ipAddress nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @ipAddress OUT, 'ipAddress'
DECLARE @lifecycleState nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @lifecycleState OUT, 'lifecycleState'
DECLARE @lifetime nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @lifetime OUT, 'lifetime'
DECLARE @privateIpId nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @privateIpId OUT, 'privateIpId'
DECLARE @publicIpPoolId nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @publicIpPoolId OUT, 'publicIpPoolId'
DECLARE @scope nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @scope OUT, 'scope'
DECLARE @timeCreated nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @timeCreated OUT, 'timeCreated'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbRequestBody
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jResp
END
GO
Curl Command
curl -X PUT
-H "if-match: officia sed"
-H "Content-Type: application/json"
-H "Date: {{date}}"
-H "Authorization: {{signature}}"
-H "x-content-sha256: {{content_sha256}}"
-d '{
"definedTags": {},
"displayName": "est exercitation proident voluptate",
"freeformTags": {},
"privateIpId": "proident magna sunt elit"
}'
https://iaas.{{region}}.oraclecloud.com/20160918/publicIps/:publicIpId
Postman Collection Item JSON
{
"name": "UpdatePublicIp",
"request": {
"method": "PUT",
"header": [
{
"description": "For optimistic concurrency control. In the PUT or DELETE call for a resource, set the `if-match`\nparameter to the value of the etag from a previous GET or POST response for that resource. The resource\nwill be updated or deleted only if the etag you provide matches the resource's current etag value.\n",
"key": "if-match",
"value": "officia sed"
},
{
"key": "Content-Type",
"value": "application/json"
},
{
"key": "Date",
"value": "{{date}}",
"description": "(Required) Current Date",
"type": "text"
},
{
"key": "Authorization",
"value": "{{signature}}",
"description": "(Required) Signature Authentication on Authorization header",
"type": "text"
},
{
"key": "x-content-sha256",
"value": "{{content_sha256}}",
"description": "(Required) Content sha256 for POST, PUT and PATCH operations",
"type": "text"
}
],
"body": {
"mode": "raw",
"raw": "{\n \"definedTags\": {},\n \"displayName\": \"est exercitation proident voluptate\",\n \"freeformTags\": {},\n \"privateIpId\": \"proident magna sunt elit\"\n}"
},
"url": {
"raw": "{{baseUrl}}/publicIps/:publicIpId",
"host": [
"{{baseUrl}}"
],
"path": [
"publicIps",
":publicIpId"
],
"variable": [
{
"key": "publicIpId",
"value": "amet ut",
"description": "(Required) The [OCID](/Content/General/Concepts/identifiers.htm) of the public IP."
}
]
},
"description": "Updates the specified public IP. You must specify the object's [OCID](/iaas/Content/General/Concepts/identifiers.htm). Use this operation if you want to:\n\n* Assign a reserved public IP in your pool to a private IP.\n* Move a reserved public IP to a different private IP.\n* Unassign a reserved public IP from a private IP (which returns it to your pool\nof reserved public IPs).\n* Change the display name or tags for a public IP.\n\nAssigning, moving, and unassigning a reserved public IP are asynchronous\noperations. Poll the public IP's `lifecycleState` to determine if the operation\nsucceeded.\n\n**Note:** When moving a reserved public IP, the target private IP\nmust not already have a public IP with `lifecycleState` = ASSIGNING or ASSIGNED. If it\ndoes, an error is returned. Also, the initial unassignment from the original\nprivate IP always succeeds, but the assignment to the target private IP is asynchronous and\ncould fail silently (for example, if the target private IP is deleted or has a different public IP\nassigned to it in the interim). If that occurs, the public IP remains unassigned and its\n`lifecycleState` switches to AVAILABLE (it is not reassigned to its original private IP).\nYou must poll the public IP's `lifecycleState` to determine if the move succeeded.\n\nRegarding ephemeral public IPs:\n\n* If you want to assign an ephemeral public IP to a primary private IP, use\n[CreatePublicIp](#/en/iaas/latest/PublicIp/CreatePublicIp).\n* You can't move an ephemeral public IP to a different private IP.\n* If you want to unassign an ephemeral public IP from its private IP, use\n[DeletePublicIp](#/en/iaas/latest/PublicIp/DeletePublicIp), which\nunassigns and deletes the ephemeral public IP.\n\n**Note:** If a public IP is assigned to a secondary private\nIP (see [PrivateIp](#/en/iaas/latest/PrivateIp)), and you move that secondary\nprivate IP to another VNIC, the public IP moves with it.\n\n**Note:** There's a limit to the number of [public IPs](#/en/iaas/latest/PublicIp/)\na VNIC or instance can have. If you try to move a reserved public IP\nto a VNIC or instance that has already reached its public IP limit, an error is\nreturned. For information about the public IP limits, see\n[Public IP Addresses](/iaas/Content/Network/Tasks/managingpublicIPs.htm).\n"
},
"response": [
{
"name": "The public IP was updated.",
"originalRequest": {
"method": "PUT",
"header": [
{
"description": "For optimistic concurrency control. In the PUT or DELETE call for a resource, set the `if-match`\nparameter to the value of the etag from a previous GET or POST response for that resource. The resource\nwill be updated or deleted only if the etag you provide matches the resource's current etag value.\n",
"key": "if-match",
"value": "officia sed"
}
],
"body": {
"mode": "raw",
"raw": "{\n \"definedTags\": {},\n \"displayName\": \"est exercitation proident voluptate\",\n \"freeformTags\": {},\n \"privateIpId\": \"proident magna sunt elit\"\n}"
},
"url": {
"raw": "{{baseUrl}}/publicIps/:publicIpId",
"host": [
"{{baseUrl}}"
],
"path": [
"publicIps",
":publicIpId"
],
"variable": [
{
"key": "publicIpId"
}
]
}
},
"status": "OK",
"code": 200,
"_postman_previewlanguage": "json",
"header": [
{
"key": "etag",
"value": "officia sed",
"description": "For optimistic concurrency control. See `if-match`.\n"
},
{
"key": "opc-request-id",
"value": "officia sed",
"description": "Unique Oracle-assigned identifier for the request. If you need to contact\nOracle about a particular request, please provide the request ID.\n"
},
{
"key": "Content-Type",
"value": "application/json"
}
],
"cookie": [
],
"body": "{\n \"assignedEntityId\": \"eu reprehenderit aliquip nulla\",\n \"assignedEntityType\": \"NAT_GATEWAY\",\n \"availabilityDomain\": \"irure commodo\",\n \"compartmentId\": \"est dolor proident\",\n \"definedTags\": {},\n \"displayName\": \"ipsum quis dolore\",\n \"freeformTags\": {},\n \"id\": \"sint commodo dolor culpa\",\n \"ipAddress\": \"dolore quis esse aliqua\",\n \"lifecycleState\": \"UNASSIGNED\",\n \"lifetime\": \"EPHEMERAL\",\n \"privateIpId\": \"non in consectetur laborum nostrud\",\n \"publicIpPoolId\": \"quis aliquip anim\",\n \"scope\": \"REGION\",\n \"timeCreated\": \"1986-03-06T20:25:38.760Z\"\n}"
},
{
"name": "Bad Request",
"originalRequest": {
"method": "PUT",
"header": [
{
"description": "For optimistic concurrency control. In the PUT or DELETE call for a resource, set the `if-match`\nparameter to the value of the etag from a previous GET or POST response for that resource. The resource\nwill be updated or deleted only if the etag you provide matches the resource's current etag value.\n",
"key": "if-match",
"value": "officia sed"
}
],
"body": {
"mode": "raw",
"raw": "{\n \"definedTags\": {},\n \"displayName\": \"est exercitation proident voluptate\",\n \"freeformTags\": {},\n \"privateIpId\": \"proident magna sunt elit\"\n}"
},
"url": {
"raw": "{{baseUrl}}/publicIps/:publicIpId",
"host": [
"{{baseUrl}}"
],
"path": [
"publicIps",
":publicIpId"
],
"variable": [
{
"key": "publicIpId"
}
]
}
},
"status": "Bad Request",
"code": 400,
"_postman_previewlanguage": "json",
"header": [
{
"key": "opc-request-id",
"value": "officia sed",
"description": "Unique Oracle-assigned identifier for the request. If you need to contact\nOracle about a particular request, please provide the request ID.\n"
},
{
"key": "Content-Type",
"value": "application/json"
}
],
"cookie": [
],
"body": "{\n \"code\": \"et exercitation Excepteur\",\n \"message\": \"irure sit\"\n}"
},
{
"name": "Unauthorized",
"originalRequest": {
"method": "PUT",
"header": [
{
"description": "For optimistic concurrency control. In the PUT or DELETE call for a resource, set the `if-match`\nparameter to the value of the etag from a previous GET or POST response for that resource. The resource\nwill be updated or deleted only if the etag you provide matches the resource's current etag value.\n",
"key": "if-match",
"value": "officia sed"
}
],
"body": {
"mode": "raw",
"raw": "{\n \"definedTags\": {},\n \"displayName\": \"est exercitation proident voluptate\",\n \"freeformTags\": {},\n \"privateIpId\": \"proident magna sunt elit\"\n}"
},
"url": {
"raw": "{{baseUrl}}/publicIps/:publicIpId",
"host": [
"{{baseUrl}}"
],
"path": [
"publicIps",
":publicIpId"
],
"variable": [
{
"key": "publicIpId"
}
]
}
},
"status": "Unauthorized",
"code": 401,
"_postman_previewlanguage": "json",
"header": [
{
"key": "opc-request-id",
"value": "officia sed",
"description": "Unique Oracle-assigned identifier for the request. If you need to contact\nOracle about a particular request, please provide the request ID.\n"
},
{
"key": "Content-Type",
"value": "application/json"
}
],
"cookie": [
],
"body": "{\n \"code\": \"et exercitation Excepteur\",\n \"message\": \"irure sit\"\n}"
},
{
"name": "Not Found",
"originalRequest": {
"method": "PUT",
"header": [
{
"description": "For optimistic concurrency control. In the PUT or DELETE call for a resource, set the `if-match`\nparameter to the value of the etag from a previous GET or POST response for that resource. The resource\nwill be updated or deleted only if the etag you provide matches the resource's current etag value.\n",
"key": "if-match",
"value": "officia sed"
}
],
"body": {
"mode": "raw",
"raw": "{\n \"definedTags\": {},\n \"displayName\": \"est exercitation proident voluptate\",\n \"freeformTags\": {},\n \"privateIpId\": \"proident magna sunt elit\"\n}"
},
"url": {
"raw": "{{baseUrl}}/publicIps/:publicIpId",
"host": [
"{{baseUrl}}"
],
"path": [
"publicIps",
":publicIpId"
],
"variable": [
{
"key": "publicIpId"
}
]
}
},
"status": "Not Found",
"code": 404,
"_postman_previewlanguage": "json",
"header": [
{
"key": "opc-request-id",
"value": "officia sed",
"description": "Unique Oracle-assigned identifier for the request. If you need to contact\nOracle about a particular request, please provide the request ID.\n"
},
{
"key": "Content-Type",
"value": "application/json"
}
],
"cookie": [
],
"body": "{\n \"code\": \"et exercitation Excepteur\",\n \"message\": \"irure sit\"\n}"
},
{
"name": "Precondition Failed",
"originalRequest": {
"method": "PUT",
"header": [
{
"description": "For optimistic concurrency control. In the PUT or DELETE call for a resource, set the `if-match`\nparameter to the value of the etag from a previous GET or POST response for that resource. The resource\nwill be updated or deleted only if the etag you provide matches the resource's current etag value.\n",
"key": "if-match",
"value": "officia sed"
}
],
"body": {
"mode": "raw",
"raw": "{\n \"definedTags\": {},\n \"displayName\": \"est exercitation proident voluptate\",\n \"freeformTags\": {},\n \"privateIpId\": \"proident magna sunt elit\"\n}"
},
"url": {
"raw": "{{baseUrl}}/publicIps/:publicIpId",
"host": [
"{{baseUrl}}"
],
"path": [
"publicIps",
":publicIpId"
],
"variable": [
{
"key": "publicIpId"
}
]
}
},
"status": "Precondition Failed",
"code": 412,
"_postman_previewlanguage": "json",
"header": [
{
"key": "opc-request-id",
"value": "officia sed",
"description": "Unique Oracle-assigned identifier for the request. If you need to contact\nOracle about a particular request, please provide the request ID.\n"
},
{
"key": "Content-Type",
"value": "application/json"
}
],
"cookie": [
],
"body": "{\n \"code\": \"et exercitation Excepteur\",\n \"message\": \"irure sit\"\n}"
},
{
"name": "Internal Server Error",
"originalRequest": {
"method": "PUT",
"header": [
{
"description": "For optimistic concurrency control. In the PUT or DELETE call for a resource, set the `if-match`\nparameter to the value of the etag from a previous GET or POST response for that resource. The resource\nwill be updated or deleted only if the etag you provide matches the resource's current etag value.\n",
"key": "if-match",
"value": "officia sed"
}
],
"body": {
"mode": "raw",
"raw": "{\n \"definedTags\": {},\n \"displayName\": \"est exercitation proident voluptate\",\n \"freeformTags\": {},\n \"privateIpId\": \"proident magna sunt elit\"\n}"
},
"url": {
"raw": "{{baseUrl}}/publicIps/:publicIpId",
"host": [
"{{baseUrl}}"
],
"path": [
"publicIps",
":publicIpId"
],
"variable": [
{
"key": "publicIpId"
}
]
}
},
"status": "Internal Server Error",
"code": 500,
"_postman_previewlanguage": "json",
"header": [
{
"key": "opc-request-id",
"value": "officia sed",
"description": "Unique Oracle-assigned identifier for the request. If you need to contact\nOracle about a particular request, please provide the request ID.\n"
},
{
"key": "Content-Type",
"value": "application/json"
}
],
"cookie": [
],
"body": "{\n \"code\": \"et exercitation Excepteur\",\n \"message\": \"irure sit\"\n}"
},
{
"name": "An error has occurred.",
"originalRequest": {
"method": "PUT",
"header": [
{
"description": "For optimistic concurrency control. In the PUT or DELETE call for a resource, set the `if-match`\nparameter to the value of the etag from a previous GET or POST response for that resource. The resource\nwill be updated or deleted only if the etag you provide matches the resource's current etag value.\n",
"key": "if-match",
"value": "officia sed"
}
],
"body": {
"mode": "raw",
"raw": "{\n \"definedTags\": {},\n \"displayName\": \"est exercitation proident voluptate\",\n \"freeformTags\": {},\n \"privateIpId\": \"proident magna sunt elit\"\n}"
},
"url": {
"raw": "{{baseUrl}}/publicIps/:publicIpId",
"host": [
"{{baseUrl}}"
],
"path": [
"publicIps",
":publicIpId"
],
"variable": [
{
"key": "publicIpId"
}
]
}
},
"status": "Internal Server Error",
"code": 500,
"_postman_previewlanguage": "json",
"header": [
{
"key": "opc-request-id",
"value": "officia sed",
"description": "Unique Oracle-assigned identifier for the request. If you need to contact\nOracle about a particular request, please provide the request ID.\n"
},
{
"key": "Content-Type",
"value": "application/json"
}
],
"cookie": [
],
"body": "{\n \"code\": \"et exercitation Excepteur\",\n \"message\": \"irure sit\"\n}"
}
]
}
